基于两行datetime值推导时长：创建含duration列的视图需求

现有表MyTable，表结构定义如下：

[Id] [nvarchar](max) NOT NULL, -- 实际并非nvarchar类型，此处简化 schema
[PropertyName] [nvarchar](max) NOT NULL,
[OriginalValue] [nvarchar](max) NOT NULL,
[UpdatedValue] [nvarchar](max) NULL,
[ChangeTimestamp] [datetime] NULL

示例数据：

需要创建一个视图，新增duration列以显示特定设置的激活时长。以上述示例为例，Value2的激活时长为1小时。

解决方案 #

可以借助SQL窗口函数LEAD()，获取同一Id和PropertyName分组下的下一次变更时间，通过时间差计算得到激活时长。

创建视图的SQL语句 #

CREATE VIEW vw_MyTable_WithDuration
AS
SELECT 
    Id,
    PropertyName,
    UpdatedValue AS ActiveValue,
    ChangeTimestamp AS ActivationTime,
    LEAD(ChangeTimestamp) OVER (PARTITION BY Id, PropertyName ORDER BY ChangeTimestamp) AS DeactivationTime,
    DATEDIFF(HOUR, ChangeTimestamp, LEAD(ChangeTimestamp) OVER (PARTITION BY Id, PropertyName ORDER BY ChangeTimestamp)) AS duration
FROM MyTable
WHERE UpdatedValue IS NOT NULL

逻辑说明 #

1. 分组排序：PARTITION BY Id, PropertyName确保只计算同一实体同一属性的变更时间差，ORDER BY ChangeTimestamp按变更时间升序排列，让LEAD()能精准获取下一次变更的时间点。
2. 时长计算：DATEDIFF(HOUR, ...)计算当前激活时间到下一次变更的小时差，得到激活时长。如果需要分钟、秒等其他单位，替换HOUR为MINUTE/SECOND即可。
3. 最新值处理：对于没有后续变更的最新值（示例中的Value3），duration会返回NULL，如果需要默认值，可使用ISNULL()调整，比如ISNULL(DATEDIFF(...), 0)表示当前仍处于激活状态。

视图查询结果 #

基于示例数据，查询视图会得到：

扩展：包含初始值的时长计算 #

如果需要把初始值（示例中的Value1）的激活时长也纳入统计，可通过CTE补充初始值的激活时间（需根据业务规则定义初始激活时间）：

CREATE VIEW vw_MyTable_WithFullDuration
AS
WITH AllValues AS (
    -- 补充初始值记录
    SELECT 
        Id,
        PropertyName,
        OriginalValue AS ActiveValue,
        -- 此处假设初始值在第一次变更前1小时激活，需根据实际业务调整
        DATEADD(HOUR, -1, MIN(ChangeTimestamp) OVER (PARTITION BY Id, PropertyName)) AS ActivationTime
    FROM MyTable
    WHERE ChangeTimestamp = (SELECT MIN(ChangeTimestamp) FROM MyTable mt WHERE mt.Id = MyTable.Id AND mt.PropertyName = MyTable.PropertyName)
    UNION ALL
    -- 原有变更记录
    SELECT 
        Id,
        PropertyName,
        UpdatedValue AS ActiveValue,
        ChangeTimestamp AS ActivationTime
    FROM MyTable
    WHERE UpdatedValue IS NOT NULL
)
SELECT 
    Id,
    PropertyName,
    ActiveValue,
    ActivationTime,
    LEAD(ActivationTime) OVER (PARTITION BY Id, PropertyName ORDER BY ActivationTime) AS DeactivationTime,
    DATEDIFF(HOUR, ActivationTime, LEAD(ActivationTime) OVER (PARTITION BY Id, PropertyName ORDER BY ActivationTime)) AS duration
FROM AllValues

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