时间戳

首页 > 解决方案 > SQL：通过多次迭代获取最小值和最大值

sql - SQL：通过多次迭代获取最小值和最大值

问题描述

我试图在 MSSQL 中为这个示例表获取按 activityid、dateid 和 personid 分组的最小开始和最大停止。

源表如下所示：

+--------+--------+------+------------ ------+------------+
| 用户名 | 日期ID | 开始 | 结束 | 活动ID |
+--------+--------+------+------------ ------+------------+
| 1 | 1 | 2021 年 11 月 1 日 12:10 | 2021 年 11 月 1 日 12:15 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 12:15 | 2021 年 11 月 1 日 12:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 12:30 | 2021 年 11 月 1 日 12:45 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 12:45 | 2021 年 11 月 1 日 13:00 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 13:00 | 2021 年 11 月 1 日 13:15 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 13:15 | 2021 年 11 月 1 日 13:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 13:30 | 11/01/2021 13:45 | 一个 |
| 1 | 1 | 11/01/2021 13:45 | 2021 年 11 月 1 日 13:55 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 13:55 | 2021 年 11 月 1 日 14:00 | 乙|
| 1 | 1 | 2021 年 11 月 1 日 14:00 | 2021 年 11 月 1 日 14:05 | 乙|
| 1 | 1 | 2021 年 11 月 1 日 14:05 | 11/01/2021 14:15 | 一个 |
| 1 | 1 | 11/01/2021 14:15 | 2021 年 11 月 1 日 14:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 14:30 | 2021 年 11 月 1 日 14:45 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 14:45 | 2021 年 11 月 1 日 15:00 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 15:00 | 2021 年 11 月 1 日 15:10 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 15:10 | 2021 年 11 月 1 日 15:15 | C |
| 1 | 1 | 2021 年 11 月 1 日 15:15 | 11/01/2021 15:30 | C |
| 1 | 1 | 11/01/2021 15:30 | 2021 年 11 月 1 日 15:45 | C |
| 1 | 1 | 2021 年 11 月 1 日 15:45 | 2021 年 11 月 1 日 16:00 | C |
| 1 | 1 | 2021 年 11 月 1 日 16:00 | 2021 年 11 月 1 日 16:10 | C |
| 1 | 1 | 2021 年 11 月 1 日 16:10 | 2021 年 11 月 1 日 16:15 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 16:15 | 2021 年 11 月 1 日 16:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 16:30 | 2021 年 11 月 1 日 16:45 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 16:45 | 2021 年 11 月 1 日 17:00 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 17:00 | 11/01/2021 17:15 | 一个 |
| 1 | 1 | 11/01/2021 17:15 | 11/01/2021 17:30 | 一个 |
| 1 | 1 | 11/01/2021 17:30 | 11/01/2021 17:45 | 一个 |
| 1 | 1 | 11/01/2021 17:45 | 2021 年 11 月 1 日 18:00 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 18:00 | 2021 年 11 月 1 日 18:15 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 18:15 | 2021 年 11 月 1 日 18:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 18:30 | 2021 年 11 月 1 日 18:40 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 18:40 | 2021 年 11 月 1 日 18:45 | 乙|
| 1 | 1 | 2021 年 11 月 1 日 18:45 | 2021 年 11 月 1 日 18:50 | 乙|
| 1 | 1 | 2021 年 11 月 1 日 18:50 | 2021 年 11 月 1 日 19:00 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 19:00 | 2021 年 11 月 1 日 19:15 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 19:15 | 2021 年 11 月 1 日 19:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 19:30 | 2021 年 11 月 1 日 19:45 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 19:45 | 2021 年 11 月 1 日 20:00 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 20:00 | 2021 年 11 月 1 日 20:15 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 20:15 | 2021 年 11 月 1 日 20:30 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 20:30 | 2021 年 11 月 1 日 20:45 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 20:45 | 2021 年 11 月 1 日 21:00 | 一个 |
+--------+--------+------+------------ ------+------------+

最终结果应该是这样的：

+--------+--------+------+------------ ------+------------+
| 用户名 | 日期ID | 开始 | 结束 | 活动ID |
+--------+--------+------+------------ ------+------------+
| 1 | 1 | 2021 年 11 月 1 日 12:10 | 2021 年 11 月 1 日 13:55 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 13:55 | 2021 年 11 月 1 日 14:05 | 乙|
| 1 | 1 | 2021 年 11 月 1 日 14:05 | 2021 年 11 月 1 日 15:10 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 15:10 | 2021 年 11 月 1 日 16:10 | C |
| 1 | 1 | 2021 年 11 月 1 日 16:10 | 2021 年 11 月 1 日 18:40 | 一个 |
| 1 | 1 | 2021 年 11 月 1 日 18:40 | 2021 年 11 月 1 日 18:50 | 乙|
| 1 | 1 | 2021 年 11 月 1 日 18:50 | 2021 年 11 月 1 日 21:00 | 一个 |
+--------+--------+------+------------ ------+------------+

我尝试使用上一行/下一行值，但它只会返回上一行，但是无法获得第一行和最后一行。

感谢您的帮助！

标签： sqlsql-server

解决方案

这是一种孤岛问题。假设相邻的值没有间隙，那么行号的差异是最简单的方法：

select userid, dateid, activityid,
       min(start), max(end)
from (select t.*,
             row_number() over (partition by userid, dateid order by start) as seqnum,
             row_number() over (partition by userid, dateid, activity_id order by start) as seqnum_2
      from t
     ) t
group by userid, dateid, activityid, (seqnum - seqnum_2)
order by userid, dateid, min(start);

请注意，startandend对于列名来说是非常糟糕的选择，因为它们是 SQL 关键字。我认为你的真实姓名更安全。

推荐阅读