Running total for each 4 rows in snowflake-CodePudding

I have one column: NMV I want to calculate the cumulative sum for it but like this:

nmv cumsum

    1  1
    2  3 
    3  6
    4  10 ---stops here
    5  5  --- starts again
    6  11
    7  18

CodePudding user response：

select sum(cumsum) from table1
group by (nmv-1)/4

CodePudding user response：

Using helper column:

CREATE OR REPLACE TABLE tab(nmv INT);
INSERT INTO tab(nmv) VALUES (1),(2),(3),(4),(5),(6),(7);

WITH cte AS (
  SELECT *, CEIL(ROW_NUMBER() OVER(ORDER BY NMV)/4) AS subgrp
  FROM tab
)
SELECT *, SUM(NMV) OVER(PARTITION BY subgrp ORDER BY NMV) AS cumsum
FROM cte;

Output: