CODEWITHSUNDEEP
clickhouse
navid karampour
navid karampour

Reputation: 93

how can I calculated point of each user per day with sum all the points from beginning to that day in clickhouse

I have this data in clickhouse:

enter image description here

final point of each user in day is sum(point) from the beginning to that day. e.g: point of user 1 in 2020-07-02 is 800 and in 2020-07-03 is 200.

I need this result: Point of each user per day:

enter image description here

Upvotes: 0

Views: 141

Answers (1)

Denny Crane
Denny Crane

Reputation: 13245

select uid, d, t from (
select uid, groupArray(date) dg, arrayCumSum(groupArray(spt)) gt from
(select uid, date, sum(pt) spt from
(select 1 tid, '2020-07-01' date, 1 uid, 500 pt
union all 
select 1 tid, '2020-07-02' date, 1 uid, 300 pt
union all 
select 1 tid, '2020-07-03' date, 1 uid, -600 pt)
group by uid, date
order by uid, date)
group by uid) array join dg as d, gt as t

┌─uid─┬─d──────────┬───t─┐
│   1 │ 2020-07-01 │ 500 │
│   1 │ 2020-07-02 │ 800 │
│   1 │ 2020-07-03 │ 200 │
└─────┴────────────┴─────┘

Upvotes: 1

Related Questions