group by 的 sum 求和

最新推荐文章于 2023-12-09 00:53:37 发布

原创最新推荐文章于 2023-12-09 00:53:37 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#date #user

存储过程专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询旨在从webproxylogA表和MSP_USERB表中选择特定字段，并按日期、用户名等进行分组，同时计算不同小时段内的处理时间总和。此案例适用于那些需要对大量日志数据进行复杂汇总分析的场景。

select
LogTime = Convert(char(10),LogTime,111),
UPPER(LTRIM(ClientUserName)),
COMPANY,
DEPT_ID,
[NAME],
ProcessingTime =sum( ProcessingTime),
H_00 = sum(Case datepart(Hour,LogTime)
when 0 then ProcessingTime else 0 end),
H_01 = sum(Case datepart(Hour,LogTime)
when 1 then ProcessingTime else 0 end),
H_02 = sum(Case datepart(Hour,LogTime)
when 2 then ProcessingTime else 0 end),
H_03 = sum(Case datepart(Hour,LogTime)
when 3 then ProcessingTime else 0 end),
H_04 = sum(Case datepart(Hour,LogTime)
when 4 then ProcessingTime else 0 end),
H_05 = sum(Case datepart(Hour,LogTime)
when 5 then ProcessingTime else 0 end),
H_06 = sum(Case datepart(Hour,LogTime)
when 6 then ProcessingTime else 0 end),
H_07 = sum(Case datepart(Hour,LogTime)
when 7 then ProcessingTime else 0 end),
H_08 = sum(Case datepart(Hour,LogTime)
when 8 then ProcessingTime else 0 end),
H_09 = sum(Case datepart(Hour,LogTime)
when 9 then ProcessingTime else 0 end),
H_10 = sum(Case datepart(Hour,LogTime)
when 10 then ProcessingTime else 0 end),
H_11 = sum(Case datepart(Hour,LogTime)
when 11 then ProcessingTime else 0 end),
H_12 = sum(Case datepart(Hour,LogTime)
when 12 then ProcessingTime else 0 end),
H_13 = sum(Case datepart(Hour,LogTime)
when 13 then ProcessingTime else 0 end),
H_14 = sum(Case datepart(Hour,LogTime)
when 14 then ProcessingTime else 0 end),
H_15 = sum(Case datepart(Hour,LogTime)
when 15 then ProcessingTime else 0 end),
H_16 = sum(Case datepart(Hour,LogTime)
when 16 then ProcessingTime else 0 end),
H_17 = sum(Case datepart(Hour,LogTime)
when 17 then ProcessingTime else 0 end),
H_18 = sum(Case datepart(Hour,LogTime)
when 18 then ProcessingTime else 0 end),
H_19 = sum(Case datepart(Hour,LogTime)
when 19 then ProcessingTime else 0 end),
H_20 = sum(Case datepart(Hour,LogTime)
when 20 then ProcessingTime else 0 end),
H_21 = sum(Case datepart(Hour,LogTime)
when 21 then ProcessingTime else 0 end),
H_22 = sum(Case datepart(Hour,LogTime)
when 22 then ProcessingTime else 0 end),
H_23 = sum(Case datepart(Hour,LogTime)
when 23 then ProcessingTime else 0 end)
from webproxylog A,MSP_USER B
where --Convert(char(10),LogTime,111) > @VAR_DATE ---AND (ClientUserName LIKE 'UNIMICRON%')
--and
ClientUserName <> 'anonymous' and a.ClientUserName=b.NETWORK_ACC
group by Convert(char(10),LogTime,111),UPPER(LTRIM(ClientUserName)),COMPANY,DEPT_ID,NAME;