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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
# name: test/sql/window/test_streaming_window_distinct.test
# description: Test streaming window support for DISTINCT (+FILTER)
# group: [window]
statement ok
PRAGMA enable_verification
statement ok
PRAGMA explain_output = PHYSICAL_ONLY;
# DISTINCT only
query TT
explain
SELECT i,
SUM(DISTINCT i % 3) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM range(10) tbl(i)
----
physical_plan <REGEX>:.*STREAMING_WINDOW.*
query II
SELECT i,
SUM(DISTINCT i % 3) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM range(10) tbl(i)
----
0 0
1 1
2 3
3 3
4 3
5 3
6 3
7 3
8 3
9 3
# DISTINCT LISTs
query TT
EXPLAIN
SELECT
LIST(DISTINCT col0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM (VALUES ({'key': 'A'}), ({'key': 'B'}), ({'key': 'A'}))
----
physical_plan <REGEX>:.*STREAMING_WINDOW.*
query I
SELECT
LIST(DISTINCT col0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM (VALUES ({'key': 'A'}), ({'key': 'B'}), ({'key': 'A'}))
----
[{'key': A}]
[{'key': A}, {'key': B}]
[{'key': A}, {'key': B}]
# DISTINCT + FILTER
query TT
explain
SELECT i,
SUM(DISTINCT i % 5) FILTER (i % 3 = 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM range(20) tbl(i)
----
physical_plan <REGEX>:.*STREAMING_WINDOW.*
query II
SELECT i,
SUM(DISTINCT i % 5) FILTER (i % 3 = 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM range(20) tbl(i)
----
0 0
1 0
2 0
3 3
4 3
5 3
6 4
7 4
8 4
9 8
10 8
11 8
12 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
|
