复购率公式

运营需求拆解：

单一复购率公式（按周计算，需要计算从4.1-8.4的数据）：

分子：本周和上周对同一商户都下过单的人数

分母：上周下过订单的总人数

注：需要考虑到可疑订单，即刷单的状况

需求拆解：难点在与分子计算；

需求进一步拆解：本周和上周对同同一商户下过单，则可以理解为用户的复购；

可以把用户和商户 看成是一组，计算他们的复购即可；

具体代码如下：

with tmp_weekly_user_god as (
    select 
    concat(to_char(DATEADD(trade_date,-WEEKDAY(trade_date),'dd'),'yyyymmdd')
    ,'~' ,to_char(DATEADD(trade_date,6-WEEKDAY(trade_date),'dd'),'yyyymmdd'))  as  weekly_range 
    ,weekofyear(trade_date) as weekly
    ,weekofyear(trade_date)+ 1 as next_weekly
    ,from_user_id
    ,to_user_id
    ,concat('-',from_user_id,to_user_id) as group_group  -- 组合
    from ypp_trade_flow 
    where trade_type = 'ORDER' -- 交易订单
    And TO_CHAR(trade_date,'yyyymmdd')  between  '20190401' and '20190804' 
    group by concat(to_char(DATEADD(trade_date,-WEEKDAY(trade_date),'dd'),'yyyymmdd')
    ,'~' ,to_char(DATEADD(trade_date,6-WEEKDAY(trade_date),'dd'),'yyyymmdd'))  
    ,from_user_id
    ,to_user_id
    ,weekofyear(trade_date)
)

-- 留存率计算

select 
t1.weekly  `周`
,t1.weekly_range `周维度`
,'全部' as `类型`
,count(distinct t1.from_user_id) as `纯用户下单人数`
,count(distinct t1.group_group) as `一组下单人数`
,count(distinct t2.group_group) as `一组本周用户大神在上周继续下单人数` 
--,concat('',round(count(distinct t1.group_group)/count(distinct t2.group_group),4)*100,'%') as `单一复购率公式`
 from tmp_weekly_user_god t1 
left join tmp_weekly_user_god t2 
on t1.group_group = t2.group_group
and t1.weekly = t2.next_weekly
group by t1.weekly,t1.weekly_range

结果集如下：


细节问题：

• 周维度：如果只使用weekofyear 只是给了一个具体的数字，而不清楚具体的周开始时间和结束时间，因此可以加上，更直观，起代码如下：

concat(to_char(DATEADD(trade_date,-WEEKDAY(trade_date),'dd'),'yyyymmdd'),'~' ,to_char(DATEADD(trade_date,6-WEEKDAY(trade_date),'dd'),'yyyymmdd'))

拆分下：

周开始时间:```to_char(DATEADD(trade_date,-WEEKDAY(trade_date),'dd'),'yyyymmdd')``

周结束时间:to_char(DATEADD(trade_date,6-WEEKDAY(trade_date),'dd'),'yyyymmdd')

• 运营的需求，细节还是有一点点问题，有没有发现，这个后续和运营沟通解决了；

参考：

[Get the week start date and week end date from week number in SQL Server - Stack Overflow](