1 连续登陆用户
1.1 需求
当前有一份用户登录数据如下图所示，数据中有两个字段，分别是userId和loginTime。
userId表示唯一的用户ID，唯一标识一个用户，loginTime表示用户的登录日期，例如第一条数据就表示A在2021年3月22日登录了。
现在需要对用户的登录次数进行统计，得到连续登陆N（N>=2）天的用户。
例如统计连续两天的登录的用户，需要返回A和C，因为A在22/23/24都登录了，所以肯定是连续两天登录，C在22和23号登录了，所以也是连续两天登录的。
例如统计连续三天的登录的用户，只能返回A，因为只有A是连续三天登录的。
1.2 分析
基于以上的需求根据数据寻找规律，要想得到连续登陆用户，必须找到两个相同用户ID的行之间登陆日期之间的关系。
例如：统计连续登陆两天的用户，只要用户ID相等，并且登陆日期之间相差1天即可。基于这个规律，我们有两种方案可以实现该需求。
方案一：实现表中的数据自连接，构建笛卡尔积，在结果中找到符合条件的id即可
方案二：使用窗口函数来实现
1.3 建表
➢ 创建表
--切换数据库 use db_function; --建表 create table tb_login( userid string, logintime string ) row format delimited fields terminated by '\t';
➢ 创建数据：vim /export/data/login.log\
A 2021-03-22 B 2021-03-22 C 2021-03-22 A 2021-03-23 C 2021-03-23 A 2021-03-24 B 2021-03-24
➢ 加载数据
load data local inpath ‘/export/data/login.log’ into table tb_login;
➢ 查询数据
select * from tb_login;
1.4 方案一：自连接过滤实现
➢ 构建笛卡尔积
select a.userid as a_userid, a.logintime as a_logintime, b.userid as b_userid, b.logintime as b_logintime from tb_login a,tb_login b;
➢ 查看数据
+-----------+--------------+-----------+--------------+ | A_USERID | A_LOGINTIME | B_USERID | B_LOGINTIME | +-----------+--------------+-----------+--------------+ | A | 2021-03-22 | A | 2021-03-22 | | B | 2021-03-22 | A | 2021-03-22 | | C | 2021-03-22 | A | 2021-03-22 | | A | 2021-03-23 | A | 2021-03-22 | | C | 2021-03-23 | A | 2021-03-22 | | A | 2021-03-24 | A | 2021-03-22 | | B | 2021-03-24 | A | 2021-03-22 | | A | 2021-03-22 | B | 2021-03-22 | | B | 2021-03-22 | B | 2021-03-22 | | C | 2021-03-22 | B | 2021-03-22 | | A | 2021-03-23 | B | 2021-03-22 | | C | 2021-03-23 | B | 2021-03-22 | | A | 2021-03-24 | B | 2021-03-22 | | B | 2021-03-24 | B | 2021-03-22 | | A | 2021-03-22 | C | 2021-03-22 | | B | 2021-03-22 | C | 2021-03-22 | | C | 2021-03-22 | C | 2021-03-22 | | A | 2021-03-23 | C | 2021-03-22 | | C | 2021-03-23 | C | 2021-03-22 | | A | 2021-03-24 | C | 2021-03-22 | | B | 2021-03-24 | C | 2021-03-22 | | A | 2021-03-22 | A | 2021-03-23 | | B | 2021-03-22 | A | 2021-03-23 | | C | 2021-03-22 | A | 2021-03-23 | | A | 2021-03-23 | A | 2021-03-23 | | C | 2021-03-23 | A | 2021-03-23 | | A | 2021-03-24 | A | 2021-03-23 | | B | 2021-03-24 | A | 2021-03-23 | | A | 2021-03-22 | C | 2021-03-23 | | B | 2021-03-22 | C | 2021-03-23 | | C | 2021-03-22 | C | 2021-03-23 | | A | 2021-03-23 | C | 2021-03-23 | | C | 2021-03-23 | C | 2021-03-23 | | A | 2021-03-24 | C | 2021-03-23 | | B | 2021-03-24 | C | 2021-03-23 | | A | 2021-03-22 | A | 2021-03-24 | | B | 2021-03-22 | A | 2021-03-24 | | C | 2021-03-22 | A | 2021-03-24 | | A | 2021-03-23 | A | 2021-03-24 | | C | 2021-03-23 | A | 2021-03-24 | | A | 2021-03-24 | A | 2021-03-24 | | B | 2021-03-24 | A | 2021-03-24 | | A | 2021-03-22 | B | 2021-03-24 | | B | 2021-03-22 | B | 2021-03-24 | | C | 2021-03-22 | B | 2021-03-24 | | A | 2021-03-23 | B | 2021-03-24 | | C | 2021-03-23 | B | 2021-03-24 | | A | 2021-03-24 | B | 2021-03-24 | | B | 2021-03-24 | B | 2021-03-24 | +-----------+--------------+-----------+--------------+
➢ 保存为表
create table tb_login_tmp as select a.userid as a_userid, a.logintime as a_logintime, b.userid as b_userid, b.logintime as b_logintime from tb_login a,tb_login b;
➢ 过滤数据：用户id相同并且登陆日期相差1
select a_userid,a_logintime,b_userid,b_logintime from tb_login_tmp where a_userid = b_userid and cast(substr(a_logintime,9,2) as int) - 1 = cast(substr(b_logintime,9,2) as int);
➢ 统计连续登陆两天的用户
select distinct a_userid from tb_login_tmp where a_userid = b_userid and cast(substr(a_logintime,9,2) as int) - 1 = cast(substr(b_logintime,9,2) as int);
➢ 问题
如果现在需要统计连续3天的用户个数，如何实现呢？或者说需要统计连续5天、连续7天、连续10天、连续30天登陆的用户如何进行计算呢？
如果使用自连接的方式会非常的麻烦才能实现统计连续登陆两天以上的用户，并且性能很差，所以我们需要使用第二种方式来实现。
1.5 方案二：窗口函数实现
➢ 窗口函数lead
➢ 功能：用于从当前数据中基于当前行的数据向后偏移取值
➢ 语法：lead(colName，N，defautValue)
➢colName：取哪一列的值
➢N：向后偏移N行
➢ defaultValue：如果取不到返回的默认值
➢分析
当前数据中记录了每个用户每一次登陆的日期，一个用户在一天只有1条信息，我们可以基于用户的登陆信息，找到如下规律：
连续两天登陆 ： 用户下次登陆时间 = 本次登陆以后的第二天
连续三天登陆 ： 用户下下次登陆时间 = 本次登陆以后的第三天
……依次类推。
我们可以对用户ID进行分区，按照登陆时间进行排序，通过lead函数计算出用户下次登陆时间，通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登录。
➢ 统计连续2天登录
select userid, logintime, --本次登陆日期的第二天 date_add(logintime,1) as nextday, --按照用户id分区，按照登陆日期排序，取下一次登陆时间，取不到就为0 lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin from tb_login;
with t1 as (
select
userid,
logintime,
–本次登陆日期的第二天
date_add(logintime,1) as nextday,
–按照用户id分区，按照登陆日期排序，取下一次登陆时间，取不到就为0
lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;
➢ 统计连续3天登录
select userid, logintime, --本次登陆日期的第三天 date_add(logintime,2) as nextday, --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0 lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin from tb_login;
with t1 as ( select userid, logintime, --本次登陆日期的第三天 date_add(logintime,2) as nextday, --按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0 lead(logintime,2,0) over (partition by userid order by logintime) as nextlogin from tb_login ) select distinct userid from t1 where nextday = nextlogin;
➢ 统计连续N天登录
select
userid,
logintime,
–本次登陆日期的第N天
date_add(logintime,N-1) as nextday,
–按照用户id分区，按照登陆日期排序，取下下一次登陆时间，取不到就为0
lead(logintime,N-1,0) over (partition by userid order by logintime) as nextlogin
from tb_login;
2 级联累加求和
2.1 需求
当前有一份消费数据如下，记录了每个用户在每个月的所有消费记录，数据表中一共有三列：
➢userId：用户唯一id，唯一标识一个用户
➢mth：用户消费的月份，一个用户可以在一个月多次消费
➢money：用户每次消费的金额
现在需要基于用户每个月的多次消费的记录进行分析，统计得到每个用户在每个月的消费总金额以及当前累计消费总金额，最后结果如下：
以用户A为例：
A在2021年1月份，共四次消费，分别消费5元、15元、8元、5元，所以本月共消费33元，累计消费33元。
A在2021年2月份，共两次消费，分别消费4元、6元，所以本月共消费10元，累计消费43元。
