hive常用函数

最新推荐文章于 2024-07-12 22:15:20 发布
最新推荐文章于 2024-07-12 22:15:20 发布
阅读量247 收藏
点赞数
文章标签： hive hadoop 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012761191/article/details/131826356
版权

行列转换

在这里插入图片描述

create table tmp_summer1(id string,name string brith string);

insert into tmp_summer1 values('001','A','20211202');
insert into tmp_summer1 values('001','B','20211202');
insert into tmp_summer1 values('002','A','20211202');
insert into tmp_summer1 values('001','B','20211202');

在这里插入图片描述

多行合并一行

--collect_set去重
-- 如果字段不是string类型则转: cast(c1 as string)
create table tmp_summer2 as 
select id
    ,concat_ws('&',collect_set(name)) as name
    ,max(brith) as brith
from tmp_summer1 a
group by id;       
 
--collect_list不去重          
select id
    ,concat_ws('&',collect_list(name)) as name
    ,max(brith) as brith
from tmp_summer1 a
group by id;

在这里插入图片描述

--列转行
create table tmp_summer3 as 
select id,name_split,brith
from tmp_summer2
lateral view explode(split(name,'&')) tmpTable as name_split

在这里插入图片描述

--将一列拆分为多列
create table tmp_summer4 as 
select id
    ,max(case when name_split='A' then name_split else null end) as name_a
    ,max(case when name_split='B' then name_split else null end) as name_b
    ,max(case when name_split='C' then name_split else null end) as name_c
    ,max(brith) as brith
from tmp_summer3
group by id;

解析JSON

-- get_json_object(json_string, '$.key')
-- 功能：解析json的字符串json_string,返回key指定的内容。如果输入的json字符串无效，那么返回NULL。这个函数每次只能返回一个数据项。
SELECT 
GET_JSON_OBJECT('{"level":"2","time":1650973942596,"type":"0"}','$.level' ) as level ;

日期函数

2021-09-16 00:00:00        ——>        20210916
to_char(ZHTCJSJ,'yyyymmdd')    ——>   from_unixtime(unix_timestamp(ZHTCJSJ),'yyyyMMdd')

from_unixtime(unix_timestamp(date1),'yyyyMMdd')

-- 当前日期	
select current_date; --// 2021-12-23 
select unix_timestamp(); --// 1640224807
-- 建议使用current_timestamp，有没有括号都可以
select current_timestamp(); --// 2021-12-23 09:57:57.638 

-- 时间戳转日期
select from_unixtime(1505456567); --//2017-09-15 14:22:47
select from_unixtime(1505456567, 'yyyyMMdd'); --//20170915
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss'); --// 2017-09-15 14:22:47

-- 日期转时间戳
select unix_timestamp('2019-09-15 14:23:00'); --//1568528580

-- 计算时间差
select datediff('2020-04-18','2019-11-21'); --//149
select datediff('2019-11-21', '2020-04-18'); --//-149

-- 查询该天是该月第几天
select dayofmonth(current_date); --//23
select dayofmonth('2021-12-23'); --//21

-- 计算月末:
select last_day(current_date); --//2021-12-31
select last_day('2021-12-15'); --//2021-12-31

-- 当月第1天:
select date_sub(current_date, dayofmonth(current_date)-1) --//2021-12-01
-- 下个月第1天:
select add_months(date_sub(current_date, dayofmonth(current_date)-1), 1);
 --//2022-01-01

-- 字符串转时间(字符串必须为:yyyy-MM-dd格式)
select to_date('2020-01-01');
select to_date('2020-01-01 12:12:12');

-- 日期、时间戳、字符串类型格式化输出标准时间格式
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(), 'yyyyMMdd');
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');

-- 计算emp表中，每个人的工龄
select *, round(datediff(current_date, hiredate)/365,1) as workingyears 
from emp;

字符串函数

-- 转小写。lower
select lower("HELLO WORLD");
-- 转大写。upper
select lower(ename), ename from emp;

-- 求字符串长度。length
select length(ename), ename from emp;
-- 字符串拼接。 concat / ||
select empno || " " ||ename idname from emp;
select concat(empno, " " ,ename) idname from emp;

-- 指定分隔符。concat_ws(separator, [string | array(string)]+) 
SELECT concat_ws('.', 'www', array('lagou', 'com'));
select concat_ws(" ", ename, job) from emp;

-- 求子串。substr
SELECT substr('www.lagou.com', 5); 
SELECT substr('www.lagou.com', -5); 
SELECT substr('www.lagou.com', 5, 5);

