CODEWITHSUNDEEP
sqlclickhouse
cag
cag

Reputation: 41

Clickhouse - Cumulative Sum or Running Total and Percent to Total

We have been very encouraged by Clickhouse. However, as we are trying to port all of our existing scripts to Clickhouse, we are running into few roadblocks. For example: CUMULATIVE SUM or RUNNING TOTAL. We are trying to find an equivalent of Window Functions e.g. SUM(SALES) OVER (PARTITION BY PRODUCT ORDER BY SALES)

  • Is there a way to get Cumulative Sum or Running Total. Any inputs or guidance is much appreciated. Thanks!

  • This is my second question on Clickhouse (again a fantastic database). We have to port our script which calculates "Percent to total".

    For e.g

    Product | Sales

P1  100 

P2  200

P3  150

P4  50

    We are looking at writing a script which can populate

    Product, Sales, PercenttoTotal

P1, 100, 20%

P2, 200, 40%

P3, 150, 30%

P4, 50,  10%

    Is there a quick way via various functions in Clickhouse using which we can accomplish this. Thanks in advance.

    • Upvotes: 4

    Views: 15074

    Answers (2)

    Pavel Shorokhov
    Pavel Shorokhov

    Reputation: 4994

    Use SUM + OVER. For example:

    SUM(events_count) OVER (ORDER BY interval)

    It helps me. Found solution here: https://www.interviewquery.com/p/sql-cumulative-sum-guide

    Upvotes: 0

    filimonov
    filimonov

    Reputation: 1744

    Percent to total: 

    SELECT 
   productid,
   count() as per_product_count,
   per_product_count * 100 / ( SELECT count() from prod_sales ) as percent_to_total
FROM prod_sales
GROUP BY productid

    Cumulative Sum or Running Total:

    Latest release (v1.1.54310-stable) add support for a function runningIncome (not documented yet) which should work just as runningDifference , but should calculate sum instead of difference.

    Check also my answers to this questions on github:

    Upvotes: 6

    Related Questions