I got Clickhouse v. 5.7.30 (24.1.5.).
Is this version got equivalent of windows function:
sum(...) over(partition by ... order by ...)
I also tried this code:
select salon_name
, date
, runningAccumulate(sumState(revenue_fact_sum), salon_name) as revenue_fact_cumsum
from revenue_plan_fact_without_cumsum
group by salon_name,
date
order by salon_name,
date
But result is strange:
Original data:
Then I need to calculate cumulative sum with:
runningAccumulate(sumState(revenue_fact), salon_name)
I must put in select all column, including column 'revenue_fact'. In group by I also need add all columns to keep all values of revenue from original table.
select salon_name
, date
, revenue_fact
, runningAccumulate(sumState(revenue_fact), salon_name) AS revenue_cumulative
from metrics_for_specializations_with_hour_plan
group by salon_name
, date
, revenue_fact
order by salon_name
, date
If I need add more columns to make "partition by", I need add this columns to select, group by and inside runningAccumulate like this:
select salon_name
, specialization_unification
, date
, revenue_fact
, runningAccumulate(sumState(revenue_fact), [salon_name, specialization_unification]) AS revenue_cumulative
from metrics_for_specializations_with_hour_plan
group by salon_name
, specialization_unification
, date
, revenue_fact
order by salon_name
, specialization_unification
, date
It's not obviously for me after reading documentation.