mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-11 07:42:54 +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>
50 lines
1.3 KiB
Plaintext
50 lines
1.3 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/window/test_nthvalue.test
|
|
-- Tests NTH_VALUE, FIRST_VALUE, LAST_VALUE window functions
|
|
CREATE TABLE test_data(i INTEGER, v VARCHAR, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO test_data VALUES
|
|
(1, 'A', 1000),
|
|
(2, 'B', 2000),
|
|
(3, 'C', 3000),
|
|
(4, 'D', 4000),
|
|
(5, 'E', 5000);
|
|
|
|
Affected Rows: 5
|
|
|
|
-- NTH_VALUE function
|
|
SELECT i, v, NTH_VALUE(v, 2) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as nth_val
|
|
FROM test_data ORDER BY i;
|
|
|
|
+---+---+---------+
|
|
| i | v | nth_val |
|
|
+---+---+---------+
|
|
| 1 | A | |
|
|
| 2 | B | B |
|
|
| 3 | C | B |
|
|
| 4 | D | B |
|
|
| 5 | E | B |
|
|
+---+---+---------+
|
|
|
|
-- FIRST_VALUE and LAST_VALUE
|
|
SELECT i, v,
|
|
FIRST_VALUE(v) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as first_val,
|
|
LAST_VALUE(v) OVER (ORDER BY i ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as last_val
|
|
FROM test_data ORDER BY i;
|
|
|
|
+---+---+-----------+----------+
|
|
| i | v | first_val | last_val |
|
|
+---+---+-----------+----------+
|
|
| 1 | A | A | A |
|
|
| 2 | B | A | B |
|
|
| 3 | C | A | C |
|
|
| 4 | D | A | D |
|
|
| 5 | E | A | E |
|
|
+---+---+-----------+----------+
|
|
|
|
DROP TABLE test_data;
|
|
|
|
Affected Rows: 0
|
|
|