Search code examples
clickhouse

How to perform sum(...) over(partition by ... order by ...) in Clickhouse?


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:

enter image description here

Original data:

enter image description here


Solution

  • 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.