hive07: hive中sql常用函数

最新推荐文章于 2024-05-30 07:15:48 发布

姚家金吼

最新推荐文章于 2024-05-30 07:15:48 发布

阅读量670

点赞数

分类专栏： hive

本文链接：https://blog.csdn.net/yaojaijinhou/article/details/110451669

版权

hive 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.case..when

数据求出不同部门男女各多少人

王奔   A   男
娜娜   A   男
宋宋   B   男
凤姐   A   女
热巴   B   女
慧慧   B   女

建表并导入数据

create table tb_emp(
name string,
dname string,
gender string
)
row format delimited fields terminated by '\t';
load data local inpath '/data/tbemp/' into table tb_emp;

1)第一种方法
select
dname,
sum(case gender when '男' then 1 else 0 end) M,
sum(case gender when '女' then 1 else 0 end) F 
from
tb_emp
group by 
dname;

2)第二种方法
select
dname,
sum(if(gender='男',1,0)) M,
sum(if(gender='女',1,0)) F
from
tb_emp
group by
dname;

2.行转列

concat() 拼接函数参数是可变参数

concat('a1',',','a2',',','a3')

concat_ws() 参数一拼接符参数二可变个数的字符串或者数组

collect_list() 聚合函数将收集的多行数据聚集成一个数组集合

collect_set() 也是聚合函数，结果会去重

列子：

原格式
+------------------+----------------+----------------+
| tb_teacher.name | tb_teacher.xz | tb_teacher.xx |
+------------------+----------------+----------------+
| 慧慧 | 处女座 | B |
| 老娜 | 射手座 | A |
| 奔奔 | 处女座 | B |
| gangge | 白羊座 | A |
| taoge | 射手座 | A |
+------------------+----------------+----------------+

改成以下格式
射手座,A 老娜|taoge
白羊座,A gangge
处女座,B 奔奔|慧慧

select
concat(xz,',',xx),
concat_ws('|',collect_list(name))
from
tb_teacher
group by
xz,xx;

3.列转行

split(name,",") ---切割为数组，第二个参数是以什么来切割

expolde(a) ---将括号里的炸裂开

lateral view ---侧窗口函数用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

例如能把explode炸裂出来的数据搞成多行数据

原格式
《八佰》   战争
《八佰》   动作
《八佰》   抗日
《八佰》   剧情
《姜子牙》   动画
《姜子牙》   神话
《姜子牙》   科幻
《姜子牙》   动作
《姜子牙》   伦理
《战狼2》   战争
《战狼2》   动作
《战狼2》   灾难

改后格式
+--------+-----------------------------+
| name | _c1 |
+--------+-----------------------------+
| 《八佰》 | ["战争","动作","抗日","剧情"] |
| 《姜子牙》 | ["动画","神话","科幻","动作","伦理"] |
| 《战狼2》 | ["战争","动作","灾难"] |
+--------+-----------------------------+

select
name,
t
from
tb_movie2
lateral view
explode(split(categorys,',')) tp as t;


----tp 虚拟表名
----t  虚拟表名里的字段名

4.窗口函数（over（））

在进行分组聚合以后，我们还想操作集合以前的数据使用到窗口函数
over() 窗口函数指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

** over() ---窗口大小为整张表
** over(partition by name ) ---- 指定窗口大小为name来分

current row：当前行 current row
n preceding：往前n行数据 n preceding
n following：往后n行数据 n following
unbounded：起点，unbounded preceding 表示从前面的起点， unbounded following表示到后面的终点 unbounded preceding unbounded following
lag(col,n)：往前第n行数据 lag 参数一字段 n
lead(col,n)：往后第n行数据 lead
ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。 ntile(5)

例子：

+-----------------+------------------+------------------+
| tb_orders.name | tb_orders.ctime | tb_orders.money |
+-----------------+------------------+------------------+
| jack | 2020-01-01 | 10 |
| tony | 2020-01-02 | 15 |
| jack | 2020-02-03 | 23 |
| tony | 2020-01-04 | 29 |
| jack | 2020-01-05 | 46 |
| jack | 2020-04-06 | 42 |
| tony | 2020-01-07 | 50 |
| jack | 2020-01-08 | 55 |
| mart | 2020-04-08 | 62 |
| mart | 2020-04-09 | 68 |
| neil | 2020-05-10 | 12 |
| mart | 2020-04-11 | 75 |
| neil | 2020-06-12 | 80 |
| mart | 2020-04-13 | 94 |
+-----------------+------------------+------------------+

