分组转置后取最大日期并筛选最近90天关联数据的SQL方案

需求明确 #

• 分组规则：按ProjectID分组，只要组内任意一个ActivityCode的DTTM在最近90天内，就提取该组的全部关联数据
• 数据转置：根据ActivityCode将行数据转为列，对应生成CustomerID、InvoiceNo、CategoryColor、Size、Quantity字段
• 日期处理：取每组的最大DTTM作为该行的报表日期ReportDate

示例数据表结构 #

原始数据表 #

CREATE TABLE original_data (
    ProjectID VARCHAR(50),
    ActivityCode VARCHAR(50), -- 对应需要转置的字段类型，如'CUST_ID'/'INV_NO'等
    Value VARCHAR(255), -- 存储各ActivityCode对应的值
    DTTM DATETIME -- 数据录入日期
);

目标结果表 #

CREATE TABLE target_report (
    ProjectID VARCHAR(50),
    ReportDate DATETIME,
    CustomerID VARCHAR(255),
    InvoiceNo VARCHAR(255),
    CategoryColor VARCHAR(255),
    Size VARCHAR(255),
    Quantity VARCHAR(255)
);

当前SQL的核心问题 #

如果直接在WHERE子句中筛选DTTM在最近90天，会漏掉同一ProjectID下延迟录入的关联数据，导致分组数据不完整。

优化后的SQL代码 #

WITH valid_projects AS (
    -- 先筛选出符合条件的ProjectID：组内至少有一条数据的DTTM在最近90天内
    SELECT DISTINCT ProjectID
    FROM original_data
    WHERE DTTM >= DATEADD(DAY, -90, GETDATE())
)
SELECT 
    od.ProjectID,
    MAX(od.DTTM) AS ReportDate,
    -- 按ActivityCode转置列
    MAX(CASE WHEN od.ActivityCode = 'CUST_ID' THEN od.Value END) AS CustomerID,
    MAX(CASE WHEN od.ActivityCode = 'INV_NO' THEN od.Value END) AS InvoiceNo,
    MAX(CASE WHEN od.ActivityCode = 'CAT_COLOR' THEN od.Value END) AS CategoryColor,
    MAX(CASE WHEN od.ActivityCode = 'SIZE' THEN od.Value END) AS Size,
    MAX(CASE WHEN od.ActivityCode = 'QTY' THEN od.Value END) AS Quantity
FROM original_data od
-- 关联有效ProjectID，获取全部分组数据
INNER JOIN valid_projects vp ON od.ProjectID = vp.ProjectID
GROUP BY od.ProjectID;

优化说明 #

1. 筛选逻辑修正：通过CTE先锁定符合条件的ProjectID，再关联原始表获取全部分组数据，彻底避免遗漏延迟录入的关联行
2. 性能优化：使用DTTM >= DATEADD(DAY, -90, GETDATE())替代DATEDIFF计算，若DTTM列存在索引，可触发索引扫描，大幅提升查询效率
3. 索引建议：若数据量较大，建议给original_data表建立复合索引(ProjectID, DTTM)，进一步加速CTE的筛选和后续分组操作
4. 转列逻辑：采用MAX(CASE...)的方式完成行转列，是SQL中高效且通用的转置方案，确保每个ActivityCode对应的值被正确提取

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