Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE example_table (
  user_id VARCHAR(4),
  ts VARCHAR(19),
  val INTEGER
);

CREATE TABLE


INSERT INTO example_table  (user_id, ts, val)VALUES
  ('uid1', '19.05.2019 01:49:50', '0'),
  ('uid1', '19.05.2019 01:50:15', '0'),
  ('uid1', '19.05.2019 01:50:20', '0'),
  ('uid1', '19.05.2019 01:59:50', '1'),
  ('uid1', '19.05.2019 02:20:10', '1'),
  ('uid1', '19.05.2019 02:20:15', '0'),
  ('uid1', '19.05.2019 02:20:19', '0'),
  ('uid1', '19.05.2019 02:30:53', '1'),
  ('uid1', '19.05.2019 11:10:25', '1'),
  ('uid1', '19.05.2019 11:13:40', '0'),
  ('uid1', '19.05.2019 11:13:50', '0'),
  ('uid1', '19.05.2019 11:20:19', '1'),
  ('uid2', '19.05.2019 15:01:44', '0'),
  ('uid2', '19.05.2019 15:05:55', '0'),
  ('uid2', '19.05.2019 17:19:35', '1'),
  ('uid2', '19.05.2019 17:20:01', '0'),
  ('uid2', '19.05.2019 17:20:35', '0'),
  ('uid2', '19.05.2019 19:15:50', '1');

INSERT 0 18


select *
, sum(val) over (partition by user_id) as res
, sum(val) over (partition by user_id order by ts) as res_order_by
, sum(val) over (
    partition by user_id
    order by ts
    rows between unbounded preceding and current row
  ) as res_order_by_unbounded_preceding
, sum(val) over (
    partition by user_id
    -- order by ts
    rows between unbounded preceding and current row
  ) as res_preceding
, sum(val) over (
    partition by user_id
    -- order by ts
    rows between current row and unbounded following
  ) as res_following
, sum(val) over (
    partition by user_id
    order by ts
    rows between unbounded preceding and unbounded following
  ) as res_orderby_preceding_following
  
from example_table;


user_id
ts
val
res
res_order_by
res_order_by_unbounded_preceding
res_preceding
res_following
res_orderby_preceding_following


uid1
19.05.2019 01:49:50
0
5
0
0
0
5
5

uid1
19.05.2019 01:50:15
0
5
0
0
0
5
5

uid1
19.05.2019 01:50:20
0
5
0
0
0
5
5

uid1
19.05.2019 01:59:50
1
5
1
1
1
5
5

uid1
19.05.2019 02:20:10
1
5
2
2
2
4
5

uid1
19.05.2019 02:20:15
0
5
2
2
2
3
5

uid1
19.05.2019 02:20:19
0
5
2
2
2
3
5

uid1
19.05.2019 02:30:53
1
5
3
3
3
3
5

uid1
19.05.2019 11:10:25
1
5
4
4
4
2
5

uid1
19.05.2019 11:13:40
0
5
4
4
4
1
5

uid1
19.05.2019 11:13:50
0
5
4
4
4
1
5

uid1
19.05.2019 11:20:19
1
5
5
5
5
1
5

uid2
19.05.2019 15:01:44
0
2
0
0
0
2
2

uid2
19.05.2019 15:05:55
0
2
0
0
0
2
2

uid2
19.05.2019 17:19:35
1
2
1
1
1
2
2

uid2
19.05.2019 17:20:01
0
2
1
1
1
1
2

uid2
19.05.2019 17:20:35
0
2
1
1
1
1
2

uid2
19.05.2019 19:15:50
1
2
2
2
2
1
2



SELECT 18

user_id	ts	val	res	res_order_by	res_order_by_unbounded_preceding	res_preceding	res_following	res_orderby_preceding_following
uid1	19.05.2019 01:49:50	0	5	0	0	0	5	5
uid1	19.05.2019 01:50:15	0	5	0	0	0	5	5
uid1	19.05.2019 01:50:20	0	5	0	0	0	5	5
uid1	19.05.2019 01:59:50	1	5	1	1	1	5	5
uid1	19.05.2019 02:20:10	1	5	2	2	2	4	5
uid1	19.05.2019 02:20:15	0	5	2	2	2	3	5
uid1	19.05.2019 02:20:19	0	5	2	2	2	3	5
uid1	19.05.2019 02:30:53	1	5	3	3	3	3	5
uid1	19.05.2019 11:10:25	1	5	4	4	4	2	5
uid1	19.05.2019 11:13:40	0	5	4	4	4	1	5
uid1	19.05.2019 11:13:50	0	5	4	4	4	1	5
uid1	19.05.2019 11:20:19	1	5	5	5	5	1	5
uid2	19.05.2019 15:01:44	0	2	0	0	0	2	2
uid2	19.05.2019 15:05:55	0	2	0	0	0	2	2
uid2	19.05.2019 17:19:35	1	2	1	1	1	2	2
uid2	19.05.2019 17:20:01	0	2	1	1	1	1	2
uid2	19.05.2019 17:20:35	0	2	1	1	1	1	2
uid2	19.05.2019 19:15:50	1	2	2	2	2	1	2