sql语句:
select
`CUSTKEY`,
COUNT(distinct(`CUSTKEY`)) count
from
^^^
(SELECT
`CUSTKEY`,
`event_date`,
lag(event_date,1,"1997-01-01") over(distribute by `CUSTKEY` sort by `event_date`) lag_1,
lead(event_date,1,"1999-99-99") over(distribute by `CUSTKEY` sort by `event_date`) lead_1
from
(select
`CUSTKEY`,
to_date(`ORDERDATE`) event_date
FROM
ods.ORDERS)t1)t2
WHERE
((moth(lag_1)-moth(event_data)) >1 )
or
((moth(event_data)-moth(lead_1) >1)
GROUP BY `CUSTKEY`