-
行转列,列转行函数,一行转多行,多行转一行
行拆多行
函数 explode(myCol)把数组拆开,常与lateral view搭配使用,
posexplode(myCol),此函数还能拿到数据的index
1.select create_time,table_id
from
dj_ods_ds.o_t_job_mail mail
ta lateral view explode(split(hive_table_id,',')) te as table_id
-
多行聚集到一行
函数collect_set(col) 会去重
collect_list(col) 不会去重,都返回数组
select no,collect_set(score) from tablss group by no;
-
tuple parse_url_tuple(url, p1, p2, ...) 解析url得到参数值,返回元组
string get_json_object(string json_string, string path) 解析json字符串
tuple json_tuple(jsonStr, k1, k2, ...) 一次返回多个值,比上面这个函数效率
-
CUBE 的使用 SELECT month, day,
COUNT(DISTINCT cookie) AS uv,
GROUPING__ID
FROM tmp.test_group
GROUP BY month,day
WITH CUBE
order by GROUPING__ID limit 20;
-
行转列 select max(case when line='xx' then num end) as `xx`,
max(case when line='yy' then num end) as `yy`
from
(select line,count(*) num from
tablea
group by line) ta
hive sql 高级函数
最新推荐文章于 2024-05-31 08:45:35 发布