Hive 内置函数

字符串函数-- 字符串长度 select length(hello) -- 替换 select replace(hello,l,x) -- 截取 select substr(hello,2,3) -- 拼接 select concat(hello,hello,hello) select hello||hello||hello select concat(id,name)from hero -- 拼接时添加分隔符, 分隔符写在第一个参数 select concat_ws(,,id,name,id) from hero --collect_set 聚合后去重 | collect_list 聚合后不去重 select province_id, collect_set(city_name) s,collect_list(city_name) l from city group by province_id -- 去除空格 select TRIM( dsdsa ) -- 转换大写 a A select upper(xxx)日期函数-- 提取日期 select substr(CURRENT_TIMESTAMP,1,4) select year(CURRENT_TIMESTAMP) select substr(CURRENT_TIMESTAMP,6,2) select month(CURRENT_TIMESTAMP) select day(CURRENT_TIMESTAMP) select hour(CURRENT_TIMESTAMP) -- bigint类型 的时间戳的转换 select from_unixtime(1782972840,yyyy-MM-dd HH:mm:ss) -- 日期间隔天数 select datediff(2020-10-01,2026-07-02) select datediff(CURRENT_DATE,2020-10-01) -- 间隔月数 select months_between(CURRENT_DATE,2020-10-01) -- 前后N天 select date_sub(CURRENT_DATE,1) select date_add(CURRENT_DATE,1)数值型函数MOD(被除数, 除数) --取余函数取模 FLOOR(数值) --向下取整 CEIL(数值) CEILING(数值) --向上取整两个函数完全等价 ROUND(数值 [,保留小数位数]) --四舍五入转换函数NVL(表达式1, 替换值) --空值替换函数 COALESCE(值1, 值2, 值3, ..., 默认兜底值) --多参数空值依次匹配升级版 NVL IF --判断语句 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 WHEN 条件3 THEN 结果3 ELSE 默认结果 END --多分支判断 CAST(原始值 AS 目标数据类型) --类型转换函数窗口函数----------------- 窗口函数 函数()over(partition by xxx order by xxx rows between xxx and yyy) -- 排名开窗 -- 通常用于 计算 Top-N row_number() 1234 rank() 1134 DENSE_RANK() 1123 -- rows between xxx and yyy preceding : 向前找 following : 向后找 CURRENT row : 当前行(计算哪一行,该行就是当前行) unbounded : 一直到窗口的边界 rows between unbounded preceding and CURRENT row -- 聚合开窗 -- 累计计算(累计求和, 累计平均) SUM avg max MIN count -- 偏移开窗: 将行和行的关系 转换成 列和列的关系 -- 计算 同比 环比 , 连续性问题 lag --取上一行数据 LAG(字段名 [,偏移行数] [,默认值]) OVER(PARTITION BY 分组字段 ORDER BY 排序字段) lead --取下一行数据 LEAD(字段名 [,偏移行数] [,默认值]) OVER(PARTITION BY 分组字段 ORDER BY 排序字段) -- 切片开窗 ntile -- 一般用于 百分比计算 ntile(数字) NTILE(切块数量) OVER(PARTITION BY 分组列 ORDER BY 排序列) SELECT a.*, ntile(4)over(PARTITION BY deptno ORDER BY sal desc) FROM emp a其他-- hive 处理json 字符串 {systemtype: android, education: doctor, marriage_status: 1, phonebrand: VIVO} -- get_json_object select a.*, get_json_object(extra1, $.phonebrand)phone from zhiyun.user_info a --------------------------------- 多维分析 ----------------------------------- -- 多维分析 grouping sets | rollup | cube select sex,city,count(0) c from zhiyun.user_info a group by sex,city GROUPING sets(sex,city) select sex,city,count(0) c from zhiyun.user_info a group by sex,city with ROLLUP select sex,city,count(0) c from zhiyun.user_info a group by sex,city with cube --------------------------------- 侧视图函数 ----------------------------------- -- 列转行(侧视图函数) select * from zhiyun.user_goods_category -- 统计 每个品类的购买人数 udf: 一进一出 udaf: 多进一出 udtf: 一进多出 --1. explode 函数 -- select split(category_detail,,) from zhiyun.user_goods_category select explode(split(category_detail,,)) from zhiyun.user_goods_category --2. 侧视图函数 select a.user_name, b.pl from zhiyun.user_goods_category a LATERAL view explode(split(category_detail,,)) b as pl--每个部门每个工种的人数 select null as job,deptno,count(1)人数 from emp group by deptno union all select job,null as deptno,count(1)人数 from emp group by job --多维分析 select deptno,job,count(1)人数 from emp group by grouping sets(deptno,job) select deptno,job,count(1)人数 from emp group by rollup(deptno,job) rollup(a,b,c) 全部行 a AB abc --立方分析 select deptno,job,count(1)人数 from emp group by cube(deptno,job)递归查询--查询smith的上级 SELECT ename,level from emp start with enmasmith connect by empno prior mgr --查询jones的下属 SELECT ename,level sys_connect_by_path(ename,--) from emp start with enmajones connect by prior empno mgr