-- 45 前后函数

-- 查询每个用户按单号顺序,前一笔订单距离当前订单的间隔天数

select *, lag(create_date,1) over(partition by user_no order by order_id) as 前一笔订单下单日期,

timestampdiff(day,lag(create_date,1 ) over(partition by user_no order by order_id),create_date) as 间隔天数

from order_tab;

# 报错,select 子句中不能使用同等级的列别名

select *, lag(create_date,1) over(partition by user_no order by order_id) as 前一笔订单下单日期,

前一笔订单下单日期- create_date as 间隔天数 from order_tab;

##课后作业,腾讯游戏数据查询

-- 问题1（Tencent）：计算每天回流玩家数量

-- 问题2（Tencent）：提取每个用户每日累计充值金额

-- 问题3（Tencent）：提取每个用户最后登录7天内的充值金额(用payment_log和user_log表来计算)

##数据如下

create database tencent;

use tencent;

create table login_user( #用户登录表

login_date date,

uid int,

country varchar(10),

install_date date,

primary key(login_date,uid)

);

insert into login_user values

('2021-01-01',10001,'us','2021-01-01'),

('2021-01-02',10001,'us','2021-01-01'),

('2021-01-13',10001,'us','2021-01-01'),

('2021-01-14',10022,'us','2021-01-01'),

('2021-01-15',10001,'us','2021-01-01'),

('2021-01-01',10005,'us','2021-01-01'),

('2021-01-11',10005,'us','2021-01-01'),

('2021-01-21',10005,'us','2021-01-01'),

('2021-01-31',10005,'us','2021-01-01'),

('2021-01-01',10002,'us','2021-01-01'),

('2021-01-02',10002,'us','2021-01-01'),

('2021-01-03',10022,'us','2021-01-01'),

('2021-01-14',10002,'us','2021-01-01'),

('2021-01-15',10002,'us','2021-01-01'),

('2021-01-06',10024,'us','2021-01-01');

create table payment_log( #充值记录表

pay_date date,

user_id int,

revenue decimal,

order_id int primary key

);

insert into payment_log values

('2021-01-01',10001,50,801),

('2021-01-01',10001,60,802),

('2021-01-01',10001,80,803),

('2021-01-02',10001,55,804),

('2021-01-02',10001,56,805),

('2021-01-02',10002,70,806),

('2021-01-02',10002,71,807),

('2021-01-03',10002,72,808),

('2021-01-05',10002,73,809),

('2021-01-11',10005,90,810),

('2021-01-21',10005,91,811),

('2021-01-31',10005,92,812),

('2021-01-03',10022,40,813);

create table user_log(

login_date date,

user_id int,

primary key(login_date,user_id)

);

insert into user_log values

('2021-01-01',10001),

('2021-01-02',10001),

('2021-01-14',10022),

('2021-01-01',10005),

('2021-01-11',10005),

('2021-01-21',10005),

('2021-01-31',10005),

('2021-01-02',10002),

('2021-01-03',10002),

('2021-01-03',10022),

('2021-01-05',10002),

('2021-01-06',10024);

-- 课后作业作答:

-- 问题1（Tencent）：计算每天回流玩家数量(定义回流玩家为:有超过7天未登录后又重新回流登录的玩家,在某日回流登录即为当日回流玩家)

#第1步,按用户显示,本次登录日期,上次登录日期,间隔天数,判断间隔天数大于7的为回流玩家

select

*,

lag(login_date,1) over(partition by uid order by login_date) as 上次登录日期,

timestampdiff(day,lag(login_date,1) over(partition by uid order by login_date),login_date) as 间隔天数

from login_user;

#第2步,group by 回流登录日期,count uid 人数

select login_date 回流登录日期,count(uid) 回流玩家数量

from

(select

*,

lag(login_date,1) over(partition by uid order by login_date) as 上次登录日期,

timestampdiff(day,lag(login_date,1) over(partition by uid order by login_date),login_date) as 间隔天数

from login_user ) t1

where t1.间隔天数 >7

group by t1.login_date;

#合在一起:

select login_date,count(*) as 回流玩家的数量

from #LAG：访问缓慢于当前行的数据；LEAD：访问领先于当前行的数据

(select

*,

lag(login_date,1) over(partition by uid order by login_date) as 上次登录日期,

timestampdiff(day,lag(login_date,1) over(partition by uid order by login_date),login_date) as 间隔天数

from login_user) as t

where 间隔天数>7

group by login_date;

-- 问题2（Tencent）：提取每个用户每日累计充值金额

select distinct user_id,pay_date,sum(revenue) over(partition by user_id order by pay_date ) 每日累计充值金额 from payment_log;

-- 问题3（Tencent）：提取每个用户最后登录7天内的充值金额

-- 如果用payment_log和login_user表来计算:

#第1步,找每个用户最后登录日期,以login_user表为准

select uid,max(login_date) 最后登录日期 from login_user group by uid;

#第2步,找每个用户每日充值

select user_id,pay_date,revenue

from payment_log ;

#第3步,两个表连接

select user_id,pay_date,revenue

from payment_log p left join

(select uid,max(login_date) 最后登录日期 from login_user group by uid)t

on p.user_id=t.uid

where timestampdiff(day,pay_date,最后登录日期)<7

group by user_id;

#*********************************************************

-- 如果用payment_log和user_log表来计算,代码如下，另外用表不一样最后结果不一样。本题祝老师忘记给大家说指定用payment_log和user_log表了。

#第一步,找每个用户最后登录日期 ,以user_log表为准

select user_id,max(login_date) as 最后登录日期

from user_log

group by user_id;

##第二步,找每个用户每日充值

select user_id,pay_date,revenue

from payment_log ;

###第三步,两个表连接

select payment_log.user_id,sum(revenue) 最后登录日的7天内的充值金额

from payment_log

left join

(select user_id,max(login_date) as 最后登录日期

from user_log

group by user_id

) t

on payment_log.user_id=t.user_id

where timestampdiff(day,pay_date,最后登录日期)<=7

group by payment_log.user_id;