对于给定的用户ID"1"和给定的日期2018-01-02,我想计算记录的总小时数,其中可能存在重叠。
计算此子集:
+-----+---------------------+---------------------+
| uid | time_start | time_end |
+-----+---------------------+---------------------+
| 1 | 2018-01-02 04:00:00 | 2018-01-02 04:30:00 |
| 1 | 2018-01-02 04:25:00 | 2018-01-02 04:35:00 |
| 1 | 2018-01-02 04:55:00 | 2018-01-02 05:15:00 |
+-----+---------------------+---------------------+
结果时间应为:00:55。
MariaDB 10.3具有窗口函数和CTE,因此您可以使用它们来生成结果。CTE通过将当前time_start
与当天的最大先前time_end
进行比较,并取其最大值(最大值),然后查询简单地按用户id和日期分组SUM
的每个会话时间,来消除会话时间中的重叠。注意,如果一个会话与另一个会话完全重叠,则CTE将重叠会话的start
和end
时间都设置为end
时间,从而导致有效会话长度为0。我已经扩展了我的演示,包括这样一个场景,以及多个重叠的会话:
WITH sessions AS
(SELECT uid,
GREATEST(time_start, COALESCE(MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), '2000-01-01')) AS start,
MAX(time_end) OVER (PARTITION BY DATE(time_start) ORDER BY time_start ROWS UNBOUNDED PRECEDING) AS end
FROM sessions)
SELECT uid, DATE(start) AS `date`, SEC_TO_TIME(SUM(TO_SECONDS(end) - TO_SECONDS(start))) AS totaltime
FROM sessions
GROUP BY uid, `date`
输出:
uid date totaltime
1 2018-01-02 00:55:00
1 2018-01-03 01:00:00
1 2018-01-04 01:15:00
dbfiddle 演示
这是一种间隙和孤岛问题。这在MySQL中是一个真正的痛苦,但我认为你可以用变量来做到这一点。
其想法是迭代记录,并注意新的开始何时与前一个"岛"重叠。它成为下一个岛屿的起点。然后,您可以聚合并获得每个岛屿的持续时间:
select island_start,
(to_seconds(max(time_start)) - to_seconds(min(time_end))) as num_seconds
from (select t.*,
(@ts := if(time_start <= @te,
if(@te := greatest(@te, time_end), @ts, @ts), -- no change on the start
if(@te := time_end, time_start, time_start)
)
) as island_start
from (select t.*
from t
order by time_start
) t cross join
(select @ts := -1, @te := -1) params
) t
group by island_start;
您可以将其用作子查询来添加差异。
这是一次很棒且愉快的练习。
因此,这里的技巧如下:
- 用户在从上次会话注销之前再次登录,并在第一次会话后结束会话;或
- 此人在从上次会话注销之前再次登录,并在结束第一次会话之前结束会话
所以,解决这个问题的诀窍就是给新会话分配一个开始时间,这个时间等于他上一个会话的结束时间。在这种情况下,您将把每个会话作为一行,并且您将能够计算时间差。好的,让我们模拟一下这个例子:
create table #temp (userId int, timeComienza datetime, timeTermina dateTime )
-- exemplo de overlap
insert into #temp values (1, '20180102 16:00', '20180102 16:30')
insert into #temp values (1, '20180102 16:25', '20180102 16:35')
insert into #temp values (1, '20180102 16:55', '20180102 17:15')
-- ejemplo de no overlap
insert into #temp values (2, '20180102 16:00', '20180102 16:30')
insert into #temp values (2, '20180102 16:35', '20180102 16:50')
insert into #temp values (2, '20180102 16:40', '20180102 16:45')
userId timeComienza timeTermina
1 2018-01-02 16:00:00.000 2018-01-02 16:30:00.000
1 2018-01-02 16:25:00.000 2018-01-02 16:35:00.000
1 2018-01-02 16:55:00.000 2018-01-02 17:15:00.000
2 2018-01-02 16:00:00.000 2018-01-02 16:30:00.000
2 2018-01-02 16:35:00.000 2018-01-02 16:50:00.000
2 2018-01-02 16:40:00.000 2018-01-02 16:45:00.000
正如你所看到的,用户1在结束他的第一个会话之前登录他的第二个会话,而用户2在他的第三个会话中遇到了几乎相同的问题,除了他的第三次会话在结束他的第二次会话之前结束(他的第二会话的完全重叠和重叠)。
我们需要做的第一件事,是提供这些会话的订单,使用订单by。
select *, ROW_NUMBER() over(partition by userId order by timeComienza) as unOrden
into #temp2
from #temp
userId timeComienza timeTermina unOrden
1 2018-01-02 16:00:00.000 2018-01-02 16:30:00.000 1
1 2018-01-02 16:25:00.000 2018-01-02 16:35:00.000 2
1 2018-01-02 16:55:00.000 2018-01-02 17:15:00.000 3
2 2018-01-02 16:00:00.000 2018-01-02 16:30:00.000 1
2 2018-01-02 16:35:00.000 2018-01-02 16:50:00.000 2
2 2018-01-02 16:40:00.000 2018-01-02 16:45:00.000 3
现在,使用我们的迭代会容易100倍。让我们创建一个结构与表#2相同的空表,作为插入分析的表。
select *
into #tablaInsertar
from #temp2
delete from #tablaInsertar
最后,这里是我们分析的核心:)
-- variable to iterate users
declare @x int = 1 , @usuarios int = 1, @usuariosMax int
--num dif de usuarios:
select @usuariosMax = count(distinct(userId)) from #temp2
while(@usuarios <= @usuariosMax)
begin
/*trabajando cada usuario*/
/*Primero necesitamos saber la longitud de cada Usuario*/
declare @trabajaUsuario int = 1, @longUsuario int
--obtiene longitud usuario
select @longUsuario = count(1) from #temp
where userId = @usuarios
while(@trabajaUsuario <= @longUsuario)
begin
if(@trabajaUsuario = 1)
begin
insert into #tablaInsertar
select
*
from #temp2
where userId = @usuarios and unOrden = @trabajaUsuario
end
else -- dado que no sea la primera fila
-- comparando horas
begin
declare @horaInicioEstePeriodo dateTime, @horaTerminaAnterior dateTime
select @horaInicioEstePeriodo = #temp2.timeComienza from #temp2 where userId = @usuarios and unOrden = @trabajaUsuario
select @horaTerminaAnterior = #temp2.timeTermina from #temp2 where userId = @usuarios and unOrden = @trabajaUsuario - 1
if(@horaInicioEstePeriodo < @horaTerminaAnterior) -- las modificaciones dado que el periodo inicio sea menro a la hora anterior
begin
insert into #tablaInsertar
select
t2.userId
, t1.timeTermina as tiempoComienzaActualizado
, t2.timeTermina
, t2.unOrden
from
(
select
#temp2.userId
,#temp2.timeComienza
, #temp2.timeTermina
, #temp2.unOrden
from #temp2
where userId = @usuarios and unOrden = @trabajaUsuario - 1
)t1
join
(
select
#temp2.userId
--, as tiempoComienzaActualizado --#temp2.timeComienza
, #temp2.timeTermina
, #temp2.unOrden
from #temp2
where userId = @usuarios and unOrden = @trabajaUsuario
) t2 on t1.userId = t2.userId and t1.unOrden + 1 = t2.unOrden
end
else -- dado que el periodo inicia sea mayor o igual a la hora anterior
begin
insert into #tablaInsertar
select
*
from #temp2
where userId = @usuarios and unOrden = @trabajaUsuario
end
end
select @trabajaUsuario += 1
end
select @usuarios += 1
end
让我们看看我们的新表格:)
select *, DATEDIFF(s,timeComienza,timeTermina) timeInSeconds
from #tablaInsertar
userId timeComienza timeTermina unOrden timeInSeconds
1 2018-01-02 16:00:00.000 2018-01-02 16:30:00.000 1 1800
1 2018-01-02 16:30:00.000 2018-01-02 16:35:00.000 2 300
1 2018-01-02 16:55:00.000 2018-01-02 17:15:00.000 3 1200
2 2018-01-02 16:00:00.000 2018-01-02 16:30:00.000 1 1800
2 2018-01-02 16:35:00.000 2018-01-02 16:50:00.000 2 900
2 2018-01-02 16:50:00.000 2018-01-02 16:45:00.000 3 -300
正如我们所看到的,用户1会话2现在正确地反映了他真正工作的300秒(5分钟)。对于用户2会话3的问题,我们有一个负数,原因是他从会话2中抽出了时间。所以,我们所需要做的就是求和正值,只知道每个用户记录的实时性,就像这样:
select
t1.userId,
sum(case when timeInSeconds > 0 then timeInSeconds else 0 end) totalTimeLogged
from
(
select *, DATEDIFF(s,timeComienza,timeTermina) timeInSeconds
from #tablaInsertar
) t1
group by t1.userId
最终结果:
userId totalTimeLogged
1 3300
2 2700