要求:
有表:用户id,订单id,下单日期
该用户符合365天内无交易且当日有交易的数据打标签,如果当天有多条记录,同样打标签。
思路:
当前订单时间 - 最近一次的下单时间 >= 365 ,即最近365天内无订单记录。
中间有个问题,一天内多次下单,只能先写子查询去重,后续再关联出全天所有记录,不知道有没有其他合适的方式。
WITH o AS
(
SELECT '01' AS userid
,'1001' AS orderid
,CAST('2023-01-01' AS DATE) AS paydate
UNION ALL
SELECT '01' AS userid
,'1002' AS orderid
,CAST('2024-08-02' AS DATE) AS paydate
UNION ALL
SELECT '01' AS userid
,'1003' AS orderid
,CAST('2024-08-02' AS DATE) AS paydate
UNION ALL
SELECT '02' AS userid
,'1000' AS orderid
,CAST('2021-01-01' AS DATE) AS paydate
UNION ALL
SELECT '02' AS userid
,'1004' AS orderid
,CAST('2023-01-02' AS DATE) AS paydate
UNION ALL
SELECT '02' AS userid
,'1005' AS orderid
,CAST('2023-08-02' AS DATE) AS paydate
UNION ALL
SELECT '03' AS userid
,'1006' AS orderid
,CAST('2023-01-01' AS DATE) AS paydate
)
SELECT o.*
,d.flag
FROM o AS o
LEFT JOIN (
SELECT userid
,paydate
,f_date
,CASE WHEN DATEDIFF(paydate,f_date) >= 365 THEN 'yes'
ELSE 'no'
END AS flag
FROM (
SELECT userid
,paydate
,LAG(paydate,1,'') OVER (PARTITION BY userid ORDER BY paydate ASC ) AS f_date
FROM (
SELECT userid
,paydate
FROM o
GROUP BY userid
,paydate
) AS c
) AS a
) AS d
ON o.userid = d.userid
AND o.paydate = d.paydate
;
结果: