SQL Server 如何按创建、档位变更和删除日期汇总每日文件数量与大小？

可以先将每个文件的档位历史整理成有效区间，再转换为每日增减量，按档位累计。这样每个文件每天只计入一个档位，也不用把数千万条文件记录逐日展开为“文件 × 日期”明细。

示例数据中，UtcTierChangedDate = -1 同时出现在初始记录和删除记录中。这里按以下口径处理：UtcTierChangedDate = -1 AND UtcDeletedDate = -1 的记录提供初始档位；带有效变更日期的记录提供档位变更；删除日期从同一文件的所有记录中提取。

先确认删除记录的含义

以 FileId = 1 为例：

初始档位：1，创建于 20210128
变更档位：2，生效于 20210201
变更档位：5，生效于 20210601
删除日期：20250101

对应的有效区间为：

[2021-01-28, 2021-02-01) → StorageTier 1
[2021-02-01, 2021-06-01) → StorageTier 2
[2021-06-01, 2025-01-01) → StorageTier 5

区间采用左闭右开形式 [StartDate, EndDate)：变更当天归入新档位，删除当天不再计入。

删除记录中的 StorageTier = 2 没有对应的有效变更日期，不能凭这一行补出一次从档位 5 到档位 2 的变更。如果该字段确实表示删除前的最终档位，现有数据就缺少这次变更的生效日期，无法准确恢复那段每日历史。

历史记录中的 UtcDeletedDate = -1 也不能单独证明文件仍然存在，因为同一 FileId 的其他行可能已经记录了删除日期。需要先汇总到文件层面。

将有效区间转换为每日增减量

假设某文件大小为 4784，在档位 2 的有效区间为 [2021-02-01, 2021-06-01)，可以生成两条事件：

2021-02-01：档位 2，文件数 +1，字节数 +4784
2021-06-01：档位 2，文件数 -1，字节数 -4784

先汇总同一天、同一档位的增减量，再按日期累计，就能得到每日存量。档位切换当天，旧档位减少，新档位增加；删除当天，最后一个档位减少。

SQL 实现

以下查询不需要外部参数，默认输出从最早创建日至当前 UTC 日期的每日结果，并为出现过的档位补零。

假设已有日历表 dbo.Calendar，其中 CalendarDate 为 date 类型，每天恰好一行，且覆盖整个统计区间。使用时需要替换为实际的表名和列名。

数据还需要满足以下条件：同一文件的创建日期、大小和删除日期一致；变更日期不早于创建日期；每个文件都保留了可识别的初始档位。查询会拒绝初始档位不唯一、同一天存在多个不同变更档位的情况。

DROP TABLE IF EXISTS #Files, #TierEvents, #DailyDeltas;

DECLARE @Today date = CONVERT(date, GETUTCDATE());
DECLARE @EndExclusive date = DATEADD(day, 1, @Today);

-- 文件层面的创建日期、删除日期、大小和初始档位。
SELECT
    FileId,
    CONVERT(date, CONVERT(char(8), MIN(UtcCreateDate)), 112)
        AS CreateDate,
    CONVERT(
        date,
        CONVERT(char(8), MIN(
            CASE WHEN UtcDeletedDate <> -1 THEN UtcDeletedDate END
        )),
        112
    ) AS DeletedDate,
    MAX(SizeBytes) AS SizeBytes,
    MIN(CASE
        WHEN UtcTierChangedDate = -1 AND UtcDeletedDate = -1
        THEN StorageTier
    END) AS InitialTier,
    COUNT(DISTINCT CASE
        WHEN UtcTierChangedDate = -1 AND UtcDeletedDate = -1
        THEN StorageTier
    END) AS InitialTierCount
INTO #Files
FROM fileStorageData
GROUP BY FileId;

