單純的練習(xí)箱吕，是從尚硅谷大數(shù)據(jù)課程之Hive（2019新版）學(xué)的，反正我記錄的都是我手敲過的柿冲。茬高。。
1.空字段賦值
函數(shù)說明：
NVL：給值為null的數(shù)據(jù)賦值假抄。格式是NVL(string1 , replace with)怎栽。如果string1為null，則NVL函數(shù)返回replace_with的值慨亲，否則返回string1的值。如果兩個(gè)參數(shù)都為null宝鼓，則返回null
例如：
> select nvl(comm,0) from emp;
2.時(shí)間類
1.date_format 格式化時(shí)間
> select date_format('1985-5-23','yyyy-MM-dd');
2.date_add 時(shí)間跟天數(shù)相加
> select date_add('1985-5-23',6);
可以填負(fù)值
3.date_sub 時(shí)間跟天數(shù)相減
> select date_sub('1985-5-23',6);
4.datediff 兩天相減
> select datediff('1985-05-23','1985-05-26');
3.CASE WHEN
1.數(shù)據(jù)準(zhǔn)備
name dept_id sex
悟空 A 男
大海 A 男
松松 B 男
鳳姐 A 女
婷姐 B 女
婷婷 B 女
2.需求
求出不同部門男女各多少人刑棵。結(jié)果要求：
A 2 1
B 1 2
3.創(chuàng)建本地emp_sex.txt導(dǎo)入數(shù)據(jù)
> create table emp_sex( name string, dept_id string, sex string) row format delimited fields terminated by '\t';
> load data local inpath '/opt/soft/files/emp_sex.txt' into table emp_sex;
4.按需求查數(shù)據(jù)
> select dept_id, sum (case sex when '男' then 1 else 0 end) male_count, sum (case sex when '女' then 1 else 0 end) female_count from emp_sex group by dept_id;
select dept_id, sum (sum(sex='男',1,0)) male_count, sum (sum(sex='女',1,0) female_count from emp_sex group by dept_id;
4.行轉(zhuǎn)列
1.函數(shù)說明：
concat(string A/col,string B/col...)：返回輸入字符串連接后的結(jié)果，支持任意個(gè)輸入字符串愚铡。
concat_ws(separator,str1,str2...)：它是一個(gè)特殊形式的concat()蛉签，第一個(gè)參數(shù)剩余參數(shù)之間的分隔符胡陪。分隔符可以是與剩余參數(shù)一樣的字符串。如果分隔符是null碍舍，返回值也會(huì)是null柠座。這個(gè)函數(shù)會(huì)跳過分隔符參數(shù)后的任何null和空字符串。分隔符將會(huì)被加到被連接的字符串之間;
concat_set(col)：函數(shù)只接受基本數(shù)據(jù)類型片橡，主要功能是將某字段的值進(jìn)行去重妈经，匯總，產(chǎn)生arry類型字段捧书。
數(shù)據(jù)準(zhǔn)備：
name constellation blood_type
孫悟空 白羊座 A
大海 射手座 A
松松 白羊座 B
豬八戒 白羊座 A
鳳姐 射手座 A
2.需求
星座血型一樣的人歸類到一起吹泡。結(jié)果要求：
射手座,A 大海|鳳姐
白羊座,A 孫悟空|豬八戒
白羊座,B 松松
3.創(chuàng)建本地?cái)?shù)據(jù)constellation.txt
create table person_info( name string, constellation string, blood_type string) row format delimited fields terminated by '\t';
load data local inpath "/opt/soft/files/constellation.txt" into table person_info;
4.查
select constellation_blood_type,concat_ws('|',collect_set(name)) from (select concat(constellation,',',blood_type) constellation_blood_type, name from person_info) t1 group by constellation_blood_type;
5.列轉(zhuǎn)行
1.函數(shù)說明
EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：用戶和split经瓷，explode等UDTF一起使用爆哑，能將一列數(shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以做數(shù)據(jù)的聚合舆吮。
2.需求
電影如下：
movie category
《疑犯追蹤》 懸疑,動(dòng)作,科幻,劇情
《Lie to me》 懸疑,警匪,動(dòng)作,心理,劇情
《戰(zhàn)狼》 戰(zhàn)爭(zhēng),動(dòng)作,災(zāi)難
我們要把電影分類的數(shù)組數(shù)據(jù)展開揭朝，結(jié)果如下：
《疑犯追蹤》 懸疑
《疑犯追蹤》 動(dòng)作
《疑犯追蹤》 科幻
《疑犯追蹤》 劇情
《Lie to me》 懸疑
《Lie to me》 警匪
《Lie to me》 動(dòng)作
《Lie to me》 心理
《Lie to me》 劇情
《戰(zhàn)狼》 戰(zhàn)爭(zhēng)
《戰(zhàn)狼》 動(dòng)作
《戰(zhàn)狼》 災(zāi)難
3.創(chuàng)建本地
create table movie_info( > movie string, > category array<string>) > row format delimited fields terminated by '\t' > collection items terminated by ',';
load data local inpath "/opt/soft/files/movie.txt" into table movie_info;
4.查
select movie,category_name from movie_info lateral view explode(category) table_tmp as category_name;
6.窗口函數(shù)
1.相關(guān)函數(shù)
OVER():指定的分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化色冀。
CURRENT ROW：當(dāng)前行
n PRECEDING：往前n行數(shù)據(jù)
n FOLLOWING：往后n行數(shù)據(jù)
UNBOUNDED：起點(diǎn)潭袱，UNBOUNDED PRECEDING 表示從前面的起點(diǎn)，UNBOUNDED FOLLOWING表示到后面的終點(diǎn)呐伞。
LAG（col,n）：往前第n行數(shù)據(jù)敌卓。
LEAD(col,n)：往后n行數(shù)據(jù)。
NTILE(n)：把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組伶氢，各個(gè)組有編號(hào)趟径，編號(hào)從1開始，對(duì)于每一行癣防，NTILE返回此行所屬的組的編號(hào)蜗巧，注意：n必須為int類型。
2.數(shù)據(jù)準(zhǔn)備 需求
name orderdate cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
jack,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
3.需求
1.查詢?cè)?017年4月份購買過的顧客及總?cè)藬?shù)
select name,count(*) over() from business where substring(orderdate,1,7)='2017-04' group by name;
over()開窗函數(shù)蕾盯，跟在聚合函數(shù)后面幕屹，等于開一部分?jǐn)?shù)據(jù)集，專門給聚合函數(shù)使用
2.查詢顧客的購買明細(xì)及月購買總額
select * ,sum(cost) over() from business;
3.上述場(chǎng)景级遭，要將cost按照日期進(jìn)行累加
select name,orderdate,sum(cost) over(distribute by name sort by orderdate) from business;
4.查詢顧客上次的購買時(shí)間
select name,orderdate,cost,lag(orderdate,1,'1970-01-01') over(distribute by name sort by orderdate) from business;
7.RANK
1.函數(shù)說明
RANK() 排序相同會(huì)重復(fù)望拖。總數(shù)不會(huì)變挫鸽。
DENSE_RANK() 排序相同時(shí)會(huì)重復(fù)说敏，總數(shù)會(huì)減少
ROW_NUMBER() 會(huì)根據(jù)順序計(jì)算
數(shù)據(jù)：
孫悟空 語文 87
孫悟空 數(shù)學(xué) 95
孫悟空 英語 68
大海 語文 94
大海 數(shù)學(xué) 56
大海 英語 84
松松 語文 64
松松 數(shù)學(xué) 86
松松 英語 84
婷婷 語文 65
婷婷 數(shù)學(xué) 85
婷婷 英語 78
看效果：
select name,subject,score, rank() over(partition by subject order by score desc) rank1,
dense_rank() over(partition by subject order by score desc) rank2,
row_number() over(partition by subject order by score desc) rank3
from score;
結(jié)果：
其他的結(jié)果就不展示了，因?yàn)槲姨摂M機(jī)搭的大數(shù)據(jù)集群是在是太慢了丢郊。盔沫。医咨。
create語句和load也不詳細(xì)寫了
練習(xí)題
1.我們有如下用戶訪問數(shù)據(jù)
userId visitDate visitCount
u01 2019/1/21 5
u02 2019/1/23 6
u03 2019/1/22 8
u04 2019/1/20 3
u01 2019/1/23 6
u01 2019/2/21 8
u02 2019/1/23 6
u01 2019/2/22 4
要求使用SQL統(tǒng)計(jì)出每個(gè)用戶的累計(jì)訪問次數(shù)，如下表：
用戶 月份 小計(jì) 累計(jì)
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3
創(chuàng)建表：
create table action(userId string,visitDate string,visitCount int) row format delimited fields terminated by "\t";
load data local inpath '/opt/soft/files/visit.txt' into table action;
解：
select userId, mn, sum_visitCount, sum(sum_visitCount) over(partition by userId order by mn) from (select userId, mn, sum(visitCount) sum_visitCount from (select userId, date_format(regexp_replace(visitDate,'/','-'),"yyyy-MM") mn, visitCount from action)t1 group by userId,mn) t2;
效果：
這個(gè)題有點(diǎn)難搞嗷！！
2.京東
有50w個(gè)京東店鋪瞳腌，每個(gè)顧客訪問任何一個(gè)店鋪的任何一個(gè)商品時(shí)，都會(huì)產(chǎn)生一條訪問日志很泊，訪問日志存儲(chǔ)的表名為visit，訪客用戶id為user_id俏蛮，被訪問的店鋪名稱為shop撑蚌。請(qǐng)統(tǒng)計(jì)：
數(shù)據(jù)：
u1 a
u2 b
u1 b
u1 a
u3 c
u4 b
u1 a
u2 c
u5 b
u4 b
u6 c
u2 c
u1 a
u2 a
u2 a
u3 a
u5 a
u5 a
u5 a
需求：
1.每個(gè)店鋪UV（訪客數(shù)）
2.每個(gè)店鋪訪問次數(shù)top3的訪客信息。輸出店鋪名搏屑、訪客id争涌、訪問次數(shù)。
建表：
create table visit(user_id string,shop string) row format delimited fields terminated by '\t';
load data local inpath '/opt/soft/files/jd.txt' into table visit;
解題：
Q1:
方法1：
select shop,count(distinct user_id) UV from visit group by shop;
方法2：（優(yōu)）
select shop,count(*) UV from (select shop,user_id from visit group by shop,user_id)t1 group by shop;
結(jié)果：
Q2：
select shop, user_id, ct from (select shop, user_id, ct, row_number() over(partition by shop order by ct desc) rk from (select shop, user_id, count(*) ct from visit group by shop,user_id)t1) t2 where rk<=3
結(jié)果：
