主页 / dba / 问题 / 61407
Accepted
Luan Huynh
Asked: 2014-03-21 19:37:01 +0800 CST

如何求和先前的总和，例如 N = (N-1) + (N-2) + ... + 1？

  • 772

我有一个表名“ TABLE_A ( id integer, no integer) ”。

我想通过“id”和当前“no sum of no”=之前的“sum of no”将“no”与 group 相加

这是我的代码：

1/ 创建表和插入数据：

create table table_a (id int, no int);

insert into table_a values(1, 10);
insert into table_a values(1, 20);
insert into table_a values(1, 30);
insert into table_a values(2, 100);
insert into table_a values(2, 200);
insert into table_a values(2, 300);
insert into table_a values(3, 1);
insert into table_a values(3, 2);
insert into table_a values(3, 3);
insert into table_a values(3, 3);

2/ 期望的结果：

id | sum_of_no
--------------
1  | 60
2  | 660
3  | 669

3/ 我的解决方案（好的）：

with t_report_code_temp as
(   
select id, sum(no) as t_code
from table_a
group by id 
)
select a.id, sum(b.t_code)
from t_report_code_temp a 
join t_report_code_temp b on b.id <= a.id
group by a.id
order by 1

我的问题：

你能给我更好的解决方法吗？

postgresql join

3 个回答

  1. 来自超立方体的克雷格·林格。

    这是我的测试：

    create table table_a (id int, no int);

insert into table_a  (1)
select a, a
from generate_series(1, 1000000) a

    克雷格·林格的询问 

    with t_report_code_temp(id, t_code) as
(   
select id, sum(no)
from table_a
group by id 
)
SELECT
  id,
  sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;

1.000.000 rows -> 5.5s
2.000.000 rows -> 7s   (run (1) twice)

    超立方体的查询

    SELECT
  id,
  SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id ;

1.000.000 rows -> 3.7s
2.000.000 rows -> 5.5s   (run (1) twice)

    我看到了窗口函数的魔力。谢谢 ！

    • 5

  2. 这看起来像是窗口函数的候选者。

    首先计算每个 id 的总和，然后按 ID 排序运行总和以获得所需的最终结果。

    with t_report_code_temp(id, t_code) as
(   
select id, sum(no)
from table_a
group by id 
)
SELECT
  id,
  sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;
    • 4
  3. Best Answer

    改进（？）克雷格·林格的回答。更少的代码，但不确定它是否更具可读性或更容易混淆：

    SELECT
  id,
  SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id 
ORDER BY id ;

    在SQL-Fiddle测试

    您的评论是对的，当窗口函数（或带有 的聚合OVER()）具有ORDER BY时，默认窗口是：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW生成带有 的运行总计SUM()。

    执行的逻辑流程是：

    FROM table_a            -- get all rows of table_a

-- WHERE                -- void here

GROUP BY id             -- make groups of rows, one for each value of "id"
      -- SUM(no) AS y   -- and calculate aggregates, like SUM(no)

-- HAVING               -- void here

SELECT                  -- calculate window functions and window aggregates 
  id,                   -- and any other function used in SELECT or ORDER BY 
  SUM(y) OVER (ORDER BY id ASC) AS sum_of_no

ORDER BY id             -- order the result set
    • 3

相关问题