-- 字符串切分。split，注意 '.' 要转义
select split("www.lagou.com", "\\.");

数学函数

-- 四舍五入。round
select round(314.15926); 
select round(314.15926, 2); 
select round(314.15926, -2);
-- 向上取整。ceil
select ceil(3.1415926);
-- 向下取整。floor
select floor(3.1415926);
-- 其他数学函数包括:绝对值、平方、开方、对数运算、三角运算等

条件函数

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;
-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 将emp表的员工工资等级分类:0-1500、1500-3000、3000以上
select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp;
 
-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END -- 复杂条件用 case when 更直观
select sal, case when sal<=1500 then 1
                 when sal<=3000 then 2
                 else 3 end sallevel
from emp;
 
-- 以下语句等价
select ename, deptno,
       case deptno when 10 then 'accounting'
                   when 20 then 'research'
                   when 30 then 'sales'
                   else 'unknown' end deptname
from emp;
 
select ename, deptno,
       case when deptno=10 then 'accounting'
            when deptno=20 then 'research'
            when deptno=30 then 'sales'
            else 'unknown' end deptname
from emp;
 
-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值;如果所有值都为 NULL，那么返回NULL
select sal, coalesce(comm, 0) from emp;
-- isnull(a) isnotnull(a)
select * from emp where isnull(comm);
select * from emp where isnotnull(comm);
-- nvl(T value, T default_value)
select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal from emp;
-- nullif(x, y) 相等为空，否则为a
SELECT nullif("b", "b"), nullif("b", "a");

UDTF

User Defined Table-Generating Functions

用户定义表生成函数，一行输入，多行输出

-- explode，炸裂函数
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行 
select explode(array('A','B','C')) as col; 
select explode(map('a', 8, 'b', 88, 'c', 888));
-- UDTF's are not supported outside the SELECT clause, nor nested in expressions
-- SELECT pageid, explode(adid_list) AS myCol... is not supported
-- SELECT explode(explode(adid_list)) AS myCol... is not supported
-- lateral view 常与 表生成函数explode结合使用
 
-- lateral view 语法:
--Lateral View 用于和UDTF函数【explode,split】结合来使用。
--首先通过UDTF函数将数据拆分成多行,再将多行结果组合成一个支持别名的虚拟表。
--主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
 
-- lateral view 的基本使用
with t1 as (
  select 'OK' cola, split('www.lagou.com', '\\.') colb
)
select cola, colc from t1 lateral view explode(colb) t2 as colc;

窗口函数

窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是:对于每个组返回多行，而聚合函数对于每个组只返回一行

over() 开窗

select sum(age) from tb1;

错误: select id,sum(age) as sum_age from tb1;

--窗口函数是针对每一行数据的，如果over中没有参数，默认的是全部结果集
select id,sum(age) over() as sum_age from tb1;

--在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小
select id,sum(age) over(partition by id) as sum_age from tb1;

-- order by
select id
    ,sum(age) over(partition by id order by age) as sum_age 
from tb1;

--Window子句 rows between ... and ...
select id
    ,sum(age) over(partition by id order by age 
    between 1 preceding and 1 following) as sum_age 
from tb1;

在这里插入图片描述

排名函数

row_number()。排名顺序增加不会重复;如1、2、3、4、… …

rank()。 排名相等会在名次中留下空位;如1、2、2、4、5、… …

dense_rank()。 排名相等会在名次中不会留下空位 ;如1、2、2、3、4、… …

select id,name,age
    ,row_number() over(partition by id order by age desc) as rank1
    ,rank() over(partition by id order by age desc) as rank2
    ,dense_rank() over(partition by id order by age desc) as rank3
from tb1;

序列函数

lag。返回当前数据行的上一行数据

lead。返回当前数据行的下一行数据

first_value。取分组内排序后，截止到当前行，第一个值

last_value。分组内排序后，截止到当前行，最后一个值

ntile。将分组的数据按照顺序切分成n片，返回当前切片值

select id, name, age,
       ntile(2) over(partition by id order by age) as ntile
from tb1;



with tmp as (
    select id, name, age,
       dense_rank() over (partition by id order by age desc) as rank
from tb1)
select id, score, rank,
       nvl(score - lag(score) over (partition by class order by score desc), 0) lagscore
  from tmp
 where rank<=3;

with as

--WITH AS 语句可以为一个子查询语句块定义一个名称,功能类似临时表

-- with子句只能被select查询块引用
-- with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高效率
-- 在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开
-- 最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来

