--計(jì)算次日、3日、7日、30日留存率
select a.log_day 首次登錄日期,
count(user_id_d1)/count(user_id_d0) retention_d1,
count(user_id_d3)/count(user_id_d0) retention_d3,
count(user_id_d7)/count(user_id_d0) retention_d7,
count(user_id_d30)/count(user_id_d0) retention_d30 from(
--找出新增
select distinct log_day,device_id||app_id user_id_d0 from user_log a where log_day=log_day'day' 
and device_id||app_id not in(select distinct device_id||app_id from user_log b
	where b.log_day<a.log_day)
)a
left join (select distinct log_day,device_id||app_id user_id_d1 from user_log where log_day=(log_day'day'+1) b on a.user_id_d0=b.user_id_d1
left join (select distinct log_day,device_id||app_id user_id_d3 from user_log where log_day=(log_day'day'+3) c on a.user_id_d0=c.user_id_d3
left join (select distinct log_day,device_id||app_id user_id_d7 from user_log where log_day=(log_day'day'+7) d on a.user_id_d0=d.user_id_d7
left join (select distinct log_day,device_id||app_id user_id_d30 from user_log where log_day=(log_day'day'+30) e on a.user_id_d0=e.user_id_d30
group by a.log_day

-- 創(chuàng)建留存率儲(chǔ)存表
create table user_retention_monitior(
log_day date,retention_d1 number,retention_d3 number,
retention_d7 number,retention_d30 number
);
-- 清空表
truncate table user_retention_monitior;
declare
day date;    --變量聲明
-- 程序主體
begin
select min(log_day) into day from user_log;   --變量賦初始值
loop
	insert into user_retention_monitior
	-- 計(jì)算留存
	select a.log_day,
	count(b.user_id_d1)/count(a.user_id_d0) retention_d1,
	count(c.user_id_d3)/count(a.user_id_d0) retention_d3,
	count(d.user_id_d7)/count(a.user_id_d0) retention_d7,
	count(e.user_id_d30)/count(a.user_id_d0) retention_d30
	 from(
	select distinct log_day,app_id||device_id user_id_d0 from user_log a where log_day=log_day'day' 
	and app_id||device_id not in( select distinct pp_id||device_id from user_log
		where log_day<log_day'day' )
	)a left join(
	select distinct log_day,app_id||device_id user_id_d1 from user_log where log_day=(log_day'day'+1)
	)b on a.user_id_d0=b.user_id_d1 left join(
	select distinct log_day,app_id||device_id user_id_d3 from user_log where log_day=(log_day'day'+3)
	)c on a.user_id_d0=c.user_id_d3 left join(
	select distinct log_day,app_id||device_id user_id_d7 from user_log where log_day=(log_day'day'+7)
	)d on a.user_id_d0=d.user_id_d7 left join(
	select distinct log_day,app_id||device_id user_id_d30 from user_log where log_day=(log_day'day'+30)
	)e on a.user_id_d0=e.user_id_d30
	group by a.log_day;
	commit;
	day:=day+1;  --循環(huán)
	exit when day>=truc(sysdate);   --結(jié)束條件
end loop;
end;
select * from user_retention_monitior;

1. 表結(jié)構(gòu)
表名： user_login
字段名：
user_id string comment '用戶id'
login_dt string comment '用戶登陸時(shí)間'
2. 數(shù)據(jù)格式
user_id login_dt
1       2020-05-01
1       2020-05-02
1       2020-05-04
3. 格式轉(zhuǎn)換（login_dt由字符串格式轉(zhuǎn)為時(shí)間格式）
to_date(from_unixtime(UNIX_TIMESTAMP(login_dt,'yyyy-MM-dd'))) as login_dt

LAG(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往上第n行值
第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往上第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL）
LEAD(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往下第n行值

//2. 要求now-lag=lead-now=1
select user_id from(
//1. 獲取當(dāng)天前后各一條數(shù)據(jù)
select user_id,login_dt,
lag(login_dt,1) over(partition by user_id order by login_dt) as lag_dt_01,
lead(login_dt,1) over(partition by user_id order by login_dt) as lead_dt_01
from user_login)temp1
where datediff(login_dt,lag_dt_01)=1 and datediff(lead_dt_01,login_dt)=1

//2. 將用戶根據(jù)登陸時(shí)間和序號(hào)的差值進(jìn)行分組，得到連續(xù)登陸的起止日期、連續(xù)登陸的天數(shù)
select user_id,
min(login_dt) as start_dt,
max(login_dt) as end_dt,
count(1) as continue_day
 from(
//1. 將用戶按登陸時(shí)間排序號(hào)
select user_id,login_dt,
row_number() over(partition by user_id order by login_dt) as rn from user_login
)temp1
group by user_id,date_sub(login_dt,rn)

每個(gè)顧客訪客訪問任何一個(gè)店鋪的任何一個(gè)商品時(shí)都會(huì)產(chǎn)生一條訪問日志.
訪問日志存儲(chǔ)的表名為Visit，訪客的用戶id為user_id，被訪問的店鋪名稱為shop.
請(qǐng)統(tǒng)計(jì)每個(gè)店鋪訪問次數(shù)top3的訪客信息.輸出店鋪名稱、訪客id、訪問次數(shù)

// 3. 取前三名
select shop,user_id,visit_num from(
// 2. 統(tǒng)計(jì)每個(gè)頁面各用戶的訪問次數(shù)排序
	select shop,user_id,visit_num,
	row_num() over(partition by shop,user_id order by visit_num desc) as visit_sort
	 from(
		// 1. 統(tǒng)計(jì)每個(gè)用戶每個(gè)頁面的訪問次數(shù)
		select shop,user_id,count(1) as visit_num from visit
		group by shop,user_id
		)temp1
)temp2
where visit_sort<=3

-----------------------------------------------------
原式數(shù)據(jù)movie表：
 movie   		 category
《疑犯追蹤》		 懸疑,動(dòng)作,科幻,劇情
《Lie to me》 	 懸疑,警匪,動(dòng)作,心理,劇情
《戰(zhàn)狼 2》 		 戰(zhàn)爭,動(dòng)作,災(zāi)難
-----------------------------------------------------
結(jié)果數(shù)據(jù)
 movie		category_name
《疑犯追蹤》 		懸疑
《疑犯追蹤》 		動(dòng)作
《疑犯追蹤》 		科幻
《疑犯追蹤》 		劇情
《Lie to me》   	懸疑
《Lie to me》    警匪
《Lie to me》    動(dòng)作
《Lie to me》    心理
《Lie to me》    劇情
《戰(zhàn)狼 2》        戰(zhàn)爭
《戰(zhàn)狼 2》        動(dòng)作
《戰(zhàn)狼 2》        災(zāi)難

select movie,category_name from movie
lateral view explode(split(category),',') tmpTable as category_name

-----------------------------------------------------
原始數(shù)據(jù)constellation表：
name 	constellation 	blood_type
孫悟空 	白羊座 			A
大海 	射手座 			A
宋宋 	白羊座 			B
豬八戒 	白羊座 			A
鳳姐 	射手座 			A
-----------------------------------------------------
結(jié)果數(shù)據(jù)
--把星座和血型一樣的人歸類到一起。結(jié)果如下：
射手座,A 	大海|鳳姐
白羊座,A 	孫悟空|豬八戒
白羊座,B 	宋宋

2. 根據(jù)新字段進(jìn)行多行合并：group_concat()
select base,
group_concat(name separator '|') as name
 from(
1. 將星座和血型整合為新字段
select concat(constellation,‘,'，blood_type) as base,name from constellation
)temp1
group by base

原始數(shù)據(jù)
----------------course表-----------------
id course 
1   a 
1   b 
1   c 
1   e 
2   a 
2   c 
2   d 
2   f 
3   a 
3   b 
3   c 
3   e
預(yù)期結(jié)果：表中的1表示選修，表中的0表示未選修
----------------結(jié)果展示------------------
id    a    b    c    d    e    f
1     1    1    1    0    1    0
2     1    0    1    1    0    1
3     1    1    1    0    1    0

3. 判斷選課信息是否可匹配到
select id,
case when array_contains(id_course,course[0]) then 1 else 0 end as a,
case when array_contains(id_course,course[1]) then 1 else 0 end as b,
case when array_contains(id_course,course[2]) then 1 else 0 end as c,
case when array_contains(id_course,course[3]) then 1 else 0 end as d,
case when array_contains(id_course,course[4]) then 1 else 0 end as e,
case when array_contains(id_course,course[5]) then 1 else 0 end as f
 from(
4. 以數(shù)組形式整理每個(gè)學(xué)生的選課信息
select id,id_course,course from(
select id,collect_set(course) as id_course from course
group by id)as temp1 cross join(
select sort_array(collect_set(course)) as course from course
)as temp2)