目錄
- 1.背景說明
- 2.簡單示例
shanzm-2024-4-3 11:11:20
1.背景說明
- 需要查詢一個以時間段為列的報表
- 可以篩選除各個時間段進行連線查詢出來
- 可以透過 Case When 將時間差轉為時間段,在進行轉列
2.簡單示例
--各個品牌尚未發貨的訂單,從訂單建立時間到當前時間的各個時間段的數量
WITH OrdersTable AS
(
SELECT NEWID() AS OrderId, 'Brand1' AS Name ,'2024-4-17 01:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 01:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 06:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 06:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 11:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 11:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 16:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 16:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 21:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand1' AS Name ,'2024-4-17 21:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId, 'Brand2' AS Name ,'2024-4-17 01:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 01:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 06:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 06:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 11:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 11:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 16:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 16:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 21:10:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-17 21:20:00' AS CreateTime
UNION ALL
SELECT NEWID() AS OrderId,'Brand2' AS Name ,'2024-4-16 08:20:00' AS CreateTime
)
,temp1 AS
(
--第一步:算出訂單與當前時間的差值(這個使用'2024-4-18 00:00:00'作為當前時間)
SELECT * , DATEDIFF(HOUR, OrdersTable.CreateTime, '2024-4-18 01:00:00') AS TimeDiff FROM OrdersTable
)
,temp2 AS (
--第二步:使用CASE WHEN 將時間差值轉為時間段
SELECT *,CASE
WHEN ( TimeDiff >= 0
AND TimeDiff < 5) THEN 'H0h~5h'
WHEN ( TimeDiff >= 5
AND TimeDiff < 10) THEN 'H5h~10h'
WHEN ( TimeDiff >= 10
AND TimeDiff < 15) THEN 'H10h~15h'
WHEN ( TimeDiff >= 15
AND TimeDiff < 20) THEN 'H15h~20h'
WHEN ( TimeDiff >= 20
AND TimeDiff < 25) THEN 'H20h~25h'
WHEN ( TimeDiff >= 25
AND TimeDiff < 30) THEN 'H25h~30h'
ELSE 'Greater30' END PeriodOfTime FROM temp1
)
,temp3 AS
(
--第三步:按時間段和品牌聚合求Count
SELECT temp2.Name,temp2.PeriodOfTime,COUNT(temp2.OrderId) AS OrderCount FROM temp2 GROUP BY Name,PeriodOfTime
)
,temp4 AS
(
--第四步:行轉列
SELECT T.Name,T.[H0h~5h],T.[H5h~10h],T.[H10h~15h],T.[H15h~20h],T.[H20h~25h],T.Greater30
FROM temp3 PIVOT (SUM(OrderCount) FOR PeriodOfTime IN([H0h~5h],[H5h~10h],[H10h~15h],[H15h~20h],[H20h~25h],[Greater30]))T
)
SELECT * FROM temp4
Name H0h~5h H5h~10h H10h~15h H15h~20h H20h~25h Greater30
------ ----------- ----------- ----------- ----------- ----------- -----------
Brand1 2 2 2 2 2 NULL
Brand2 2 2 2 2 2 1