时间戳

首页 > 解决方案 > GROUP_CONCAT 根据分组行的其他字段的值

mysql - GROUP_CONCAT 根据分组行的其他字段的值

问题描述

我有一个列如下表：

| id | name  | entrance | eName  |
|----|-------|----------|--------|
| 1  | user1 | 5:10     | enter1 |
| 2  | user2 | 2:15     | enter1 |
| 3  | user3 | 3:05     | enter3 |
| 1  | user1 | 5:12     | enter2 |
| 1  | user1 | 7:30     | enter2 |

我尝试做的是eName根据entrance字段的不同值进行分组，如果值之间的差异为（2），那么将它们分组到别处将其返回为新行？

所需的输出如下：

| id | name  |
|----|-------|--------       |
| 1  | user1 | enter1, enter2|
| 2  | user2 | enter1        |
| 3  | user3 | enter3        |
| 1  | user1 | enter2        |

标签： mysqlsqldatetimegroup-concatgaps-and-islands

解决方案

我将其理解为间隙和岛屿问题，其中岛屿由同一用户的相邻入口组成，最大间隙为 2 分钟。

这是一种使用窗口函数的方法（在 MySQL 8.0 中可用）：

select id, name, group_concat(ename order by entrance) enames, 
    count(*) cnt_entrances, min(entrance) first_entrance, max(entrance) last_entrance
from (
    select t.*,
        sum(entrance > lag_entrance + interval 2 minute) over(partition by id, name order by entrance) grp
    from (
        select t.*, 
            lag(entrance, 1, entrance) over(partition by id, name order by entrance) lag_entrance
        from mytable t
    ) t
) t
group by id, name, grp

lag()为您提供“上一次”进入的日期，然后我们使用sum()每次满足超过 2 分钟的间隔时递增的累积值来定义组。我在结果集中添加了几列以使其更易于理解。

DB Fiddlde 上的演示：

编号 | 姓名 | 名称 | cnt_entrances | 第一入口 | 最后一个入口
-: | :---- | :------------ | ------------: | :------------- | :------------
 1 | 用户1 | 输入1，输入2 | 2 | 05:10:00 | 05:12:00     
 1 | 用户1 | 输入2 | 1 | 07:30:00 | 07:30:00     
 2 | 用户2 | 输入1 | 1 | 02:15:00 | 02:15:00     
 3 | 用户3 | 输入3 | 1 | 03:05:00 | 03:05:00

推荐阅读