sql - 累积一个汇总列

Question

我可能需要一些有关 SQL 语句的帮助。所以我有一个看起来像这样的表“cont”:

cont_id     name       weight
----------- ---------- -----------
1           1          10
2           1          20
3           2          40
4           2          15
5           2          20
6           3          15
7           3          40
8           4          60
9           5          10
10          6          5

然后我总结了权重列并按名称分组:

name       wsum
---------- -----------
2          75
4          60
3          55
1          30
5          10
6          5

结果应该有一个累积的列，应该是这样的:

name       wsum        acc_wsum
---------- ----------- ------------
2          75          75
4          60          135
3          55          190
1          30          220
5          10          230
6          5           235

但我没有设法让最后一条语句起作用..

编辑:这个声明做到了(感谢戈登)

select t.*,
    (select sum(wsum) from (select name, SUM(weight) wsum
    from cont
    group by name)
    t2 where t2.wsum > t.wsum or (t2.wsum = t.wsum and t2.name <= t.name)) as acc_wsum
from (select name, SUM(weight) wsum
from cont
group by name) t
order by wsum desc

Answer 1

因此，最好的方法是使用累积总和:

select t.*,
       sum(wsum) over (order by wsum desc) as acc_wsum
from (<your summarized query>) t

order by条款使这个累积。

如果您没有该功能(在 SQL Server 2012 和 Oracle 中)，则相关子查询是一种简单的方法，假设求和的权重是不同的值:

select t.*,
       (select sum(wsum) from (<your summarized query>) t2 where t2.wsum >= t.wsum) as acc_wsum
from (<your summarized query>) t

这应该适用于所有 SQL 方言。要处理累积权重可能重复的情况:

select t.*,
       (select sum(wsum) from (<your summarized query>) t2 where t2.wsum > t.wsum or (t2.wsum = t.wsum and t2.name <= t.name) as acc_wsum
from (<your summarized query>) t