HiveQL/SparQL 常用函数

一、窗口函数

1.1 基础结构

窗口函数 = 分析函数 + over函数

分析函数：sum ( )、max ( )、row_number ( ) 等等

over函数：over (partition by user_id order by date desc/asc)

按照user_id字段分区，每个分区内根据date字段排序，asc：升序（默认升序） desc：降序

1.2 row_number/rank/dense_rank：排序函数

a）row_number ( ) over ( )：依次排序且不会并列重复

b）rank ( ) over ( )：排序相等会在名次中留下空位

c）dense_rank ( ) over ( )：排序相等不会在名次中留下空位

xxxxxxxxxx
10
1
SELECT
2
    user_id,
3
    --依次排序且不会并列重复
4
    row_number() over(partition by user_id order by date) as row_number,
5
    --排序相等会在名次中留下空位
6
    rank() over(partition by user_id order by date) as rank,
7
    --排序相等不会在名次中留下空位
8
    dense_rank() over(partition by user_id order by date) as dense_rank
9
FROM
10
    app_launch

1.3 sum/avg/max/min：聚合函数

xxxxxxxxxx
8
1
SELECT
2
    user_id,
3
    sum(salary) over(partition by user_id order by date) as sum,--起点到当前行总和
4
    avg(salary) over(partition by user_id order by date) as avg,--起点到当前行平均值
5
    max(salary) over(partition by user_id order by date) as max,--起点到当前行最大值
6
    min(salary) over(partition by user_id order by date) as min --起点到当前行最小值
7
FROM
8
    app_launch

xxxxxxxxxx
1
1
SELECT
2
    user_id,
3
    sum(salary) over(partition by user_id) as sum, --分组内所有行的总和
4
    avg(salary) over(partition by user_id) as avg, --分组内所有行的平均值
5
    max(salary) over(partition by user_id) as max, --分组内所有行的最大值
6
    min(salary) over(partition by user_id) as min  --分组内所有行的最小值
7
FROM
8
    app_launch

1.4 lag/lead/first_value/last_value：取上下行函数

• lag (col, n, default) over ( )：用于统计窗口内往上第n行值

• lead (col, n, default) over ( )：用于统计窗口内往下第n行值

  • 第1个参数：列名
  • 第2个参数：往上/往下第n行（默认为1）
  • 第3个参数：当往上/往下第n行为NULL时候，取默认值，不写为NULL

• first_value (col) over ( )：取分组内排序后，截止到当前行，第一个值

xxxxxxxxxx
1
15
1
1
SELECT
2
    user_id,
3
    lag(date, 1, "") over(partition by user_id order by date) as lag,
4
    lead(date, 1) over(partition by user_id order by date) as lead,
5
    first_value(date) over(partition by user_id order by date) as first_value,
6
    first_value(date) over(partition by user_id order by date desc) as last_value
7
FROM
8
    app_launch

• last_value (col) over ( )：取分组内排序后，截止到当前行，最后一个值

xxxxxxxxxx
1
1
SELECT
2
    user_id,
3
    last_value(date) over(partition by user_id order by date) as last_value,
4
    last_value(date) over(partition by user_id order by date desc) as last_value2
5
FROM
6
    app_launch

1.5 ntile：分析函数，用于百分点、n分片等

NTILE()把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

NTILE()函数将排序分区中的行划分为特定数量的组。从每个组分配一个从一开始的桶号。对于每一行，NTILE()函数返回一个桶号，表示行所属的组。

• n是一个字面正整数。桶号的范围是1到n。
• 在PARTITION BY从返回的结果集划分FROM子句为分区到的NTILE()函数被应用。
• ORDER BY子句指定将NTILE()值分配给分区中的行的顺序。

xxxxxxxxxx
4
1
NTILE(n) OVER (
2
    PARTITION BY <expression>[{,<expression>...}]
3
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
4
) 

然后可以根据桶号，选取前或后n分之几的数据。

用户收入表：user_salary

xxxxxxxxxx
1
1
SELECT
2
    user_id,
3
    salary,
4
    ntile(2) over(partition by user_id order by salary) as rn
5
FROM
6
    user_salary

xxxxxxxxxx
13
1
-- 收入前50%用户的平均收入
2
SELECT
3
    avg(salary) as avg_salary
