mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-16 13:00:40 +00:00
9dbf6dd8d0
* test: migrate window tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: blank line at the end Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com>
80 lines
2.5 KiB
Plaintext
80 lines
2.5 KiB
Plaintext
-- Migrated from DuckDB test: Multiple window tests with NULL handling
|
|
-- Tests window functions with NULL values
|
|
CREATE TABLE null_test("id" INTEGER, val INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO null_test VALUES
|
|
(1, 100, 1000), (2, NULL, 2000), (3, 300, 3000), (4, NULL, 4000), (5, 500, 5000);
|
|
|
|
Affected Rows: 5
|
|
|
|
-- Window functions with NULL values
|
|
SELECT "id", val,
|
|
ROW_NUMBER() OVER (ORDER BY val NULLS LAST) as row_num,
|
|
RANK() OVER (ORDER BY val NULLS LAST) as rank_val
|
|
FROM null_test ORDER BY "id";
|
|
|
|
+----+-----+---------+----------+
|
|
| id | val | row_num | rank_val |
|
|
+----+-----+---------+----------+
|
|
| 1 | 100 | 1 | 1 |
|
|
| 2 | | 4 | 4 |
|
|
| 3 | 300 | 2 | 2 |
|
|
| 4 | | 5 | 4 |
|
|
| 5 | 500 | 3 | 3 |
|
|
+----+-----+---------+----------+
|
|
|
|
-- LEAD/LAG with NULL values
|
|
SELECT "id", val,
|
|
LAG(val, 1) OVER (ORDER BY "id") as prev_val,
|
|
LEAD(val, 1) OVER (ORDER BY "id") as next_val
|
|
FROM null_test ORDER BY "id";
|
|
|
|
+----+-----+----------+----------+
|
|
| id | val | prev_val | next_val |
|
|
+----+-----+----------+----------+
|
|
| 1 | 100 | | |
|
|
| 2 | | 100 | 300 |
|
|
| 3 | 300 | | |
|
|
| 4 | | 300 | 500 |
|
|
| 5 | 500 | | |
|
|
+----+-----+----------+----------+
|
|
|
|
-- Aggregate window functions with NULL
|
|
SELECT "id", val,
|
|
SUM(val) OVER (ORDER BY "id" ROWS UNBOUNDED PRECEDING) as running_sum,
|
|
COUNT(val) OVER (ORDER BY "id" ROWS UNBOUNDED PRECEDING) as running_count
|
|
FROM null_test ORDER BY "id";
|
|
|
|
+----+-----+-------------+---------------+
|
|
| id | val | running_sum | running_count |
|
|
+----+-----+-------------+---------------+
|
|
| 1 | 100 | 100 | 1 |
|
|
| 2 | | 100 | 1 |
|
|
| 3 | 300 | 400 | 2 |
|
|
| 4 | | 400 | 2 |
|
|
| 5 | 500 | 900 | 3 |
|
|
+----+-----+-------------+---------------+
|
|
|
|
-- FIRST_VALUE/LAST_VALUE with NULL
|
|
SELECT "id", val,
|
|
FIRST_VALUE(val) OVER (ORDER BY "id" ROWS UNBOUNDED PRECEDING) as first_val,
|
|
LAST_VALUE(val) OVER (ORDER BY "id" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as last_val
|
|
FROM null_test ORDER BY "id";
|
|
|
|
+----+-----+-----------+----------+
|
|
| id | val | first_val | last_val |
|
|
+----+-----+-----------+----------+
|
|
| 1 | 100 | 100 | 100 |
|
|
| 2 | | 100 | |
|
|
| 3 | 300 | 100 | 300 |
|
|
| 4 | | 100 | |
|
|
| 5 | 500 | 100 | 500 |
|
|
+----+-----+-----------+----------+
|
|
|
|
DROP TABLE null_test;
|
|
|
|
Affected Rows: 0
|
|
|