SQL - 获取“X"连续值的总和，其中 X 是另一行中的整数(带类别) [英] SQL - Getting Sum of 'X' Consecutive Values where X is an Integer in another Row (With Categories)

问题描述

例如，我想对当前行中的所有值求和，直到提供的计数.见下表:

Say for example, I wanted to SUM all the values from the current row until the provided count. See table below:

例如:

• A 类，第 1 行:10+15+25 = 50(因为它添加了第 1 行到第 3 行)
• A 类，第 2 行:15+25+30+40 = 110(因为它添加了第 2 行到第 5 行)
• 类别 A，第 5 行:40+60 = 100(因为它添加了第 5 行和第 6 行.由于计数为 5，但类别在第 6 行结束，因此它对所有可用数据求和，即 Rows仅 5 和 6，因此值为 100.
• B 类也一样.

我该怎么做?

推荐答案

您可以使用窗口函数来做到这一点:

You can do this using window functions:

with tt as (
      select t.*,
             sum(quantity) over (partition by category order by rownumber) as running_quantity,
             max(rownumber) over (partition by category) as max_rownumber
      from t
     )
select tt.*,
       coalesce(tt2.running_quantity, ttlast.running_quantity) - tt.running_quantity + tt.quantity
from tt left join
     tt tt2
     on tt2.category = tt.category and
        tt2.rownumber = tt.rownumber + tt.count - 1 left join
     tt ttlast
     on ttlast.category = tt.category and
        ttlast.rownumber = ttlast.max_rownumber
order by category, rownumber;

我可以想象在某些情况下这会快得多——特别是如果 count 值相对较大.对于较小的 count 值，横向连接可能更快，但值得检查性能是否重要.

I can imagine that under some circumstances this would be much faster -- particularly if the count values are relatively large. For small values of count, the lateral join is probably faster, but it is worth checking if performance is important.

实际上，纯窗口函数方法可能是最好的方法:

Actually, a pure window functions approach is probably the best approach:

with tt as (
      select t.*,
             sum(quantity) over (partition by category order by rownumber) as running_quantity
      from t
     )
select tt.*,
       (coalesce(lead(tt.running_quantity, tt.count - 1) over (partition by tt.category order by tt.rownumber),
                 first_value(tt.running_quantity) over (partition by tt.category order by tt.rownumber desc)
                ) - tt.running_quantity + tt.quantity
       )
from tt
order by category, rownumber;

这里是db<>fiddle.

Here is a db<>fiddle.

这篇关于SQL - 获取“X"连续值的总和，其中 X 是另一行中的整数(带类别)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！