如何统计上月数据？窗口函数实现时返回值与当月一致问题排查

原写法错误原因 #

你之前的逻辑无法得到正确结果核心有两个问题：

• 窗口函数的PARTITION BY只会将当前查询结果集内分区键取值相同的行分到同一组计算。你给2021年2月的行设置的分区键是2021-01，但2021年1月的真实数据行的分区键是2020-12，两类行不会被划分到同一个分区，所以统计出来的数值只能是当前月的行数。
• 你的查询GROUP BY子句包含了唯一主键crime_id，相当于没有做聚合，每行都是单条案件记录，叠加DISTINCT也完全没有作用。

正确实现方案 #

符合「禁止SELECT子句子查询、禁止关联子查询」要求的方案可以用聚合+LAG偏移窗口函数实现：
先按犯罪分类、月份聚合得到当月案件量，再通过按分类分区、月份排序的LAG函数取上一行的当月值，即为当前月对应的上月案件量。

中文别名版本查询语句

SELECT DISTINCT
    crimes.category AS "犯罪分类",
    crimes.month AS "统计月份",
    COALESCE(LAG(COUNT(crimes.crime_id), 1) OVER (PARTITION BY crimes.category ORDER BY crimes.month ASC), 0) AS "上月案件数",
    COUNT(crimes.crime_id) OVER (PARTITION BY crimes.category, crimes.month) AS "当月案件数"
FROM crimes
WHERE crimes.month >= :起始月份 AND crimes.month <= :结束月份
GROUP BY crimes.category, crimes.month
ORDER BY crimes.category, crimes.month ASC;

原英文别名版本查询语句

SELECT DISTINCT
    crimes.category AS "Crime category",
    crimes.month AS "Month",
    COALESCE(LAG(COUNT(crimes.crime_id), 1) OVER (PARTITION BY crimes.category ORDER BY crimes.month ASC), 0) AS "Previous month crimes",
    COUNT(crimes.crime_id) OVER (PARTITION BY crimes.category, crimes.month) AS "Current month crimes"
FROM crimes
WHERE crimes.month >= :start_month AND crimes.month <= :end_month
GROUP BY crimes.category, crimes.month
ORDER BY crimes.category, crimes.month ASC;

逻辑说明 #

• 首先按category（犯罪分类）和month（月份）做分组，得到每个分类对应每个月份的分组结果
• 嵌套窗口函数COUNT(crimes.crime_id)先计算当前分类当前月的案件总数
• 再用LAG函数偏移1位，取同分类下上一个月的计数值，就是当前月对应的上月案件数
• COALESCE是用来处理时间范围内第一个月没有上月数据的情况，默认返回0，不需要可以直接去掉该函数包裹。

内容的提问来源于stack exchange，提问作者Aliaksei A. Shysh