mysql组

wwodge7n 于 2021-08-13 发布在 Java

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

我有一张这样的table：

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

上表是一个例子，有两个不同的人，每个季度有一行，他们在工作。。。
在mysql中，将这个表“转换”到另一个表的最佳方法是什么？在这个表中，有一列表示“person”，一列表示“start”，一列表示“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

我尝试过用左连接到同一个原始表，但没有成功。。。

谢谢。

谢谢你的回复。我现在已经用上面的表格试过了-不幸的是它没有给出那个结果。。。我的sql字符串是：

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分钟时，应该创建一个新行。

sql mysql window-functions Date gaps-and-islands

来源：https://stackoverflow.com/questions/62082968/mysql-group-from-quarters-to-periods

2条答案

按热度按时间

kxeu7u2r1#

这是一个缺口和孤岛问题。您可以使用窗口函数（仅在mysql 8.0中提供）来解决此问题：

select 
    person,
    min(act_date) start_date,
    max(act_date) end_date
from (
    select
        t.*,
        sum(act_date <> lag_act_date + interval 15 minute)
            over(partition by person order by act_date) grp
    from (
        select 
            t.*,
            lag(act_date) 
                over(partition by person order by act_date) lag_act_date
        from mytable t
    ) t
) t
group by person, grp
order by min(act_date)

其思想是使用一个累积和来构建相邻记录的组：每当间隔不是15分钟时，总和就会递增。

赞(0）回复(0）举报 2021-08-13

3gtaxfhh2#

这是一个缺口和孤岛问题。我不知道为什么gmb的解决方案不起作用，但这应该：

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)

赞(0）回复(0）举报 2021-08-13

我来回答

mysql组

谢谢。

2条答案

相关问题

热门标签

最新问答