1：CAST (expression AS data_type)

CAST函數(shù)用于將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型。CAST()函數(shù)的參數(shù)是一個(gè)表達(dá)式，它包括用AS關(guān)鍵字分隔的源值和目標(biāo)數(shù)據(jù)類型。可以轉(zhuǎn)換的類型是有限制的。

這個(gè)類型可以是以下值其中的一個(gè)：二進(jìn)制，同帶binary前綴的效果 : BINARY? ??

字符型，可帶參數(shù) : CHAR() ? ??

日期 : DATE ? ??

時(shí)間: TIME ? ??

日期時(shí)間型 : DATETIME ? ??

浮點(diǎn)數(shù) : DECIMAL ? ? ?

整數(shù) : SIGNED ? ??

無符號(hào)整數(shù) : UNSIGNED

2：binary

Mysql默認(rèn)查詢是不分大小寫的，可以在SQL語句中加入 binary來區(qū)分大小寫；BINARY不是函數(shù)，是類型轉(zhuǎn)換運(yùn)算符，它用來強(qiáng)制它后面的字符串為一個(gè)二進(jìn)制字符串，可以理解為在字符串比較的時(shí)候區(qū)分大小寫

例如 ：select * from sys_user where binary loginno = `qqq`

3：explode

explode就是將sql或者h(yuǎn)ive一行中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。lateral view（側(cè)視圖）的意義是配合explode（或者其他的UDTF），一個(gè)語句生成把單行數(shù)據(jù)拆解成多行后的數(shù)據(jù)結(jié)果集。形成的虛擬表tb1 相當(dāng)于再和原表聯(lián)結(jié) 形成了新表interests1直接使用explode 一次只能生成一個(gè)字段 要生成多列就是使用lateral view（側(cè)視圖）

drop table explode_lateral_view;

create table explode_lateral_view

(`area` string,

`goods_id` string,

`sale_info` string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '|'

STORED AS textfile;

導(dǎo)入數(shù)據(jù)：

INSERT INTO management_cc.explode_lateral_view VALUES('a:shandong,b:beijing,c:hebei','1,2,3,4,5,6,7,8,9','{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]');

表內(nèi)數(shù)據(jù)如下

select explode(split(goods_id,',')) as goods_id from explode_lateral_view;

select explode(split(area,',')) as area from explode_lateral_view;

select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as? sale_info from explode_lateral_view;

然后我們想用get_json_object來獲取key為monthSales的數(shù)據(jù)：

select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as? sale_info from explode_lateral_view;

然后掛了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

UDTF explode不能寫在別的函數(shù)內(nèi)

如果你這么寫，想查兩個(gè)字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;

會(huì)報(bào)錯(cuò)FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'

使用UDTF的時(shí)候，只支持一個(gè)字段，這時(shí)候就需要LATERAL VIEW出場(chǎng)了

select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

select goods_id2,sale_info,area2

from explode_lateral_view

LATERAL VIEW explode(split(goods_id,','))goods as goods_id2

LATERAL VIEW explode(split(area,','))area as area2;

select

get_json_object(concat('{',sale_info_r,'}'),'$.source') as source,

get_json_object(concat('{',sale_info_r,'}'),'$.monthSales') as monthSales,

get_json_object(concat('{',sale_info_r,'}'),'$.userCount') as userCount,

get_json_object(concat('{',sale_info_r,'}'),'$.score') as score

from explode_lateral_view

LATERAL VIEW explode(split(

regexp_replace(

regexp_replace(sale_info,'\\{',''),

'}]',

''

),'},'))sale_info as sale_info_r;