4
FROM
5
(
6
    SELECT
7
        user_id,
8
        salary,
9
        ntile(2) over(partition by user_id order by salary) as rn
10
    FROM
11
        user_salary
12
) a
13
where rn = 1

1.6 count over

xxxxxxxxxx
1
1
SELECT
2
    *,
3
    count(1) over(partition by department) as cnt1,
4
    count(1) over(order by department) as cnt2,
5
    count(1) over(partition by department order by name) as cnt3
6
FROM
7
    dm_homed.test_count_over

二、数组类型的函数

2.1 array：生成数组格式

array (val1, val2, val3, …)：将多个数据生成数组格式

array类型的数据可以通过'数组名[index]'的方式访问，index从0开始:

xxxxxxxxxx
5
1
SELECT ARRAY(0,1,2);
2
returns [0,1,2]
3

4
SELECT ARRAY(0,1,2)[1]
5
returns 1

2.2 collect_list/collect_set：分组中某列转为数组返回

将分组中某列转为一个数组返回，不同的是collect_list不去重而collect_set去重

collect_set的参数必须是string类型

x
1
SELECT
2
    user_id,
3
    concat_ws(',', collect_list/collect_set(cast(date as string))) as date
4
FROM
5
    app_launch
6
GROUP BY 
7
    user_id

2.3 array_contains：判断数组是否包含某个元素

判断数组是否包含某个元素，返回true/false

xxxxxxxxxx
2
1
SELECT array_contains(ARRAY(0,1), 0);
2
returns true

2.4 split：字符串切分为数组

将字符串切分为数组

xxxxxxxxxx
2
1
SELECT split('装修避坑,选材经验,设计案例', ',');
2
returns ["装修避坑", "选材经验", "设计案例"]

三、正则表达式函数

3.1 like：判断是否匹配

主要用在where子句中，用来筛选内容，%是通配符，返回true/false

xxxxxxxxxx
4
1
SELECT "news_home/electric, news_home" like '%news_home%'
2
return true
3
SELECT "news_house/electric" like '%news_home%'
4
return false

3.2 regexp/rlike：正则判断是否

主要用在where子句中，用来筛选内容，返回true/false

xxxxxxxxxx
24
1
category = "news_home/electric, news_home"
2
category1 = "news_home, news_home/electric"
3
SELECT category regexp 'news_home$'
4
return true
5
SELECT category1 regexp 'news_home$'
6
return false
7

8
-- 判断是否为纯数字  "^[0-9]+$"
9
SELECT "123123123" RLIKE "^[0-9]+$"
10
return true
11
SELECT "123123123a" RLIKE "^[0-9]+$"
12
return false
13

14
-- 判断是否为纯字母   "^[A-Za-z]+$"
15
SELECT "sdkfjsdfa" RLIKE "^[A-Za-z]+$"
16
return true
17
SELECT "sdkfjsdfa1231231" RLIKE "^[0-9]+$"
18
return false--判断是否为纯字母   "^[A-Za-z]+$"
19

20
-- 判断是否为有中文   "[\u4E00-\u9FFF]+"
21
SELECT 'news_house我们electric' RLIKE '[\u4E00-\u9FFF]+'
22
return true
23
SELECT 'news_house/electric' RLIKE '[\u4E00-\u9FFF]+'
24
return false

3.3 regexp_extract：正则匹配

用来从内容中匹配指定样式的内容，缺点是不能提取满足条件的所有内容

regexp_extract ( date, pattern, index )

• 第1个参数：待匹配字符串

• 第2个参数：匹配规则

• 第3个参数：返回正则完成的第几个结果

  • 0：返回正则匹配的整个结果
  • 1：返回正则表达式中匹配的第一个括号中的的结果，以此类推

xxxxxxxxxx
1
23
1
data = "news_home/electric, news_home, news_home/electric, news_home"
2

3
SELECT regexp_extract(data, '(news_home(?!/))', 0);
4
-- (?!/)表示后一位没有'/'
5
return news_home
6
SELECT regexp_extract(data, '.*(news_home),||.*,(news_home)$');
7
-- 匹配在中间和在末尾的news_home
8
return news_home
9

