题解 | #计算用户的平均次日留存率#

使用窗口函数，应该是最简单的，lead偏移函数

SELECT 
SUM(IF(lead3=1,1,0))/COUNT(device_id)
FROM
(SELECT 
device_id,
date,
lead(date,1) over(PARTITION BY device_id ORDER BY date ) lead2,
(lead(date,1) over(PARTITION BY device_id ORDER BY date )) - date AS lead3
FROM
(SELECT DISTINCT device_id,date from question_practice_detail) m1
ORDER BY device_id
) m2

偏移分析函数概念

1. lag(col,n,default)：用于统计分组内往上第n行值。

第一个参数为列名

第二个参数为往上第n行（可选，不填默认为1）

第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

2. lead(col,n,default)：与lag相反，统计分组内往下第n行值。

第一个参数为列名

第二个参数为往下第n行（可选，不填默认为1）

第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

first_value(col)：用于取分组内排序后，截止到当前行，第一个col的值。

last_value(col)：用于取分组内排序后，截止到当前行，最后一个col的值。

例子1

SELECT
	user_name,
	pay_time,
	lag( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag1,
	lag( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag2,
	lag( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag3,
	lag( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lag4 
FROM
	user_order 
WHERE
	user_name IN ( 'lisi', 'yantian' )

例子2

SELECT
	user_name,
	pay_time,
	lead( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead1,
	lead( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead2,
	lead( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead3,
	lead( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead4 
FROM
	user_order 
WHERE
	user_name IN ( 'lisi', 'yantian' );