[大数据] 关于hive内置函数的使用

hive> select ename,nvl(deptno,'空') from adx.emp_test ;
OK
SMITH   20
ALLEN   300
WARD    500
JONES   20
MARTIN  1400
BLAKE   30
CLARK   10
SCOTT   20
KING    空
TURNER  0
ADAMS   20
JAMES   30
FORD    20
MILLER  10
ZHANGS  空
ZHANGS  空
ZHANGS  空
Time taken: 12.961 seconds, Fetched: 17 row(s)

2.like and?rlike?

? 2.1%匹配符与like的灵活使用

1) job字段中以“店”字为中心前后匹配多个字符，得到所有满足条件的数据

hive> select * from adx.emp_test where job like '%店%';
     
OK
7369    SMITH   店员    7902    1990-12-17      1800.0  20
7876    ADAMS   店员    7788    1997-5-23       2100.0  20
7900    JAMES   店员    7698    1991-12-3       1950.0  30
7934    MILLER  店员    7782    1992-1-23       2300.0  10
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL
7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL
Time taken: 11.876 seconds, Fetched: 7 row(s)

2) 匹配出job字段中以“店”字开头的所有数据

hive> select * from adx.emp_test where job like '店%';

OK
7369    SMITH   店员    7902    1990-12-17      1800.0  20
7876    ADAMS   店员    7788    1997-5-23       2100.0  20
7900    JAMES   店员    7698    1991-12-3       1950.0  30
7934    MILLER  店员    7782    1992-1-23       2300.0  10
Time taken: 13.289 seconds, Fetched: 4 row(s)

3) 匹配出job字段中以“员”字结尾的所有数据

hive> select * from adx.emp_test where job like '%员';

OK
7369    SMITH   店员    7902    1990-12-17      1800.0  20
7499    ALLEN   售货员  7698    1991-2-20       2600.0  300
7521    WARD    售货员  7698    1991-2-22       2250.0  500
7654    MARTIN  售货员  7698    1991-9-28       2250.0  1400
7844    TURNER  售货员  7698    1991-9-8        2500.0  0
7876    ADAMS   店员    7788    1997-5-23       2100.0  20
7900    JAMES   店员    7698    1991-12-3       1950.0  30
7934    MILLER  店员    7782    1992-1-23       2300.0  10
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL
7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL
Time taken: 14.176 seconds, Fetched: 11 row(s)

? 2.2_ 和like 的灵活使用

1) job字段数据中匹配出以“店”字开头的所有数据，_ 表示向后匹配一个字符，结果如下

hive> select * from adx.emp_test where job like '店_';

OK
7369    SMITH   店员    7902    1990-12-17      1800.0  20
7876    ADAMS   店员    7788    1997-5-23       2100.0  20
7900    JAMES   店员    7698    1991-12-3       1950.0  30
7934    MILLER  店员    7782    1992-1-23       2300.0  10
Time taken: 14.076 seconds, Fetched: 4 row(s)

2) 匹配出job字段数据中以“店”为中心，前后各匹配出一个字符

select * from adx.emp_test where job like '_店_';

OK
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL
7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL
Time taken: 13.015 seconds, Fetched: 3 row(s)

3) 匹配出job字段中以“员”结尾 向前匹配出一个字段

hive> select * from adx.emp_test where job like '_员';

OK
7369    SMITH   店员    7902    1990-12-17      1800.0  20
7876    ADAMS   店员    7788    1997-5-23       2100.0  20
7900    JAMES   店员    7698    1991-12-3       1950.0  30
7934    MILLER  店员    7782    1992-1-23       2300.0  10
Time taken: 13.711 seconds, Fetched: 4 row(s)

4) 匹配出job字段中以员结尾，向前匹配出两个字段(在员字前加两个_即可，匹配几个加几个_)

hive> select * from adx.emp_test where job like '__员';

OK
7499    ALLEN   售货员  7698    1991-2-20       2600.0  300
7521    WARD    售货员  7698    1991-2-22       2250.0  500
7654    MARTIN  售货员  7698    1991-9-28       2250.0  1400
7844    TURNER  售货员  7698    1991-9-8        2500.0  0
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL
7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL
Time taken: 12.94 seconds, Fetched: 7 row(s)

?2.3_ 、like 、% 三者联合使用

1) 向前匹配出多个字符，向后匹配一个字符

hive> select * from adx.emp_test where job like '%店_';

OK
7369    SMITH   店员    7902    1990-12-17      1800.0  20
7876    ADAMS   店员    7788    1997-5-23       2100.0  20
7900    JAMES   店员    7698    1991-12-3       1950.0  30
7934    MILLER  店员    7782    1992-1-23       2300.0  10
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL
7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL

