我有一个表格,格式如下
Name|Token| Date |
---------------------
John|7 |2010-4-30|
John|7 |2011-4-30|
John|9 |2011-5-30|
John|9 |2012-7-30|
John|9 |2015-1-30|
John|7 |2016-10-1|
John|9 |2016-11-3|
John|9 |2018-1-1 |
John|7 |2021-9-9 |
我试图将数据从这个表复制到一个新表,我需要从每个人的令牌9的最新一组行中找出最老的日期。在这种情况下,我需要得到2016-11-3
作为我的输出。现在对于所有令牌都是9的情况,我可以很容易地使用group by
子句和min(date)
并获得我的结果,但在这种情况下,如果我使用group by
子句和min(date)
,我会得到像john|9|2011-5-30|
这样的结果,但这是不正确的,因为令牌7
打破了2016-10-1
上的集合。
额外的情况下案例1:
Name|Token| Date |
---------------------
John|7 |2010-4-30|
John|7 |2011-4-30|
John|9 |2011-5-30|
John|9 |2012-7-30|
回答=比;2011-5-30。因为第3行和第4行,都有标记9,并且紧挨在一起因此序列
John|9 |2011-5-30|
John|9 |2012-7-30|
案例2:
Name|Token| Date |
---------------------
John|7 |2010-4-30|
John|7 |2011-4-30|
John|9 |2011-5-30|
John|9 |2012-7-30|
John|9 |2015-1-30|
John|7 |2016-10-1|
John|9 |2016-11-3|
John|9 |2018-1-1 |
John|7 |2021-9-9 |
John|9 |2022-1-1 |
回答→2022-1-1,因为以9作为标记并且彼此紧接(在本例中只有1行)的最新行集是
John|9 |2022-1-1 |
案例3:
Name|Token| Date |
---------------------
John|9 |2010-4-30|
John|9 |2011-4-30|
John|9 |2011-5-30|
John|9 |2012-7-30|
John|9 |2015-1-30|
John|9 |2016-10-1|
John|9 |2016-11-3|
John|9 |2018-1-1 |
John|7 |2021-9-9 |
答案->2010-4-30,这里包含标记为9的序列的最新行集是
John|9 |2010-4-30|
John|9 |2011-4-30|
John|9 |2011-5-30|
John|9 |2012-7-30|
John|9 |2015-1-30|
John|9 |2016-10-1|
John|9 |2016-11-3|
John|9 |2018-1-1 |
这是一个空白&岛屿问题的转折。你可以这样做:
with
g as (
select *,
sum(inc) over(partition by name order by date) as grp
from (
select *,
case when token <> lag(token) over(partition by name order by date)
then 1 else 0 end as inc
from t
) x
)
select g.name, min(g.date) as min_date
from g
join (
select name, max(grp) as max_grp from g where token = 9 group by name
) m on m.name = g.name and m.max_grp = g.grp
group by g.name
结果(为测试目的修改数据):
name min_date
------ ----------
Alice 2019-03-01
John 2016-11-03
参见运行示例:db<>fiddle。