我正在嘗試計算每個作業流程的平均天數。
試試看
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 函式的解決方案，但我想知道是否有更簡單的方法，例如 PIVOT 或 UNPIVOT 或 Window 函式？
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
uj5u.com熱心網友回復：
您需要LAGCTE 中的日期，并且可以使用 DATEDIFF
DROP TABLE IF EXISTS #ProcessLog;
CREATE TABLE #ProcessLog
(
WorkFlow VARCHAR(100),
ExecutionDate DATE,
PRIMARY KEY (WorkFlow, ExecutionDate)
);
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');
WITH CTE As(
SELECT
WorkFlow
,ExecutionDate
, LAG(ExecutionDate) OVER(PARTITION BY WorkFlow ORDER BY ExecutionDate) date2
FROM
#ProcessLog)
SELECT WorkFlow,AVG(DATEDIFF(day, date2,ExecutionDate)) FROM CTE GROUP BY WorkFlow
| 作業流程 | （無列名） |
|---|---|
| Α | 7 |
| 布拉沃 | 9 |
| 查理 | 14 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/533142.html
下一篇：需要幫助讀取多行行