10
SELECT regexp_extract('id=1,num=1,id=2,num=2', 'id=(.*?),.*id=(.*?),.*', 1)
11
1
12
SELECT regexp_extract('id=1,num=1,id=2,num=2', 'id=(.*?),.*id=(.*?),.*', 2)
13
2
14
SELECT regexp_extract('id=1,num=1', 'id=(.*?),.*id=(.*?),.*', 2) --匹配不上为空
15
""
16

17
--匹配满足条件的
18
SELECT regexp_extract('insert overwrite table ecom.dim_shop_df partition', '.*overwrite table (.*?) partition.*', 1)
19
ecom.dim_shop_df
20

21
--或的使用
22
SELECT regexp_extract('insert overwrite table ecom. dim_shop_df partition', '.*(overwrite table|OVERWRITE TABLE).*?\\.(.*?) (partition|PARTITION).*', 2)
23
ecom.dim_shop_df

3.4 regexp_replace：正则代替

用在select 子句中，用来将目标内容的特定部分替换成指定的内容

xxxxxxxxxx
9
1
data = "装修避坑: 0.358001,选材经验: 0.828523,设计案例: 0.329146"
2

3
SELECT regexp_replace(data, ':(.*?),', ',');
4
-- 替换": 0.828523,"为","
5
return "装修避坑,选材经验,设计案例"
6

7
SELECT regexp_replace([1645357130369064,1645005576984579], '\\[|\\]', '');
8
-- 替换[或]为"",[前需要加两个斜杠
9
return "1645357130369064,1645005576984579"

3.5 常用匹配

xxxxxxxxxx
1
15
1
-- 匹配第一个出现的数字结果
2
select regexp_extract('耐克123/Nike','([0-9]+)',1)
3
123
4

5
-- 匹配第一个出现的字母结果
6
select regexp_extract('abc耐克/Nike','([a-zA-Z]+)',1)
7
abc
8

9
-- 匹配第一个出现的中文结果
10
select regexp_extract('耐克/Nike','([\u4E00-\u9FFF]+)', 1)
11
耐克
12

13
-- 匹配所有的字母：将非字母的替换为空
14
select REGEXP_REPLACE('abc耐克/Nike','([^a-z]+)','')
15
abcike

四、字符串函数

4.1 concat：字符串拼接

x
1
SELECT concat("welcome", " ", "to", " ", "beijing");
2
return "welcome to beijing"

4.2 concat_ws：列表格式拼接

xxxxxxxxxx
1
1
data_list = ["装修避坑", "选材经验", "设计案例"]
2
SELECT concat_ws(',' , data_list);
3
return "装修避坑,选材经验,设计案例"
4

5
SELECT concat_ws(',' , "装修避坑", "选材经验", "设计案例");
6
return "装修避坑,选材经验,设计案例"

4.3 instr：字符串查找

instr(str1, str2, start_index, nth_appearance)：返回查找到的位置，未找到返回为0

• str1：源字符串，要在此字符串中查找
• str2：要在str1中查找的字符串
• start_index：代表从str1的哪个位置开始查找，默认为1。此参数为正，从左到右开始检索，如果此参数为负，从右到左检索
• nth_appearance：查找第几次出现的str2，默认为1

xxxxxxxxxx
3
1
SELECT instr('syranmo','syra')       -- 返回 1
2
SELECT instr('syranmo','ranm')       -- 返回 3
3
SELECT instr('syranmo','abcd')       -- 返回 0

4.4 substr：字符串截取

substr(string str1, int start, int len)：返回字符串str1从start位置开始，长度为len的字符串；len不传，默认从start位置到结尾的字符串

• str1：要截取的字符串
• start：开始的位置
• len：截取的长度

xxxxxxxxxx
8
1
SELECT substr('12345',3) 
2
345
3

4
SELECT substr('12345',1,4) 
5
1234
6

7
SELECT substr('12345',-2,2) 
8
45

五、时间处理函数

5.1 from_unixtime/unix_timestamp：时间戳转换

from_unixtime(ts, date_fomat)：时间戳转换为日期格式

unix_timestamp(date_fomat, ts)：日期格式转换为时间戳

xxxxxxxxxx
13
1
--from_unixtime
2
SELECT from_unixtime(1562160194, 'yyyyMMdd HH:mm:ss');
3
20190703 21:23:14
4

5
SELECT from_unixtime(1562160194, 'yyyy-MM-dd');
6
2019-07-03
7

8
SELECT from_unixtime(1562160194, 'yyyyMMdd');
9
20190703
10

11
--unix_timestamp
12
SELECT unix_timestamp('2019-07-03 21:23:14', 'yyyy-MM-dd HH:mm:ss');
13
1562160194

5.2 pdate2date/date2pdate：日期格式转换

xxxxxxxxxx
5
1
SELECT pdate2date('2020-07-23');
2
20200723
3

4
SELECT date2pdate('20200723');
5
2020-07-23

5.3 date_sub/add_months：返回前n天/月的日期

date_sub ('${DATE}', n)：返回DATE的前n天的日期，n：正数是减，负数是加

add_months('${DATE}', n)：返回DATE的后n月的日期，n：正数是加，负数是减

xxxxxxxxxx
13
1
--date_sub
2
select date_sub('2020-07-09', 4);
3
2020-07-05
4

5
select date_sub('2020-07-09', -4);
6
2020-07-13
7

8
--add_months
9
select add_months('2020-07-09', -1)
10
2020-06-09
11

12
select add_months('2020-03-31', -1)
13
2020-02-29

5.4 datediff：计算日期差

datediff (end_date, start_date) 求两个指定日期的差，end_date - start_date

end_date/start_date的日期格式必须是'yyyy-MM-dd'格式

1
SELECT datediff('2019-07-23', '2019-07-01');
2
22

5.5 trunc：返回当月第一天/返回双月第一天

trunc('${DATE}', 'MM')

xxxxxxxxxx
15
1
返回当月第一天
2
select trunc('2020-07-09', 'MM')
3
2020-07-01
4

5
返回上个月第一天
6
select trunc(add_months('2020-07-09', -1), 'MM')
7
2020-06-01
8

9
返回上个双月第一天
10
select trunc(if(month('${DATE}')%2 = 0, add_months('${DATE}', -3), add_months('${DATE}', -2)), 'MM'))
11
2022-01-01
12

