SQL如何计算每个季度的累计利润_窗口函数时间分区-SQL-PHP中文网

窗口函数计算季度累计利润时必须显式指定ORDER BY时间字段并配合PARTITION BY年份，先聚合再开窗，且需确保时间字段可排序、无歧义。

sql如何计算每个季度的累计利润_窗口函数时间分区

窗口函数必须按时间顺序排序，否则累计值会错乱

用 SUM() OVER() 计算季度累计利润时，数据库不会自动按时间排序。哪怕你的原始数据是按日期排好的，窗口函数仍可能打乱顺序，导致 SUM 累加路径错误——比如把 Q4 的利润加到 Q1 前面。

正确做法是在 ORDER BY 子句中显式指定时间字段（如 order_date 或 quarter_start），且该字段必须能唯一、稳定地反映季度先后关系：

SELECT
  quarter,
  profit,
  SUM(profit) OVER (ORDER BY quarter ROWS UNBOUNDED PRECEDING) AS cum_profit
FROM quarterly_profit;

注意：quarter 字段要是字符串（如 '2023-Q1'）或可排序的日期型（如 DATE '2023-01-01'）。如果存成 INT 类型的 202301，也能正常排序；但存成无序编号（如 1, 2, 3, 4）且没关联年份，就会跨年混乱。

季度分组不能只靠 GROUP BY，得用 PARTITION BY 控制累计范围

如果想“每个年度内单独累计”，而不是全量历史累计，PARTITION BY 就不能漏。常见错误是只写 ORDER BY quarter，结果 2022-Q4 的利润被加进 2023-Q1 的累计值里。

按自然年分隔累计，需从原始日期提取年份字段（推荐用 EXTRACT(YEAR FROM ...) 或 YEAR(...)，依数据库而定）：

SELECT
  EXTRACT(YEAR FROM order_date) AS year,
  CONCAT(EXTRACT(YEAR FROM order_date), '-Q', EXTRACT(QUARTER FROM order_date)) AS quarter,
  profit,
  SUM(profit) OVER (
    PARTITION BY EXTRACT(YEAR FROM order_date)
    ORDER BY EXTRACT(QUARTER FROM order_date)
    ROWS UNBOUNDED PRECEDING
  ) AS cum_profit_per_year
FROM sales;

PARTITION BY 决定“重置点”：每年开头累计值归零
ORDER BY 决定“累加方向”：必须是季度编号（1→4），不能是随机别名
若用字符串拼接的 quarter（如 '2023-Q1'），直接 ORDER BY quarter 也可，但要确保格式统一、无空格、零填充（'2023-Q01' 比 '2023-Q1' 更安全）

ROWS UNBOUNDED PRECEDING 是默认行为，但显式写出更可靠

有些数据库（如 PostgreSQL）在未指定 ROWS 或 RANGE 时，默认使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，但 MySQL 8.0+ 和 SQL Server 要求显式声明，否则报错 Window frame 'rows between unbounded preceding and current row' is required for this function。

所以别省略 ROWS UNBOUNDED PRECEDING，尤其当你要兼容多个数据库或未来迁移时：

✅ 正确：SUM(profit) OVER (PARTITION BY year ORDER BY qtr ROWS UNBOUNDED PRECEDING)
❌ 风险：SUM(profit) OVER (PARTITION BY year ORDER BY qtr) —— 在部分引擎下不报错但逻辑隐晦，易被误读为 RANGE 行为
⚠️ 注意：RANGE UNBOUNDED PRECEDING 在季度字段上等价于 ROWS（因为季度值离散、无重复），但一旦 ORDER BY 字段有重复（比如多笔同季度利润没做聚合），RANGE 会把所有同值行一起纳入当前行窗口，造成重复累加

先聚合再开窗，避免同一季度多行导致累计失真

原始销售表通常按订单粒度存储，一个季度可能有几百条记录。如果直接在明细行上开窗，SUM() OVER() 会逐行累加，导致“Q1累计”出现多个不同值（第一单、第二单……最后一单），而不是你想要的“Q1总利润 → Q1+Q2总利润 → ……”。

必须先按季度聚合，再对聚合结果开窗：

WITH quarterly AS (
  SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(QUARTER FROM order_date) AS qtr,
    SUM(profit) AS quarter_profit
  FROM sales
  GROUP BY 1, 2
)
SELECT
  year,
  qtr,
  quarter_profit,
  SUM(quarter_profit) OVER (
    PARTITION BY year
    ORDER BY qtr
    ROWS UNBOUNDED PRECEDING
  ) AS cum_profit
FROM quarterly;

这个两步结构不能跳过。有人试图用子查询嵌套 SUM(SUM()) OVER()，语法上可行但可读性差，且容易在 GROUP BY 和窗口逻辑间产生歧义。

真正麻烦的是时间字段缺失或不规范：比如只有 sale_month（整数 1–12）没年份，或 quarter 是文本但混着 'Q1 2023' 和 '2023 Q1'。这种脏数据必须前置清洗，窗口函数救不了格式混乱的时间维度。