利用表的自关联查询
表A
CUS_ID | TXN_DT | ID |
1 | 20180101 | 1 |
2 | 20180101 | 2 |
3 | 20180101 | 3 |
1 | 20180102 | 4 |
2 | 20180102 | 5 |
2 | 20180102 | 6 |
1 | 20180103 | 7 |
3 | 20180103 | 8 |
with t as (
select
cus_id
,txn_dt
from a
qualify row_number()over(partition by cus_id,txn_dt order by id ) = 1
)
select
cus_id
from t m
inner join t n
on m.cus_id = n.cus_id
and m.txn_dt <= n.txn_dt +2
group by 1
having count(cus_id) = 3
;
--或者
select
cus_id
from a m
inner join a n
on m.cus_id = n.cus_id
and m.txn_dt <= n.txn_dt +2
group by 1
having count(distinct txn_dt) = 3
;