预订应用场景下SQL存储资源历史变更以生成利用率报告的方案咨询

方案一：天级资源快照表（最适合你的报表场景） #

这是兼顾开发成本和查询效率的最优选择，完全规避你提到的单日多条变更处理问题：

• 新建表ResourceDailySnapshot，核心字段参考：

  CREATE TABLE ResourceDailySnapshot (
      Id INT PRIMARY KEY IDENTITY(1,1),
      SnapshotDate DATE NOT NULL UNIQUE,
      ValidResourceCount INT NOT NULL,
      CreatedAt DATETIME2 DEFAULT GETUTCDATE()
  )
  

• 数据维护逻辑：用Azure Functions设置每日UTC零点触发的定时任务，拉取当前系统中生效的总资源数写入本表，一天仅生成一条记录，无冗余数据。
• 报表计算逻辑：查询指定日期范围时，先从本表求和得到区间总可售资源天数（所有日期的ValidResourceCount相加），再除以同区间内已预订的资源天数，即可得到准确的利用率，历史数据不会随后续资源增减发生变化。

方案二：资源变更日志表 + 预计算视图（适合需要细粒度变更追溯的场景） #

如果你需要留存每次资源增减的明细记录，可以在你原本的思路上做优化，避免每次查询都处理单日多条变更：

• 新建表ResourceChangeLog，核心字段参考：

  CREATE TABLE ResourceChangeLog (
      Id INT PRIMARY KEY IDENTITY(1,1),
      ChangeTime DATETIME2 NOT NULL,
      ChangeType TINYINT NOT NULL, -- 0=新增,1=删除,2=调整
      DeltaCount INT NOT NULL, -- 本次变更的数量，新增为正、删除为负
      TotalAfterChange INT NOT NULL, -- 本次变更完成后的总资源数，写入日志时直接计算存储
      OperateUser NVARCHAR(100) NULL
  )
  

• 预先创建报表专用的计算视图，用SQL窗口函数直接生成每天的有效资源数，不用每次报表查询时重复处理逻辑：

  CREATE VIEW vw_ResourceDailyCount
  AS
  WITH DateSeries AS (
      -- 生成从最小变更日期到当前的所有日期序列
      SELECT CAST(MIN(ChangeTime) AS DATE) AS DateVal FROM ResourceChangeLog
      UNION ALL
      SELECT DATEADD(DAY, 1, DateVal) FROM DateSeries WHERE DateVal < CAST(GETUTCDATE() AS DATE)
  )
  SELECT 
      ds.DateVal,
      ISNULL(LAST_VALUE(rcl.TotalAfterChange) OVER (PARTITION BY ds.DateVal ORDER BY rcl.ChangeTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
      LAG(LAST_VALUE(rcl.TotalAfterChange) OVER (ORDER BY ds.DateVal)) OVER (ORDER BY ds.DateVal)) AS DailyResourceCount
  FROM DateSeries ds
  LEFT JOIN ResourceChangeLog rcl ON CAST(rcl.ChangeTime AS DATE) = ds.DateVal
  

• 报表直接查询该视图即可获得每日的资源总数，和方案一的查询复杂度一致。

适配你的技术栈的优化建议 #

• .NET 5 可以直接用EF Core操作上述两张表，Azure Functions的定时触发器配置简单，无需额外运维成本。
• 如果报表查询量较大，可以给vw_ResourceDailyCount创建聚集列存储索引做成物化视图，查询性能优于普通表。

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