mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-07 05:42:57 +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>
92 lines
2.2 KiB
Plaintext
92 lines
2.2 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/window/test_lead_lag.test
|
|
CREATE TABLE win("id" INTEGER, v INTEGER, t INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO win VALUES
|
|
(1, 1, 2, 1000),
|
|
(1, 1, 1, 2000),
|
|
(1, 2, 3, 3000),
|
|
(2, 10, 4, 4000),
|
|
(2, 11, -1, 5000);
|
|
|
|
Affected Rows: 5
|
|
|
|
-- LAG function with offset 2
|
|
SELECT "id", v, t, LAG(v, 2, NULL) OVER (PARTITION BY "id" ORDER BY t ASC) as lag_val
|
|
FROM win ORDER BY "id", t;
|
|
|
|
+----+----+----+---------+
|
|
| id | v | t | lag_val |
|
|
+----+----+----+---------+
|
|
| 1 | 1 | 1 | |
|
|
| 1 | 1 | 2 | |
|
|
| 1 | 2 | 3 | 1 |
|
|
| 2 | 11 | -1 | |
|
|
| 2 | 10 | 4 | |
|
|
+----+----+----+---------+
|
|
|
|
-- LEAD function with offset 1
|
|
SELECT "id", v, t, LEAD(v, 1, -999) OVER (PARTITION BY "id" ORDER BY t ASC) as lead_val
|
|
FROM win ORDER BY "id", t;
|
|
|
|
+----+----+----+----------+
|
|
| id | v | t | lead_val |
|
|
+----+----+----+----------+
|
|
| 1 | 1 | 1 | 1 |
|
|
| 1 | 1 | 2 | 2 |
|
|
| 1 | 2 | 3 | -999 |
|
|
| 2 | 11 | -1 | 10 |
|
|
| 2 | 10 | 4 | -999 |
|
|
+----+----+----+----------+
|
|
|
|
-- LAG with default value
|
|
SELECT v, LAG(v, 1, 0) OVER (ORDER BY t) as lag_with_default FROM win ORDER BY t;
|
|
|
|
+----+------------------+
|
|
| v | lag_with_default |
|
|
+----+------------------+
|
|
| 11 | 0 |
|
|
| 1 | 11 |
|
|
| 1 | 1 |
|
|
| 2 | 1 |
|
|
| 10 | 2 |
|
|
+----+------------------+
|
|
|
|
-- LEAD with offset 2 and default value
|
|
SELECT "id", v, t, LEAD(v, 2, -999) OVER (PARTITION BY "id" ORDER BY t ASC) as lead_val2
|
|
FROM win ORDER BY "id", t;
|
|
|
|
+----+----+----+-----------+
|
|
| id | v | t | lead_val2 |
|
|
+----+----+----+-----------+
|
|
| 1 | 1 | 1 | 2 |
|
|
| 1 | 1 | 2 | -999 |
|
|
| 1 | 2 | 3 | -999 |
|
|
| 2 | 11 | -1 | -999 |
|
|
| 2 | 10 | 4 | -999 |
|
|
+----+----+----+-----------+
|
|
|
|
DROP TABLE win;
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Test with VALUES clause (similar to DuckDB original)
|
|
SELECT c1, LEAD(c1, 2) OVER (ORDER BY c0) as lead_val
|
|
FROM (VALUES
|
|
(1, 2, 1000),
|
|
(2, 3, 2000),
|
|
(3, 4, 3000),
|
|
(4, 5, 4000)
|
|
) a(c0, c1, ts) ORDER BY c0;
|
|
|
|
+----+----------+
|
|
| c1 | lead_val |
|
|
+----+----------+
|
|
| 2 | 4 |
|
|
| 3 | 5 |
|
|
| 4 | |
|
|
| 5 | |
|
|
+----+----------+
|
|
|