How to calculate the amount of displacement using reset based on a condition in SQL teradate?

Question

I have this data and I want to summarize the USAGE_FLAG field, but reset when it drops to 0 or moves to a new identifier, keeping the data set 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 tried the MSUM() function using GROUP BY but will not preserve the order I want above. It groups seven and the number of weeks that I do not need.

Does anyone know if this can be done? I am using teradata p>

Answer 1

In standard SQL, the current amount can be executed using the window processing 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 that Teradata supports window functions, I just don’t know if it supports order in the window definition.

Resetting the amount is a bit trickier. First you need to create “group identifiers” that change every time use_flag goes to 0. The following works in PostgreSQL: I don’t know if this works in Teradata:

 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; 

Answer 2

Try the code below using the RESET function, which works fine.

 select su_id, week, usage_flag, SUM(usage_flag) OVER ( PARTITION BY su_id ORDER BY week RESET WHEN usage_flag < /* preceding row */ SUM(usage_flag) OVER ( PARTITION BY su_id ORDER BY week ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ROWS UNBOUNDED PRECEDING ) from emp_su; 

Answer 3

Try below SQL:

 select su_id, week, usage_flag, SUM(usage_flag) OVER (PARTITION BY su_id ORDER BY week RESET WHEN usage_flag = 0 ROWS UNBOUNDED PRECEDING ) from emp_su; 

Here RESET WHEN use_flag = 0 will be reset by the sum whenever the amount of use_flag drops to 0