小菜鸟
0%

函数

发表于 更新于 分类于 阅读次数： Valine：

函数

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值也会按照顺序进行排序，完全不会重复