mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-16 04:50:38 +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>
53 lines
1.1 KiB
Plaintext
53 lines
1.1 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/window/test_rank.test
|
|
CREATE TABLE test_data(i INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO test_data VALUES (1, 1000), (1, 2000), (2, 3000), (2, 4000), (3, 5000);
|
|
|
|
Affected Rows: 5
|
|
|
|
-- RANK function with ties
|
|
SELECT i, RANK() OVER (ORDER BY i) as rank_val FROM test_data ORDER BY ts;
|
|
|
|
+---+----------+
|
|
| i | rank_val |
|
|
+---+----------+
|
|
| 1 | 1 |
|
|
| 1 | 1 |
|
|
| 2 | 3 |
|
|
| 2 | 3 |
|
|
| 3 | 5 |
|
|
+---+----------+
|
|
|
|
-- DENSE_RANK function
|
|
SELECT i, DENSE_RANK() OVER (ORDER BY i) as dense_rank_val FROM test_data ORDER BY ts;
|
|
|
|
+---+----------------+
|
|
| i | dense_rank_val |
|
|
+---+----------------+
|
|
| 1 | 1 |
|
|
| 1 | 1 |
|
|
| 2 | 2 |
|
|
| 2 | 2 |
|
|
| 3 | 3 |
|
|
+---+----------------+
|
|
|
|
-- ROW_NUMBER function
|
|
SELECT i, ROW_NUMBER() OVER (ORDER BY i) as row_num FROM test_data ORDER BY ts;
|
|
|
|
+---+---------+
|
|
| i | row_num |
|
|
+---+---------+
|
|
| 1 | 1 |
|
|
| 1 | 2 |
|
|
| 2 | 3 |
|
|
| 2 | 4 |
|
|
| 3 | 5 |
|
|
+---+---------+
|
|
|
|
DROP TABLE test_data;
|
|
|
|
Affected Rows: 0
|
|
|