-- 求每个人的订单明细和总量
select 
*,
count(1) over(partition by name) as ct,
sum(money) over(partition by name) as moneys
from
tb_orders

---money相加为  默认为起始行和当前行
select
*,
sum(money) over(partition by name order by ctime)
from
tb_orders;

------上一行和当前行
select
*,
sum(money) over(partition by name order by ctime rows between 1 preceding and current row)
from
tb_orders;

---当前行和上一行和下一行
select
*,
sum(money) over(partition by name order by ctime rows between 1 preceding and 1 following)
from
tb_orders;

select
* ,
lag(ctime,1 , '人家是第一次购买')  over(partition by name   order by ctime) 
from
tb_orders  ;

5.编号函数（后可跟窗口函数）

rank() 排序相同时会重复，总数不会变

dense_rank() 排序相同时会重复，总数会减少

row_number() 会根据顺序计算,排序相同时不会重复

数据

name login_date
a,2020-11-01
a,2020-11-02
a,2020-11-03
a,2020-11-04
a,2020-11-05
a,2020-11-06
a,2020-11-07
b,2020-11-08
b,2020-11-01
b,2020-11-02
b,2020-11-04
b,2020-11-05
b,2020-11-06

求连续登陆n天的用户
select
name,
val,
count(1)  cnt
from
(select
t1.*,
date_sub(t1.login_data,t1.dn) as val   ----参数一：日期  参数二：天数   参数一减去参数二
from
(select
*,
rank() over(partition by name order by login_data) dn
from
tb_account) t1) t2
group by
name,val
having
cnt>2
;

6.json解析函数：表生成函数

实现步骤

数据
benben,fengjie_furong,xiaoben:18_daben:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

创建原始表
drop table tb_user;
create table tb_user(
name string,
friends array<string>,   ----array集合
children map<string,int>,
address struct<street:string,city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
load data local inpath '/data/mess/' into table tb_user;

+---------------+-----------------------+--------------------------------------+----------------------------------------------+
| tb_user.name  |    tb_user.friends    |           tb_user.children           |               tb_user.address                |
+---------------+-----------------------+--------------------------------------+----------------------------------------------+
| benben        | ["fengjie","furong"]  | {"xiaoben":18,"daben":19}            | {"street":"hui long guan","city":"beijing"}  |
| yangyang      | ["caicai","susu"]     | {"xiao yang":18,"xiaoxiao yang":19}  | {"street":"chao yang","city":"beijing"}      |
+---------------+-----------------------+--------------------------------------+----------------------------------------------+

---数组取值
arr[index]
eg:select friends[0] from tb_user;

---求长度
size(a)   a为任意数，array和map...都行
eg:select size(children) from tb_user;
eg:select friends[if(2>size(friends),0,2)] from 

---map集合
map_keys(map) 根据提供的map获取其所有的key
eg:select map_keys(children) from tb_user;

map_values(map)  根据map集合获取所有的值
eg: select map_values(children) from tb_user;

---staruct  结构体获取属性
通过struct.属性名   来获取属性值
eg: select struct.city from tb_user;

7.自定义函数

根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore() split()

自定义UDF函数的方法

在java中自定义函数上传到hdfs中，然后在hive上执行
1.创建一个meven工程
2.导入依赖

<dependencies>

       <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->

       <dependency>

           <groupId>org.apache.hive</groupId>

           <artifactId>hive-exec</artifactId>

           <version>1.2.1</version>

       </dependency>

</dependencies>

3.创建一个类

public class Lower extends UDF {

	public String evaluate (final String s) {
	
		if (s == null) {
			return null;
		}
		return s.toLowerCase();
	}
}

4.打包上传HDFS中

5.在hive上创建函数

create function sayHello as 'com._51doit.functions.MyFunction'  using jar 'hdfs://linux01:8020/func.jar' ;


sayhello ----自己起的函数名
hdfs://linux01:8020/func.jar   ---运行的jar包

姚家金吼

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
hive07: hive中sql常用函数

1.case..when数据求出不同部门男女各多少人王奔 A 男娜娜 A 男宋宋 B 男凤姐 A 女热巴 B 女慧慧 B 女建表并导入数据create table tb_emp(name string,dname string,gender string)row format delimited fields terminated by '\t';load data local inp...
复制链接

扫一扫

专栏目录