时间戳

首页 > 解决方案 > 获取每个用户在 24 个时间段中的每小时通话数据

sql-server - 获取每个用户在 24 个时间段中的每小时通话数据

问题描述

 WITH CTE1
     AS (SELECT 'ABC' AS [Name], 
                4 AS [Call Count], 
                0 AS [Time_Slot]
         UNION
         SELECT 'XYX' AS [Name], 
                7 AS [Call Count], 
                1 AS [Time_Slot]
         UNION
         SELECT 'TRT' AS [Name], 
                6 AS [Call Count], 
                6 AS [Time_Slot]
         UNION
         SELECT 'DCFG' AS [Name], 
                8 AS [Call Count], 
                7 AS [Time_Slot]
         UNION
         SELECT 'DCS' AS [Name], 
                45 AS [Call Count], 
                18 AS [Time_Slot]
         UNION
         SELECT 'XYX' AS [Name], 
                45 AS [Call Count], 
                9 AS [Time_Slot]
        )
     SELECT *
     FROM CTE1;

考虑上面代码的输出是

Name    Call Count  Time_Slot
ABC          4         0
DCFG         50        7
DCS          45        18
TRT          6         6
XYX          7         1
XYX          45        9

我想为每个用户输出 24 小时数据，如下所示，

This data is for user DCFG likewise I wanted for each user (ABC, DCS, TRT, XYX)


Name    Call Count  Time_Slot
DCFG       0        0
DCFG       0        1
DCFG       0        2
DCFG       0        3
DCFG       0        4
DCFG       0        5
DCFG       0        6
DCFG       50       7
DCFG       0        8
DCFG       0        9
DCFG       0        10
DCFG       0        11
DCFG       0        12
DCFG       0        13
DCFG       0        14
DCFG       0        15
DCFG       0        16
DCFG       0        17
DCFG       0        18
DCFG       0        19
DCFG       0        20
DCFG       0        21
DCFG       0        22
DCFG       0        23

现在，我尝试过的

使用连接是有意义的，我将无法实现我想要的，通过使用交叉连接，我得到了所有时隙，但它为每个用户行重复了 24 个时隙条目，例如用户'XYX'CTE1 有两个条目作为以下

XYX          7         1
XYX          45        9

Cross Join 为每行上方创建 24 个时隙条目。

任何人都可以向我建议如何实现这一目标，谢谢提前

标签： sql-server

解决方案

使用（内联）计数，CROSS JOIN将其用于您的用户表（我假设您有一个），然后LEFT JOIN将其用于上面的数据集：

WITH Tally AS(
    SELECT I
    FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23))V(I)),
UserTimes AS(
    SELECT U.[Name],
           T.I AS TimeSlot
    FROM dbo.Users U
         CROSS JOIN T)
SELECT YT.[Name],
       C.CallCount,
       YT.TimeSlot
FROM UserTimes UT
     LEFT JOIN CTE1 C ON UT.[Name] = C.[Name]
                     AND UT.TimeSlot = C.TimeSlot;

推荐阅读