今日面试数据分析师岗位，遇到了关于用户留存的SQL题，由于准备不充分，没有写好。搜寻了网上一些写法，记录一下。题目如下：
留存率计算
字段及表说明：
表名：user_log
字段名：
log_day:登录日期 格式20190214
device_id:用户设备id
app_id:用户app的id，其中device_id和app_id确定唯一的用户
网上有方案：SQL计算留存率等指标
由于前期在阿里天池数据集中下载了淘宝用户行为的数据，就以此为基础进行SQL留存分析的训练。原始数据量较大，有1亿条左右，随机抽取500万条，以页面访问作为留存分析行为。
表结构是这样的：
- 计算每日留存率
基准日期以20171124为例，计算之后的1,2,3,4,5,6,7,14,30留存情况
SELECT date0
,COUNT(user_id_d0) AS day_0
,COUNT(user_id_d1) AS day_1
,COUNT(user_id_d2) AS day_2
,COUNT(user_id_d3) AS day_3
,COUNT(user_id_d4) AS day_4
,COUNT(user_id_d5) AS day_5
,COUNT(user_id_d6) AS day_6
,COUNT(user_id_d7) AS day_7
,COUNT(user_id_d14) AS day_14
,COUNT(user_id_d30) AS day_30
,COUNT(user_id_d1)*.1/COUNT(user_id_d0) AS retention_d1
,COUNT(user_id_d2)*.1/COUNT(user_id_d0) AS retention_d2
,COUNT(user_id_d3)*.1/COUNT(user_id_d0) AS retention_d3
,COUNT(user_id_d4)*.1/COUNT(user_id_d0) AS retention_d4
,COUNT(user_id_d5)*.1/COUNT(user_id_d0) AS retention_d5
,COUNT(user_id_d6)*.1/COUNT(user_id_d0) AS retention_d6
,COUNT(user_id_d7)*.1/COUNT(user_id_d0) AS retention_d7
,COUNT(user_id_d14)*.1/COUNT(user_id_d0) AS retention_d14
,COUNT(user_id_d30)*.1/COUNT(user_id_d0) AS retention_d30
FROM(
SELECT date0
,user_id_d0
,user_id_d1
,user_id_d2
,user_id_d3
,user_id_d4
,user_id_d5
,user_id_d6
,user_id_d7
,user_id_d14
,user_id_d30
FROM(--找出新增
SELECT CAST(date AS date) AS date0
,user_id AS user_id_d0
FROM user_behavior t1
WHERE date='2017-11-24' AND
behavior_type='pv' AND
user_id not in(SELECT distinct user_id
FROM user_behavior s
WHERE s.date<t1.date
)
) t1
LEFT JOIN
(
SELECT CAST(date AS date) AS date1
,user_id AS user_id_d1
FROM user_behavior
WHERE behavior_type='pv'
) t2
ON t1.date0=DATEADD(day,-1,CAST(t2.date1 AS date)) AND
t1.user_id_d0=t2.user_id_d1
LEFT JOIN
(
SELECT CAST(date AS date) AS date2
,user_id AS user_id_d2
FROM user_behavior
WHERE behavior_type='pv'
) t3
ON t1.date0=DATEADD(day,-2,CAST(date2 AS date)) AND
t1.user_id_d0=t3.user_id_d2
LEFT JOIN
(
SELECT CAST(date AS date) AS date3
,user_id AS user_id_d3
FROM user_behavior
WHERE behavior_type='pv'
) t4
ON t1.date0=DATEADD(day,-3,CAST(date3 AS date)) AND
t1.user_id_d0=t4.user_id_d3
LEFT JOIN
(
SELECT CAST(date AS date) AS date4
,user_id AS user_id_d4
FROM user_behavior
WHERE behavior_type='pv'
) t5
ON t1.date0=DATEADD(day,-4,CAST(date4 AS date)) AND
t1.user_id_d0=t5.user_id_d4
LEFT JOIN
(
SELECT CAST(date AS date) AS date5
,user_id AS user_id_d5
FROM user_behavior
WHERE behavior_type='pv'
) t6
ON t1.date0=DATEADD(day,-5,CAST(date5 AS date)) AND
t1.user_id_d0=t6.user_id_d5
LEFT JOIN
(
SELECT CAST(date AS date) AS date6
,user_id AS user_id_d6
FROM user_behavior
WHERE behavior_type='pv'
) t7
ON t1.date0=DATEADD(day,-6,CAST(date6 AS date)) AND
t1.user_id_d0=t7.user_id_d6
LEFT JOIN
(
SELECT CAST(date AS date) AS date7
,user_id AS user_id_d7
FROM user_behavior
WHERE behavior_type='pv'
) t8
ON t1.date0=DATEADD(day,-7,CAST(date7 AS date)) AND
t1.user_id_d0=t8.user_id_d7
LEFT JOIN
(
SELECT CAST(date AS date) AS date14
,user_id AS user_id_d14
FROM user_behavior
WHERE behavior_type='pv'
) t9
ON t1.date0=DATEADD(day,-14,CAST(date14 AS date)) AND
t1.user_id_d0=t9.user_id_d14
LEFT JOIN
(
SELECT CAST(date AS date) AS date30
,user_id AS user_id_d30
FROM user_behavior
WHERE behavior_type='pv'
) t10
ON t1.date0=DATEADD(day,-30,CAST(date30 AS date)) AND
t1.user_id_d0=t10.user_id_d30
GROUP BY date0
,user_id_d0
,user_id_d1
,user_id_d2
,user_id_d3
,user_id_d4
,user_id_d5
,user_id_d6
,user_id_d7
,user_id_d14
,user_id_d30
) t11
GROUP BY date0;参考文章：SQL计算留存率等指标
当数据量大的时候，涉及到多个大表关联，效率很低，执行速度很慢。
2. 计算每日留存
SELECT first_day
,day_0
,day_1
,day_2
,day_3
,day_4
,day_5
,day_6
,day_7
,day_14
,day_30
,day_1*.1/day_0 AS retention_d1
,day_2*.1/day_0 AS retention_d2
,day_3*.1/day_0 AS retention_d3
,day_4*.1/day_0 AS retention_d4
,day_5*.1/day_0 AS retention_d5
,day_6*.1/day_0 AS retention_d6
,day_7*.1/day_0 AS retention_d7
,day_14*.1/day_0 AS retention_14
,day_30*.1/day_0 AS retention_30
FROM (SELECT first_day
,SUM(CASE WHEN by_day = THEN 1 ELSE END) day_0
,SUM(CASE WHEN by_day = 1 THEN 1 ELSE END) day_1
,SUM(CASE WHEN by_day = 2 THEN 1 ELSE END) day_2
,SUM(CASE WHEN by_day = 3 THEN 1 ELSE END) day_3
,SUM(CASE WHEN by_day = 4 THEN 1 ELSE END) day_4
,SUM(CASE WHEN by_day = 5 THEN 1 ELSE END) day_5
,SUM(CASE WHEN by_day = 6 THEN 1 ELSE END) day_6
,SUM(CASE WHEN by_day = 7 THEN 1 ELSE END) day_7
,SUM(CASE WHEN by_day = 14 THEN 1 ELSE END) day_14
,SUM(CASE WHEN by_day = 30 THEN 1 ELSE END) day_30
FROM(SELECT user_id,
date,
first_day,
DATEDIFF(day,first_day,date) AS by_day
FROM(SELECT t1.user_id,
t1.date,
t3.first_day
FROM(SELECT user_id,
CAST(date AS date) AS date
FROM user_behavior
WHERE behavior_type='pv'
GROUP BY user_id,
CAST(date AS date)
) t1
LEFT JOIN
(SELECT user_id,
min(date) first_day
FROM (SELECT user_id,
CAST(date AS date) AS date
FROM user_behavior
WHERE behavior_type='pv'
GROUP BY user_id,
CAST(date AS date)
) t2
GROUP BY user_id
) t3
ON t1.user_id = t3.user_id
) t4
) t5
GROUP BY first_day
) t6
ORDER BY first_day ;参考文章：如何用SQL做留存率分析
结果展现每日的留存情况：
以上的方式适合面试的时候使用，优点是仅在一条SQL内就可以根据给定的行为数据，将留存逻辑反映出来，但是没有考虑性能方面。
近期又想了一个办法，可能更适合实际工作中使用，为用户的行为数据记录打上新用户，老用户的标识，然后再留存分析，这样效率较高。
步骤一：首先将原始数据，打上新老用户标记：
1.对user_id的PV（页面浏览）日期打标识，创表结构
CREATE TABLE all_user_pv_flag_1
( user_id INT
,date DATE
,flag VARCHAR(10) --“new：新增用户，old：老用户”
);
--插入数据
INSERT INTO all_user_pv_flag_1
select user_id
,CAST(date AS DATE) AS date
,CASE WHEN rn=1 THEN 'new'
WHEN rn>=2 THEN 'old' END AS flag
FROM(SELECT user_id
,date
,RANK() OVER (PARTITION BY user_id ORDER BY date) AS rn
FROM user_behavior
WHERE behavior_type='pv'
) t1
GROUP BY user_id
,CAST(date AS DATE)
,CASE WHEN rn=1 THEN 'new'
WHEN rn>=2 THEN 'old' END ;
2.分品类user_id首次PV（页面浏览）日期打标识，创表结构
CREATE TABLE user_pv_flag_1
( user_id INT
,category_id INT
,date DATE
,flag VARCHAR(10) --“new：新增用户，old：老用户”
) ;
--插入数据
INSERT INTO user_pv_flag_1
SELECT user_id
,category_id
,CAST(date AS date) AS date
,CASE WHEN rn=1 THEN 'new'
WHEN rn>=2 THEN 'old' END AS flag
FROM(SELECT user_id
,category_id
,date
,RANK() OVER (PARTITION BY user_id,category_id ORDER BY date) AS rn
FROM user_behavior
WHere behavior_type='pv'
) t1
GROUP BY user_id
,category_id
,CAST(date AS DATE)
,CASE WHEN rn=1 THEN 'new'
WHEN rn>=2 THEN 'old' END ;步骤二：计算留存
情况一：以指定日期为开始时间，计算次留，3留，5留，7留，15留，30留
1.单日留存
(1)先将用户的最新PV日期，后期PV日期，mapping成同一行，然后再计算时间间隔，计算间隔标记的数量，即为几日留存量。
--单日整体留存
SELECT t5.start_date
,NULL AS category_id
,t5.day_0
,t6.day_1
,t6.day_3
,t6.day_5
,t6.day_7
,t6.day_15
,t6.day_30
FROM(
SELECT date AS start_date
,COUNT(1) AS day_0
FROM all_user_pv_flag
WHERE date='2017-11-24' AND
flag='new'
GROUP BY date
) t5
LEFT JOIN
(
SELECT start_date
,SUM(CASE WHEN by_day=1 THEN 1 ELSE END) AS day_1
,SUM(CASE WHEN by_day=3 THEN 1 ELSE END) AS day_3
,SUM(CASE WHEN by_day=5 THEN 1 ELSE END) AS day_5
,SUM(CASE WHEN by_day=7 THEN 1 ELSE END) AS day_7
,SUM(CASE WHEN by_day=15 THEN 1 ELSE END) AS day_15
,SUM(CASE WHEN by_day=30 THEN 1 ELSE END) AS day_30
FROM(SELECT user_id
,start_date
,DATEDIFF(day,start_date,end_date) AS by_day
FROM (SELECT t1.user_id
,t1.start_date
,t2.end_date
FROM(SELECT user_id
,date AS start_date
FROM all_user_pv_flag
WHERE date='2017-11-24' AND
flag='new'
) t1
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE date in ('2017-11-25','2017-11-27','2017-11-29','2017-12-01','2017-12-09','2017-12-24')
AND flag='old'
GROUP BY user_id
,date
) t2
ON t1.user_id=t2.user_id
) t3
) t4
GROUP BY start_date
) t6
ON t5.start_date=t6.start_date
UNION ALL
--单日分品类留存
SELECT t5.start_date
,t5.category_id
,t5.day_0
,t6.day_1
,t6.day_3
,t6.day_5
,t6.day_7
,t6.day_15
,t6.day_30
FROM(
SELECT date AS start_date
,category_id
,COUNT(1) AS day_0
FROM user_pv_flag
WHERE date='2017-11-24' AND
flag='new'
GROUP BY date
,category_id
) t5
LEFT JOIN
(
SELECT start_date
,category_id
,SUM(CASE WHEN by_day=1 THEN 1 ELSE END) AS day_1
,SUM(CASE WHEN by_day=3 THEN 1 ELSE END) AS day_3
,SUM(CASE WHEN by_day=5 THEN 1 ELSE END) AS day_5
,SUM(CASE WHEN by_day=7 THEN 1 ELSE END) AS day_7
,SUM(CASE WHEN by_day=15 THEN 1 ELSE END) AS day_15
,SUM(CASE WHEN by_day=30 THEN 1 ELSE END) AS day_30
FROM(SELECT user_id
,category_id
,start_date
,DATEDIFF(day,start_date,end_date) AS by_day
FROM (SELECT t1.user_id
,t1.category_id
,t1.start_date
,t2.end_date
FROM(SELECT user_id
,category_id
,date AS start_date
FROM user_pv_flag
WHERE date='2017-11-24' AND
flag='new'
) t1
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE date in ('2017-11-25','2017-11-27','2017-11-29','2017-12-01','2017-12-09','2017-12-24')
AND flag='old'
GROUP BY user_id
,category_id
,date
) t2
ON t1.user_id=t2.user_id AND
t1.category_id=t2.category_id
) t3
) t4
GROUP BY start_date
,category_id
) t6
ON t5.start_date=t6.start_date AND
t5.category_id=t6.category_id ;
执行时间：6s
(2)先将首次、目标日期的用户明细查询出来，然后进行多张表关联，通过对user_id计数的方法计算几日留存量。
--单日整体留存
SELECT t1.start_date
,NULL AS category_id
,COUNT(t1.user_id) AS day_0
,COUNT(t2.user_id) AS day_1
,COUNT(t3.user_id) AS day_3
,COUNT(t4.user_id) AS day_5
,COUNT(t5.user_id) AS day_7
,COUNT(t6.user_id) AS day_15
,COUNT(t7.user_id) AS day_30
FROM(
SELECT user_id
,date AS start_date
FROM all_user_pv_flag
WHERE flag='new' AND date='2017-11-24'
) t1
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old' AND date='2017-11-25'
) t2
ON t1.user_id=t2.user_id
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old' AND date='2017-11-27'
) t3
ON t1.user_id=t3.user_id
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old' AND date='2017-11-29'
) t4
ON t1.user_id=t4.user_id
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old' AND date='2017-12-01'
) t5
ON t1.user_id=t5.user_id
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old' AND date='2017-12-09'
) t6
ON t1.user_id=t6.user_id
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old' AND date='2017-12-24'
) t7
ON t1.user_id=t7.user_id
GROUP BY t1.start_date
UNION ALL
--单日分品类留存
SELECT t1.start_date
,t1.category_id
,COUNT(t1.user_id) AS day_0
,COUNT(t2.user_id) AS day_1
,COUNT(t3.user_id) AS day_3
,COUNT(t4.user_id) AS day_5
,COUNT(t5.user_id) AS day_7
,COUNT(t6.user_id) AS day_15
,COUNT(t7.user_id) AS day_30
FROM(
SELECT user_id
,category_id
,date AS start_date
FROM user_pv_flag
WHERE flag='new' AND date='2017-11-24'
) t1
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old' AND date='2017-11-25'
) t2
ON t1.user_id=t2.user_id AND
t1.category_id=t2.category_id
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old' AND date='2017-11-27'
) t3
ON t1.user_id=t3.user_id AND
t1.category_id=t3.category_id
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old' AND date='2017-11-29'
) t4
ON t1.user_id=t4.user_id AND
t1.category_id=t4.category_id
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old' AND date='2017-12-01'
) t5
ON t1.user_id=t5.user_id AND
t1.category_id=t5.category_id
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old' AND date='2017-12-09'
) t6
ON t1.user_id=t6.user_id AND
t1.category_id=t6.category_id
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old' AND date='2017-11-24'
) t7
ON t1.user_id=t7.user_id AND
t1.category_id=t7.category_id
GROUP BY t1.start_date
,t1.category_id ;
执行时间：9s
情况二：以全部日期为开始时间，计算次留，3留，5留，7留，15留，30留
(1)先将用户的最新PV日期，后期PV日期，mapping成同一行，然后再计算时间间隔，计算间隔标记的数量，即为几日留存量。
--多日整体留存
SELECT t5.start_date
,NULL AS category_id
,t5.day_0
,t6.day_1
,t6.day_3
,t6.day_5
,t6.day_7
,t6.day_15
,t6.day_30
FROM(
SELECT date AS start_date
,COUNT(1) AS day_0
FROM all_user_pv_flag
WHERE flag='new'
GROUP BY date
) t5
LEFT JOIN
(
SELECT start_date
,SUM(CASE WHEN by_day=1 THEN 1 ELSE END) AS day_1
,SUM(CASE WHEN by_day=3 THEN 1 ELSE END) AS day_3
,SUM(CASE WHEN by_day=5 THEN 1 ELSE END) AS day_5
,SUM(CASE WHEN by_day=7 THEN 1 ELSE END) AS day_7
,SUM(CASE WHEN by_day=15 THEN 1 ELSE END) AS day_15
,SUM(CASE WHEN by_day=30 THEN 1 ELSE END) AS day_30
FROM(SELECT user_id
,start_date
,DATEDIFF(day,start_date,end_date) AS by_day
FROM (SELECT t1.user_id
,t1.start_date
,t2.end_date
FROM(SELECT user_id
,date AS start_date
FROM all_user_pv_flag
WHERE flag='new'
) t1
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
GROUP BY user_id
,date
) t2
ON t1.user_id=t2.user_id
) t3
) t4
GROUP BY start_date
) t6
ON t5.start_date=t6.start_date
UNION ALL
---多日分品类留存
SELECT t5.start_date
,t5.category_id
,t5.day_0
,t6.day_1
,t6.day_3
,t6.day_5
,t6.day_7
,t6.day_15
,t6.day_30
FROM(
SELECT date AS start_date
,category_id
,COUNT(1) AS day_0
FROM user_pv_flag
WHERE flag='new'
GROUP BY date
,category_id
) t5
LEFT JOIN
(
SELECT start_date
,category_id
,SUM(CASE WHEN by_day=1 THEN 1 ELSE END) AS day_1
,SUM(CASE WHEN by_day=3 THEN 1 ELSE END) AS day_3
,SUM(CASE WHEN by_day=5 THEN 1 ELSE END) AS day_5
,SUM(CASE WHEN by_day=7 THEN 1 ELSE END) AS day_7
,SUM(CASE WHEN by_day=15 THEN 1 ELSE END) AS day_15
,SUM(CASE WHEN by_day=30 THEN 1 ELSE END) AS day_30
FROM(SELECT user_id
,category_id
,start_date
,DATEDIFF(day,start_date,end_date) AS by_day
FROM (SELECT t1.user_id
,t1.category_id
,t1.start_date
,t2.end_date
FROM(SELECT user_id
,category_id
,date AS start_date
FROM user_pv_flag
WHERE flag='new'
) t1
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
GROUP BY user_id
,category_id
,date
) t2
ON t1.user_id=t2.user_id AND
t1.category_id=t2.category_id
) t3
) t4
GROUP BY start_date
,category_id
) t6
ON t5.start_date=t6.start_date AND
t5.category_id=t6.category_id ;
执行时间：40s
(2)先将首次、目标日期的用户明细查询出来，然后进行多张表关联，通过对user_id计数的方法计算几日留存量。
--多日整体留存
SELECT t1.start_date
,NULL AS category_id
,COUNT(t1.user_id) AS day_0
,COUNT(t2.user_id) AS day_1
,COUNT(t3.user_id) AS day_3
,COUNT(t4.user_id) AS day_5
,COUNT(t5.user_id) AS day_7
,COUNT(t6.user_id) AS day_15
,COUNT(t7.user_id) AS day_30
FROM(
SELECT user_id
,date AS start_date
FROM all_user_pv_flag
WHERE flag='new'
) t1
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
) t2
ON t1.user_id=t2.user_id AND
t1.start_date=dateadd(day,-1,t2.end_date)
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
) t3
ON t1.user_id=t3.user_id AND
t1.start_date=dateadd(day,-3,t3.end_date)
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
) t4
ON t1.user_id=t4.user_id AND
t1.start_date=dateadd(day,-5,t4.end_date)
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
) t5
ON t1.user_id=t5.user_id AND
t1.start_date=dateadd(day,-7,t5.end_date)
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
) t6
ON t1.user_id=t6.user_id AND
t1.start_date=dateadd(day,-15,t6.end_date)
LEFT JOIN
(
SELECT user_id
,date AS end_date
FROM all_user_pv_flag
WHERE flag='old'
) t7
ON t1.user_id=t7.user_id AND
t1.start_date=dateadd(day,-30,t7.end_date)
GROUP BY t1.start_date
UNION ALL
--多日分品类留存
SELECT t1.start_date
,t1.category_id
,COUNT(t1.user_id) AS day_0
,COUNT(t2.user_id) AS day_1
,COUNT(t3.user_id) AS day_3
,COUNT(t4.user_id) AS day_5
,COUNT(t5.user_id) AS day_7
,COUNT(t6.user_id) AS day_15
,COUNT(t7.user_id) AS day_30
FROM(
SELECT user_id
,category_id
,date AS start_date
FROM user_pv_flag
WHERE flag='new'
) t1
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
) t2
ON t1.user_id=t2.user_id AND
t1.category_id=t2.category_id AND
t1.start_date=dateadd(day,-1,t2.end_date)
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
) t3
ON t1.user_id=t3.user_id AND
t1.category_id=t3.category_id AND
t1.start_date=dateadd(day,-3,t3.end_date)
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
) t4
ON t1.user_id=t4.user_id AND
t1.category_id=t4.category_id AND
t1.start_date=dateadd(day,-5,t4.end_date)
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
) t5
ON t1.user_id=t5.user_id AND
t1.category_id=t5.category_id AND
t1.start_date=dateadd(day,-7,t5.end_date)
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
) t6
ON t1.user_id=t6.user_id AND
t1.category_id=t6.category_id AND
t1.start_date=dateadd(day,-15,t6.end_date)
LEFT JOIN
(
SELECT user_id
,category_id
,date AS end_date
FROM user_pv_flag
WHERE flag='old'
) t7
ON t1.user_id=t7.user_id AND
t1.category_id=t7.category_id AND
t1.start_date=dateadd(day,-30,t7.end_date)
GROUP BY t1.start_date
,t1.category_id ;
执行时间：59s
通过对比执行时间，可以看出来将各个目标日期的用户PV数据单独存表，再进行多张表关联的效率是比较低的。相反，将再次PV的所有目标日期数据通过筛选日期，整合到一张表，然后与开始日期用户数据进行关联，再计算几日留存数据，速度更快。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31537575/viewspace-3163003/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
