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

Question

我有一个包含以下结构和数据的表:

| 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 No. 2 的前两个连续结果是在 05/05 和 06/05。
3 号 ID 的前两个连续阴性结果在 01/05 和 02/05 上出现。

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

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

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

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

Answer 1

这是一个间隙和孤岛问题，您希望从 '-' 的第一个孤岛开始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