SQL经典用法之用户连续访问区间记录

标签：jiange 06 05 rng 用法 访问 2022 SQL start

案例：有一张DWS表记录了每个用户近30天活跃情况，需要通过该表记录得出用户近30天的连续访问间隔分布情况

逻辑梳理：

1，要通过埋点日志数据，筛选出最近30天的日志记录，然后按照用户ID分组，通过对用户活跃时间进行排序得出近30天的连续活跃区间情况：使用开窗函数ROW_NUMBER() 实现

得到如下一张表：

user_id　　rng_start　　rng_end　　

uid01　　2022-05-20　　2022-05-26　

uid01　　2022-05-29　　2022-06-01

uid01　　2022-06-03　　2022-06-20

uid02　　2022-05-22　　2022-05-30

uid02　　2022-06-03　　2022-06-06

uid03　　2022-05-20　　2022-05-26

uid04　　2022-06-03　　2022-06-20

uid05　　2022-06-19　　2022-06-20

 

2,通过上面的表我们要得到下面这张表：

user_id　　jiange_days　　jiange_cnts

uid01　　间隔0天　　26

uid02　　间隔0天　　11

uid03　　间隔0天　　6

uid04　　间隔0天　　17

uid05　　间隔0天　　1

uid01　　间隔2天　　1

uid01　　间隔3天　　1

uid02　　间隔4天　　1

实现逻辑：

对每一个区间，x=rng_end - rng_start ,得到：间隔0天，x次

对于相邻区间，y=rng_2_start - rng_1_end,下一个区间开始-上个区间结束，得到间隔y天，1次

--sqltext:

--隔0天的次数

select　　user_id,

　　　　　0 as jiange_days,

　　　　　datediff(rng_end,rng_start)  as jiange_cnts

from　　dws_api_uca_ rng

--隔n天的次数：lead() over()将两个相邻区间的结束 和起始放到一行

如：

user_id　　rng_start　　rng_end　　lead(rng_start) over()

uid01　　2022-05-20　　2022-05-26　　2022-05-29

uid01　　2022-05-29　　2022-06-01　　2022-06-03

uid01　　2022-06-03　　2022-06-20　　null

select 　　user_id,

　　　　　datediff(lead(rng_start) over(partition by user_id order by rng_start), rng_end)  as  jiange_days

from 　　dws_api_uca_ rng

3,合并间隔0天和间隔n天的数据

--隔0天的次数

select　　user_id,

　　　　　concat('间隔',0,'天') as jiange_days,

　　　　　datediff(rng_end,rng_start)  as jiange_cnts

from　　dws_api_uca_ rng

union all

select 　　user_id,

　　　　　concat('间隔',jiange_days,'天') as jiange_days ,

　　　　　sum(1) as jiange_cnts

from 

(select 　　user_id,

　　　　　datediff(lead(rng_start) over(partition by user_id order by rng_start), rng_end)  as  jiange_days

from 　　dws_api_uca_ rng

) t

where t.jiange_days is not null 

group by 　user_id,jiange_days

 

 

 

 

 

 

搜索

复制

标签：jiange,06,05,rng,用法,访问,2022,SQL,start
From： https://www.cnblogs.com/db-record/p/16653309.html