CODEWITHSUNDEEP
javascriptaggregatesnowflake-cloud-data-platform
Jay Tilala
Jay Tilala

Reputation: 181

Cumulative sum in Snowflake

As shown in above image, I want cumulative sum (like C_SUM) of VAL based on following two constraints.

  1. For each group, my sum should reset to 0.
  2. If cumulative sum goes to negative, it should reset to 0.

Upvotes: 0

Views: 1848

Answers (1)

Jay Tilala
Jay Tilala

Reputation: 181

  • We can create custom aggregate function using UDTF-js.

  • UDTF-js is similar to SQL-UDTF, But in UDTF-js we can write js (javascript) code.

  • Before going to solution, let’s get little intro about UDF-js main 3 components. All these components will be executed for each group separately.

    1. initialize: it will execute first, once for each group.
    2. processRow: after execution of the initialize block, processRow will execute for each row.
    3. finalize: it will execute once per partition at the end of the group
# CREATE Aggregate Function
CREATE OR REPLACE FUNCTION "CUM_SUM"(VAL double)
    RETURNS TABLE (C_SUM double)
    LANGUAGE JAVASCRIPT
    AS '{
    processRow: function (row, rowWriter, context) {
      this.sum = this.sum + row.VAL;
      
      if (this.sum<0){
        this.sum=0;
      }
      rowWriter.writeRow({C_SUM: this.sum});
    },
    finalize: function (rowWriter, context) {
    },
    initialize: function(argumentInfo, context) {
     this.sum = 0;
    }}';

# Select Query
WITH MyTable AS (
    SELECT 'A' AS GP, 1 AS ID, 500.0 as VAL
      UNION ALL SELECT 'A', 2,-400.0
      UNION ALL SELECT 'A', 3,100.0
      UNION ALL SELECT 'A', 4,-50.0
      UNION ALL SELECT 'A', 5,-40.0
      UNION ALL SELECT 'A', 6,-100.0
      UNION ALL SELECT 'A', 7,-50.0
      UNION ALL SELECT 'A', 8,-10.0
      UNION ALL SELECT 'A', 9,30.0
      UNION ALL SELECT 'A', 10,-10.0
      UNION ALL SELECT 'B', 11,600.0
      UNION ALL SELECT 'B', 12,-1000.0
      UNION ALL SELECT 'B', 13,-1600.0
      UNION ALL SELECT 'B', 14,1000.0
      UNION ALL SELECT 'B', 15,-500.0
      UNION ALL SELECT 'B', 16,-400.0
 )
SELECT *
FROM MyTable, TABLE( CUM_SUM(TO_DOUBLE(VAL)) OVER  (PARTITION BY gp) ) as t
order by GP

Upvotes: 1

Related Questions