HiveQL常用查询函数——nvl、case when、concat、collect_set、collect_list、explode lateral view、窗口函数、rank

本文主要是介绍HiveQL常用查询函数——nvl、case when、concat、collect_set、collect_list、explode lateral view、窗口函数、rank,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

目录1. nvl(value,default_value)2. case wheneg1:对表emp_sex,求每个部门男女人数eg2:统计每个国家隶属洲的人口数(已知字段数据按照另一种条件分组)eg3:统计不同国家男女个数(完成不同条件的分组)3. concat、concat_ws、collect_set(列转行)4. explode & lateral view(行转列)5. 窗口函数(聚合函数+窗口函数,指定聚合函数的聚合范围)(where后不能接别名,嵌套子查询)6. row_number & dense_rank & rank(后接over窗口函数)1. nvl(value,default_value)
空字段赋值,若value为空,则赋值default_value;若value非空,则返回原本value值。default_value可以是数值、'字符串',也可以是字段# 将comm列为NULL的值,设为'two'
0: jdbc:hive2://hadoop100:10000> select comm,nvl(comm , 'two') nvl_comm from emp;
+---------+-----------+--+
|  comm   | nvl_comm  |
+---------+-----------+--+
| NULL    | two       |
| 300.0   | 300.0     |
| 500.0   | 500.0     |
| NULL    | two       |
| 1400.0  | 1400.0    |
| NULL    | two       |
| NULL    | two       |
| NULL    | two       |
| NULL    | two       |
| 0.0     | 0.0       |
| NULL    | two       |
| NULL    | two       |
| NULL    | two       |
| NULL    | two       |
+---------+-----------+--+# 将comm列为NULL的值,设为empno列同一行的值
0: jdbc:hive2://hadoop100:10000> select empno,comm,nvl(comm , empno) from emp;
+--------+---------+---------+--+
| empno  |  comm   |   _c2   |
+--------+---------+---------+--+
| 7369   | NULL    | 7369.0  |
| 7499   | 300.0   | 300.0   |
| 7521   | 500.0   | 500.0   |
| 7566   | NULL    | 7566.0  |
| 7654   | 1400.0  | 1400.0  |
| 7698   | NULL    | 7698.0  |
| 7782   | NULL    | 7782.0  |
| 7788   | NULL    | 7788.0  |
| 7839   | NULL    | 7839.0  |
| 7844   | 0.0     | 0.0     |
| 7876   | NULL    | 7876.0  |
| 7900   | NULL    | 7900.0  |
| 7902   | NULL    | 7902.0  |
| 7934   | NULL    | 7934.0  |
+--------+---------+---------+--+2. case when
case 某字段 when A then B else C end:当某字段的值为A时候,对新列取值B,否则取值Ceg1:对表emp_sex,求每个部门男女人数
eg1:表emp_sex
0: jdbc:hive2://hadoop100:10000> select * from emp_sex;
+---------------+------------------+--------------+--+
| emp_sex.name  | emp_sex.dept_id  | emp_sex.sex  |
+---------------+------------------+--------------+--+
| 张三            | A                | 男            |
| 李四            | B                | 男            |
| 王五            | B                | 男            |
| 赵六            | A                | 女            |
| 婷婷            | A                | 女            |
| 浩浩            | B                | 女            |
| 强强            | A                | 男            |
+---------------+------------------+--------------+--+要求每个部门的男女各多少人?
分析:先对dept_id进行分组,然后对每一组的数据进行case when聚合
select dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;+----------+-------------+---------------+--+
| dept_id  | male_count  | female_count  |
+----------+-------------+---------------+--+
| A        | 2           | 2             |
| B        | 2           | 1             |
+----------+-------------+---------------+--+
eg2:统计每个国家隶属洲的人口数(已知字段数据按照另一种条件分组)
+--------------+-----------------+--+
| num.country  | num.population  |
+--------------+-----------------+--+
| 中国           | 600             |
| 美国           | 100             |
| 加拿大          | 100             |
| 英国           | 200             |
| 法国           | 300             |
| 日本           | 250             |
| 德国           | 200             |
| 墨西哥          | 50              |
| 印度           | 280             |
+--------------+-----------------+--+
sql语句和查询结果如下hive (hive_db1)> select sum(population) sum,> case country> when '中国' then '亚洲'> when '美国' then '美洲'> when '加拿大' then '美洲'> when '日本' then '亚洲'> when '中国' then '亚洲'> when '印度' then '亚洲'> when '墨西哥' then '美洲'> else '其他洲' end> from num> group by> case country> when '中国' then '亚洲'> when '美国' then '美洲'> when '加拿大' then '美洲'> when '日本' then '亚洲'> when '中国' then '亚洲'> when '印度' then '亚洲'> when '墨西哥' then '美洲'> else '其他洲' end; sum	_c1
1130	亚洲
700	其他洲
250	美洲
eg3:统计不同国家男女个数(完成不同条件的分组)
+--------------+----------+-----------------+--+
| sex.country  | sex.sex  | sex.population  |
+--------------+----------+-----------------+--+
| 中国           | 男        | 340             |
| 中国           | 女        | 260             |
| 美国           | 男        | 45              |
| 美国           | 女        | 55              |
| 加拿大          | 男        | 51              |
| 加拿大          | 女        | 49              |
| 英国           | 男        | 40              |
| 英国           | 女        | 60              |
+--------------+----------+-----------------+--+
sql语句和查询结果如下select country,
sum(case when sex = '男' then population else 0 end) male,
sum(case when sex = '女' then population else 0 end) female
from sex group by country;+----------+-------+---------+--+
| country  | male  | female  |
+----------+-------+---------+--+
| 中国       | 340   | 260     |
| 加拿大      | 51    | 49      |
| 美国       | 45    | 55      |
| 英国       | 40    | 60      |
+----------+-------+---------+--+3. concat、concat_ws、collect_set(列转行)
concat(string1,string2,string3...):返回多个字符串的拼接结果,若其中任一参数为null,则返回null;concat_ws(separator,string1,string2...):concat的特殊形式,给拼接字符串结果加上分隔符separator,若分隔符是null,则返回null;Hive中collect相关函数有两个:collect_set和collect_list,都是列转行,将某列转为一个数组返回,区别是collect_list不去重,而collect_set去重collect_set(col):将字段col的值进行去重汇总,产生Array类型字段,只接受Hive的基本数据类型。collect_list(col):将字段col的值进行不去重汇总,产生Array类型字段,只接受Hive的基本数据类型。collect_ws(',',collect_set(area)):将去重后的array转换一下连接方式collect_set(area)[0]:取去重后array里的第一个字段ps:对比一下count(distinct 字段):对字段去重后,求个数eg:星座表如下
+--------------+-----------------------+--------------------+--+
| person.name  | person.constellation  | person.blood_type  |
+--------------+-----------------------+--------------------+--+
| 小王           | 白羊座                   | A                  |
| 小李           | 射手座                   | A                  |
| 小张           | 白羊座                   | B                  |
| 小赵           | 白羊座                   | A                  |
| 小明           | 射手座                   | A                  |
+--------------+-----------------------+--------------------+--+要求:把星座和血型一样的人归类拼接在一起
分析:先把星座和血型归并一张表,再聚合星座和血型相同的人,需要去重只留一个nameselect base_info, concat_ws('|',collect_set(name)) name
from (
select name, concat(constellation,',',blood_type) base_info
from person
) t1
group by base_info;+------------+--------+--+
| base_info  |  name  |
+------------+--------+--+
| 射手座,A      | 小李|小明  |
| 白羊座,A      | 小王|小赵  |
| 白羊座,B      | 小张     |
+------------+--------+--+4. explode & lateral view(行转列)
俗称爆炸函数,能将hive中一系列复杂的array或map结构拆分成多行。局限性:使用explode时,select只能查询explode的字段,查询别的字段会报错。0: jdbc:hive2://hadoop100:10000> select explode(array('xiaowang','xiaoli','xiaozhang'));
+------------+--+
|    col     |
+------------+--+
| xiaowang   |
| xiaoli     |
| xiaozhang  |
+------------+--+0: jdbc:hive2://hadoop100:10000> select explode(map('xiaowang','18','xiaoli','20'));
+-----------+--------+--+
|    key    | value  |
+-----------+--------+--+
| xiaowang  | 18     |
| xiaoli    | 20     |
+-----------+--------+--+
使用lateral view可以解决爆炸函数的局限性,同时查询字段和爆炸函数字段select 查询字段,Afrom tablenamelateral view explode(炸开字段) tmp_tbl(临时表名)as A;ps:如果字段中元素类型是集合,还需要创建表的时候指定集合元素的分隔符collection items terminated by ','1. 创建表(注意:要指定字段中集合的分隔符)
create table movie(
name string,
category array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';+--------------+-----------------------------+--+
|  movie.name  |       movie.category        |
+--------------+-----------------------------+--+
| 《疑犯追踪》       | ["悬疑","动作","科幻","剧情"]       |
| 《Lie to me》  | ["悬疑","警匪","动作","心理","剧情"]  |
| 《战狼2》        | ["战争","动作","灾难"]            |
+--------------+-----------------------------+--+2. 要求将电影分类中的array数据展开
select name,category_info 
from movie
lateral view explode(category) tmp_tbl as category_info;3. 得出结果:
+--------------+----------------+--+
|     name     | category_info  |
+--------------+----------------+--+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+--+5. 窗口函数(聚合函数+窗口函数,指定聚合函数的聚合范围)
如果查询结果,既想显示聚合前的数据,又想显示聚合后的数据,用开窗函数开窗函数结合聚合函数(UDAF函数)使用标准语法:UDAF()over(partition by col1,col2 order by col3 窗口子句(rows between ... and ...))as 列别名(partition by...order by...)可以替换为(distribute by... sort by...)partition by分区可以跟多个字段,但order by排序只能跟一个字段1. over():结合聚合函数使用,决定数据的聚合范围,默认的聚合范围是被where、group by等条件限制的整个数据窗口;2. over(partition by ~):将数据先进行分组,聚合范围就是每一个分组,每一个分组聚合出一个数据;3. over(partition by ~ order by ~):将数据先分组,再排序,可以改变聚合范围,每一次都是从开始行到当前行的一个聚合窗口;4. over(partition by ~ order by ~ 窗口子句rows between and):先按字段1分组,再按字段2排序,然后每次聚合窗口是动态变化的;ps:窗口函数别名后,where条件不能跟别名,所以要用嵌套子查询。窗口字句(rows between ~ and ~):聚合范围动态变化,对窗口范围进行粒度更细的动态划分current row:当前行n preceding:往前n行数据n following:往后n行数据unbounded:起点;unbounded preceding 从前面的起点;unbounded following到后面的终点;eg:聚合范围是当前行的前2行到后3行rows between 2 preceding and 3 following5. 三个UDAF聚合函数:(后接over()窗口函数)lag(col,n,defaultValue):查询字段col,当前行往前数第n行的数据,若为null显示默认值defaultValue;ps:lag(col):指默认每次取字段col当前行的上一行数据lead(col,n,defaultValue):查询字段col,当前行往后数第n行的数据,若为null显示默认值defaultValue;ps:lead(col):指默认每次取字段col当前行的下一行数据ntile(n):将有序分的窗口范围,平均分成n份,每一份编号依次为1、2、3...、n;如果要取其中第m份,需要用到嵌套子查询;原始表:三个字段依次是商品名称、购买日期、购买价格+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 46             |
| jack           | 2017-04-06          | 42             |
| tony           | 2017-01-07          | 50             |
| jack           | 2017-01-08          | 55             |
| mart           | 2017-04-08          | 62             |
| mart           | 2017-04-09          | 68             |
| neil           | 2017-05-10          | 12             |
| mart           | 2017-04-11          | 75             |
| neil           | 2017-06-12          | 80             |
| mart           | 2017-04-13          | 94             |
+----------------+---------------------+----------------+--+
(1)查询在2017年4月份购买过的顾客及总人数   over()select name, count(*) over ()
from business
where orderdate like '2017-04%'
group by name;+-------+-----------------+--+
| name  | count_window_0  |
+-------+-----------------+--+
| mart  | 2               |
| jack  | 2               |
+-------+-----------------+--+
(2)查询顾客的购买明细及月购买总额   over(partition by ~)分析:对客户购买日期进行月份分组,然后进行聚合得出月购买总额;用开窗函数select *, sum(cost) over(partition by month(orderdate))
from business;+----------------+---------------------+----------------+---------------+--+
| business.name  | business.orderdate  | business.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+--+
| jack           | 2017-01-01          | 10             | 205           |
| jack           | 2017-01-08          | 55             | 205           |
| tony           | 2017-01-07          | 50             | 205           |
| jack           | 2017-01-05          | 46             | 205           |
| tony           | 2017-01-04          | 29             | 205           |
| tony           | 2017-01-02          | 15             | 205           |
| jack           | 2017-02-03          | 23             | 23            |
| mart           | 2017-04-13          | 94             | 341           |
| jack           | 2017-04-06          | 42             | 341           |
| mart           | 2017-04-11          | 75             | 341           |
| mart           | 2017-04-09          | 68             | 341           |
| mart           | 2017-04-08          | 62             | 341           |
| neil           | 2017-05-10          | 12             | 12            |
| neil           | 2017-06-12          | 80             | 80            |
+----------------+---------------------+----------------+---------------+--+
(3)将每个顾客的cost按照日期进行累加   over(partition by ~ order by ~)分析:先按name分区,然后再按时间进行排序select *, sum(cost) over(partition by name order by orderdate)
from business;+----------------+---------------------+----------------+---------------+--+
| business.name  | business.orderdate  | business.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+--+
| jack           | 2017-01-01          | 10             | 10            |
| jack           | 2017-01-05          | 46             | 56            |
| jack           | 2017-01-08          | 55             | 111           |
| jack           | 2017-02-03          | 23             | 134           |
| jack           | 2017-04-06          | 42             | 176           |
| mart           | 2017-04-08          | 62             | 62            |
| mart           | 2017-04-09          | 68             | 130           |
| mart           | 2017-04-11          | 75             | 205           |
| mart           | 2017-04-13          | 94             | 299           |
| neil           | 2017-05-10          | 12             | 12            |
| neil           | 2017-06-12          | 80             | 92            |
| tony           | 2017-01-02          | 15             | 15            |
| tony           | 2017-01-04          | 29             | 44            |
| tony           | 2017-01-07          | 50             | 94            |
+----------------+---------------------+----------------+---------------+--+
(4)查看顾客上次的购买时间   lag(col,n,default)over()分析:按照name和购买时间分组排序后,字段orderdate每一行上一行的数据就是上一次购买时间select *, lag(orderdate,1,'-1')
over(partition by name order by orderdate)
from business;+----------------+---------------------+----------------+---------------+--+
| business.name  | business.orderdate  | business.cost  | lag_window_0  |
+----------------+---------------------+----------------+---------------+--+
| jack           | 2017-01-01          | 10             | -1            |
| jack           | 2017-01-05          | 46             | 2017-01-01    |
| jack           | 2017-01-08          | 55             | 2017-01-05    |
| jack           | 2017-02-03          | 23             | 2017-01-08    |
| jack           | 2017-04-06          | 42             | 2017-02-03    |
| mart           | 2017-04-08          | 62             | -1            |
| mart           | 2017-04-09          | 68             | 2017-04-08    |
| mart           | 2017-04-11          | 75             | 2017-04-09    |
| mart           | 2017-04-13          | 94             | 2017-04-11    |
| neil           | 2017-05-10          | 12             | -1            |
| neil           | 2017-06-12          | 80             | 2017-05-10    |
| tony           | 2017-01-02          | 15             | -1            |
| tony           | 2017-01-04          | 29             | 2017-01-02    |
| tony           | 2017-01-07          | 50             | 2017-01-04    |
+----------------+---------------------+----------------+---------------+--+
(5)查询前20%时间的订单信息   ntile(n)over()别名分析:按照时间排序,把时间分成5份,取第一份,类似于之前的分桶,这里用到ntile(n)函数;由于where不能跟别名,所以嵌套子查询select name,orderdate,cost
from(
select *, ntile(5) over(order by orderdate) num
from business
) t1
where num=1;+-------+-------------+-------+--+
| name  |  orderdate  | cost  |
+-------+-------------+-------+--+
| jack  | 2017-01-01  | 10    |
| tony  | 2017-01-02  | 15    |
| tony  | 2017-01-04  | 29    |
+-------+-------------+-------+--+
6. row_number & dense_rank & rank(后接over窗口函数)
排序函数,常与窗口函数结合使用(1)row_number():依据顺序排序,不会出现重复数字,eg:1,2,3,4,5,6,7(2)dense_rank():排序相同时会重复,总数会减少,eg:1,2,2,3,3,4,5(3)rank():排序相同时会重复,但重复值后的数会跳跃,总数不变,eg:1,2,2,4,4,6,7eg:计算每门学科的成绩排序原始表+------------------+---------------------+-------------------+--+
| score_info.name  | score_info.subject  | score_info.score  |
+------------------+---------------------+-------------------+--+
| 张三               | 语文                  | 87                |
| 张三               | 数学                  | 95                |
| 张三               | 英语                  | 68                |
| 李四               | 语文                  | 94                |
| 李四               | 数学                  | 56                |
| 李四               | 英语                  | 84                |
| 王五               | 语文                  | 64                |
| 王五               | 数学                  | 86                |
| 王五               | 英语                  | 84                |
| 赵六               | 语文                  | 65                |
| 赵六               | 数学                  | 85                |
| 赵六               | 英语                  | 75                |
+------------------+---------------------+-------------------+--+
三种排序查询结果如下:select *,
row_number() over(partition by subject order by score desc) row_number,
dense_rank() over(partition by subject order by score desc) dense_rank,
rank() over(partition by subject order by score desc) rank
from score_info;+------------------+---------------------+-------------------+-------------+-------------+-------+--+
| score_info.name  | score_info.subject  | score_info.score  | row_number  | dense_rank  | rank  |
+------------------+---------------------+-------------------+-------------+-------------+-------+--+
| 张三               | 数学                  | 95                | 1           | 1           | 1     |
| 王五               | 数学                  | 86                | 2           | 2           | 2     |
| 赵六               | 数学                  | 85                | 3           | 3           | 3     |
| 李四               | 数学                  | 56                | 4           | 4           | 4     |
| 王五               | 英语                  | 84                | 1           | 1           | 1     |
| 李四               | 英语                  | 84                | 2           | 1           | 1     |
| 赵六               | 英语                  | 75                | 3           | 2           | 3     |
| 张三               | 英语                  | 68                | 4           | 3           | 4     |
| 李四               | 语文                  | 94                | 1           | 1           | 1     |
| 张三               | 语文                  | 87                | 2           | 2           | 2     |
| 赵六               | 语文                  | 65                | 3           | 3           | 3     |
| 王五               | 语文                  | 64                | 4           | 4           | 4     |
+------------------+---------------------+-------------------+-------------+-------------+-------+--+

 

这篇关于HiveQL常用查询函数——nvl、case when、concat、collect_set、collect_list、explode lateral view、窗口函数、rank的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/487627

相关文章

HarmonyOS学习(七)——UI(五)常用布局总结

自适应布局 1.1、线性布局(LinearLayout) 通过线性容器Row和Column实现线性布局。Column容器内的子组件按照垂直方向排列,Row组件中的子组件按照水平方向排列。 属性说明space通过space参数设置主轴上子组件的间距,达到各子组件在排列上的等间距效果alignItems设置子组件在交叉轴上的对齐方式,且在各类尺寸屏幕上表现一致,其中交叉轴为垂直时,取值为Vert

JS常用组件收集

收集了一些平时遇到的前端比较优秀的组件,方便以后开发的时候查找!!! 函数工具: Lodash 页面固定: stickUp、jQuery.Pin 轮播: unslider、swiper 开关: switch 复选框: icheck 气泡: grumble 隐藏元素: Headroom

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

【C++】_list常用方法解析及模拟实现

相信自己的力量,只要对自己始终保持信心,尽自己最大努力去完成任何事,就算事情最终结果是失败了,努力了也不留遗憾。💓💓💓 目录   ✨说在前面 🍋知识点一:什么是list? •🌰1.list的定义 •🌰2.list的基本特性 •🌰3.常用接口介绍 🍋知识点二:list常用接口 •🌰1.默认成员函数 🔥构造函数(⭐) 🔥析构函数 •🌰2.list对象

常用的jdk下载地址

jdk下载地址 安装方式可以看之前的博客: mac安装jdk oracle 版本:https://www.oracle.com/java/technologies/downloads/ Eclipse Temurin版本:https://adoptium.net/zh-CN/temurin/releases/ 阿里版本: github:https://github.com/

活用c4d官方开发文档查询代码

当你问AI助手比如豆包,如何用python禁止掉xpresso标签时候,它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来

30常用 Maven 命令

Maven 是一个强大的项目管理和构建工具,它广泛用于 Java 项目的依赖管理、构建流程和插件集成。Maven 的命令行工具提供了大量的命令来帮助开发人员管理项目的生命周期、依赖和插件。以下是 常用 Maven 命令的使用场景及其详细解释。 1. mvn clean 使用场景:清理项目的生成目录,通常用于删除项目中自动生成的文件(如 target/ 目录)。共性规律:清理操作

poj 3050 dfs + set的妙用

题意: 给一个5x5的矩阵,求由多少个由连续6个元素组成的不一样的字符的个数。 解析: dfs + set去重搞定。 代码: #include <iostream>#include <cstdio>#include <set>#include <cstdlib>#include <algorithm>#include <cstring>#include <cm

C++操作符重载实例(独立函数)

C++操作符重载实例,我们把坐标值CVector的加法进行重载,计算c3=c1+c2时,也就是计算x3=x1+x2,y3=y1+y2,今天我们以独立函数的方式重载操作符+(加号),以下是C++代码: c1802.cpp源代码: D:\YcjWork\CppTour>vim c1802.cpp #include <iostream>using namespace std;/*** 以独立函数

019、JOptionPane类的常用静态方法详解

目录 JOptionPane类的常用静态方法详解 1. showInputDialog()方法 1.1基本用法 1.2带有默认值的输入框 1.3带有选项的输入对话框 1.4自定义图标的输入对话框 2. showConfirmDialog()方法 2.1基本用法 2.2自定义按钮和图标 2.3带有自定义组件的确认对话框 3. showMessageDialog()方法 3.1