13
返回上个双月最后一天
14
select date_sub(trunc(if(month('${DATE}')%2 = 0, add_months('${DATE}', -1), add_months('${DATE}', 0)), 'MM'), 1))
15
2022-02-28

六、行转列

6.1 explode：单字段行转列

explode(array)：将一行数组转成多列，只支持单个字段的行转列

xxxxxxxxxx
1
1
SELECT explode(array(0,1,2)) as num

6.2 lateral view explode：行转列

lateral view explode(array(0,1,2))：将某列字段切分成多列，其他字段均不变

xxxxxxxxxx
1
1
SELECT
2
    user_id,
3
    vid2
4
FROM
5
    dm_homed.ods_server_impressions_log_hourly 
6
    lateral view explode(split(version_id, ',')) vid_table as vid2
7
WHERE
8
    date = '${date}'

6.3 lateral view posexplode：行转列，带顺序

场景1：行转列，并带上每个元素在数组中的顺序

1
WITH prod_table AS (
2
    SELECT
3
        '111' as rule_id,
4
        '1203,1202,1201,1204' as prod_list
5
)
6

7
SELECT
8
    rule_id,
9
    product_id,
10
    rnk
11
FROM
12
    prod_table
13
    lateral view posexplode(split(prod_list, ',')) p as rnk, product_id

场景2：两行同时行转列，并按数组中相同的顺序放置为1行

1
WITH prod_table AS (
2
    SELECT
3
        '111' as rule_id,
4
        '1203,1202,1201,1204' as prod_list,
5
        '300,200,100,400' as gmv_list
6
)
7

8
SELECT
9
    rule_id,
10
    product_id,
11
    gmv
12
FROM
13
    prod_table
14
    lateral view posexplode(split(prod_list, ',')) p as rnk1, product_id
15
    lateral view posexplode(split(gmv_list, ',')) p as rnk2, gmv
16
WHERE
17
    rnk1 = rnk2

七、分位数函数

7.1 percentile & percentile_approx

percentile(col, pth)：求准确的第pth个百分位数

percentile_approx(col, pth, acc)：求近似的第pth个百分位数，acc默认10000（万分之一）

