T-SQL实现往期记录补全：季度行动报表实时视图需求

问题背景 #

现有季度行动报表表（假设表名为quarterly_actions），分支机构按季度上报行动进度或新增行动，但存在问题：当行动状态变为ready后，该行动不会出现在后续季度的上报数据中（比如Q1的行动2、Q2的行动3）。业务要求每个季度的报表必须包含所有行动，包括往期已完成行动的最后上报数据，且需实时数据，只能通过视图实现，不能用存储过程。

原始数据 #

ReportingPeriod   Company   ActionId  Action                Budget    State
2022-03           Alpha     1         Cleaner Produktion    10        prepared
2022-03           Alpha     2         New Backbone           5        ready
2022-03           Alpha     3         IT Security           20        ongoing

2022-06           Alpha     1         Cleaner Produktion    12        prepared
2022-06           Alpha     3         IT Security           20        ready
2022-06           Alpha     4         New Office chairs      2        prepared

2022-09           Alpha     1         Cleaner Produktion    12        prepared
2022-09           Alpha     4         New Office chairs      2        ongoing

期望输出 #

ReportingPeriod   Company   ActionId   Action              Budget   State
2022-03           Alpha     1          Cleaner Produktion  10       prepared
2022-03           Alpha     2          New Backbone         5       ready
2022-03           Alpha     3          IT Security         20       ongoing
2022-06           Alpha     1          Cleaner Produktion  12       prepared
2022-06           Alpha     2          New Backbone         5       ready
2022-06           Alpha     3          IT Security         20       ready
2022-06           Alpha     4          New Office chairs    2       prepared
2022-09           Alpha     1          Cleaner Produktion  12       prepared
2022-09           Alpha     2          New Backbone         5       ready
2022-09           Alpha     3          IT Security         20       ready
2022-09           Alpha     4          New Office chairs    2       ongoing

视图实现方案 #

以下SQL适用于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等），核心思路是先生成所有季度与所有行动的组合，再为每个组合匹配截至该季度的最新上报记录：

CREATE VIEW complete_quarterly_actions AS
WITH all_periods_actions AS (
    -- 生成所有存在的季度与所有存在的行动（按公司分组）的笛卡尔积
    SELECT 
        p.ReportingPeriod,
        a.Company,
        a.ActionId,
        a.Action
    FROM (SELECT DISTINCT ReportingPeriod FROM quarterly_actions) p
    CROSS JOIN (SELECT DISTINCT Company, ActionId, Action FROM quarterly_actions) a
),
latest_records AS (
    -- 为每个行动标记截至各季度的最新上报记录
    SELECT 
        p.ReportingPeriod,
        p.Company,
        p.ActionId,
        p.Action,
        q.Budget,
        q.State,
        ROW_NUMBER() OVER (
            PARTITION BY p.Company, p.ActionId, p.ReportingPeriod
            ORDER BY q.ReportingPeriod DESC
        ) AS rn
    FROM all_periods_actions p
    LEFT JOIN quarterly_actions q 
        ON q.Company = p.Company 
        AND q.ActionId = p.ActionId 
        AND q.ReportingPeriod <= p.ReportingPeriod
)
SELECT 
    ReportingPeriod,
    Company,
    ActionId,
    Action,
    Budget,
    State
FROM latest_records
WHERE rn = 1
ORDER BY ReportingPeriod, ActionId;

方案说明 #

1. all_periods_actions CTE：提取所有唯一的季度和所有唯一的行动信息（按公司分组），通过交叉连接得到每个季度对应所有行动的基础组合。
2. latest_records CTE：将基础组合与原表关联，筛选出每个行动在当前季度及之前的所有上报记录，用窗口函数ROW_NUMBER()按上报时间倒序排序，标记出每个(季度,公司,行动)组合的最新记录。
3. 最后筛选出标记为rn=1的最新记录，即为每个季度该行动的最新状态数据，包含已完成的ready行动。

这个视图会实时反映原表的数据变化，满足实时报表的需求。

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