-- 45 前后函數(shù)

-- 查詢每個(gè)用戶按單號(hào)順序,前一筆訂單距離當(dāng)前訂單的間隔天數(shù)

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 間隔天數(shù)

from order_tab;

# 報(bào)錯(cuò),select 子句中不能使用同等級的列別名

select *, lag(create_date,1) over(partition by user_no order by order_id) as 前一筆訂單下單日期,

前一筆訂單下單日期- create_date as 間隔天數(shù) from order_tab;

##課后作業(yè),騰訊游戲數(shù)據(jù)查詢

-- 問題1（Tencent）：計(jì)算每天回流玩家數(shù)量

-- 問題2（Tencent）：提取每個(gè)用戶每日累計(jì)充值金額

-- 問題3（Tencent）：提取每個(gè)用戶最后登錄7天內(nèi)的充值金額(用payment_log和user_log表來計(jì)算)

##數(shù)據(jù)如下

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);

-- 課后作業(yè)作答:

-- 問題1（Tencent）：計(jì)算每天回流玩家數(shù)量(定義回流玩家為:有超過7天未登錄后又重新回流登錄的玩家,在某日回流登錄即為當(dāng)日回流玩家)

#第1步,按用戶顯示,本次登錄日期,上次登錄日期,間隔天數(shù),判斷間隔天數(shù)大于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 間隔天數(shù)

from login_user;

#第2步,group by 回流登錄日期,count uid 人數(shù)

select login_date 回流登錄日期,count(uid) 回流玩家數(shù)量

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 間隔天數(shù)

from login_user ) t1

where t1.間隔天數(shù) >7

group by t1.login_date;

#合在一起:

select login_date,count(*) as 回流玩家的數(shù)量

from #LAG：訪問緩慢于當(dāng)前行的數(shù)據(jù)；LEAD：訪問領(lǐng)先于當(dāng)前行的數(shù)據(jù)

(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 間隔天數(shù)

from login_user) as t

where 間隔天數(shù)>7

group by login_date;

-- 問題2（Tencent）：提取每個(gè)用戶每日累計(jì)充值金額

select distinct user_id,pay_date,sum(revenue) over(partition by user_id order by pay_date ) 每日累計(jì)充值金額 from payment_log;

-- 問題3（Tencent）：提取每個(gè)用戶最后登錄7天內(nèi)的充值金額

-- 如果用payment_log和login_user表來計(jì)算:

#第1步,找每個(gè)用戶最后登錄日期,以login_user表為準(zhǔn)

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

#第2步,找每個(gè)用戶每日充值

select user_id,pay_date,revenue

from payment_log ;

#第3步,兩個(gè)表連接

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表來計(jì)算,代碼如下，另外用表不一樣最后結(jié)果不一樣。本題祝老師忘記給大家說指定用payment_log和user_log表了。

#第一步,找每個(gè)用戶最后登錄日期 ,以user_log表為準(zhǔn)

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

from user_log

group by user_id;

##第二步,找每個(gè)用戶每日充值

select user_id,pay_date,revenue

from payment_log ;

###第三步,兩個(gè)表連接

select payment_log.user_id,sum(revenue) 最后登錄日的7天內(nèi)的充值金額

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;