计算留存率的一个小问题,求助

hydral 2015-08-19 10:21:04
现有如下两张表:

用户表
用户ID 注册时间
1 20150303
2 20150304
3 20150305
4 20150306

订单表
用户ID 订单创建时间
1 20150506
1 20150405
2 20150304
3 20150506
3 20150607

数据抽取起始时间2015年1月 结束时间2015年7月
计算隔月留存率 :当月有首笔交易用户数 次月仍有交易用户数

请教大神们,SQL该如何写,谢谢!
...全文
80 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Tiger_Zhao 2015-08-19
那么不需要用户表了,把首单日期当作注册时间用。
还有上面o2的关联条件写错了。
WITH /* 测试数据
订单表(用户ID,订单创建时间)AS(
SELECT 1,'20150506' UNION ALL
SELECT 1,'20150405' UNION ALL
SELECT 2,'20150304' UNION ALL
SELECT 3,'20150506' UNION ALL
SELECT 3,'20150607'
), */
u AS (
SELECT 用户ID,
MIN(订单创建时间) AS 注册时间
FROM 订单表
GROUP BY 用户ID
)
,m AS (
SELECT DATEADD(month,number,'2015-01-01') d0,
DATEADD(month,number+1,'2015-01-01') d1,
DATEADD(month,number+2,'2015-01-01') d2
FROM master..spt_values
WHERE type = 'p'
AND number < 7
)
SELECT CONVERT(varchar(7),m.d0,120) 月,
COUNT(DISTINCT o1.用户ID) 当月有首笔交易用户数,
COUNT(DISTINCT o2.用户ID) 次月仍有交易用户数
FROM m
JOIN u
ON u.注册时间 >= m.d0
AND u.注册时间 < m.d1
JOIN 订单表 o1
ON u.用户ID = o1.用户ID
AND o1.订单创建时间 >= m.d0
AND o1.订单创建时间 < m.d1
LEFT JOIN 订单表 o2
ON u.用户ID = o2.用户ID
AND o2.订单创建时间 >= m.d1
AND o2.订单创建时间 < m.d2
GROUP BY CONVERT(varchar(7),m.d0,120)

月      当月有首笔交易用户数 次月仍有交易用户数
------- -------------------- ------------------
2015-03 1 0
2015-04 1 1
2015-05 1 1


又:回复请不要大段包含原文。
回复
hydral 2015-08-19
引用 1 楼 Tiger_Zhao 的回复:
WITH /* 测试数据
用户表(用户ID,注册时间)AS(
    SELECT 1,'20150303' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150305' UNION ALL
    SELECT 4,'20150306'
)
,订单表(用户ID,订单创建时间)AS(
    SELECT 1,'20150506' UNION ALL
    SELECT 1,'20150405' UNION ALL
    SELECT 2,'20150304' UNION ALL
    SELECT 3,'20150506' UNION ALL
    SELECT 3,'20150607'
),*/
m AS (
    SELECT DATEADD(month,number,'2015-01-01') d0,
           DATEADD(month,number+1,'2015-01-01') d1,
           DATEADD(month,number+2,'2015-01-01') d2
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 7
)
    SELECT CONVERT(varchar(7),m.d0,120) 月,
           COUNT(DISTINCT o1.用户ID) 当月有首笔交易用户数,
           COUNT(DISTINCT o2.用户ID) 次月仍有交易用户数
      FROM m
      JOIN 用户表 u
        ON u.注册时间 >= m.d0
       AND u.注册时间 < m.d1
      JOIN 订单表 o1
        ON u.用户ID = o1.用户ID
       AND o1.订单创建时间 >= m.d0
       AND o1.订单创建时间 < m.d1
 LEFT JOIN 订单表 o2
        ON u.用户ID = o2.用户ID
       AND o2.订单创建时间 >= m.d0
       AND o2.订单创建时间 < m.d1
  GROUP BY CONVERT(varchar(7),m.d0,120)
月      当月有首笔交易用户数 次月仍有交易用户数
------- -------------------- ------------------
2015-03                    1                  1
非常感谢! 还有有一种情况用户注册之后,当月可能没有订单,过了几个月之后才产生首单,这种情况该如何处理呢?
回复
Tiger_Zhao 2015-08-19
WITH /* 测试数据
用户表(用户ID,注册时间)AS(
SELECT 1,'20150303' UNION ALL
SELECT 2,'20150304' UNION ALL
SELECT 3,'20150305' UNION ALL
SELECT 4,'20150306'
)
,订单表(用户ID,订单创建时间)AS(
SELECT 1,'20150506' UNION ALL
SELECT 1,'20150405' UNION ALL
SELECT 2,'20150304' UNION ALL
SELECT 3,'20150506' UNION ALL
SELECT 3,'20150607'
),*/
m AS (
SELECT DATEADD(month,number,'2015-01-01') d0,
DATEADD(month,number+1,'2015-01-01') d1,
DATEADD(month,number+2,'2015-01-01') d2
FROM master..spt_values
WHERE type = 'p'
AND number < 7
)
SELECT CONVERT(varchar(7),m.d0,120) 月,
COUNT(DISTINCT o1.用户ID) 当月有首笔交易用户数,
COUNT(DISTINCT o2.用户ID) 次月仍有交易用户数
FROM m
JOIN 用户表 u
ON u.注册时间 >= m.d0
AND u.注册时间 < m.d1
JOIN 订单表 o1
ON u.用户ID = o1.用户ID
AND o1.订单创建时间 >= m.d0
AND o1.订单创建时间 < m.d1
LEFT JOIN 订单表 o2
ON u.用户ID = o2.用户ID
AND o2.订单创建时间 >= m.d0
AND o2.订单创建时间 < m.d1
GROUP BY CONVERT(varchar(7),m.d0,120)

月      当月有首笔交易用户数 次月仍有交易用户数
------- -------------------- ------------------
2015-03 1 1
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-19 10:21
社区公告
暂无公告