如何用Unpivot/Pivot或窗口函数计算工作流平均间隔天数？

需求说明 #

我需要计算每个工作流的平均间隔天数，已创建测试临时表#ProcessLog并插入示例数据，当前通过INNER JOIN方式实现了需求，但希望了解是否存在更简便的实现方案，例如使用PIVOT、UNPIVOT或窗口函数。

测试代码 #

DROP TABLE IF EXISTS #ProcessLog;
GO

CREATE TABLE #ProcessLog
(
WorkFlow        VARCHAR(100),
ExecutionDate   DATE,
PRIMARY KEY (WorkFlow, ExecutionDate)
);
GO

INSERT INTO #ProcessLog VALUES
('Alpha','6/01/2018'),('Alpha','6/14/2018'),('Alpha','6/15/2018'),
('Bravo','6/1/2018'),('Bravo','6/2/2018'),('Bravo','6/19/2018'),
('Charlie','6/1/2018'),('Charlie','6/15/2018'),('Charlie','6/30/2018');
GO

现有INNER JOIN解法 #

WITH cte as ( SELECT ROW_NUMBER() OVER(ORDER by Workflow) as n, * 
              FROM       #ProcessLog as p1 )

SELECT cte.workflow, (abs(DATEDIFF(DAY, cte.ExecutionDate, cte2.ExecutionDate))
      + abs(DATEDIFF(DAY, cte2.ExecutionDate, cte3.ExecutionDate)) ) / 2 as average_days
FROM       CTE  
INNER JOIN CTE as cte2  ON cte.n +1 = cte2.n  AND cte.WorkFlow  = cte2.WorkFlow
INNER JOIN CTE as cte3  ON cte2.n +1 = cte3.n AND cte2.WorkFlow = cte3.WorkFlow  

优化方案：使用LAG窗口函数 #

你的现有解法存在局限性——仅能处理每个工作流恰好有3条执行记录的场景，一旦工作流的记录数量变化，查询就会失效。用LAG()窗口函数可以实现更通用、简洁的计算：

实现代码 #

SELECT 
    WorkFlow,
    AVG(DATEDIFF(DAY, PrevExecutionDate, ExecutionDate)) AS average_days
FROM (
    SELECT 
        WorkFlow,
        ExecutionDate,
        -- 按工作流分组，获取当前记录的上一条执行日期
        LAG(ExecutionDate) OVER (PARTITION BY WorkFlow ORDER BY ExecutionDate) AS PrevExecutionDate
    FROM #ProcessLog
) AS SubQuery
WHERE PrevExecutionDate IS NOT NULL  -- 过滤每个工作流的第一条记录（无前置日期）
GROUP BY WorkFlow;

方案优势 #

• 通用性强：不受工作流记录数量限制，无论每个工作流有2条还是N条记录，都能正确计算平均间隔
• 代码简洁：避免了多层自关联的复杂逻辑，结构更清晰
• 逻辑直观：直接基于每条记录的前置数据计算间隔，再通过分组求平均，符合需求的自然逻辑

关于PIVOT/UNPIVOT的说明 #

PIVOT和UNPIVOT主要用于行转列或列转行的场景，和当前计算相邻日期间隔的需求匹配度不高，因此不是最优选择。窗口函数是更贴合这类序列数据计算的工具。

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