T-SQL——关于时间段类报表

目录

• 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