IF EXISTS (SELECT 1 FROM #Files WHERE InitialTierCount <> 1)
BEGIN
    ;THROW 50001, N'存在缺失或不唯一的初始档位，需先明确源数据含义。', 1;
END;

CREATE UNIQUE CLUSTERED INDEX IX_Files ON #Files(FileId);

-- 初始档位与带日期的变更分别建事件。
-- 若创建当天已有明确的变更，则当天使用变更档位。
;WITH RawEvents AS (
    SELECT
        FileId,
        InitialTier AS StorageTier,
        CreateDate AS StartDate,
        0 AS EventPriority
    FROM #Files

    UNION ALL

    SELECT
        FileId,
        StorageTier,
        CONVERT(date, CONVERT(char(8), UtcTierChangedDate), 112),
        1
    FROM fileStorageData
    WHERE UtcTierChangedDate <> -1
), Prioritized AS (
    SELECT
        *,
        MAX(EventPriority) OVER (
            PARTITION BY FileId, StartDate
        ) AS DayPriority
    FROM RawEvents
)
SELECT DISTINCT FileId, StorageTier, StartDate
INTO #TierEvents
FROM Prioritized
WHERE EventPriority = DayPriority;

IF EXISTS (
    SELECT 1
    FROM #TierEvents
    GROUP BY FileId, StartDate
    HAVING COUNT(*) > 1
)
BEGIN
    ;THROW 50002, N'同一文件同一天存在多个生效档位，缺少确定先后顺序的依据。', 1;
END;

CREATE UNIQUE CLUSTERED INDEX IX_TierEvents
ON #TierEvents(FileId, StartDate);

-- 用下一次变更、删除日和统计终点共同确定区间终点。
;WITH OrderedEvents AS (
    SELECT
        FileId,
        StorageTier,
        StartDate,
        LEAD(StartDate) OVER (
            PARTITION BY FileId ORDER BY StartDate
        ) AS NextStartDate
    FROM #TierEvents
), Intervals AS (
    SELECT
        e.StorageTier,
        e.StartDate,
        x.EndDate,
        f.SizeBytes
    FROM OrderedEvents AS e
    JOIN #Files AS f ON f.FileId = e.FileId
    CROSS APPLY (
        SELECT MIN(v.EndDate) AS EndDate
        FROM (VALUES
            (e.NextStartDate),
            (f.DeletedDate),
            (@EndExclusive)
        ) AS v(EndDate)
    ) AS x
    WHERE e.StartDate < x.EndDate
), BoundaryEvents AS (
    SELECT
        StorageTier,
        StartDate AS EventDate,
        CAST(1 AS bigint) AS FileDelta,
        SizeBytes AS ByteDelta
    FROM Intervals

    UNION ALL

    SELECT
        StorageTier,
        EndDate,
        CAST(-1 AS bigint),
        -SizeBytes
    FROM Intervals
)
SELECT
    StorageTier,
    EventDate,
    SUM(FileDelta) AS FileDelta,
    SUM(ByteDelta) AS ByteDelta
INTO #DailyDeltas
FROM BoundaryEvents
GROUP BY StorageTier, EventDate;

CREATE UNIQUE CLUSTERED INDEX IX_DailyDeltas
ON #DailyDeltas(StorageTier, EventDate);

DECLARE @FirstDate date = (SELECT MIN(CreateDate) FROM #Files);

;WITH Tiers AS (
    SELECT DISTINCT StorageTier
    FROM #TierEvents
), DailyGrid AS (
    SELECT c.CalendarDate, t.StorageTier
    FROM dbo.Calendar AS c
    CROSS JOIN Tiers AS t
    WHERE c.CalendarDate BETWEEN @FirstDate AND @Today
)
SELECT
    CONVERT(int, CONVERT(char(8), g.CalendarDate, 112)) AS DateKey,
    g.StorageTier,
    SUM(COALESCE(d.FileDelta, CAST(0 AS bigint))) OVER (
        PARTITION BY g.StorageTier
        ORDER BY g.CalendarDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS FileCount,
    SUM(COALESCE(d.ByteDelta, CAST(0 AS bigint))) OVER (
        PARTITION BY g.StorageTier
        ORDER BY g.CalendarDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS TotalBytes
FROM DailyGrid AS g
LEFT JOIN #DailyDeltas AS d
    ON d.StorageTier = g.StorageTier
   AND d.EventDate = g.CalendarDate
ORDER BY g.CalendarDate, g.StorageTier;

所有日期运算都在转换为 date 后执行。不要对 YYYYMMDD 整数直接加减 1，否则跨月、跨年时会得到错误日期。

示例数据应得到什么结果

按上述口径，两个文件的大小合计为 4784 + 9862 = 14646 bytes。以下是几个日期的预期结果，单元格格式为“文件数 / bytes”：

这些预期值根据业务规则推导，未在目标数据库中执行验证。题目中 20210408 的数量只是输出格式示例，与这两条文件历史不对应。

数千万行数据时需要注意什么

中间数据量主要取决于档位事件数。每段有效区间生成两条边界事件，最终只展开“日期 × 档位”，避免了按文件逐日展开的数据量。

读取基础表、按 FileId 汇总、窗口排序和创建临时表索引仍会消耗资源。上面的临时表用于保存文件级信息、档位事件和每日增减量；具体耗时需要结合实际执行计划、逻辑读取、内存授予和 tempdb 使用情况判断，不能仅凭查询形式保证性能。

如果需要将结果保存为日快照，可以将最后的 SELECT 写入独立结果表，无需修改基础表。长期运行时也可以维护独立的事件汇总表，但需要能够识别新增、修改和补录的历史数据。历史事件发生变化后，相应日期之后的累计结果也必须更新。

统计范围需要由业务约定。这里选择最早创建日至当前 UTC 日期；若只需要截至最后删除日的历史，或希望包含未来日期，应调整统计终点。若将起点改为较晚的日期，还必须先计算起点之前的档位余额，不能直接截掉此前所有增减事件。

备注：内容仅供参考。