1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
# name: test/sql/window/test_range_optimisation.test
# description: Range search optimisation stress tests
# group: [window]
statement ok
CREATE TABLE rides (
id INTEGER,
requested_date DATE,
city VARCHAR,
wait_time INTEGER
);
statement ok
INSERT INTO rides VALUES
(0, '2023-01-05', 'San Francisco', 2925),
(1, '2023-01-03', 'San Francisco', 755),
(2, '2023-01-03', 'San Francisco', 2880),
(3, '2023-01-05', 'San Francisco', 1502),
(4, '2023-01-03', 'San Francisco', 2900),
(5, '2023-01-01', 'San Francisco', 1210),
(6, '2023-01-04', 'San Francisco', 200),
(7, '2023-01-02', 'San Francisco', 980),
(8, '2023-01-02', 'San Francisco', 430),
(9, '2023-01-05', 'San Francisco', 2999),
(10, '2023-01-01', 'San Francisco', 856),
(11, '2023-01-02', 'San Francisco', 490),
(12, '2023-01-02', 'San Francisco', 720),
query IIIII
SELECT "id", "requested_date", "city", "wait_time", min("wait_time") OVER win_3d
FROM rides
WINDOW win_3d AS (
PARTITION BY "city"
ORDER BY requested_date ASC
RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 1 DAYS PRECEDING)
ORDER BY "requested_date", "city", "id"
----
5 2023-01-01 San Francisco 1210 NULL
10 2023-01-01 San Francisco 856 NULL
7 2023-01-02 San Francisco 980 856
8 2023-01-02 San Francisco 430 856
11 2023-01-02 San Francisco 490 856
12 2023-01-02 San Francisco 720 856
1 2023-01-03 San Francisco 755 430
2 2023-01-03 San Francisco 2880 430
4 2023-01-03 San Francisco 2900 430
6 2023-01-04 San Francisco 200 430
0 2023-01-05 San Francisco 2925 200
3 2023-01-05 San Francisco 1502 200
9 2023-01-05 San Francisco 2999 200
|
