非连续登录用户，计算每个用户当日、当日到次日、3天内的消费订单量

非连续登录用户，计算每个用户当日、当日到次日、3天内的消费订单量

背景：需要统计每日付费用户，其当日内购买订单量、当日到次日内购买订单量、3天内的购买订单量分布情况；

with   tmp_order_detail as (
  select 
    to_char(t1.create_time,'yyyymmdd') as ds 
    ,order_id    --订单ID
    ,order_no   --订单编号
    ,cat_name  --品类
    ,from_user_id_old as from_user_id  --用户ID
    ,to_user_id_old as to_user_id
    ,from_user_id as from_uid 
    ,to_user_id as to_uid
    ,price --单价
    ,total_amount --总价
    ,pay_money  --支付金额
    ,status  --订单状态
    ,`count` as hours    --局数
    ,coupon_id  --优惠券ID
    ,t1.create_time --创建时间
    ,begin_time --开始时间
    ,pay_time  --支付时间
    ,confirm_time
    ,finish_time  --完成时间
    ,end_time  --结束时间
    ,case when  order_type IN(2,12) then '约单'
        when  order_type IN(1,10) then '定向单'
        else '其他' end as order_type_name 
    ,case when pay_status not in (1) then '未支付'
          when pay_status in     (1) then '已支付'
          else '其他'
      end as  pay_status_name
    ,case when is_new_cd =1 then '当天新用户'
          else '老用户' end as  is_new
    ,case when is_newpay = 1 then '新付费用户'
          else '老付费用户' end as  is_newpay
    -- ,concat(to_char(DATEADD(create_time,-WEEKDAY(create_time),'dd'),'yyyymmdd')
    --   ,'~',to_char(DATEADD(create_time,6-WEEKDAY(create_time),'dd'),'yyyymmdd')) as weekly_range
    -- ,weekofyear(create_time) as weekly
  from  dwd_bixin_order_day t1
  left join (select id,cat_name from t_biz_cat) t2
  on (t1.play_category = t2.id) 
  where to_char(t1.create_time,'yyyymmdd')  between  '20210201' and to_char(DATEADD(getdate(),-1,'dd'),'yyyymmdd') 
  and status in (2,3) -- 完成订单

)


,tmp_da_user as (
  select 
    ds
  ,'' as cat_name
  ,from_uid
  ,count(distinct order_id) as 完成订单数
  from tmp_order_detail 
  group by 
  ds
--   ,cat_name
  ,from_uid
)

,tmp_da_user_ext as (
  select 
  t1.ds
  ,t1.cat_name
  ,t1.from_uid
  ,sum(case when datediff( to_date(t2.ds,'yyyymmdd') ,to_date(t1.ds,'yyyymmdd'),'dd') = 0 then  t1.完成订单数 end) as 当天完成订单数
  ,sum(case when  datediff( to_date(t2.ds,'yyyymmdd') ,to_date(t1.ds,'yyyymmdd'),'dd') = 1
  then t2.完成订单数 end) as 第次日完成订单数
   ,sum(case when datediff( to_date(t2.ds,'yyyymmdd') ,to_date(t1.ds,'yyyymmdd'),'dd') = 2
  then t2.完成订单数 end) as 第3天内完成订单数 
  from tmp_da_user t1 
  left join tmp_da_user t2 
  on (t1.from_uid = t2.from_uid 
  and t1.cat_name = t2.cat_name 
  )
  group by 
  t1.ds
  ,t1.cat_name
  ,t1.from_uid
)


select *
,当天完成订单数+nvl(第次日完成订单数,0) as 注册到次日完成订单数
,当天完成订单数+nvl(第次日完成订单数,0)+nvl(第3天内完成订单数,0) as 注册到3天内完成订单数
from tmp_da_user_ext 
where from_uid = '1833812040000108'

结果如下图所示：

易错点

使用left join 直接sum 会导致重复计算,因此需要避免；

其他方法

可以使用 窗口函数.lead()over()OVER(PARTITION BY ORDER BY asc ) 解决；