--相当于建了两个临时表a和b
with
a as (select * from tb1),
b as (select * from tb2)
select * from a, b where a.id = b.id;
等木鱼的猫
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive 常用函数
fengfengchen95的博客
12-23 198
1.日期比较函数: datediff语法: datediff(string enddate,string startdate) 返回值: int 说明: 返回结束日期减去开始日期的天数。 举例：hive> select datediff('2016-12-30','2016-12-29'); 返回1 2.日期增加函数: date_add语法: date_add(string...
Hive 常用函数
长不大的大灰狼
03-08 1846
Hive 常用函数
Hive常用函数大全
weixin_45425565的博客
09-09 4819
Hive常用函数大全
Hive常用函数
lala1483的博客
05-31 527
Hive常用函数 1、lead(a,b,c) over() ：当前窗口向后偏移，a是需要偏移的列，b是偏移量，c是若没有值用默认值填充； 2、lag(a,b,c) over() ：当前窗口向前偏移，a是需要偏移的列，b是偏移量，c是若没有值用默认值填充； 3、datediff(d1,d2) : d1和d2日期的差值； 4、if(boolean,a,b) : 如果第一个参数成立返回a，否则返回b； 5、floor() : 向下取整；【中位数：+1向下取整 +2 向下取整数】； 6、nvl（表达式1，表达式2
Hive常用函数大全.pdf
04-14
hive常用函数大全，日期类别，函数类别等常用函数都可有；
hive常用函数参数手册
10-21
hive常用的函数参考手册，仅供参考，可能版本不一样，函数方法不一致,请以进官方文档为准，https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
Hadoop-19 Flume Agent批量采集数据到HDFS集群 监听Hive的日志 操作则把记录写入到HDFS 方便后续分析
永远好奇，无限进步！
07-09 725
上一节完成了Flume启动测试、FlumeConf编写、Flume发送和接收数据测试。本节将对Hive的操作数据进行收集，并将结果写入到HDFS中，方便后续计算。这里是三台公网云服务器，每台 2C4G，搭建一个Hadoop的学习环境，供我学习。之前已经在 VM 虚拟机上搭建过一次，但是没留下笔记，这次趁着前几天薅羊毛的3台机器，赶紧尝试在公网上搭建体验一下。2C4G 编号 h1212C4G 编号 h1222C2G 编号 h123。
HIVE启动报错HiveException java.lang.RuntimeException:
2301_77836489的博客
07-09 435
HIVE启动报错HiveException java.lang.RuntimeException:
Hive/Spark窗口函数
初阳
07-09 779
结果中，对于每一行的last_value的结果都是当前值，并不分区中按salary升序的最后一个值。前面在提到last_value时，特意强调了该函数的结果并不是分区中的最后一个值，结合上述介绍的window specification再来看下该函数的结果值。注意，默认情况下last_value取的是第一行截止到当前行的最后一个值(当前行的值)，并不是整个分区中排序后的最后一个值。注意，如果order by的结果相同，则rank得到的结果都相同，在这里的语义是排序结果相同，因此等级编号也相同。
【找不到视图问题解决】@RestController 与 @Controller注解的使用区别
最新发布
wzc200406的博客
07-12 702
@RestController 与 @Controller注解的使用区别
jsp转换成servlet的详解
2301_79714460的博客
07-10 863
此知识点实在手写tomcat的时候衍生出来的东西，想要真正搞懂这个东西，就有此博客。
数据湖仓一体(四)安装hive
知行合一
07-12 179
安装hive
RequestContextHolder多线程获取不到request对象
qq_21480329的博客
07-09 410
RequestContextHolder多线程获取不到request对象
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
永远好奇，无限进步！
07-10 382
上一节完成了Flume的数据采集，通过3个Agent的编写，实现了双写到本地+HDFS。本节将开始 Sqoop，Sqoop 是一款开源的工具，主要用于在 Hadoop（Hive） 与 传统数据库（MySQL等）之间进行数据的传递。这里是三台公网云服务器，每台 2C4G，搭建一个Hadoop的学习环境，供我学习。之前已经在 VM 虚拟机上搭建过一次，但是没留下笔记，这次趁着前几天薅羊毛的3台机器，赶紧尝试在公网上搭建体验一下。2C4G 编号 h1212C4G 编号 h1222C2G 编号 h123。
数据建设实践之大数据平台(五)安装hive
知行合一
07-11 164
安装hive
FlinkModule加载HiveModule异常
yuanlaishidahuaa的博客
07-11 136
但我把这个依赖贴近pom文件后，用maven加载时，maven对这个包爆红，网上查说是需要scala环境，目前还没解决。加在不出来这个模块，网上查说是要加下面这个依赖。HiveModule这个模块加载不出来。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值