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
|
SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;
SELECT a, b, group_concat(b, '.') OVER (
ORDER BY a
ROWS
BETWEEN 1 PRECEDING
AND 1 FOLLOWING
) AS group_concat FROM t1;
SELECT c, a, b, group_concat(b, '.') OVER (
PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;
SELECT c, a, b, group_concat(b, '.') OVER (
PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY a;
SELECT a, b, c,
group_concat(b, '.') OVER (ORDER BY c) AS group_concat
FROM t1 ORDER BY a;
SELECT c, a, b, group_concat(b, '.') OVER (
ORDER BY c, a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;
SELECT c, a, b,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
) AS no_others,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) AS current_row,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP
) AS grp,
group_concat(b, '.') OVER (
ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
) AS ties
FROM t1 ORDER BY c, a;
SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c != 'two') OVER (
ORDER BY a
) AS group_concat
FROM t1 ORDER BY a;
SELECT a AS a,
row_number() OVER win AS row_number,
rank() OVER win AS rank,
dense_rank() OVER win AS dense_rank,
percent_rank() OVER win AS percent_rank,
cume_dist() OVER win AS cume_dist
FROM t2
WINDOW win AS (ORDER BY a);
SELECT a AS a,
b AS b,
ntile(2) OVER win AS ntile_2,
ntile(4) OVER win AS ntile_4
FROM t2
WINDOW win AS (ORDER BY a);
SELECT b AS b,
lead(b, 2, 'n/a') OVER win AS lead,
lag(b) OVER win AS lag,
first_value(b) OVER win AS first_value,
last_value(b) OVER win AS last_value,
nth_value(b, 3) OVER win AS nth_value_3
FROM t1
WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
SELECT group_concat(b, '.') OVER (
win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM t1
WINDOW win AS (PARTITION BY a ORDER BY c);
|
