每小时分组需要与前一小时数据相加，并与SQLServer中的另一个字段相减

v09wglhw 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(393)

我必须使用sql server查询显示联系人中心代理在1小时内的总登录和注销计数。
我已经开发了一个查询，可以获取每天每小时的登录和注销计数。但我需要一个能显示

SUM(All the Login Count) - SUM(All the Logout Out)

需要在下一个时间间隔中显示为按小时登录，或者将上一个按小时登录的数据与当前时间间隔的登录计数相加，然后减去当前时间间隔的注销计数。
预期数据：

目前实际数据：

我也使用了滞后函数，但没有得到想要的输出。你能纠正我在下面的问题中犯的错误吗？
如果需要任何create语句，请告诉我。
我的问题：

Declare @FromDate datetime
Declare @ToDate datetime
Declare @AgentID varchar(max);

set @FromDate='2020-05-22 00:00:00'; 
set @ToDate='2020-05-22 23:59:59'; 

;with 

LoginCount AS (
SELECT 
CONVERT(Date,A1.DateTime) AS Date,
    case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end AS INTERVAL,
    COUNT(Event) LoginCount
    FROM Agent_Event_Detail A1
    WHERE LoginDateTime BETWEEN @FromDate and @ToDate 
    AND A1.Event=1

GROUP BY CONVERT(Date,A1.DateTime), case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end 
)
--select * from LoginCount Order by INTERVAL

,LogoutCount AS (
SELECT 
CONVERT(Date,A1.DateTime) AS Date,
    case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end AS INTERVAL,
    COUNT(Event) LogoutCount
    FROM Agent_Event_Detail A1
    WHERE LoginDateTime BETWEEN @FromDate and @ToDate 
    AND A1.Event=2

GROUP BY CONVERT(Date,A1.DateTime), case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end 
)
,

--select * from LogoutCount Order by INTERVAL

cteRanked AS
(
   SELECT LoginCount, Date,INTERVAL, ROW_NUMBER() OVER(ORDER BY Date,INTERVAL) rownum
   FROM LoginCount
),
cteRanked1 AS
(
   SELECT LogoutCount, Date,INTERVAL, ROW_NUMBER() OVER(ORDER BY Date,INTERVAL) rownum
   FROM LogoutCount
),

HourlyLoginCount AS
(
SELECT ISNULL(c1.Date,c2.Date) LoginDate,ISNULL(c1.INTERVAL,c2.INTERVAL) LoginInterval,c1.LoginCount,
ISNULL(c2.Date,c1.Date)LogoutDate,ISNULL(c2.INTERVAL,c1.INTERVAL)LogoutInterval,c2.LogoutCount,
ISNULL(c1.LoginCount -c2.LogoutCount,0) [LoginDifference]
 from cteRanked c1 FULL JOIN cteRanked1 c2
ON c1.Date=c2.Date and c1.INTERVAL=c2.INTERVAL
)

select LoginDate,LoginInterval,ISNULL(LoginCount,0)LoginCount,ISNULL(LogoutCount,0)LogoutCount,LoginDifference,
(LoginCount)+(LAG(LoginDifference,1,0) OVER (PARTITION BY LoginDate, LoginInterval ORDER BY LoginDate,LoginInterval))-(LogoutCount)[Hourly Login Count] 
from HourlyLoginCount
order by LoginDate,LoginInterval

sql window-functions sql-server Date tsql

来源：https://stackoverflow.com/questions/62003157/hourly-grouping-needs-to-add-with-previous-hour-data-and-subtract-with-another-f

2条答案

按热度按时间

kokeuurv1#

你可以用 sum() over() 例子

Declare @YourTable Table ([LoginDate] date,LoginInterval varchar(50),[Login] int,[Logout] int)  Insert Into @YourTable Values 
 ('2020-05-22','00:00-00:59',6,5)
,('2020-05-22','01:00-01:59',1,0)
,('2020-05-22','04:00-04:59',3,0)
,('2020-05-22','05:00-05:59',71,1)
,('2020-05-22','06:00-06:59',112,23)

Select *
      ,Totals  = sum(Login) over (partition by LoginDate order by LoginInterval) 
                -sum(Logout) over (partition by LoginDate order by LoginInterval)
 from @YourTable

退货

LoginDate   LoginInterval   Login   Logout  Totals
2020-05-22  00:00-00:59     6       5       1
2020-05-22  01:00-01:59     1       0       2
2020-05-22  04:00-04:59     3       0       5
2020-05-22  05:00-05:59     71      1       75
2020-05-22  06:00-06:59     112     23      164

请注意：作为文本的示例数据比图像更有用

赞(0）回复(0）举报 2021-07-26

lztngnrs2#

我建议：

with slots as (
    select @fromDate dt
    union all select dateadd(hour, 1,  dt) from slots where dt < @toDate
)
select 
    s.dt,
    sum(case when a.event = 1 then 1 else 0 end) loginCount,
    sum(case when a.event = 2 then 1 else 0 end) logoutCount,
    sum(sum(case when a.event = 1 then 1 else -1 end)) over(order by s.dt) hourly_login_count
from slots s
left join agent_event_detail a
    on  a.event in (1, 2) 
    and a.loginDateTime >= s.dt 
    and a.loginDateTime <  dateadd(hour, 1, s.dt)
group by s.dt

cte生成边界内每小时时段的列表。然后，外部查询将带 left join ，并执行条件聚合以统计登录和注销次数。使用窗口函数执行附加计算。

赞(0）回复(0）举报 2021-07-26

我来回答

每小时分组需要与前一小时数据相加，并与SQLServer中的另一个字段相减

2条答案

相关问题

热门标签

最新问答