SQL Server 按clientId返回一周中每一天的所有记录计数

eimct9ow 于 2022-12-26 发布在其他

关注(0)|答案(3)|浏览(147)

我正在尝试编写一个查询，它将按客户端返回客户端记录（过去7天）的每日计数。
给定具有以下重要字段的表：

ClientId,    ProcessTime,

我想生成一个表，将clientId和过去7天作为列标题0 - 7（包括今天），并包含当天处理的记录：

ClientId, 0, 1, 2, 3, 4, 5, 6, 7

我提出了下面的查询，它使我接近：

Select ClientId, Count(Id) as [Count], max(DATEDIFF(DAY,ProcessTime,GETDATE())) as [Day]
from CallRecords
WHERE DATEDIFF(DAY,ProcessTime,GETDATE()) <= 7
group by ClientId, Dateadd(day, Datediff(day, 0, ProcessTime), 0)
order by 1, 2

其产生：

ClientId Count Day 
1        60    0
1        4707  1
1        11613 2

但是，如何将这些结果转换为一种格式，以便获得客户端列表和最近7天的结果（以列的形式）？

sql-server

来源：https://stackoverflow.com/questions/25109580/return-the-count-of-all-records-per-day-of-week-by-clientid

3条答案

按热度按时间

uxhixvfz1#

SELECT ClientID 
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 1 THEN 1 ELSE NULL END) AS [Sunday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 2 THEN 1 ELSE NULL END) AS [Monday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 3 THEN 1 ELSE NULL END) AS [Tuesday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 4 THEN 1 ELSE NULL END) AS [Wednesday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 5 THEN 1 ELSE NULL END) AS [Thursday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 6 THEN 1 ELSE NULL END) AS [Friday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 7 THEN 1 ELSE NULL END) AS [Saturday]
FROM CallRecords
WHERE ProcessTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 7, 0) 
GROUP BY ClientID

赞(0）回复(0）举报 2022-12-26

pgx2nnw82#

您需要一个pivot，SQLServer本身就支持它：

select ClientId, [0], [1], [2], [3], [4], [5], [6], [7]
from (select ClientId, DATEDIFF(DAY,ProcessTime,GETDATE()) days
    from CallRecords
    where DATEDIFF(DAY,ProcessTime,GETDATE()) <= 7) t
pivot (count(days) for days in ([0], [1], [2], [3], [4], [5], [6], [7])) pt

SQL小提琴

赞(0）回复(0）举报 2022-12-26

vngu2lb83#

透视表是解决此问题的方法。下面是Microsoft透视表文档Microsoft PIVOT的链接。若要查找星期几，应使用DatePart函数Microsoft DatePart Function

select ClientId as 'Client', [1], [2], [3], [4], [5], [6], [7] from (
    select ClientId, Id, CAST(DatePart(dw,ProcessTime) as int) as [DayOfWeek]
    from CallRecords
) as SourceTable
PIVOT (
    COUNT(Id)
    FOR [DayOfWeek] IN ([1], [2], [3], [4], [5], [6], [7])
) as pvt

checkout 此SQLFiddle

赞(0）回复(0）举报 2022-12-26

我来回答

SQL Server 按clientId返回一周中每一天的所有记录计数

3条答案

相关问题

热门标签

最新问答