1. 借鑒
2. 開始
行轉(zhuǎn)列
相關(guān)函數(shù)
- CONCAT(string A/col, string B/col…)
返回輸入字符串連接后的結(jié)果失乾，支持任意個輸入字符串; - CONCAT_WS(separator, str1, str2,...)
它是一個特殊形式的 CONCAT()碾盐。第一個參數(shù)表示參數(shù)間的分隔符邮利。如果分隔符是 NULL芹缔，返回值也將為 NULL。這個函數(shù)會跳過分隔符參數(shù)后的任何 NULL 和空字符串吮炕。分隔符將被加到被連接的字符串之間歧蒋。只能拼接字符串類型 - COLLECT_SET(col)
聚合函數(shù)，函數(shù)只接受基本數(shù)據(jù)類型咪惠，它的主要作用是將某字段的值進行去重匯總击吱，產(chǎn)生array類型字段。 - COLLECT_LIST(col)
聚合函數(shù)遥昧，函數(shù)只接受基本數(shù)據(jù)類型覆醇，產(chǎn)生array類型字段
數(shù)據(jù)準(zhǔn)備
db_company.company 數(shù)據(jù)下載
表如下：
# 列注釋
# id 主鍵
# name， 產(chǎn)品名稱
# code炭臭， 企業(yè)code
# dimension 領(lǐng)域
# score 分?jǐn)?shù)
create external table db_company.company
(
id bigint,
name string,
code string,
dimension string,
score bigint
)
row format delimited
fields terminated by ',';
加載數(shù)據(jù)：
load data local inpath '/opt/envs/datas/company/company.txt' into table db_company.company;
CONCAT
拼接字符串
- 查詢小米旗下分類和名稱永脓，以:分隔
select concat(dimension, ':', name) from db_company.company where code = '35462360';
結(jié)果如下
商標(biāo):小米 Play
商標(biāo):小米 Plus
商標(biāo):小米
專利:小米掛面
專利:小米紅棗粥
CONCAT_WS
拼接字符串，只能輸入字符串
- 查詢分類鞋仍，名稱和分?jǐn)?shù)常摧，以:分隔
這里我們的score是bigint類型，會出現(xiàn)以下錯誤:
select concat_ws(':', dimension, name, score) from db_company.company where code = '35462360';
錯誤提示
FAILED: SemanticException [Error 10016]: Line 1:39 Argument type mismatch 'score': Argument 4 of function CONCAT_WS must be "string or array<string>", but "bigint" was found.
正確的姿勢如下：
select concat_ws(':', dimension, name, cast(score as string)) from db_company.company where code = '35462360';
查詢結(jié)果如下：
商標(biāo):小米 Play:5
商標(biāo):小米 Plus:3
商標(biāo):小米:3
專利:小米掛面:3
專利:小米紅棗粥:3
COLLECT_SET
-
① 獲得名字的數(shù)組
select collect_set(name) from company;結(jié)果如下：
["小米 Play","小米 Plus","小米","至信","中勝","信用通行證","小米掛面","小米紅棗粥","至信圖像"] -
② 將 ① 中獲得名字以 | 分隔
select concat_ws('|', collect_set(name)) from company;結(jié)果如下:
小米 Play|小米 Plus|小米|至信|中勝|(zhì)信用通行證|小米掛面|小米紅棗粥|至信圖像 -
③ 查詢每個企業(yè)威创，不同領(lǐng)域下的產(chǎn)品的名稱落午，以,分隔
我們需要的是下面的數(shù)據(jù)
35462360:商標(biāo) 小米 Play,小米 Plus,小米 35462361:商標(biāo) 至信,中勝,信用通行證 35462360:專利 小米掛面,小米紅棗粥 35462361:專利 至信圖像第一步：拼接第一列
select concat(code, ':', dimension), name from company;獲得以下數(shù)據(jù)
35462360:商標(biāo) 小米 Play 35462360:商標(biāo) 小米 Plus 35462360:商標(biāo) 小米 35462361:商標(biāo) 至信 35462361:商標(biāo) 中勝 35462361:商標(biāo) 信用通行證 35462360:專利 小米掛面 35462360:專利 小米紅棗粥 35462361:專利 至信圖像第二步：分組
select t1.code_dimension, concat_ws(',', collect_set(t1.name)) from ( select concat(code, ':', dimension) as code_dimension, name from company ) as t1 group by code_dimension;結(jié)果如下：
35462360:專利 小米掛面,小米紅棗粥 35462360:商標(biāo) 小米 Play,小米 Plus,小米 35462361:專利 至信圖像 35462361:商標(biāo) 至信,中勝,信用通行證
列轉(zhuǎn)行
相關(guān)函數(shù)
- SPLIT(string str, string pattern)
按照pattern字符串分割str，會返回分割后的字符串?dāng)?shù)組 - EXPLODE(col)
將array或者map結(jié)構(gòu)拆分成多行肚豺。 - LATERAL VIEW
用于和split, EXPLODE等UDTF一起使用溃斋，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進行聚合吸申。
數(shù)據(jù)準(zhǔn)備
db_movie.movie_douban 數(shù)據(jù)下載
表如下：
create database db_movie;
use db_movie;
create external table db_movie.movie_douban
(
name string,
tag array<string>,
country string
)
row format delimited
fields terminated by ','
collection items terminated by '/';
加載數(shù)據(jù)：
load data local inpath '/opt/envs/datas/movie/movie.txt' into table db_movie.movie_douban;
SPLIT
注意這里split輸出兩個字符串類型梗劫，而我們的tag是array類型，所以這里我們做一個沒有意義的測試截碴。
select split(concat_ws(',', tag), ',')
from db_movie.movie_douban;
根據(jù)結(jié)果我們會發(fā)現(xiàn)它跟直接查詢tag字段是一樣的梳侨。
["犯罪","劇情"]
["劇情","愛情"]
["劇情","犯罪","懸疑"]
["劇情","歌舞"]
["劇情","愛情"]
["劇情","喜劇","愛情","戰(zhàn)爭"]
["劇情","歷史","戰(zhàn)爭"]
["劇情","歷史"]
["劇情","犯罪","懸疑"]
["劇情","犯罪","懸疑"]
["劇情","動作","犯罪"]
["劇情","動畫","奇幻"]
["劇情","愛情","災(zāi)難"]
["劇情"]
EXPLODE
- 查看電影總共有多少個分類
這里為了好看，我也將列轉(zhuǎn)行了日丹，看官在試的時候可以去掉最外層查詢
結(jié)果如下：select concat_ws(' ', collect_set(t2.name)) from ( select distinct t.name as name from (select explode(tag) as name from db_movie.movie_douban) as t ) as t2;劇情 動作 動畫 歷史 喜劇 奇幻 懸疑 戰(zhàn)爭 歌舞 災(zāi)難 愛情 犯罪
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
udtf：目前我們知道有split和explode
tableAlias：表別名
columnAlias：列別名
- ① 將分類數(shù)據(jù)展開成以下
別看好像挺簡單走哺，犯難了吧。這里我們可以一步到位name tag_category 肖申克的救贖 犯罪 肖申克的救贖 劇情 霸王別姬 劇情 霸王別姬 愛情 控方證人 劇情 控方證人 犯罪 控方證人 懸疑 伊麗莎白 劇情 伊麗莎白 歌舞 阿甘正傳 劇情 阿甘正傳 愛情 美麗人生 劇情 美麗人生 喜劇 美麗人生 愛情 美麗人生 戰(zhàn)爭 辛德勒的名單 劇情 辛德勒的名單 歷史 辛德勒的名單 戰(zhàn)爭 茶館 劇情 茶館 歷史 控方證人 劇情 控方證人 犯罪 控方證人 懸疑 十二怒漢 劇情 十二怒漢 犯罪 十二怒漢 懸疑 這個殺手不太冷 劇情 這個殺手不太冷 動作 這個殺手不太冷 犯罪 千與千尋 劇情 千與千尋 動畫 千與千尋 奇幻 泰坦尼克號 劇情 泰坦尼克號 愛情 泰坦尼克號 災(zāi)難 忠犬八公的故事 劇情select name, tag_category from db_movie.movie_douban lateral view explode(tag) table_tmp as tag_category; - ② 查詢每種類型的電影
結(jié)果如下哲虾，是不是有點意思割坠，一開始我們的是以電影維度統(tǒng)計的電影的標(biāo)簽，現(xiàn)在我們以標(biāo)簽維度統(tǒng)計電影select t.tag_category, concat_ws(',', collect_set(t.name)) from ( select name, tag_category from db_movie.movie_douban lateral view explode(tag) table_tmp as tag_category ) as t group by tag_category;劇情 肖申克的救贖,霸王別姬,控方證人,伊麗莎白,阿甘正傳,美麗人生,辛德勒的名單,茶館,十二怒漢,這個殺手不太冷,千與千尋,泰坦尼克號,忠犬八公的故事 動作 這個殺手不太冷 動畫 千與千尋 歷史 辛德勒的名單,茶館 喜劇 美麗人生 奇幻 千與千尋 懸疑 控方證人,十二怒漢 戰(zhàn)爭 美麗人生,辛德勒的名單 歌舞 伊麗莎白 災(zāi)難 泰坦尼克號 愛情 霸王別姬,阿甘正傳,美麗人生,泰坦尼克號 犯罪 肖申克的救贖,控方證人,十二怒漢,這個殺手不太冷
