如何关联sales与report_dates表，按报告日期生成累计金额报表？

现有两张业务表：sales表和report_dates表，结构与数据如下：

sales表 #

report_dates表 #

需求是按report_dates中的日期计算累计金额，且无需包含早于销售日期的报告日期。目前已实现按payment_date的月末日期计算累计金额的SQL如下：

SELECT
    sales.id  
    ,EOMONTH(sales.payment_date)  
    ,sales.colour
    ,SUM(sales.amount) OVER (PARTITION BY sales.id, sales.colour ORDER BY EOMONTH(sales.payment_date)) 
FROM sales
ORDER BY pl.AGREEMENT_RK, pl.OPER_DATE

需要将该逻辑与report_dates表关联，生成如下指定格式的报表：

目标报表格式 #

以下是适配需求的SQL语句（以SQL Server为例，EOMONTH函数适用）：

WITH sales_monthly AS (
    -- 计算每个id-colour组合到各月末的累计金额
    SELECT 
        id,
        colour,
        EOMONTH(payment_date) AS month_end,
        SUM(amount) OVER (PARTITION BY id, colour ORDER BY EOMONTH(payment_date)) AS cum_amount
    FROM sales
),
id_colour_list AS (
    -- 获取所有唯一的id-colour组合
    SELECT DISTINCT id, colour FROM sales
)
-- 关联报告日期，计算每个报告日期对应的累计金额
SELECT 
    ic.id,
    ic.colour,
    rd.date AS report_date,
    -- 取当前报告日期之前的最大累计金额，即截至该日期的累计值
    MAX(COALESCE(sm.cum_amount, 0)) AS cum_amount
FROM id_colour_list ic
CROSS JOIN report_dates rd
LEFT JOIN sales_monthly sm 
    ON ic.id = sm.id 
    AND ic.colour = sm.colour
    AND sm.month_end <= rd.date
-- 过滤掉早于该id-colour最早销售月末的报告日期
WHERE rd.date >= (SELECT MIN(EOMONTH(payment_date)) FROM sales WHERE id = ic.id AND colour = ic.colour)
GROUP BY ic.id, ic.colour, rd.date
ORDER BY ic.id, ic.colour, rd.date;

逻辑说明 #

1. sales_monthly CTE：延续你原有的逻辑，计算每个id-colour组合到每个付款月末的累计金额；
2. id_colour_list CTE：提取所有需要展示的id-colour唯一组合，确保每个组合都能匹配到符合条件的报告日期；
3. 主查询用CROSS JOIN生成id-colour与报告日期的全量组合，再通过左连接关联月度累计数据；
4. 用MAX(COALESCE(...))获取当前报告日期之前的最大累计金额，自动填充后续无新增销售的日期的累计值；
5. WHERE子句过滤掉早于该id-colour最早销售月末的报告日期，满足“无需包含早于销售日期的报告日期”的要求。

内容的提问来源于stack exchange，提问作者Aleksandr Veselov