Time taken: 14.748 seconds, Fetched: 7 row(s)

2) 向后匹配多个字符，向前匹配一个字符

hive> select * from adx.emp_test where job like '_店%';

OK
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL
7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL
Time taken: 12.854 seconds, Fetched: 3 row(s)

? ?2.4 not A like B? 和? ?A not like B

1) A not like B

hive> select 'abcd' not like 'abc';
OK
true


2) not A like B

hive> select not 'abcd' like 'abc';
OK
true

? ?2.5 rlike(可使用java中所有正则表达式)?Java 正则表达式 | 菜鸟教程

1) ^\\d+(\\.\\d+)?  (具体使用可以参考上面链接灵活运用)


hive> select '1.38' rlike '^\\d+(\\.\\d+)?';
OK
true
Time taken: 0.115 seconds, Fetched: 1 row(s)
hive> select '13800000' rlike '^\\d+(\\.\\d+)?';
OK
true
Time taken: 0.096 seconds, Fetched: 1 row(s)

3.case when

? ? ? ? ? ??

1) 统计店员和售货员的数量

hive> select 
    > sum(case job when '店员' then 1 else 0 end) e1,
    > sum(case job when '售货员' then 1 else 0 end) e2 
    > from adx.emp_test ;


OK
4       4
Time taken: 19.069 seconds, Fetched: 1 row(s)

4.行转列

? 4.1 concat

1)使用concat 对列字段进行连接 concat(字段1,'连接符',字段2,'连接符',字段3......)

hive> select concat(empno,'-',ename) from adx.emp_test;

OK
7369-SMITH
7499-ALLEN
7521-WARD
7566-JONES
7654-MARTIN
7698-BLAKE
7782-CLARK
7788-SCOTT
7839-KING
7844-TURNER
7876-ADAMS
7900-JAMES
7902-FORD
7934-MILLER
7788-ZHANGS
7789-ZHANGS
7790-ZHANGS
Time taken: 16.608 seconds, Fetched: 17 row(s)

? ?4.2 concat_ws

2) CONCAT_WS(separator, str1, str2,...) 第一个参数为分隔符

hive> select concat_ws('-',hiredate,job) from adx.emp_test;

OK
1990-12-17-店员
1991-2-20-售货员
1991-2-22-售货员
1991-4-2-经理
1991-9-28-售货员
1991-5-1-经理
1991-6-9-经理
1997-4-19-分析师
6000.00-总统
1991-9-8-售货员
1997-5-23-店员
1991-12-3-店员
1991-12-3-分析师
1992-1-23-店员
1995-4-20-小店员
1995-4-20-小店员
1995-4-20-小店员
Time taken: 14.592 seconds, Fetched: 17 row(s)

? ?4.3 collect_set()

3) COLLECT_SET(str) 函数主要用户将某一字段去重汇总成一个array

hive> select COLLECT_SET(job) from adx.emp_test;

OK
["店员","售货员","经理","分析师","总统","小店员"]
Time taken: 18.322 seconds, Fetched: 1 row(s)

? 4.4 collect_set()结合concat_ws使用

4) collect_set() 结合concat_ws()结合使用

hive> select concat_ws('_',COLLECT_SET(job)) from adx.emp_test;

OK
店员_售货员_经理_分析师_总统_小店员
Time taken: 18.143 seconds, Fetched: 1 row(s)

5.列转行

? 5.1 explode(col)(能够将hive中的一个array，map等一列拆开成多行数据)

格式：lateral view? udtf(col)? tableAlias? as columnAlias

1)查看电影数据
hive> select move，category from move_info;

《星际穿越》       [科幻,剧情]

《碟中谍4》        [谍战,动作,警匪,剧情]

《小丑回魂》       [恐怖,惊悚]

Time taken: 18.143 seconds, Fetched: 3 row(s)

2)使用lateral view explode(col) 将分类数据变成多行

hive>select move,cate from move_info lateral view explode(category) tableAlias as cate;

《星际穿越》        科幻 

《星际穿越》        剧情

《碟中谍4》         谍战

《碟中谍4》         动作

《碟中谍4》         警匪

《碟中谍4》         剧情

《小丑回魂》        恐怖

《小丑回魂》        惊悚

Time taken: 18.143 seconds, Fetched: 8 row(s)

6.窗口函数

? 6.1 over()指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化

1)结合over() 查询员工的业绩总额以及明细

hive> select *,sum(sal) over() from adx.emp_test;

