1,json轉換為結構化對象

select get_json_object({"store":{"fruit":["aa, "bb, "cc"]}
,"owner":"amy"},$.store.fruit[0]);

select find_in_set(ab,ef,ab,de);
select repeat(abc,5);

2,解析URL

parse_url(url, partToExtract[, key])
-- extracts a part from a URL --
解析URL字元串,
partToExtract的選項包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。

3,正則查找與替換

regexp_extract(string subject, string pattern, int index)
substr(string A, int start, int len),substring(string A, int start, int len)
regexp_replace(string A, string B, string C)

select concat_ws(,abc,def,gh)
select reverse(abcedfg)

4,是否包含子字元串

//測試字元串是否包含子字元串
select if(split(父串,子串)[1] is not NULL,1,NULL)

5,數字IP轉換成常規IP

//將數字IP轉換成常規IP
字元串是如何轉換而來的呢?其實並不複雜,以「218.22.123.26」這個IP地址為例,
IP地址轉換成數字串方法如下:先將 「218.22.123.26」轉換為十六進位「DA.16.7B.1A」,
然後去掉小數點後,變為「DA167B1A」,最後將這個十六進位數轉換為十進位「3658906394」,
那麼「218.22.123.26」就變為「3658906394」了。其他IP地址轉換為數字串也是使用同樣的方法。
SELECT concat_ws(.,CONV(substr(hex(3658906394),1,2),16,10),
CONV(substr(hex(3658906394),3,2),16,10),
CONV(substr(hex(3658906394),5,2),16,10),
CONV(substr(hex(3658906394),7,2),16,10));

select CONV(substr(hex(3658906394),3,4),16,10);
substr(string A, int start, int len)
substring(string A, intstart, int len)

6,集合查找函數

集合查找函數:find_in_set
find_in_set(string str, string strList)
返回str在strlist第一次出現的位置,strlist是用逗號分割的字元串。如果沒有找該str字元,則返回0

cast(<expr> as <type>)
size(Array<T>)
from_unixtime(int unixtime)
to_date(string timestamp)

7,正則表達式的基礎學習

. 一個單一字元
* 零次或多次
+ 一次或多次
?零次或一次
{n,m} 至少匹配n次,最多匹配m次

[a-z] //匹配所有的小寫字母
[A-Z] //匹配所有的大寫字母
[a-zA-Z] //匹配所有的字母
[0-9] //匹配所有的數字
[0-9.-] //匹配所有的數字,句號和減號
[ f

] //匹配所有的白字元
[^a-z] //除了小寫字母以外的所有字元
[^\/^] //除了()(/)(^)之外的所有字元
[^"] //除了雙引號(")和單引號()之外的所有字元

8,位運算

select 2&2------2
select 3&2------2
select 1|2------3
select 2|2------2
select ~2------ -3

bin(BIGINT a)
hex(BIGINT a) hex(string a)
unhex(string a)
conv(BIGINT num, int from_base, int to_base)---
select conv(150354d103e2a6cfb,16,10)
pmod(double a, double b) 返回餘數絕對值
cast(expr as <type>) 類型轉換

9,日期運算

date_add(string startdate, int days)
date_sub(string startdate, int days)
unix_timestamp(string date, string pattern) 將指定格式的時間 轉換成秒
from_unixtime(bigint unixtime[, string format])

select from_unixtime(1463042015872,yyyy-MM-dd HH:mm:ss)
select sentences(Hello there! How are you?)

10,語句分析

SELECT ngrams(sentences(Hello there! How are you?), 2, 100 ,1000)
結果:[{"ngram":["Hello, "there"],"estfrequency":1.0},{"ngram":["How, "are"],"estfrequency":1.0},{"ngram":["are, "you"],"estfrequency":1.0}]
SELECT context_ngrams(sentences(lower(tweet)), array(null,null), 100, [, 1000]) FROM twitter;

11,集合

collect_set(col) 無重複記錄
explode(array<TYPE> a) 拆分成列

12,分析函數(很重要)

分析函數:LAG--排序向上去第n,LEAD--排序向下去第n,
FIRST_VALUE--取分組排序當前行的第一個,
LAST_VALUE--取分組排序當前行的最後一個就是自己;

SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
FROM lxw1234
ORDER BY cookieid,createtime;

推薦閱讀:

相关文章