时间戳

首页 > 解决方案 > 如何找到按日期排序的两个连续行，包含特定值？

sql - 如何找到按日期排序的两个连续行，包含特定值？

问题描述

我有一个表，其中包含以下结构和数据：

| ID    | Date          | Result    |
|----   |------------   |--------   |
| 1     | 30/04/2020    | +         |
| 1     | 01/05/2020    | -         |
| 1     | 05/05/2020    | -         |
| 2     | 03/05/2020    | -         |
| 2     | 04/05/2020    | +         |
| 2     | 05/05/2020    | -         |
| 2     | 06/05/2020    | -         |
| 3     | 01/05/2020    | -         |
| 3     | 02/05/2020    | -         |
| 3     | 03/05/2020    | -         |
| 3     | 04/05/2020    | -         |

我正在尝试编写一个 SQL 查询（我正在使用 SQL Server），它返回给定 ID的前两个连续否定结果的日期。例如，对于 ID 号。1，前两个连续的负面结果是在 01/05 和 05/05。ID 号 2 的前两个连续结果分别在 05/05 和 06/05。ID 3 的前两个连续阴性结果分别在 01/05 和 02/05 出现。

所以查询应该产生以下结果：

| ID    | FirstNegativeDate     |
|----   |-------------------    |
| 1     | 01/05                 |
| 2     | 05/05                 |
| 3     | 01/05                 |

请注意，日期不一定相隔一天。有时，两个连续的阴性测试可能相隔几天。但它们仍应被视为“连续阴性测试”。换句话说，两个阴性测试只有在它们之间有阳性测试结果时才“连续”。

这如何在 SQL 中完成？我已经阅读了一些内容，看起来可能需要 PARTITION BY 语句，但我不确定它是如何工作的。

标签： sqlsql-serverdatewindow-functionsgaps-and-islands

解决方案

这是一个间隙和孤岛问题，您希望从'-'s 的第一个孤岛开始，它至少包含两行。

我会推荐lead()和聚合：

select id, min(date) first_negative_date
from (
    select t.*, lead(result) over(partition by id order by date) lead_result
    from mytable t
) t
where result = '-' and lead_result = '-'
group by id

推荐阅读