7790    ZHANGS  小店员  NULL    1995-4-20       4500.0  NULL    NULL    47535.0
7789    ZHANGS  小店员  NULL    1995-4-20       2500.0  NULL    NULL    47535.0
7788    ZHANGS  小店员  NULL    1995-4-20       3500.0  NULL    NULL    47535.0
7934    MILLER  店员    7782    1992-1-23       2300.0  10      NULL    47535.0
7902    FORD    分析师  7566    1991-12-3       4000.0  20      NULL    47535.0
7900    JAMES   店员    7698    1991-12-3       1950.0  30      NULL    47535.0
7876    ADAMS   店员    7788    1997-5-23       2100.0  20      NULL    47535.0
7844    TURNER  售货员  7698    1991-9-8        2500.0  0       30      47535.0
7839    KING    总统    NULL    6000.00 10.0    NULL    NULL    47535.0
7788    SCOTT   分析师  7566    1997-4-19       4000.0  20      NULL    47535.0
7782    CLARK   经理    7839    1991-6-9        3450.0  10      NULL    47535.0
7698    BLAKE   经理    7839    1991-5-1        3850.0  30      NULL    47535.0
7654    MARTIN  售货员  7698    1991-9-28       2250.0  1400    30      47535.0
7566    JONES   经理    7839    1991-4-2        3975.0  20      NULL    47535.0
7521    WARD    售货员  7698    1991-2-22       2250.0  500     30      47535.0
7499    ALLEN   售货员  7698    1991-2-20       2600.0  300     30      47535.0
7369    SMITH   店员    7902    1990-12-17      1800.0  20      NULL    47535.0
Time taken: 20.136 seconds, Fetched: 17 row(s)

? ?6.2?over()? 结合partition by? order by? 、distribute by? sort by? 的使用(两者效果一样，分区排序累加)

1) over(partition by 字段名 order by  字段名)

hive> select job,hiredate,sal,sum(sal) over(partition by job order by hiredate) from adx.emp_test;


OK
分析师  1991-12-3       4000.0  4000.0
分析师  1997-4-19       4000.0  8000.0
售货员  1991-2-20       2600.0  2600.0
售货员  1991-2-22       2250.0  4850.0
售货员  1991-9-28       2250.0  7100.0
小店员  1995-4-20       2500.0  10500.0
小店员  1995-4-20       3500.0  10500.0
小店员  1995-4-20       4500.0  10500.0
经理    1991-4-2        3975.0  3975.0
经理    1991-5-1        3850.0  7825.0
经理    1991-6-9        3450.0  11275.0
Time taken: 19.475 seconds, Fetched: 11 row(s)


2) over(distribute by 字段名 sort by 字段名)分区排序，采用hash算法，在map端将查询结果中hash值相同的结果分发到对应的reduce文件中。

hive> select job,hiredate,sal,sum(sal) over(distribute by job sort by hiredate) from adx.emp_test;

OK
分析师  1991-12-3       4000.0  4000.0
分析师  1997-4-19       4000.0  8000.0
售货员  1991-2-20       2600.0  2600.0
售货员  1991-2-22       2250.0  4850.0
售货员  1991-9-28       2250.0  7100.0
小店员  1995-4-20       2500.0  10500.0
小店员  1995-4-20       3500.0  10500.0
小店员  1995-4-20       4500.0  10500.0
经理    1991-4-2        3975.0  3975.0
经理    1991-5-1        3850.0  7825.0
经理    1991-6-9        3450.0  11275.0
Time taken: 19.475 seconds, Fetched: 11 row(s)

7.Rank

RANK() 排序相同时会重复，总数不会变，可以并列
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

hive>select  ename,job,sal,rank() over(partition by job order by sal) rank1,
dense_rank() over(partition by job order by sal) rank2,
row_number() over(partition by job order by sal) rank3 from adx.emp_test;

OK
SCOTT   分析师  4000.0  1       1       1
FORD    分析师  4000.0  1       1       2
WARD    售货员  2250.0  1       1       1
MARTIN  售货员  2250.0  1       1       2
TURNER  售货员  2500.0  3       2       3
ALLEN   售货员  2600.0  4       3       4
ZHANGS  小店员  2500.0  1       1       1
ZHANGS  小店员  3500.0  2       2       2
ZHANGS  小店员  4500.0  3       3       3
SMITH   店员    1800.0  1       1       1
JAMES   店员    1950.0  2       2       2
ADAMS   店员    2100.0  3       3       3
MILLER  店员    2300.0  4       4       4
KING    总统    10.0    1       1       1
CLARK   经理    3450.0  1       1       1
BLAKE   经理    3850.0  2       2       2
JONES   经理    3975.0  3       3       3
Time taken: 19.33 seconds, Fetched: 17 row(s)

