transactions 2, PostgreSQL

transactions 2

Language: Layout:

drop table if exists transactions;
create table transactions 
(
 year int,
 month  int, 
 bank   text,
 amount numeric
);
 
insert into transactions 
values
(2018, 1, 'one', 103),
(2018, 4, 'one', 199),
(2018, 4, 'one', 199),
(2018, 4, 'one', 199),
(2018, 7, 'one', 74),
(2018, 11, 'one', 5),

(2019, 1, 'one', 10),
(2019, 2, 'one', 1),
(2019, 3, 'one', 7),
(2019, 10, 'one', 42),
(2019, 11, 'one', 11),

(2019, 1, 'two', 5),
(2019, 2, 'two', 11),
(2019, 3, 'two', 9),
(2019, 7, 'two', 4),
(2019, 9, 'two', 1),

(2018, 1, 'three', 15),
(2018, 5, 'three', 110),
(2018, 6, 'three', 92),
(2018, 11, 'three', 47),
(2018, 12, 'three', 19)
;

with all_years as (
  select y.year, m.month, b.bank
  from generate_series(2018, 2019) as y(year)
    cross join generate_series(1,12) as m(month)
    cross join (select distinct bank from transactions) as b(bank)
)
select ay.*, sum(amount) over (partition by ay.bank order by ay.year, ay.month)
from all_years ay
  left join transactions t on (ay.year, ay.month, ay.bank) = (t.year::int, t.month::int, t.bank)
order by bank, year, month;


absolute service time: 0,51 sec

	year	month	bank	sum
1	2018	1	one	103
2	2018	2	one	103
3	2018	3	one	103
4	2018	4	one	700
5	2018	4	one	700
6	2018	4	one	700
7	2018	5	one	700
8	2018	6	one	700
9	2018	7	one	774
10	2018	8	one	774
11	2018	9	one	774
12	2018	10	one	774
13	2018	11	one	779
14	2018	12	one	779
15	2019	1	one	789
16	2019	2	one	790
17	2019	3	one	797
18	2019	4	one	797
19	2019	5	one	797
20	2019	6	one	797
21	2019	7	one	797
22	2019	8	one	797
23	2019	9	one	797
24	2019	10	one	839
25	2019	11	one	850
26	2019	12	one	850
27	2018	1	three	15
28	2018	2	three	15
29	2018	3	three	15
30	2018	4	three	15
31	2018	5	three	125
32	2018	6	three	217
33	2018	7	three	217
34	2018	8	three	217
35	2018	9	three	217
36	2018	10	three	217
37	2018	11	three	264
38	2018	12	three	283
39	2019	1	three	283
40	2019	2	three	283
41	2019	3	three	283
42	2019	4	three	283
43	2019	5	three	283
44	2019	6	three	283
45	2019	7	three	283
46	2019	8	three	283
47	2019	9	three	283
48	2019	10	three	283
49	2019	11	three	283
50	2019	12	three	283
51	2018	1	two	NULL
52	2018	2	two	NULL
53	2018	3	two	NULL
54	2018	4	two	NULL
55	2018	5	two	NULL
56	2018	6	two	NULL
57	2018	7	two	NULL
58	2018	8	two	NULL
59	2018	9	two	NULL
60	2018	10	two	NULL
61	2018	11	two	NULL
62	2018	12	two	NULL
63	2019	1	two	5
64	2019	2	two	16
65	2019	3	two	25
66	2019	4	two	25
67	2019	5	two	25
68	2019	6	two	25
69	2019	7	two	29
70	2019	8	two	29
71	2019	9	two	30
72	2019	10	two	30
73	2019	11	two	30
74	2019	12	two	30