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

select
    avg(if(datediff(date2,date1)=1, 1, 0)) as avg_ret
from
    (
        select
            distinct device_id,
            date as date1,
            lead (date, 1, null) over (
                partition by
                    device_id
                order by
                    date
            ) as date2
        from(
        select distinct device_id, date
        from question_practice_detail
    )as unique_id_qpd
)as id_date_qpd

一、 if(判断，真，假) 函数

二、开窗函数:函数 + over(partition by <分组用列> order by <排序用列>)

*开窗函数不会互相干扰，因此在同一个查询语句中可以同时使用多个开窗函数

分为三类：

1、排序开窗函数

① row_number() -- 相同值排名顺延，返回结果1、2、3、4

② rank() -- 相同结果排名相同，后续排名不连续，返回结果为 1、2、2、4

③ dense_rank() -- 相同结果排名相同，后续排名顺延，返回结果为 1、2、2、3

④ ntile(n) -- 分组排名，将数据分为n组并返回对应组号1、2......n。

select
    grades,
    subjects,
    results,
    row_number() over (
        partition by
            grades,
            subjects
        order by
            results desc
    ) as row_numbers,
    rank() over (
        partition by
            grades,
            subjects
        order by
            results desc
    ) as ranks,
    dense_rank() over (
        partition by
            grades,
            subjects
        order by
            results desc
    ) as dense_ranks,
    ntile (3) over (
        partition by
            grades,
            subjects
        order by
            results desc
    ) as ntiles
from
    test11
#    表示将数据集按照grades、subjects字段进行分组后，根据对应排序函数并按照results字段降序返回排名

2、聚合开窗函数

① sum() -- 分组求和

② count() -- 分组求总数

③ min() -- 分组求最小值

④ max() -- 分组求最大值

⑤ avg() --分组求均值

3、其他开窗函数

① lag(字段名,n,0) -- 移位开窗函数，表示返回向上第n行指定字段对应数据。其中n代表向上偏移n行，0代表若偏移行数超出表范围则返回0也可以改成其他值，若不写则默认null

② lead(字段名,n,0) -- 移位开窗函数，与lag()相反，表示返回向下第n行指定字段对应数据

③ first_value() -- 取分组内排序后，截止到当前行，第一个值

④ last_value() -- 取分组内排序后，截止到当前行，最后一个值

三、datediff（）

语法 ：DATEDIFF(datepart,startdate,enddate)
例子 ：SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate  
	结果：1
	SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDate
	结果：-1