二、时间函数

1.to_date()

将YYYY-MM-dd HH:MM:SS 的格式的日期转化为YYYY-MM-dd 格式

hive> select to_date('2022-06-27 15:18:30');
OK
2022-06-27
Time taken: 0.084 seconds, Fetched: 1 row(s)

2.year()?

year()用于获取日期中的年份，返回为int类型

hive> select year('2022-06-27 15:18:30');
OK
2022
Time taken: 0.125 seconds, Fetched: 1 row(s)

3.month()

同上，month()函数是用于获取日期字符串中的月份

hive> select month('2022-06-27 15:18:30');
OK
6
Time taken: 0.1 seconds, Fetched: 1 row(s)

4.day()

获取日期字符窜中的天数

hive> select day('2022-06-27 15:18:30');
OK
27
Time taken: 0.088 seconds, Fetched: 1 row(s)

5.hour()

获取日期字符串中的小时

hive> select hour('2022-06-27 15:18:30');
OK
15
Time taken: 0.109 seconds, Fetched: 1 row(s)

6.minute()

hive> select minute('2022-06-27 15:18:30');
OK
18
Time taken: 0.104 seconds, Fetched: 1 row(s)

7.second()

hive> select second('2022-06-27 15:18:30');
OK
30
Time taken: 0.082 seconds, Fetched: 1 row(s)

8.date_add()

date_add(str1,nums) str1为日期字符串，nums为想要增加几天，整数位增加负数为减少

正数：
hive> select date_add('2022-06-27 15:18:30',2);
OK
2022-06-29
Time taken: 0.115 seconds, Fetched: 1 row(s)

负数：
hive> select date_add('2022-06-27 15:18:30',-2);
OK
2022-06-25
Time taken: 0.09 seconds, Fetched: 1 row(s)

9.date_sub()

date_sub(str1,nums) 同上，不过此函数为减少函数，正数则减少，负数则增加

正数：

hive> select date_sub('2022-06-27 15:18:30',2);
OK
2022-06-25
Time taken: 0.095 seconds, Fetched: 1 row(s)

负数：

hive> select date_sub('2022-06-27 15:18:30',-2);
OK
2022-06-29
Time taken: 0.088 seconds, Fetched: 1 row(s)

10.weekofyear

周期函数，一周为一个周期返回int 

hive> select weekofyear('2022-06-27 15:18:30');
OK
26
Time taken: 0.09 seconds, Fetched: 1 row(s)

11.datediff()

日期比较函数 datediff(date1,date2) 比较两个日期相差的天数date1-date2


hive> select datediff('2022-06-27 15:18:30','2022-06-28 15:17:30');
OK
-1
Time taken: 0.078 seconds, Fetched: 1 row(s)

13.date_format

日期自定义格式化


1) hive> select date_format('2022-06-27 15:18:30','yyyy-MM');

OK
2022-06
Time taken: 0.103 seconds, Fetched: 1 row(s)

2)hive> select date_format('2022-06-27 15:18:30','yyyy-MM-ss');

OK
2022-06-30
Time taken: 0.085 seconds, Fetched: 1 row(s)

14.next_day()

后去下一周的某一天的日期

举例：


     1)获取下一周周一的日期 next_day('日期','Mo') ,Mo为monday 的缩写

       hive> select next_day('2022-06-27 15:18:30','Mo');
       OK
       2022-07-04
       Time taken: 0.104 seconds, Fetched: 1 row(s)
    
     2) 获取下周二日期
      
       hive> select next_day('2022-06-27 15:18:30','Tu');
       OK
       2022-06-28
       Time taken: 0.089 seconds, Fetched: 1 row(s)
  
以此类推，周三We 、周四 Th、周五Fri等等

15.last_day()

获取当月的最后一天的日期

hive> select last_day('2022-06-27 15:18:30');
OK
2022-06-30
Time taken: 0.083 seconds, Fetched: 1 row(s)

16.日期和时间戳之间的互相转换

? 16.1 from_unixtime() (时间戳转换为日期格式) and?unix_timestamp() (日期格式转换为时间戳) 精确度不高

秒级别10位：

hive> select from_unixtime(1656384068,'YYYY-MM-dd HH:mm:SS');
OK
2022-06-28 10:41:00
Time taken: 0.982 seconds, Fetched: 1 row(s)
    

毫秒级别13位:

