transactions, PostgreSQL

transactions

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, 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,55 sec

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