本篇文章長更，歡迎大家收藏和喜歡
2018.12.25 字符串函數(shù)2
1.正則表達式解析函數(shù)：regexp_extract
語法: regexp_extract(string subject, string pattern, int index)
返回值: string
說明：將字符串subject按照pattern正則表達式的規(guī)則拆分，返回index指定的字符牛柒。
舉例：
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) fromlxw_dual;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) fromlxw_dual;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) fromlxw_dual;
foothebar
注意，在有些情況下要使用轉(zhuǎn)義字符，下面的等號要用雙豎線轉(zhuǎn)義，這是java正則表達式的規(guī)則圈浇。
select data_field,
? ???regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
? ???regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
? ???regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc
? ???from pt_nginx_loginlog_st
? ???where pt = '2012-03-26'limit 2;
2. URL解析函數(shù)：parse_url
語法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])
返回值: string
說明：返回URL中指定的部分。partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
舉例：
hive> selectparse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') fromlxw_dual;
facebook.com
hive> selectparse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1') from lxw_dual;
v1
3. json解析函數(shù)：get_json_object
語法: get_json_object(string json_string, string path)
返回值: string
說明：解析json的字符串json_string,返回path指定的內(nèi)容靴寂。如果輸入的json字符串無效牛柒，那么返回NULL霞扬。
舉例：
hive> select get_json_object('{"store":
>??{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
>? ?"bicycle":{"price":19.95,"color":"red"}
>? ?},
> "email":"amy@only_for_json_udf_test.net",
>??"owner":"amy"
> }
> ','$.owner') from lxw_dual;
amy
2018.12.25 字符串函數(shù)2
1.去空格函數(shù)：trim
語法: trim(string A)
返回值: string
說明：去除字符串兩邊的空格
舉例：
hive> select trim(' abc ') from lxw_dual;
abc
2.左邊去空格函數(shù)：ltrim
語法: ltrim(string A)
返回值: string
說明：去除字符串左邊的空格
舉例：
hive> select ltrim(' abc ') from lxw_dual;
abc
3.右邊去空格函數(shù)：rtrim
語法: rtrim(string A)
返回值: string
說明：去除字符串右邊的空格
舉例：
hive> select rtrim(' abc ') from lxw_dual;
abc
4.強制類型轉(zhuǎn)換
select cast(‘1’ as bigint) from dual;
2018.12.20 字符串函數(shù)1
1.字符串長度函數(shù)：length
語法: length(string A)
返回值: int
說明：返回字符串A的長度
舉例：
hive> select length('abcedfg') from lxw_dual;
2.字符串截取函數(shù)：substr,substring
語法: substr(string A, int start),substring(string A, int start)
返回值: string
說明：返回字符串A從start位置到結(jié)尾的字符串
舉例：
hive> select substr('abcde',3) from lxw_dual;
cde
hive> select substring('abcde',3) from lxw_dual;
cde
hive>??selectsubstr('abcde',-1) from lxw_dual;??（和ORACLE相同）
3.字符串轉(zhuǎn)大寫函數(shù)：upper,ucase
語法: upper(string A) ucase(string A)
返回值: string
說明：返回字符串A的大寫格式
舉例：
hive> select upper('abSEd') from lxw_dual;
ABSED
hive> select ucase('abSEd') from lxw_dual;
ABSED
4.字符串轉(zhuǎn)小寫函數(shù)：lower,lcase
語法: lower(string A) lcase(string A)
返回值: string
說明：返回字符串A的小寫格式
舉例：
hive> select lower('abSEd') from lxw_dual;
absed
hive> select lcase('abSEd') from lxw_dual;
absed
2018.12.17 統(tǒng)計函數(shù)
Null值不參與計算
?個數(shù)統(tǒng)計函數(shù): count
?總和統(tǒng)計函數(shù): sum
?平均值統(tǒng)計函數(shù): avg
?最小值統(tǒng)計函數(shù): min
?最大值統(tǒng)計函數(shù): max
2018.12.09 條件判斷函數(shù)
1.case when
select
case when X1??then A when X2 then B else C end result
當(dāng)滿足X1條件，result輸出A拉背，當(dāng)滿足X2條件舌狗，result輸出B葡兑，都不滿足輸出C
2.if
select
if(判斷條件,true返回值, 其他返回值)
2018.12.06 排序函數(shù)
排序函數(shù)包括Order by、Sort by、Distribute By轨蛤、Cluster By…你知道它們的用法和不同嗎蜜宪？
1. Order by：“我是全局排序”
解讀：order by 會對輸入做全局排序虫埂，因此只會有一個reduce（多個reduce無法保證全局有序）；這樣當(dāng)輸入規(guī)模較大時圃验，會導(dǎo)致較長的計算時間掉伏。
2. distribute by：“我類似于分區(qū)，通常和Sort by一起使用” 解讀：根據(jù)distribute by指定的字段把數(shù)據(jù)劃分到不同的輸出reduce文件中澳窑。
3. Sort by：“我可以基于分區(qū)排序” 解讀：sort by不是全局排序斧散，其在數(shù)據(jù)進入reduce前完成排序，因此摊聋，當(dāng)有多個reduce時鸡捐，只能保證單個reduce輸出有序，不能保證全局有序麻裁。
4. Cluster By：“我的本領(lǐng)大箍镜，通常等同于 Distribute By + Sort by” 解讀：cluster by 除了具有 distribute by 的功能外還兼具 sortby 的功能。 但是cluster by默認(rèn)升序煎源，不能指定排序規(guī)則為asc 或者desc色迂。
介紹完畢，再來個示例吧：
例：近期參加初級手销、中級歇僧、高級認(rèn)證的考生成績?nèi)缦拢?/p>
1）通過order by score asc，結(jié)果如下：
注意：在hive.mapred.mode=strict模式下锋拖，使用order by時必須添加limit限制诈悍，能夠大幅減少reducer數(shù)據(jù)規(guī)模。例如兽埃，當(dāng)限制limit 10時侥钳，如果map的個數(shù)為20，則reducer的輸入規(guī)模為10*20
2）通過distribute by grade sort by score asc讲仰，結(jié)果如下：
3） cluster by grade等價于distribute by grade sort by grade asc慕趴，但并不等價distribute by grade sort by score asc。細(xì)心的小伙伴發(fā)現(xiàn)不同了嗎鄙陡？所以cluster by的本領(lǐng)還是有點局限冕房，distribute by colname1結(jié)合sort by colname2更實用哦。
2018.11.22 正則表達式??regexp_extract
語法: ? ?regexp_extract(string?subject, ?string?pattern, ?int?index)? ? ? ?返回值:string
說明： ?將字符串subject按照pattern正則表達式的規(guī)則拆分趁矾，返回index指定的字符耙册。
第一參數(shù)：? 要處理的字段
第二參數(shù): ? ?需要匹配的正則表達式
第三個參數(shù):0是顯示與之匹配的整個字符串? ?1 是顯示第一個括號里面的? ? 2 是顯示第二個括號里面的字段...
實例：
regexp_extract('00aa0', '([0-9]+)', 0)? ?--00
rregexp_extract('00aa0', '([0-9]+)([a-z]+)', 0)? --00aa
正則中常用的表達式
中文:[\u4e00-\u9fa5]?
英文字母:[a-zA-Z]?
數(shù)字:[0-9]?
匹配中文，英文字母和數(shù)字及_: ^[\u4e00-\u9fa5_a-zA-Z0-9]+$
同時判斷輸入長度：[\u4e00-\u9fa5_a-zA-Z0-9_]{4,10}
2018.11.20?行轉(zhuǎn)列毫捣、列轉(zhuǎn)行详拙、多行轉(zhuǎn)單行帝际、一列轉(zhuǎn)多列
行轉(zhuǎn)列、列轉(zhuǎn)行饶辙、多行轉(zhuǎn)單行蹲诀、一列轉(zhuǎn)多列
①行轉(zhuǎn)列：
應(yīng)用場景：
select
name,
concat_ws('', collect_set(ywscore)) ywscore,? --多行轉(zhuǎn)一行
concat_ws('', collect_set(sxscore))?sxscore,
from
(select?
name，
case when? subject='語文' then score end 'ywscore',
case when? subject='語文' then score end 'sxscore'
from XXXX)?
group by name
collect_list--不去重弃揽，可替換collect_set
②列轉(zhuǎn)行（一列轉(zhuǎn)多行）
select name,score1
from XXXXX
lateral view explode(split(score, ',')) as score1? --spilt一列變多列
2018.08.08 字符串替換
字符串替換函數(shù)
regexp_replace('abcde', 'a', 'm') ;--mbcde
注意regexp_replace可以連用脯爪，最多連三次
regexp_replace(regexp_replace(regexp_replace('abcde', 'a', 'm'), 'b', 'm'), 'c', 'm') --mmmde
2018.06.01 時間函數(shù)
8.其他日期函數(shù)
查詢當(dāng)月第幾天: dayofmonth(current_date);
月末: last_day(current_date)
當(dāng)月第1天: date_sub(current_date,dayofmonth(current_date)-1)
下個月第1天: add_months(date_sub(current_date,dayofmonth(current_date)-1),1)
2018.05.27 時間函數(shù)
1.日期轉(zhuǎn)時間戳：從1970-01-01 00:00:00 UTC到指定時間的秒數(shù)
select unix_timestamp(); 獲得當(dāng)前時區(qū)的UNIX時間戳
select unix_timestamp('2018-05-27 14:23:00');--1527402180
select unix_timestamp('2018-05-27 14:23:00','yyyy-MM-dd HH:mm:ss');
select unix_timestamp('20180527 14:23:00','yyyyMMdd HH:mm:ss');
2.時間戳轉(zhuǎn)日期
select from_unixtime(1527402180);--2018-05-27
select from_unixtime(1505456567,'yyyyMMdd');
select from_unixtime(1505456567,'yyyy-MM-dd HH:mm:ss');
3.獲取當(dāng)前日期: current_date
select current_date? ? ?--2018-05-27
4.日期時間轉(zhuǎn)日期：to_date(string timestamp)
select to_date('2018-05-27 11:12:00') ;? ? --2018-05-27
5.獲取日期中的年/月/日/時/分/秒/周
select year(dt),month(dt),day(dt),hour(dt),minute(dt),second(dt),weekofyear(dt)
6.計算兩個日期之間的天數(shù): datediff
select datediff('2018-05-27','2018-05-25') ; --2
7.日期增加和減少: date_add/date_sub(string startdate,int days)
select date_add('2018-05-27',1) ; --2018-05-28
select date_sub('2018-05-27',1) ; --2018-05-26
2018.03.21 取以經(jīng)緯度范圍多少公里數(shù)據(jù)
( abs(latitude - 30.528941640491446) * 111 <= 5 and abs(longitude - 120.69242) * 111 <= 5)?
30.528941640491446是經(jīng)度，120.69242是緯度矿微，5是公里
2018.03.10 map類型字段
直接使用ext_columns["key"]可得到value
json串解析：str_to_map(page_param) ['"skuid"']
2018.03.04 字符串分割函數(shù)：split()
split(split(ct_url,'com/') [1], '.html') [0]
使用多個符號分割用[,]痕慢，不僅可以取符號分割，還可以取字母涌矢，常用詞組等
split(cfv_cate_90dcate3, '[,#]') [4]? ?
注意：分割后結(jié)果為數(shù)組掖举，數(shù)組指針從0開始，所以必須帶上[指針]調(diào)用
2018.02.27 分組排序函數(shù)：row_number()娜庇，dense_rank()塔次，rank()
應(yīng)用場景：對品類下去品牌銷量TOP3，品牌下取銷量TOP商品思灌，各班級英語成績最高的前3名俺叭，班級中各科成績學(xué)生排名等。
實例：對品牌下型號銷量進行排序泰偿，取各品牌銷量前三型號熄守。
建表，寫入表數(shù)據(jù)如下：
取數(shù)腳本：
“
SELECT
brand,
size,
salenum,
row_number() over(partition by brand order by salenum) row_number,
dense_rank() over(partition by brand order by salenum) dense_rank,
rank() over(partition by brand order by salenum) rank
from
dev_3c_xtzc.moring_share_rownumber_3c_zwh aa
”
結(jié)果：
其中：1.over為窗口函數(shù)耗跛，取當(dāng)前查詢的結(jié)果集2.partition可理解為切片分區(qū)裕照，根據(jù)brand進行分區(qū)3.row_number、dense_rank和rank的區(qū)別是row_number遇到相同分?jǐn)?shù)调塌，不做并列晋南，直接遞增排序，dense_rank和rank會取并列值羔砾，rank并列值后跳過間隔排序负间，即最大值和row_number的一致。
