0%

函数

函数

hive中有很多内置函数可以使用

查看内置函数

1
show functions;

查看函数的用法及作用

1
desc function floor_day;

查看函数的详细用法

1
desc function extended floor_day;

函数分类

hive中函数分为三类

  • UDF 一进一出
  • UDAF 多进一出
  • UDTF 一进多出

多是指多行

常用函数

nvl空字段赋值

1
2
3
4
5
# 用来给某个字段赋默认值
nvl(value,default_value) - Returns default value if value is null else returns value
Example:
> SELECT nvl(null,'bla') FROM src LIMIT 1;
bla

concat字符拼接

concat

1
2
3
4
5
6
7
8
# 返回字符串拼接之后的结果,参数为多个字符串
concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data bin1, bin2, ... binN
Returns NULL if any argument is NULL.
示例:
hive (study_hive)> select concat('1','aa');
OK
_c0
1aa

concat_ws

1
2
3
4
5
6
7
# 第一个参数为分隔符,剩余参数可以使字符串也可以是数组
concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.
示例:
hive (study_hive)> select concat_ws(',','aa','bb');
OK
_c0
aa,bb

collect_set汇总

1
2
3
4
5
6
7
# 将数据汇总后转为数组输出
collect_set(x) - Returns a set of objects with duplicate elements eliminated
示例:
hive (study_hive)> select collect_set('aa');
OK
_c0
["aa"]

split切割

1
2
3
4
5
6
7
# 根据表达式进行切割字符串
split(str, regex) - Splits str around occurances that match regex
示例:
hive (study_hive)> select split('a,b,c',',');
OK
_c0
["a","b","c"]

explode拆分

1
2
3
4
5
6
7
8
9
# 将数组或者map结构的数据拆分为多行
explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns
示例:
hive (study_hive)> select explode(split('a,b,c',','));
OK
col
a
b
c

lateral view(侧视图)可以配合explode来进行使用,将单行数据拆解成多行的数据集

1
2
3
-- 这里的category存储的是视频的分类数组 
-- lateral view explode(category) temp就相当于一个虚拟表,之后会与原表video的该行数据进行笛卡尔积操作
select category_name from video lateral view explode(category) temp as category_name;

over窗口函数

指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化

以一个小数据为例,该表中只有四条数据

1
2
3
4
5
6
7
hive (study_hive)> select * from test_partitioned;
OK
test_partitioned.id test_partitioned.money test_partitioned.date
1 20.0 20210413
2 50.0 20210413
3 80.0 20210414
6 80.0 20210415

在使用窗口函数时,如果窗口函数中没有加任何任何条件时,是将查询出来的整个数据集作为窗口,此时这个sum函数是根据这个窗口所传进来的数据进行计算的,即20+50=70

1
2
3
4
5
hive (study_hive)> select id,sum(money) over() from test_partitioned where `date` = '20210413';
OK
id sum_window_0
2 70.0
1 70.0

在over中可以对数据窗口进行限制,如使用partition by 根据字段的值进行限制,sum所对应的也是按照该数据集中的id进行分组然后在进行求和

1
2
3
4
5
6
7
hive (study_hive)> select id,sum(money) over(partition by `date`) from test_partitioned;
OK
id sum_window_0
3 80.0
6 80.0
2 70.0
1 70.0

高级用法

over中的可用窗口函数,在使用order by时使用

如果order by之后没有使用特殊的窗口函数,默认则是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(从开始到当前行)

如果没有使用order by也没有使用特殊的窗口函数,默认则是ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING(从头到尾)

1
2
3
4
5
6
CURRENT ROW:当前行
n PRECEDING:往前 n 行数据
n FOLLOWING:往后 n 行数据
UNBOUNDED:起点,
UNBOUNDED PRECEDING 表示从前面的起点,
UNBOUNDED FOLLOWING 表示到后面的终点

语法

1
2
3
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

示例:

1
2
-- 按照c分组 按照d排序 取前三行和后三行的平均值
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM T;

over前的窗口函数

1
2
3
LAG(col,n,default_val):往前第 n 行数据
LEAD(col,n, default_val):往后第 n 行数据
NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从 1 开始,对于每一行,NTILE 返回此行所属的组的编号。注意:n 必须为 int 类型。

示例:

1
SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C)FROM T;
rank函数

rank有三个函数

  • RANK() 排序相同时会重复,总数不会变
  • DENSE_RANK() 排序相同时会重复,总数会减少
  • ROW_NUMBER() 会根据顺序计算

全部数据

1
2
3
4
5
6
test_partitioned.id    test_partitioned.money    test_partitioned.date
1 20.0 20210413
2 50.0 20210413
3 80.0 20210414
4 200.0 20210414
6 80.0 20210415

分别进行测试验证

RANK()

1
2
3
4
5
6
7
8
select *,rank() over(order by money) from test_partitioned;

test_partitioned.id test_partitioned.money test_partitioned.date rank_window_0
1 20.0 20210413 1
2 50.0 20210413 2
6 80.0 20210415 3
3 80.0 20210414 3
4 200.0 20210414 5

使用money进行排序,money相同的rank值是一样的,排序相同时会重复,但是总数不会变,3 3 5

DENSE_RANK()

1
2
3
4
5
6
7
8
select *,dense_rank() over(order by money) from test_partitioned;

test_partitioned.id test_partitioned.money test_partitioned.date dense_rank_window_0
1 20.0 20210413 1
2 50.0 20210413 2
6 80.0 20210415 3
3 80.0 20210414 3
4 200.0 20210414 4

使用money进行排序,money相同的rank值是一样的,排序相同时会重复,但是总数会减少,3 3 4

ROW_NUMBER()

1
2
3
4
5
6
7
8
select *,row_number() over(order by money) from test_partitioned;

test_partitioned.id test_partitioned.money test_partitioned.date row_number_window_0
1 20.0 20210413 1
2 50.0 20210413 2
6 80.0 20210415 3
3 80.0 20210414 4
4 200.0 20210414 5

使用money进行排序,money相同的rank值也会按照顺序进行排序,完全不会重复