如何计算基于Teradata数据中的SQL条件与移动复位总和？(How to calculate m

我有这样的数据，我想总结领域USAGE_FLAG但是当它下降到0或移动到一个新的ID保持由订购的数据集重置SU_ID和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

所以我想创建该表：

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

我曾尝试MSUM()使用函数GROUP BY ，但它不会让我在上面要的顺序。 这组7的和周数一起，我不想。

任何人都知道，如果这是可能的吗？ 我使用的Teradata

Answer 1:

在标准SQL运行总和可以使用窗函数来完成：

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

我知道Teradata的支持窗口的功能，我只是不知道是否还通过在窗口定义支持的订单。

重置总和是有点复杂。 首先，您需要创建“组ID”，每个usage_flag变为0 PostgreSQL中的以下工作时间而改变，我不知道，如果这个工程在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:

试试下面的代码，在使用它工作正常复位功能。

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:

请尝试以下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;

这里复位当usage_flag = 0每当总和usage_flag下降到0将复位总和

文章来源: How to calculate moving sum with reset based on condition in teradata SQL?