sql - 如何在 Sql Server 中的相邻记录中查找具有相同值的记录？ (我相信正确的术语是区域？？)

Question

查找具有相同值的相邻记录的开始和结束时间？

我有一个包含心率读数(以每分钟节拍为单位)和日期时间字段的表格。 (实际上这些字段是 heartrate_id 、 heartrate 和 datetime 。)这些数据是由每 6 秒记录一次心率和时间的设备生成的。有时心率监测器会给出错误的读数，并且记录的每分钟心跳会“坚持”一段时间。通过棒，我的意思是每分钟的节拍值在相邻的时间是相同的。

基本上我需要找到所有心率相同的记录(例如每分钟 5 次心跳，每分钟 100 次心跳等)，但只在相邻的记录中。如果设备记录 3 次连续读数(或 100 次连续读数)每分钟 25 次，我需要定位这些事件。结果需要有心率、心率开始时间和心率结束时间，理想情况下，结果应该是这样的:

heartrate starttime endtime
--------- --------- --------
1.00      21:12:00  21:12:24
35.00     07:00:12  07:00:36

我已经尝试了几种不同的方法，但到目前为止我已经出局了。任何帮助将不胜感激!

Answer 1

编辑:

经过审查，我对这个答案的原始工作都不是很好。这实际上属于被称为 gaps-and-islands 的问题类别。 ，并且这个修订后的答案将使用我从第一次回答这个问题以来从类似问题/学到的信息中收集到的信息。

事实证明，这个查询可以通过 完成。拍品 比我原先想象的更简单:

WITH Grouped_Run AS (SELECT heartRate, dateTime,
                            ROW_NUMBER() OVER(ORDER BY dateTime) -  
                            ROW_NUMBER() OVER(PARTITION BY heartRate ORDER BY dateTime) AS groupingId
                     FROM HeartRate)

SELECT heartRate, MIN(dateTime), MAX(dateTime)
FROM Grouped_Run
GROUP BY heartRate, groupingId
HAVING COUNT(*) > 2

SQL Fiddle Demo

那么这里发生了什么？间隙和孤岛问题的定义之一是需要连续值的“组”(或缺乏连续值)。通常生成序列来解决这个问题，利用一个经常被忽视/过于直观的事实:减去序列产生一个恒定值。

例如，想象以下序列和减法(行中的值不重要):

position   positionInGroup  subtraction
=========================================
1          1                0
2          2                0
3          3                0
4          1                3
5          2                3
6          1                5
7          4                3
8          5                3

position是在所有记录上生成的简单序列。 positionInGroup是为每组不同记录生成的简单序列。在这种情况下，实际上有 3 组不同的记录(从 position = 1, 4, 6 开始)。 subtraction是其他两列之间差异的结果。请注意，不同组的值可能会重复!
序列必须共享的关键属性之一是它们必须在 中的数据行上生成。同 命令，否则这会中断。

那么 SQL 是如何做到这一点的呢？通过使用 ROW_NUMBER()此函数将在记录的“窗口”上生成一系列数字:

ROW_NUMBER() OVER(ORDER BY dateTime)

将生成 position序列。

ROW_NUMBER() OVER(PARTITION BY heartRate ORDER BY dateTime)

将生成 positionInGroup序列，每个 heartRate作为一个不同的群体。
在大多数此类查询的情况下，两个序列的值并不重要，重要的是减法(以获得序列组)，因此我们只需要减法的结果。
我们还需要 heartRate以及他们提供答案的时间。

最初的答案询问了每次心跳“运行”的开始和结束时间。这是一个标准 MIN(...)/ MAX(...) ，这意味着 GROUP BY .我们需要同时使用原始 heartRate列(因为这是一个非聚合列) 和 我们生成的 groupingId (它标识每个卡住值的当前“运行”)。

部分问题只要求重复三遍或更多次的运行。 HAVING COUNT(*) > 2是一条忽略长度小于等于 2 的游程的指令；它计算每组的行数。