SQL如何在值更改时检索第一行

Question

我有一个包含ID、日期和标志的表。我想检索某一年内标志从0显式更改为1的第一条记录。

ID    DATE        FLAG
1    2019-01-31     0
1    2019-02-28     1
1    2019-03-31     1
2    2019-01-31     1
2    2019-02-28     1
2    2019-03-31     1
2    2019-04-30     0
2    2019-05-31     1
3    2019-01-31     0
3    2019-02-28     1
3    2019-03-31     0
3    2019-04-30     1

因此，我的预期结果是，1是2019-01-31，2将被排除在外，3则是2019-02-28。

到目前为止，我有这个查询，它在大多数情况下都有效，但当我进行QA时，这个查询似乎并不排除那些从1开始，切换到0，然后返回到1的查询。

查询

SELECT t.ID,
Date, 
Flag
FROM table t
WHERE Flag = '1' 
AND t.Date > (SELECT MAX(t2.Date) FROM table t2 WHERE t2.ID = t.ID AND t2.Flag = '0' AND t2.Date BETWEEN '2019-01-01' AND '2019-12-31') 
AND t.Date BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date);

任何帮助都将是伟大的，谢谢！

编辑：我添加了SQLFiddle

Answer 1

使用lag()获取上一个标志，然后聚合：

select id, min(date)
from (select *,
lag(flag) over (partition by id, year(date) order by date) as prev_flag
from temp
) t
where flag = 1 and prev_flag = 0
group by id, year(date);

SQL Fiddle

这将返回每年的结果。当然，如果您愿意，您可以在子查询中按年份进行筛选。

Answer 2

我知道您想要第一个1-仅用于以0开头的系列。

如果你运行的是MySQL 8.0，你可以使用窗口函数：

select id, min_date
from (
select 
id,
flag,
row_number() over(partition by id order by date) rn,
min(case when flag = 1 then date end) over(partition by id) min_date
from mytable
) t
where rn = 1 and flag = 0

子查询按date对具有相同id的记录进行排序，并计算组中第一个1标志的日期；外部查询对每个组的第一条记录进行过滤，确保它具有0标志，并显示计算出的日期。