How to calculate moving sum with reset based on condition in teradata SQL?

sql, sum, teradata

Solution

In standard SQL a running sum can be done using a windowing function:

select su_id,
       week,
       usage_flag, 
       sum(usage_flag) over (partition by su_id order by week) as running_sum
from the_table;

I know Teradata supports windowing functions, I just don't know whether it also supports an order by in the window definition.

Resetting the sum is a bit more complicated. You first need to create "group IDs" that change each time the usage_flag goes to 0. The following works in PostgreSQL, I don't know if this works in Teradata as well:

select su_id,
       week,
       usage_flag,
       sum(usage_flag) over (partition by su_id, group_nr order by week) as running_sum
from (
  select t1.*,
         sum(group_flag) over (partition by su_id order by week) as group_nr
  from (
      select *,
             case
                when usage_flag = 0 then 1
                else 0
              end as group_flag
      from the_table
  ) t1
) t2
order by su_id, week;

Problem

I have this data and I want to sum the field `USAGE_FLAG` but reset when it drops to 0 or moves to a new ID keeping the dataset ordered by `SU_ID` and `WEEK`: ``` SU_ID WEEK USAGE_FLAG 100 1 0 100 2 7 100 3 7 100 4 0 101 1 0 101 2 7 101 3 0 101 4 7 102 1 7 102 2 7 102 3 7 102 4 0 ``` So I want to create this table: ``` SU_ID WEEK USAGE_FLAG SUM 100 1 0 0 100 2 7 7 100 3 7 14 100 4 0 0 101 1 0 0 101 2 7 7 101 3 0 0 101 4 7 7 102 1 7 7 102 2 7 14 102 3 7 21 102 4 0 0 ``` I have tried the `MSUM()` function using `GROUP BY` but it won't keep the order I want above. It groups the 7's and the week numbers together which I don't want. Anyone know if this is possible to do? I'm using teradata

Original source