MySQL Group从季度到期间 [英] MySQL Group from quarters to periods
问题描述
我有一个这样的表:
Person smallint(5) act_time datetime
1 2020-05-29 07:00:00
1 2020-05-29 07:15:00
1 2020-05-29 07:30:00
2 2020-05-29 07:15:00
2 2020-05-29 07:30:00
1 2020-05-29 10:30:00
1 2020-05-29 10:45:00
上表是一个示例,其中有2个不同的人,他们工作的每个季度都有一行...
The table above is an example with 2 different persons and there is a row for each quarter they are at work...
在MySQL中将表转换"到另一个表的最佳方法是什么,在该表中有一个列"person",一个列"start"和一个"stop".
What is the best way in MySQL to "convert" this table to another table where there is a column for "person", a column for "start" and one for "stop".
结果是这样的:
Person Start Stop
1 2020-05-29 07:00:00 2020-05-29 07:45:00
2 2020-05-29 07:15:00 2020-05-29 07:45:00
1 2020-05-29 10:30:00 2020-05-29 11:00:00
我尝试过左连接到相同的原始表,但是没有用...
I have tried with a left join to the same original table but it didn't work...
感谢您的答复.我现在已经在上表中进行了尝试-不幸的是它没有给出结果...我的sql字符串是:
Thanks for your reply. I have now tried it with the table above - and it unfortunately doesn't give that result... My sql string is:
select person,min(act_time) start_date,max(act_time) end_date from (select t.*,row_number() over(order by act_time) rn1,row_number() over(partition by person order by act_time) rn2 from test t) t group by person, rn1 - rn2 order by min(act_time)
But the result it generates is:
1 29-05-2020 07:00:00 29-05-2020 07:15:00
2 29-05-2020 07:15:00 29-05-2020 07:15:00
1 29-05-2020 07:30:00 29-05-2020 07:30:00
2 29-05-2020 07:30:00 29-05-2020 07:30:00
1 29-05-2020 10:30:00 29-05-2020 10:45:00
应该类似于3行的结果.因此,当同一个人的行之间的间隔超过15分钟时,应该创建一个新行.
It should be like the result with 3 rows. So when there is a gap in more than 15 minutes between to rows with the same person it should create a new row.
推荐答案
这是一个孤岛问题.我不确定为什么GMB的解决方案不起作用,但这应该:
This is a gaps-and-islands problem. I'm not sure why GMB's solution doesn't work, but this should:
select person, min(act_date) as start_date,
max(act_date) + interval 15 minute as end_date
from (select t.*,
sum(case when prev_act_date >= act_date - interval 15 minute then 0 else 1 end) over
(partition by person order by act_date) as grp
from (select t.*,
lag(act_date) over (partition by person order by act_date) as prev_act_date
from t
) t
) t
group by person, grp
order by min(act_date)
这篇关于MySQL Group从季度到期间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!