如何实现每日数据关联上月最后一日对应值的SQL查询？

我需要汇总每日数据，并将每条数据与上月最后一日的对应值关联计算（比如查询2022年2月3日数据时，关联2022年1月31日的值）。

当前基础查询 #

我现在用以下语句获取每日汇总数据：

SELECT 
    TGL, SUM(A.NOM_IDR) AS TotValue
FROM(
    SELECT 
        B.DATE AS TGL
        , (A.NOMINAL_IDR * -1) as NOM_IDR
    FROM 
        MIS.FACT_LOAN A
    LEFT OUTER JOIN 
        MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
    WHERE 
        YEAR(B.DATE) = '2022'
    )
GROUP BY TGL;

查询结果示例：

TGL        | TotValue 
2022-01-31    300000      
2022-02-01    400000      
2022-02-02    200000 
...
2022-02-26    370000 
2022-03-01    250000

已尝试的操作 #

我已经写出了获取每月最后一日及其对应TotValue的查询：

SELECT
        B.DATE AS LASTDAYPERMONTH
        , SUM(A.NOMINAL_IDR * -1) AS LastMonthValue
     FROM MIS.FACT_LOAN A
     LEFT JOIN 
        MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
     INNER JOIN
        (SELECT MAX(B.DATE) AS MaxDatePerMonth
         FROM MIS.FACT_LOAN A
         LEFT JOIN 
            MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
         WHERE YEAR(B.DATE) = '2022'
         GROUP BY MONTH(B.DATE)
        ) aa ON aa.MaxDatePerMonth = B.DATE
     WHERE YEAR(B.DATE) = '2022'
     GROUP BY B.DATE

但不知道如何将其与基础查询关联，得到如下目标结果：

TGL        | TotValue | LastMonthValue
2022-01-31    300000         0
2022-02-01    400000      300000
2022-02-02    200000      300000
...
2022-02-26    370000      300000
2022-03-01    250000      370000

解决方案 #

可以用CTE（公共表表达式）先分别计算每日汇总和每月最后一日汇总，再通过日期的年月关联匹配上月最后一日的值：

WITH DailySummary AS (
    -- 基础每日汇总查询，简化嵌套结构
    SELECT 
        B.DATE AS TGL, 
        SUM(A.NOMINAL_IDR * -1) AS TotValue
    FROM MIS.FACT_LOAN A
    LEFT OUTER JOIN MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
    WHERE YEAR(B.DATE) = '2022'
    GROUP BY B.DATE
),
MonthEndSummary AS (
    -- 每月最后一日的汇总数据，提取年月用于关联
    SELECT
        B.DATE AS LastDayOfMonth,
        SUM(A.NOMINAL_IDR * -1) AS LastMonthValue,
        YEAR(B.DATE) AS Year,
        MONTH(B.DATE) AS Month
    FROM MIS.FACT_LOAN A
    LEFT JOIN MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
    INNER JOIN (
        SELECT 
            MAX(DATE) AS MaxDatePerMonth,
            YEAR(DATE) AS Year,
            MONTH(DATE) AS Month
        FROM MIS.DIM_PERIOD
        WHERE YEAR(DATE) = '2022'
        GROUP BY YEAR(DATE), MONTH(DATE)
    ) aa ON aa.MaxDatePerMonth = B.DATE
    WHERE YEAR(B.DATE) = '2022'
    GROUP BY B.DATE, YEAR(B.DATE), MONTH(B.DATE)
)
-- 关联每日数据与上月月末数据
SELECT
    ds.TGL,
    ds.TotValue,
    COALESCE(mes.LastMonthValue, 0) AS LastMonthValue
FROM DailySummary ds
LEFT JOIN MonthEndSummary mes 
    ON mes.Year = YEAR(DATEADD(MONTH, -1, ds.TGL))
    AND mes.Month = MONTH(DATEADD(MONTH, -1, ds.TGL));

适配不同SQL方言的日期处理 #

如果你的数据库不支持DATEADD，可以替换为对应函数：

• PostgreSQL: YEAR(ds.TGL - INTERVAL '1 month') 和 MONTH(ds.TGL - INTERVAL '1 month')
• MySQL: YEAR(DATE_SUB(ds.TGL, INTERVAL 1 MONTH)) 和 MONTH(DATE_SUB(ds.TGL, INTERVAL 1 MONTH))

内容的提问来源于stack exchange，提问作者H. D. U.