sql实现24小时归因

with popup as (
-- 弹窗点击
select distinct_id
     , sc_user_id
     , time         click_at
     , test_flag
     , plan_id
     , bucket_desc
     , exper_id
     , page_type
     , sc_city
     , ds
from events
where ds in ('${yesterday}','${today}') 
and plan_id != ''
and event = 'popupClick'
), pay as (
-- 支付
select distinct_id 
     , order_id
     , sc_user_id 
     , time         pay_at
     , ds
from events
where ds in ('${yesterday}','${today}') 
and event = 'submitOrder'
), orders as (
select user_id
     , order_id
     , real_amount + gift_card_amount order_amount
from orders
where ds = '${today}'
and paid_at != '1970-01-01 00:00:00'
)
, diff as (
-- 取24小时支付
select pay.distinct_id
     , pay.sc_user_id
     , pay.pay_at
     , popup.click_at
     , popup.test_flag
     , popup.plan_id
     , popup.bucket_desc
     , popup.exper_id
     , popup.page_type
     , datediff(to_date(pay_at ,'yyyy-mm-dd hh:mi:ss.ff3'),  to_date(click_at , 'yyyy-mm-dd hh:mi:ss.ff3'), 'ss') diff
     , popup.sc_city
     , popup.ds
     , coalesce(orders.order_id)    order_id
     , coalesce(cast(orders.order_amount as decimal(16, 4)), 0) order_amount
from pay
inner join popup
on popup.distinct_id = pay.distinct_id
inner join orders
on orders.user_id = pay.distinct_id
and orders.order_id = pay.order_id
where datediff(to_date(pay_at ,'yyyy-mm-dd hh:mi:ss.ff3'),  to_date(click_at , 'yyyy-mm-dd hh:mi:ss.ff3'), 'ss') >= 0  
and datediff(to_date(pay_at ,'yyyy-mm-dd hh:mi:ss.ff3'),  to_date(click_at , 'yyyy-mm-dd hh:mi:ss.ff3'), 'ss') <= 86400
), rk as (
-- 排序
select distinct_id
     , sc_user_id
     , pay_at
     , click_at
     , test_flag
     , plan_id
     , bucket_desc
     , exper_id
     , page_type
     , diff
     , order_amount 
     , order_id
     , sc_city
     , dense_rank() over(partition by distinct_id order by pay_at asc) pay_rk
     , dense_rank() over(partition by distinct_id order by click_at desc) click_rk
     , ds
from diff
)
insert overwrite table dwd_log_track_reach_pay_attribution_di partition (ds)
-- 取距离最近的一次点击和支付行为和点击
select distinct_id
     , sc_user_id
     , pay_at
     , click_at
     , test_flag
     , plan_id
     , bucket_desc
     , exper_id
     , page_type
     , diff
     , order_amount 
     , order_id
     , sc_city
     , ds
from rk
where pay_rk = 1
and click_rk = 1
;