如何根据 teradata SQL 中的条件计算带有重置的移动总和? [英] How to calculate moving sum with reset based on condition in teradata SQL?

问题描述

我有这些数据，我想对字段 USAGE_FLAG 求和，但当它降至 0 或移动到新 ID 时重置，保持数据集按 SU_ID 和 <代码>星期:

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

所以我想创建这个表:

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

我已尝试使用 GROUP BY 的 MSUM() 函数，但它不会保持我想要的上述顺序.它将我不想要的 7 和周数组合在一起.

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.

有人知道这是否可行吗?我正在使用 teradata

Anyone know if this is possible to do? I'm using teradata

推荐答案

在标准 SQL 中，可以使用窗口函数完成运行总和:

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;

我知道 Teradata 支持窗口函数，只是不知道它是否也支持窗口定义中的 order by.

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

重置总和有点复杂.您首先需要创建每次使用标志变为 0 时都会更改的组 ID".以下适用于 PostgreSQL，我不知道这是否也适用于 Teradata:

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;

这篇关于如何根据 teradata SQL 中的条件计算带有重置的移动总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！