hive> select from_unixtime(cast(substring('1656384068000',1,10) as bigint),'YYYY-MM-dd HH:mm:SS');
OK
2022-06-28 10:41:00
Time taken: 0.129 seconds, Fetched: 1 row(s)



将日期转换为秒级别也就是十位数的时间戳

hive> select unix_timestamp('2022-06-28 10:48:01','YYYY-MM-dd HH:mm:SS');
OK
1640486880
Time taken: 0.103 seconds, Fetched: 1 row(s)

? ?16.2 to_utc_timestamp()? and? ?from_utc_timestap() 精确度高建议使用

1) to_utc_timestamp()  北京时间转UTC时间

hive> select to_utc_timestamp(1656384481000,'UTC');
OK
2022-06-28 02:48:01
Time taken: 1.133 seconds, Fetched: 1 row(s)



hive> select to_utc_timestamp(1656384481000,'GMT');
OK
2022-06-28 10:48:01
Time taken: 0.115 seconds, Fetched: 1 row(s)


2) from_utc_timestamp() UTC转北京时间

hive> select to_utc_timestamp(1656384481000,'PRC');
OK
2022-06-28 02:48:01
Time taken: 1.133 seconds, Fetched: 1 row(s)



hive> select to_utc_timestamp(1656384481000,'Asia/Shanghai');
OK
2022-06-28 10:48:01
Time taken: 0.115 seconds, Fetched: 1 row(s)

三、聚合函数 (实际工作中大部分情况下是结合group by 一起使用)

? ? ? 1.count(col) count(distinct col)

1) count(col)
hive> select job,count(job) from adx.emp_test group by job;


OK
分析师  2
售货员  4
小店员  3
店员    4
总统    1
经理    3
Time taken: 19.68 seconds, Fetched: 6 row(s)

2) count(distinct col)

hive> select job,count(distinct job) from adx.emp_test group by job;

OK
分析师  1
售货员  1
小店员  1
店员    1
总统    1
经理    1
Time taken: 18.438 seconds, Fetched: 6 row(s)

? ?2.sum(col) sum(distinct col)

1) sum(col)

hive> select job,sum(sal) from adx.emp_test group by job;

OK
分析师  8000.0
售货员  9600.0
小店员  10500.0
店员    8150.0
总统    10.0
经理    11275.0
Time taken: 19.157 seconds, Fetched: 6 row(s)

2) sum(distinct col)

hive> select job,sum(distinct sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  7350.0
小店员  10500.0
店员    8150.0
总统    10.0
经理    11275.0
Time taken: 18.5 seconds, Fetched: 6 row(s)

3.max(col) max(distinct col)

1) max(col) 

hive> select job,max(sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  2600.0
小店员  4500.0
店员    2300.0
总统    10.0
经理    3975.0
Time taken: 16.961 seconds, Fetched: 6 row(s)

2) max(distinct col)

hive> select job,max(distinct sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  2600.0
小店员  4500.0
店员    2300.0
总统    10.0
经理    3975.0
Time taken: 19.492 seconds, Fetched: 6 row(s)

4.min(col)? min(distinct col)

1) min(col)? 

hive> select job,min(sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  2250.0
小店员  2500.0
店员    1800.0
总统    10.0
经理    3450.0
Time taken: 19.155 seconds, Fetched: 6 row(s)

2) min(distinct col)

hive> select job,min(distinct sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  2250.0
小店员  2500.0
店员    1800.0
总统    10.0
经理    3450.0
Time taken: 19.608 seconds, Fetched: 6 row(s)

5.avg(col)? avg(distinct col)

1) avg(col)

hive> select job,avg(sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  2400.0
小店员  3500.0
店员    2037.5
总统    10.0
经理    3758.3333333333335
Time taken: 19.702 seconds, Fetched: 6 row(s)



2) avg(distinct col)

hive> select job,avg(distinct sal) from adx.emp_test group by job;

OK
分析师  4000.0
售货员  2450.0
小店员  3500.0
店员    2037.5
总统    10.0
经理    3758.3333333333335
Time taken: 19.142 seconds, Fetched: 6 row(s)

大数据最新文章

实现Kafka至少消费一次

亚马逊云科技：还在苦于ETL？Zero ETL的时代

初探MapReduce

【SpringBoot框架篇】32.基于注解+redis实现

Elasticsearch：如何减少 Elasticsearch 集

Go redis操作

Redis面试题

专题五 Redis高并发场景

基于GBase8s和Calcite的多数据源查询

Redis——底层数据结构原理

加:2022-07-03 10:53:18 更:2022-07-03 10:54:48

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/23 14:09:20-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码