如何在BigQuery中按年度累计计算各公司月度HEADCOUNT？

现有TEST_TABLE表存储各公司每月最后一天的员工数，表结构及示例数据如下：

需要生成新表，包含截至当月的年度累计员工数（HEADCOUNT_TO_DATE），期望结果如下：

尝试了以下SQL语句，但仅返回各月员工数，未实现累计：

SELECT 
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(CASE WHEN LAST_DAY_MONTH BETWEEN DATE_TRUNC(LAST_DAY_MONTH, year) AND LAST_DAY_MONTH THEN HEADCOUNT END)
FROM `TEST_TABLE`
GROUP BY 1,2

补充要求：仅累计同一年度的员工数，例如2022-12-31需累计2022全年12个月的数据，2023-01-31仅累计2023年1月的数据。

你原来的SQL使用普通SUM()加分组，只会计算当前行的HEADCOUNT（CASE条件的范围仅包含当前行日期），无法实现累计。要实现年度内的累计求和，需要用窗口函数，按公司和年度分区，再按月份排序计算累计和。

以下是正确的SQL语句（以BigQuery为例，其他数据库语法类似，仅日期函数可能略有差异）：

SELECT
  LAST_DAY_MONTH,
  COMPANNY,
  SUM(HEADCOUNT) OVER (
    PARTITION BY COMPANNY, EXTRACT(YEAR FROM LAST_DAY_MONTH)
    ORDER BY LAST_DAY_MONTH
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS HEADCOUNT_TO_DATE
FROM `TEST_TABLE`
ORDER BY COMPANNY, LAST_DAY_MONTH;

代码说明： #

• PARTITION BY COMPANNY, EXTRACT(YEAR FROM LAST_DAY_MONTH)：按公司和年份分区，确保累计仅在同公司同年度内进行。
• ORDER BY LAST_DAY_MONTH：按日期排序，保证累计顺序是从年初到当前月。
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：指定累计范围为分区的第一行到当前行，部分数据库可省略此句（默认即为该范围）。

如果你的数据库不支持EXTRACT(YEAR FROM ...)，可替换为对应日期函数：比如MySQL用YEAR(LAST_DAY_MONTH)，SQL Server用DATEPART(YEAR, LAST_DAY_MONTH)。

内容的提问来源于stack exchange，提问作者Mark Wekking