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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
CREATE EXTENSION extra_window_functions;
CREATE TABLE things (
part integer NOT NULL,
ord integer NOT NULL,
val integer
);
COPY things FROM stdin;
1 1 64664
1 2 8779
1 3 14005
1 4 57699
1 5 98842
1 6 88563
1 7 70453
1 8 82824
1 9 62453
2 1 \N
2 2 51714
2 3 17096
2 4 41605
2 5 15366
2 6 87359
2 7 98990
2 8 34982
2 9 3343
3 1 21903
3 2 24605
3 3 6242
3 4 24947
3 5 79535
3 6 66903
3 7 42269
3 8 31143
3 9 \N
4 1 \N
4 2 49723
4 3 23958
4 4 80796
4 5 \N
4 6 41066
4 7 72991
4 8 33734
4 9 \N
5 1 \N
5 2 \N
5 3 \N
5 4 \N
5 5 \N
5 6 \N
5 7 \N
5 8 \N
5 9 \N
\.
/* FLIP_FLOP */
SELECT part, ord, val,
flip_flop(val % 2 = 0) OVER w AS flip_flop_1,
flip_flop(val % 2 = 0, val % 2 = 1) OVER w AS flip_flop_2
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
/* LAG */
SELECT part, ord, val,
lag(val) OVER w AS lag,
lag_ignore_nulls(val) OVER w AS lag_in,
lag_ignore_nulls(val, 2) OVER w AS lag_in_off,
lag_ignore_nulls(val, 2, -9999999) OVER w AS lag_in_off_d
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
/* LEAD */
SELECT part, ord, val,
lead(val) OVER w AS lead,
lead_ignore_nulls(val) OVER w AS lead_in,
lead_ignore_nulls(val, 2) OVER w AS lead_in_off,
lead_ignore_nulls(val, 2, 9999999) OVER w AS lead_in_off_d
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
/* FIRST_VALUE */
SELECT part, ord, val,
first_value(val) OVER w AS fv,
first_value_ignore_nulls(val) OVER w AS fv_in,
first_value_ignore_nulls(val, 9999999) OVER w AS fv_in_d
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
/* LAST_VALUE */
SELECT part, ord, val,
last_value(val) OVER w AS lv,
last_value_ignore_nulls(val) OVER w AS lv_in,
last_value_ignore_nulls(val, -9999999) OVER w AS lv_in_d
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
/* NTH_VALUE */
SELECT part, ord, val,
nth_value(val, 3) OVER w AS nth,
nth_value_ignore_nulls(val, 3) OVER w AS nth_in
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
SELECT part, ord, val,
nth_value(val, 3) OVER w AS nth,
nth_value_from_last(val, 3) OVER w AS nth_fl
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
SELECT part, ord, val,
nth_value_from_last(val, 3) OVER w AS nth_fl,
nth_value_from_last_ignore_nulls(val, 3) OVER w AS nth_fl_in
FROM things
WINDOW w AS (PARTITION BY part ORDER BY ord ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
ORDER BY part, ord;
DROP TABLE things;
DROP EXTENSION extra_window_functions;
|
