4

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.

    • Bhanuchander Udhayakumar
    • 1,581
    • 1
    • 12
    • 30
    cag
    • 41
    • 1
    • 1
    • 4
    • Are you able to share code or research you've already tried? – stealththeninja Nov 08 '17 at 05:29
    • 1
      We have tried looking at various options - the obvious one being (in absence of WINDOW functions) to look at JOINS - but this running total would require SELF INNER JOIN with (e.g. a1.Sales <= a2.sales), which Clickhouse doesn't seem to support. Any other thoughts? Thanks! – cag Nov 08 '17 at 10:39
    • 1
      Have you tried something like that: Select Sales/Sum(Sales)*100 where ... ? – crak Nov 13 '17 at 09:19

    1 Answers1

    6

    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:

    filimonov
    • 1,666
    • 1
    • 9
    • 20