如何实现按用户、ID_REW、POC分组查询上月及去年同月Points总和？

你之前用CTE+LAG函数遇到的问题，核心是LAG是基于现有数据行的偏移，而不是实际的时间月份偏移——如果某个分组中间有月份没有数据，LAG(1)会拿到上一条存在的记录，而不是真正的上月数据。下面提供两种可行方案，适配不同需求场景：

方案1：自连接（高效，仅处理已有数据行） #

直接通过日期计算关联上月、去年同月的分组总和，无需补全缺失月份，没有对应数据时自动返回NULL。

通用SQL示例（适用于PostgreSQL、SQL Server等） #

WITH monthly_totals AS (
    -- 先按用户、ID_REW、POC、月份分组计算Points总和
    SELECT
        "user",
        id_rew,
        poc_id,
        DATE_TRUNC('month', transaction_date) AS month_start, -- 取月份起始日期（如2024-05-01）
        SUM(points) AS monthly_points
    FROM your_table_name
    GROUP BY "user", id_rew, poc_id, DATE_TRUNC('month', transaction_date)
)
SELECT
    curr."user",
    curr.id_rew,
    curr.poc_id,
    curr.month_start,
    curr.monthly_points,
    prev.monthly_points AS prev_month, -- 上月总和
    prev_year.monthly_points AS prev_year -- 去年同月总和
FROM monthly_totals curr
-- 关联上月的分组数据
LEFT JOIN monthly_totals prev
    ON curr."user" = prev."user"
    AND curr.id_rew = prev.id_rew
    AND curr.poc_id = prev.poc_id
    AND curr.month_start = prev.month_start + INTERVAL '1 month'
-- 关联去年同月的分组数据
LEFT JOIN monthly_totals prev_year
    ON curr."user" = prev_year."user"
    AND curr.id_rew = prev_year.id_rew
    AND curr.poc_id = prev_year.poc_id
    AND curr.month_start = prev_year.month_start + INTERVAL '12 months'
ORDER BY curr."user", curr.id_rew, curr.poc_id, curr.month_start;

MySQL适配版 #

MySQL的日期函数略有不同，调整如下：

WITH monthly_totals AS (
    SELECT
        `user`,
        id_rew,
        poc_id,
        DATE_FORMAT(transaction_date, '%Y-%m-01') AS month_start,
        SUM(points) AS monthly_points
    FROM your_table_name
    GROUP BY `user`, id_rew, poc_id, DATE_FORMAT(transaction_date, '%Y-%m-01')
)
SELECT
    curr.`user`,
    curr.id_rew,
    curr.poc_id,
    curr.month_start,
    curr.monthly_points,
    prev.monthly_points AS prev_month,
    prev_year.monthly_points AS prev_year
FROM monthly_totals curr
LEFT JOIN monthly_totals prev
    ON curr.`user` = prev.`user`
    AND curr.id_rew = prev.id_rew
    AND curr.poc_id = prev.poc_id
    AND curr.month_start = DATE_ADD(prev.month_start, INTERVAL 1 MONTH)
LEFT JOIN monthly_totals prev_year
    ON curr.`user` = prev_year.`user`
    AND curr.id_rew = prev_year.id_rew
    AND curr.poc_id = prev_year.poc_id
    AND curr.month_start = DATE_ADD(prev_year.month_start, INTERVAL 12 MONTH)
ORDER BY curr.`user`, curr.id_rew, curr.poc_id, curr.month_start;

方案2：补全日期维度（展示所有月份，包括无数据的） #

如果需要展示每个分组的所有连续月份（哪怕该月份没有数据），可以先构建日期维度表，补全缺失的月份记录，再用LAG函数计算偏移。

PostgreSQL示例 #

WITH monthly_totals AS (
    SELECT
        "user",
        id_rew,
        poc_id,
        DATE_TRUNC('month', transaction_date) AS month_start,
        SUM(points) AS monthly_points
    FROM your_table_name
    GROUP BY "user", id_rew, poc_id, DATE_TRUNC('month', transaction_date)
),
-- 生成覆盖数据时间范围的所有月份
date_dim AS (
    SELECT generate_series(
        (SELECT MIN(month_start) FROM monthly_totals),
        (SELECT MAX(month_start) FROM monthly_totals),
        INTERVAL '1 month'
    ) AS month_start
),
-- 补全每个分组的所有月份记录，无数据则monthly_points为NULL
filled_monthly AS (
    SELECT
        groups."user",
        groups.id_rew,
        groups.poc_id,
        dd.month_start,
        mt.monthly_points
    FROM date_dim dd
    -- 生成所有用户-rew-poc的唯一组合
    CROSS JOIN (SELECT DISTINCT "user", id_rew, poc_id FROM monthly_totals) AS groups
    LEFT JOIN monthly_totals mt
        ON dd.month_start = mt.month_start
        AND groups."user" = mt."user"
        AND groups.id_rew = mt.id_rew
        AND groups.poc_id = mt.poc_id
)
-- 用LAG计算上月、去年同月数据（此时月份是连续的，偏移准确）
SELECT
    "user",
    id_rew,
    poc_id,
    month_start,
    monthly_points,
    LAG(monthly_points) OVER (
        PARTITION BY "user", id_rew, poc_id ORDER BY month_start
    ) AS prev_month,
    LAG(monthly_points, 12) OVER (
        PARTITION BY "user", id_rew, poc_id ORDER BY month_start
    ) AS prev_year
FROM filled_monthly
ORDER BY "user", id_rew, poc_id, month_start;

关键说明 #

• 自连接方案更高效，适合只需要现有数据行、同时获取对应上月/去年同月数据的场景；
• 日期维度补全方案适合需要展示完整时间序列（包括无数据月份）的报表场景；
• 注意替换your_table_name为实际表名，points为实际存储积分的字段名（你描述中的rw可能是该字段的缩写）。

内容的提问来源于stack exchange，提问作者Alan Iza