xxxxxxxxxx
5
1
--score精确的25分位数
2
SELECT percentile(score, 0.25);
3

4
--score近似的25分位数，精度默认10000（万分之一），在数据量较大的情况下近似计算更快
5
SELECT percentile_approx(score, 0.25, 10000);

八、json函数

8.1 get_json_object：获取json对象的value

get_json_object (json_str, '$.key1')

• json_str：填写json对象变量

• key1：填写要获取json的key值

xxxxxxxxxx
5
1
SELECT get_json_object('{"sku_id":712557421,"stock_num":100}', '$.sku_id');
2
# 712557421
3

4
SELECT get_json_object('{"sku_id":712557421,"stock_num":100}', '$.stock_num');
5
# 100

8.2 json_tuple：一次获取多个对象的value

json_tuple(json_str, key1, key2, ...)

• json_str：填写json对象变量

• key1：填写要获取json的key值

• key2：填写要获取json的key值

xxxxxxxxxx
9
1
--json
2
SELECT
3
    sku_id,
4
    stock_num
5
FROM
6
(
7
    SELECT '{"sku_id":7125,"stock_num":0}' as sku
8
) a
9
lateral view json_tuple(sku, 'sku_id', 'stock_num') a as sku_id, stock_num

xxxxxxxxxx
1
19
1
--解析数组格式的json
2
SELECT
3
    sku_id,
4
    stock_num
5
FROM
6
(
7
    SELECT 
8
        regexp_replace(
9
            regexp_replace(sku, '\\[|\\]', ''),   --替换括号 
10
            '\\},\\{',  '\\}###\\{'               --替换逗号，为了切分准备
11
        ) as sku_list
12
    FROM
13
    (
14
        SELECT
15
            '[{"sku_id":7125,"stock_num":0},{"sku_id":7126,"stock_num":10}]' as sku
16
    ) a
17
) a
18
lateral view explode(split(sku_list, '###')) sku_infos as sku   --通过###切分
19
lateral view json_tuple(sku, 'sku_id', 'stock_num') a as sku_id, stock_num

九、其他

9.1 greatest：多列最大值

greatest (col1, col2, ...)：求多列的最大值

xxxxxxxxxx
2
1
SELECT greatest(1, 2, 3, 4);
2
# 4

9.2 rand：随机函数

• rand ()：返回一个0到1范围内的随机数，返回值: double

• rand(int seed)：如果指定种子seed，则会等到一个稳定的随机数序列

xxxxxxxxxx
11
1
SELECT rand();
2
0.590256460382232
3

4
SELECT rand();
5
0.699686875752123
6

7
SELECT rand(100);
8
0.6841403791584381
9

10
SELECT rand(100);
11
0.6841403791584381

9.3 max_pt：读取表中最大分区

max_pt('库名.表名', '分区名', '分区格式')：返回表中最大分区

• 优势：函数会读取hive表最大分区，重跑时可有效避免空分区

• 劣势：数据回溯时，不能保证取当时单天的，取的最新数据

• 注意：分区名需为string格式，不能为bigint，所以尽量把分区格式设成 string

• 使用场景：

  • 分区数据不会缺失的表，建议还是用date = '${date}'
  • 对于分区不全的表，可能在某些时间段没有分区的，为了避免数据为空，建议用max_pt

xxxxxxxxxx
8
1
show partitions dm_temai.repeat_product_records_dict;
2
date=20200712
3
date=20200713
4
date=20200714
5
date=20200715
6

7
SELECT max_pt('dm_temai.repeat_product_records_dict', 'date', 'yyyyMMdd');
8
20200715

9.4 coalesce/nvl：返回第一个非空值

• coalesce(v1, v2, …)：返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

• nvl(v1, v2)：返回两个参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

xxxxxxxxxx
1
11
1
SELECT coalesce(NULL, '1', '2')
2
1
3

4
SELECT coalesce(NULL, NULL, NULL)
5
NULL
6

7
SELECT nvl(1, NULL)
8
1
9

10
SELECT nvl(NULL, 1)
11
1

十、参考

Hive on Spark解析

sum/avg/max/min解析

lag/lead/first_value/last_value解析

ntile分析函数

Hive函数大全