regress/sql/multi_insert_select_window.sql

5 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
7    user_id, time, rnk
14     WINDOW my_win AS (PARTITION BY user_id ORDER BY time DESC)
18 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
22 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
24    user_id, time, rnk
28     *, rank() OVER (PARTITION BY user_id ORDER BY time DESC) as rnk
34 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
38 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg, value_3_agg)
40    user_id, time, lag_event_type, row_no
46     events_table WINDOW my_win AS (PARTITION BY user_id ORDER BY time DESC)
50 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
54 INSERT INTO agg_results_window (user_id, value_1_agg, value_2_agg)
56    user_id, rnk, tme
60     user_id, rank() OVER my_win as rnk, avg(value_2) as tme
64     user_id,  date_trunc('day', time)
65   WINDOW my_win AS (PARTITION BY user_id ORDER BY avg(event_type) DESC)
69 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
73 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
75    min(user_id), min(time), lag_event_type
81     events_table WINDOW my_win AS (PARTITION BY user_id ORDER BY time DESC)
87 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
91 INSERT INTO agg_results_window (user_id, value_1_agg, value_2_agg)
95     DISTINCT users_table.user_id, lag(users_table.user_id) OVER w1, rank() OVER w1
99     users_table.user_id = events_table.user_id and
101 …WINDOW w1 AS (PARTITION BY users_table.user_id, events_table.event_type ORDER BY events_table.time)
105 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
109 INSERT INTO agg_results_window (user_id, value_1_agg, value_2_agg)
113     DISTINCT users_table.user_id, lag(users_table.user_id) OVER w1, rank() OVER w2
117     users_table.user_id = events_table.user_id and
119 …WINDOW w1 AS (PARTITION BY users_table.user_id, events_table.event_type ORDER BY events_table.time…
120   w2 AS (PARTITION BY users_table.user_id, (events_table.value_2 % 25) ORDER BY events_table.time)
124 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
128 INSERT INTO agg_results_window (user_id, value_1_agg, value_2_agg, value_3_agg)
129 SELECT sub_1.user_id, max(lag_1), max(rank_1), max(rank_2) FROM
132 …  DISTINCT users_table.user_id, lag(users_table.user_id) OVER w1 as lag_1, rank() OVER w2 as rank_1
136     users_table.user_id = events_table.user_id and
138 …WINDOW w1 AS (PARTITION BY users_table.user_id, events_table.event_type ORDER BY events_table.time…
139   w2 AS (PARTITION BY users_table.user_id, (events_table.value_2 % 25) ORDER BY events_table.time)
144 …  DISTINCT users_table.user_id, lag(users_table.user_id) OVER w1 as lag_2, rank() OVER w2 as rank_2
148     users_table.user_id = events_table.user_id and
150   WINDOW w1 AS (PARTITION BY users_table.user_id, events_table.value_2 ORDER BY events_table.time),
151   w2 AS (PARTITION BY users_table.user_id, (events_table.value_2 % 50) ORDER BY events_table.time)
153  ON(sub_1.user_id = sub_2.user_id)
155   sub_1.user_id;
158 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
162 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
164    avg(user_id), max(time), my_rank
168     user_id, date_trunc('day', time) as time, rank() OVER my_win as my_rank
172     user_id, date_trunc('day', time)
173     WINDOW my_win AS (PARTITION BY user_id ORDER BY count(*) DESC)
181 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
185 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
187    avg(user_id), max(time), my_rank
191     user_id, date_trunc('day', time) as time, rank() OVER my_win as my_rank
195     user_id, date_trunc('day', time)
196     WINDOW my_win AS (PARTITION BY user_id, avg(event_type%10)::int ORDER BY count(*) DESC)
204 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
209 INSERT INTO agg_results_window (user_id, value_1_agg)
211    avg(user_id), my_rank
215     user_id, rank() OVER my_win as my_rank
219     user_id
220     WINDOW my_win AS (PARTITION BY user_id, max(event_type) ORDER BY count(*) DESC)
226 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
230 INSERT INTO agg_results_window(user_id, value_2_agg)
233     DISTINCT user_id, SUM(value_2) OVER (PARTITION BY user_id)
237     user_id, value_1, value_2
245 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
248 INSERT INTO agg_results_window(user_id, value_2_agg)
249 SELECT user_id, max(sum) FROM (
251     user_id, SUM(value_2) OVER (PARTITION BY user_id, value_1)
255     user_id, value_1, value_2
257 GROUP BY user_id;
260 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
264 INSERT INTO agg_results_window(user_id)
266   user_id
273       avg(value_3) OVER (PARTITION BY user_id)
277       users_table.user_id = events_table.user_id
280   user_id;
283 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
289 INSERT INTO agg_results_window(user_id, value_2_agg)
292     user_id, COUNT(*) OVER (PARTITION BY user_id, MIN(value_2))
300 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
304 INSERT INTO agg_results_window(user_id, value_2_agg, value_3_agg, value_4_agg)
306   user_id, rank, SUM(ABS(value_2 - value_3)) AS difference, COUNT(*) AS distinct_users
309     *, rank() OVER (PARTITION BY user_id ORDER BY value_2 DESC)
312       user_id, value_2, sum(value_3) OVER (PARTITION BY user_id, value_2) as value_3
317   user_id, rank;
320 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
323 INSERT INTO agg_results_window(user_id, value_1_agg)
326     f3.user_id, ABS(f2.sum - f3.sum)
329       user_id, sum(value_3) OVER (PARTITION BY user_id)
333       user_id, value_3
337     user_id, sum(value_2) OVER (PARTITION BY user_id)
341     user_id, value_2
344   f3.user_id=f2.user_id
348 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
352 INSERT INTO agg_results_window(user_id, value_1_agg)
357       DISTINCT user_id, count(id) OVER (PARTITION BY user_id, id)
363 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
367 INSERT INTO agg_results_window (user_id, value_1_agg)
370     DISTINCT user_id, rank() OVER (PARTITION BY user_id ORDER BY value_1)
374     user_id, value_1 HAVING count(*) > 1
378 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
382 INSERT INTO agg_results_window (user_id, value_1_agg)
385     DISTINCT user_id, rank() OVER (PARTITION BY user_id ORDER BY value_1)
389     user_id, value_1 HAVING count(*) > 1
397 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
403   DISTINCT user_id, rank() OVER (PARTITION BY user_id ORDER BY value_1)
407   user_id, value_1
410 INSERT INTO agg_results_window(user_id, value_1_agg)
416 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
420 INSERT INTO agg_results_window(user_id, value_1_agg)
432 INSERT INTO agg_results_window(user_id, value_1_agg)
434   user_id, max(avg)
437 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (1…
439 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (6…
441 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (1…
443 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (1…
445 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (2…
447 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (2…
450   user_id
459 INSERT INTO agg_results_window(user_id, value_1_agg)
461   user_id, max(avg)
464 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (1…
466 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (6…
468 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (1…
470 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (1…
472 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (2…
474 …(SELECT avg(value_3) over (partition by user_id), user_id FROM events_table where event_type IN (2…
477   user_id;
480 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
483 INSERT INTO agg_results_window(user_id, value_1_agg)
486         ( SELECT user_id,
490               user_id, sum(value_2) over (partition by user_id) AS counter
495               user_id, sum(value_2) over (partition by user_id) AS counter
499           user_id)
502             user_id, sum(counter)
505               user_id, sum(value_2) over (partition by user_id) AS counter
510               user_id, sum(value_2) over (partition by user_id) AS counter
514             user_id
525 INSERT INTO agg_results_window(user_id, value_1_agg)
528         ( SELECT user_id,
532               user_id, sum(value_2) over (partition by user_id) AS counter
537               user_id, sum(value_2) over (partition by user_id) AS counter
541           user_id)
544             user_id, sum(counter)
547               user_id, sum(value_2) over (partition by user_id) AS counter
552               user_id, sum(value_2) over (partition by user_id) AS counter
556             user_id
561 SELECT count(*), count(DISTINCT user_id), avg(user_id) FROM agg_results_window;
565 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
567    user_id, time, rnk
581 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
583    user_id, time, rnk
597 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
599    user_id, time, rnk
613 INSERT INTO agg_results_window (user_id, value_1_agg, value_2_agg)
617     DISTINCT users_table.user_id, lag(users_table.user_id) OVER w1, rank() OVER w2
621     users_table.user_id = events_table.user_id and
623 …WINDOW w1 AS (PARTITION BY users_table.user_id, events_table.event_type ORDER BY events_table.time…
624   w2 AS (PARTITION BY users_table.user_id+1, (events_table.value_2 % 25) ORDER BY events_table.time)
629 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
631    user_id, time, my_rank
635     user_id, date_trunc('day', time) as time, rank() OVER my_win as my_rank
639     user_id, date_trunc('day', time)
645 INSERT INTO agg_results_window (user_id, agg_time, value_2_agg)
647    user_id, time, my_rank
651     user_id, date_trunc('day', time) as time, rank() OVER my_win as my_rank
655     user_id, date_trunc('day', time)
661 INSERT INTO agg_results_window (user_id, value_2_agg, value_3_agg)
665     DISTINCT users_table.user_id, lag(users_table.user_id) OVER w1, rank() OVER w2
669     users_table.user_id = events_table.user_id and
671 …WINDOW w1 AS (PARTITION BY users_table.user_id, events_table.event_type ORDER BY events_table.time…
675 INSERT INTO agg_results_window(user_id, agg_time, value_2_agg)
678     user_id, date_trunc('day', time) as time, sum(rank) OVER w2
681         user_id as user_id, time, rank() over w1
688       w2 as (PARTITION BY user_id, time)
691 INSERT INTO agg_results_window(user_id)
693   user_id
704       users_table.user_id = events_table.user_id
707   user_id;
709 INSERT INTO agg_results_window(user_id, value_2_agg)
712     user_id, COUNT(*) OVER (PARTITION BY sum(user_id), MIN(value_2))
716     user_id
719 INSERT INTO agg_results_window(user_id, value_1_agg)
722         ( SELECT user_id,
726               user_id, sum(value_2) over (partition by user_id) AS counter
731               user_id, sum(value_2) over (partition by user_id) AS counter
735           user_id)
738             user_id, sum(counter)
741               user_id, sum(value_2) over (partition by user_id) AS counter
746               user_id, sum(value_2) over (partition by event_type) AS counter
750             user_id