SQL Server：按存储层的文件大小每日聚合（含创建/变更/删除日期）

生产环境中有一张包含数千万行的文件存储元数据事实表，结构不可修改，需要基于它生成每日快照表，展示每个日期各存储层的文件总数和总大小。

复现用表结构与数据 #

DROP TABLE IF EXISTS fileStorageData;
GO

CREATE TABLE fileStorageData (
    FileId             INT,
    StorageTier        INT,
    UtcCreateDate      INT,   -- YYYYMMDD格式，UTC创建日期
    UtcTierChangedDate INT,   -- YYYYMMDD格式，存储层生效起始日期；-1表示从未变更存储层
    UtcDeletedDate     INT,   -- YYYYMMDD格式，文件删除日期；-1表示文件仍存在
    SizeBytes          BIGINT -- 文件固定大小（字节）
);

CREATE UNIQUE INDEX fileStorageData_U01
ON fileStorageData (
    FileId,
    StorageTier,
    UtcCreateDate,
    UtcTierChangedDate,
    UtcDeletedDate
);

INSERT INTO fileStorageData VALUES
    (1,  1, 20210128, -1,        -1,        4784),
    (1,  2, 20210128, 20210201,  -1,        4784),
    (1,  5, 20210128, 20210601,  -1,        4784),
    (1,  2, 20210128, -1,        20250101,  4784),

    (15, 1, 20210128, -1,        -1,        9862),
    (15, 2, 20210128, 20230201,  -1,        9862),
    (15, 5, 20210128, 20240601,  -1,        9862),
    (15, 2, 20210128, -1,        20250101,  9862);

列语义 #

• 同一个FileId可因存储层迁移多次出现
• UtcCreateDate：文件创建日期
• UtcTierChangedDate：该存储层的生效起始日期，-1表示从未变更过存储层（即原始层）
• UtcDeletedDate：文件删除日期，-1表示文件仍存在
• 所有日期为日粒度UTC日期，无时间信息
• SizeBytes：每个文件的固定大小，不会随存储层变更改变

需求 #

生成无参数的每日结果表，结构如下：

DateKey    StorageTier   FileCount   TotalBytes
---------  ------------  ----------  -----------
20210408   1             1000        1234567
20210408   2             2000        3456789
20210408   5             300         4567890

需展示每个日期的：

• 存储层级
• 该存储层当日的文件总数
• 该存储层当日的文件总大小（字节）

业务规则 #

• 每个文件每日仅归属一个存储层
• 若UtcTierChangedDate = -1，文件始终保留在原始存储层
• 若存在存储层变更，取生效日期≤当日的最新存储层
• 文件在删除当日及之后不再计入统计
• 基础表结构不可修改
• 可使用标准日历/日期表

已尝试方案及问题 #

曾尝试以下方法，但均存在问题：

• 窗口函数（LEAD()、按FileId分区的ROW_NUMBER()）：出现文件重复统计的情况
• 用LEAD()构建层变更时间区间历史：丢失部分层变更记录
• 直接关联日期维度表展开行数据：日粒度下逻辑梳理困难，性能不佳

解决方案 #

假设存在标准日历表Calendar，包含DateKey字段（INT类型，格式为YYYYMMDD），以下SQL可高效生成符合要求的每日快照：

WITH FileTierHistory AS (
    SELECT
        FileId,
        StorageTier,
        SizeBytes,
        UtcCreateDate,
        -- 确定当前存储层的生效起始日期
        CASE WHEN UtcTierChangedDate = -1 THEN UtcCreateDate ELSE UtcTierChangedDate END AS TierStartDate,
        -- 获取下一个存储层的生效日期，作为当前层的结束参考；无后续变更则用99991231表示永久有效
        LEAD(CASE WHEN UtcTierChangedDate = -1 THEN UtcCreateDate ELSE UtcTierChangedDate END, 1, 99991231) 
            OVER (PARTITION BY FileId ORDER BY CASE WHEN UtcTierChangedDate = -1 THEN UtcCreateDate ELSE UtcTierChangedDate END) AS NextTierStartDate,
        UtcDeletedDate
    FROM fileStorageData
),
ValidTierIntervals AS (
    SELECT
        FileId,
        StorageTier,
        SizeBytes,
        TierStartDate,
        -- 计算当前存储层的有效结束日期：取下一层生效前一天、删除日期前一天的较小值；未删除则用下一层生效前一天
        CASE
            WHEN UtcDeletedDate = -1 THEN NextTierStartDate - 1
            ELSE LEAST(NextTierStartDate - 1, UtcDeletedDate - 1)
        END AS TierEndDate
    FROM FileTierHistory
    -- 过滤无效区间（起始日期不能晚于结束日期）
    WHERE TierStartDate <= CASE
            WHEN UtcDeletedDate = -1 THEN NextTierStartDate - 1
            ELSE LEAST(NextTierStartDate - 1, UtcDeletedDate - 1)
        END
)
-- 关联日历表统计每日数据
SELECT
    c.DateKey,
    v.StorageTier,
    COUNT(DISTINCT v.FileId) AS FileCount,
    SUM(v.SizeBytes) AS TotalBytes
FROM Calendar c
JOIN ValidTierIntervals v
    ON c.DateKey BETWEEN v.TierStartDate AND v.TierEndDate
GROUP BY c.DateKey, v.StorageTier
ORDER BY c.DateKey, v.StorageTier;

方案逻辑说明 #

1. FileTierHistory CTE：为每个文件的每个存储层记录，明确其生效起始日期，并通过LEAD()函数获取下一个存储层的生效日期，作为当前层有效周期的结束参考点。
2. ValidTierIntervals CTE：整理出每个存储层的有效时间区间，同时结合文件删除日期限制——文件在删除当日及之后不再统计，因此结束日期取「下一层生效前一天」和「删除日期前一天」的较小值，过滤掉无效的时间区间。
3. 最终统计：关联日历表，将每个有效时间区间与对应日期匹配，按日期和存储层分组统计文件数（用COUNT(DISTINCT)确保每个文件每日仅统计一次）和总大小。

性能优化提示 #

• 利用现有唯一索引fileStorageData_U01，该索引包含FileId和UtcTierChangedDate，可加速PARTITION BY FileId后的排序操作。
• 确保日历表Calendar仅包含需要统计的日期范围（如从最早的文件创建日期到当前日期），避免不必要的关联。

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