字符串函数
-- 字符串长度 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 enma='smith' connect by empno =prior mgr --查询jones的下属 SELECT ename,level sys_connect_by_path(ename,'-->') from emp start with enma='jones' connect by prior empno = mgr
