SQL-根据日期差异选择行
假设我们有下表：
| 代码 | DT |
|---|---|
| c1 | 2020-10-01 |
| c1 | 2020-10-05 |
| c1 | 2020-10-09 |
| c1 | 2020-10-10 |
| c1 | 2020-10-20 |
| c2 | 2020-10-07 |
| c2 | 2020-10-09 |
| c2 | 2020-10-15 |
| c2 | 2020-10-16 |
| c2 | 2020-10-20 |
| c2 | 2020-10-24 |
回答
您可以使用match_recognize相对轻松地解决这个问题
with data(code, dt) as (
select 'c1', to_date('2020-10-01', 'YYYY-MM-DD') from dual union all
select 'c1', to_date('2020-10-05', 'YYYY-MM-DD') from dual union all
select 'c1', to_date('2020-10-09', 'YYYY-MM-DD') from dual union all
select 'c1', to_date('2020-10-10', 'YYYY-MM-DD') from dual union all
select 'c1', to_date('2020-10-20', 'YYYY-MM-DD') from dual union all
select 'c2', to_date('2020-10-07', 'YYYY-MM-DD') from dual union all
select 'c2', to_date('2020-10-09', 'YYYY-MM-DD') from dual union all
select 'c2', to_date('2020-10-15', 'YYYY-MM-DD') from dual union all
select 'c2', to_date('2020-10-16', 'YYYY-MM-DD') from dual union all
select 'c2', to_date('2020-10-20', 'YYYY-MM-DD') from dual union all
select 'c2', to_date('2020-10-24', 'YYYY-MM-DD') from dual
)
select *
from data match_recognize (
partition by code
order by dt
measures
init.dt dt
one row per match
pattern (init less_than_7_days*)
define
less_than_7_days as less_than_7_days.dt - init.dt < 7
)
您只需按代码分区，按日期排序，然后获取日期差异小于 7（与 init 相比）的任何行init和 0 多行 ( less_than_7_days*)。您为整个匹配返回 1 行（初始化 + 后续行），其中将包含日期从init
