postgresql 如何计算SQL中每组行的列的累积和？

6tdlim6h 于 2023-04-11 发布在 PostgreSQL

关注(0)|答案(1)|浏览(218)

我在SQL中有一个表，其中包含列“NDP”，“Code_PAYS”，“FLUX”，“Year”，“Month_Num”和“valeur_tnd”。我想计算具有相同“NDP”，“Code_PAYS”，“FLUX”和“Year”值的每组行的“valeur_tnd”的累积总和。此外，我希望在每个新的年份将累积总和重置为0，我希望它是按月使用的“月数”列。
我试过使用窗口函数SUM（）OVER（PARTITION BY ...），但我在让它正确地工作时遇到了麻烦，因为我需要分组和排序。
谁能帮我用SQL代码来实现我的表中每组行的累计和？按月排序，使用“Month_Num”列？提前感谢您的帮助。

INSERT INTO TABLE_ytd
SELECT * ,
SUM("VALEUR_TND") OVER (PARTITION BY "NDP", "Code_PAYS", "FLUX", "Year"
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTD_VALEUR_TND,
FROM "table1" ;

google，chatgpt，many sql quary

postgresql

来源：https://stackoverflow.com/questions/75970891/how-can-i-calculate-the-cumulative-sum-of-a-column-for-each-group-of-rows-in-sql

1条答案

按热度按时间

ruyhziif1#

下面的查询演示了一个SUM表达式，它可以实现所描述的结果：

WITH t(
  year,
  month_num,
  ndp,
  code_pays,
  flux,
  valeur_tnd
) AS (
  VALUES (2021, 1, 'NDP_1', 124, 1, 210101),
         (2021, 2, 'NDP_1', 124, 1, 102),
         (2021, 3, 'NDP_1', 124, 1, 103),
         (2021, 4, 'NDP_1', 124, 1, 104),
         (2021, 5, 'NDP_1', 124, 1, 105),
         (2021, 6, 'NDP_1', 124, 1, 106),
         (2021, 7, 'NDP_1', 124, 1, 107),
         (2021, 8, 'NDP_1', 124, 1, 108),
         (2021, 9, 'NDP_1', 124, 1, 109),
         (2021, 10, 'NDP_1', 124, 1, 110),
         (2021, 11, 'NDP_1', 124, 1, 111),
         (2021, 12, 'NDP_1', 124, 1, 112),
         (2022, 1, 'NDP_1', 124, 1, 220101),
         (2022, 2, 'NDP_1', 124, 1, 102),
         (2022, 3, 'NDP_1', 124, 1, 103),
         (2022, 4, 'NDP_1', 124, 1, 104),
         (2022, 5, 'NDP_1', 124, 1, 105),
         (2022, 6, 'NDP_1', 124, 1, 106),
         (2022, 7, 'NDP_1', 124, 1, 107),
         (2022, 8, 'NDP_1', 124, 1, 108),
         (2022, 9, 'NDP_1', 124, 1, 109),
         (2022, 10, 'NDP_1', 124, 1, 110),
         (2022, 11, 'NDP_1', 124, 1, 111),
         (2022, 12, 'NDP_1', 124, 1, 112),
         (2023, 1, 'NDP_1', 124, 1, 230101),
         (2023, 2, 'NDP_1', 124, 1, 102),
         (2023, 3, 'NDP_1', 124, 1, 103))
SELECT
  *,
  sum(valeur_tnd) OVER (PARTITION BY year, ndp, code_pays
                        ORDER BY year, month_num
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS valeur_ytd
FROM
  t;

valeur_tnd* 的值使得 ytd_valeur_tnd 将是一些分组列的编码;即，〈2位数年份〉〈2位数月份〉〈2位数月份号之和〉。

赞(0）回复(0）举报 2023-04-11

我来回答

postgresql 如何计算SQL中每组行的列的累积和？

1条答案

相关问题

热门标签

最新问答