Hive SQL 查询函数手册

SELECT t1.a as name_a, -- 说明a
      t2.b as name_b, -- 说明b
      max(t2.f) as name_f -- 说明f
FROM
  (SELECT <list1>
   FROM T) AS t1
<LEFT / RIGHT> JOIN
  (SELECT <list2>
   FROM C) AS c1 ON t1.id = c1.id AND t1.name = c1.name
WHERE X=a AND (Y > b or Z != c)
GROUP BY t1.m, t2.p
ORDER BY J, H DESC

SELECT UID,
       collect_set(order_id)
FROM dwd.order_detail
GROUP BY UID
-- 返回类似 111 | ["123","124"]，集合的内容去重

SELECT UID,
       concat_ws(',', collect_set(cast(order_id AS string)))
FROM dwd.order_detail
GROUP BY UID
-- 返回类似 111 | 12345,45678

-- 返回：tom
select get_json_object('{"name":"tom"}', '$.name')

SELECT cast(date as date) -- 返回 date 类型；
-- timestamp 中的年/月/日的值是依赖与当地的时区，结果返回 date 类型
SELECT cast(timestamp as date)
-- 如果 string 是 yyyy-MM-dd 格式的，则相应的年/月/日的 date 类型的数据将会返回；
-- 但如果 string 不是 yyyy-MM-dd 格式的，结果则会返回 NULL
SELECT cast(string as date)
-- 基于当地的时区，生成一个对应 date 的年/月/日的时间戳值；
SELECT cast(date as timestamp)
-- date所代表的年/月/日时间将会转换成 yyyy-MM-dd 的字符串。
SELECT cast(date as string)
-- 转化为 bigint
SELECT cast('1' as BIGINT)

-- 文本连接：concat->demo
select CONCAT('concat','->','demo')
-- 文本截取：hive
select substr('This is hive demo',9,4)
-- 返回：202108 月份
select substr('202108', 0, 6)
-- 按字符拆分并取部分，返回：abc-def
SELECT substring_index('abc-def-ghi', '-', 2)
-- 文本长度：6
select length('hadoop')
-- 文本大写：HADOOP HADOOP
select upper('hadoop'), ucase('hadoop')
-- 文本小写：hadoop hadoop
select lower('HADOOP'), lcase('HADOOP')
-- HHhadoop, 保证长度，空为向左拼 H
select lpad('hadoop',8,'H')
-- 用指定字符合成指定长度字符：hadooppp
select rpad('hadoop',8,'p')
-- 字符重复：HadoopHadoop
select repeat('Hadoop',2)
-- 字符反转：poodaH
select reverse('Hadoop')
-- 按字符拆分成列表：["hadoop","supports","split","function"]
select split('hadoop~supports~split~function','~')
-- 转为千分位格式, 21,312,442.123
SELECT format_number(21312442.12345, 3)

-- HA^G^FER$JY 替换为 HA$G$FER$JY
select regexp_replace('HA^G^FER$JY',"\\^","\\$")
-- bar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2)

-- TRUE col 列 array 中是否包含 hello
array_contains(col, 'hello')

-- 0 做除数（分母）时返回 NULL
select 1/0

-- 累积求和
sum(col) over(partition by grpcol order by ordcol asc/desc)
-- 滑动求和
sum(col) over(partition by grpc order by ordc range between ... and ...)
-- 按值大小增加序号
row_number() OVER (ORDER BY amount DESC) AS rn
-- 窗口计数
count() OVER (partition by grpcol order by ordcol) AS rn
-- 窗口去重计数，注：不支持 count(distinct col) over(partition by ..)
size(collect_set(col3) over(partition by col1 ORDER BY col2 asc))
-- 窗口去重计数，方法2
dense_rank() over(partition by [dt] order by [math])
 + dense_rank() over(partition by [dt] order by [math] desc)
 - 1

-- 当前时间 2019-10-01 00:54:14.736
select current_timestamp()
-- 当前 日期 2019-10-01
select current_date()
-- 当前日期加一天，2019-10-02
select date_add(current_date(), 1)
-- 当前时间减一天， 2019-09-30
select date_sub(current_date(),1)
-- 当前日期所在月份的第一天，2019-10-01
select trunc(current_timestamp(), 'MONTH')
-- UNIX 时间戳（秒）转指定格式， 20190001， 'yyyymmdd' 也可以
select from_unixtime(unix_timestamp(current_date()), 'yyyyMMdd')
-- 如果是毫秒要除以1000
select from_unixtime(cast(buy_time/1000 as BIGINT),'yyyyMMdd')
-- 带时分秒，buy_time
select from_unixtime(buy_time,'yyyy-MM-dd hh:mm:ss')

-- 根据时间格式转为UNIX时间戳
SELECT unix_timestamp(20190808,'yyyyMMdd')
-- 算出日期为周几，2000-01-03 为固定值，是个周一
SELECT datediff('2019-08-15', '2000-01-03')%7+1 AS week_day
-- 算出自然周数，2000-01-03 为固定值
-- 可用于按周分组等, weekofyear() 跨年会不足一周
SELECT floor(datediff('2019-08-08', '2000-01-03')/7) AS week_number
SELECT datediff('2019-08-30', '2000-01-03') DIV 7
-- 格式化数据
SELECT date_format(time, 'yyyy-MM-dd HH:mm:ss')
-- 只取日期 2021-09-01
SELECT to_date('2021-09-01 14:00:04')
-- 当月的最后一天，返回 2021-02-28
SELECT last_day('2021-02-22')
-- 返回当月第一天，如 2019-09-01
select trunc(sysdate, 'mm')
-- 当前月的季度数的算法，如 4 月为 2
select floor(substr('2019-09-01',6,2)/3.1)+1
-- 昨日的日期，形式如 20220304
select regexp_replace(date_sub(current_date(),1), '-', ''))

-- if 语句语法，请为 Null 的设置为 0
SELECT if(var IS NULL, 0 ,var) AS var_name
-- 满足一定条件的总数，Null count() 不计数
SELECT count(if(score>=80, score, NULL)) AS good
-- case when 枚举翻译
SELECT page_id AS page_id,
      CASE page_id
          WHEN 001 THEN '第一名'
          WHEN 002 THEN '第二名'
          ELSE '-'
      END AS name

-- reflect (也可 java_method) 支持调用 java 自带函数，计算一行最高成绩
select reflect("java.lang.Math","max", englist, chinese) from exam
-- 返回 1	true	3	2	3	2.718281828459045	1.0
SELECT reflect("java.lang.String", "valueOf", 1),
       reflect("java.lang.String", "isEmpty"),
       reflect("java.lang.Math", "max", 2, 3),
       reflect("java.lang.Math", "min", 2, 3),
       reflect("java.lang.Math", "round", 2.5),
       reflect("java.lang.Math", "exp", 1.0),
       reflect("java.lang.Math", "floor", 1.9)

-- stack(INT n, v1, v2, ..., vk)，n 为列数（与 as 数对应）
SELECT stack(2, 'b', 'y', 'b2') as (b, y)
-- 可简少写 as
SELECT stack(1, page_id, page_name) as (page_id, page_name) FROM tt

-- explode(列)：将列中复杂的array或者map结构数据拆分成多行
SELECT t.info_id,t.d
FROM
  (SELECT explode(info_detail) AS (info_id, detail)
   FROM my_table) as t

-- inline(Array<Struct [, Struct]> a) 分解struct数组到表中
select *
from table1 t
  lateral view inline(array_of_structs) a;

-- GROUPING SETS ( (a,b) ) 指定维度组合
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS ((a,b))
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS ((a,b),a)
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS (a,b)
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS ((a,b),a,b, ())

-- WITH CUBE 所有维度的组合
SELECT a,b,c,SUM(d) FROM tab GROUP BY a, b, c WITH CUBE
-- 相当于
GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())

-- WITH ROLLUP 以最左侧的维度为主进行层级聚合
SELECT a,b,c,SUM(d) GROUP BY a, b, c WITH ROLLUP
-- 相当于
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ())

-- 显示 GROUPING__ID 列，取值 a,b:11->3, a,null:01->1, null,null:00->0
SELECT a, b, count(*), GROUPING__ID
FROM tab
GROUP BY a, b WITH ROLLUP;

SELECT a, b, count(*), GROUPING__ID,  grouping(a, b), grouping(a)
FROM tab
GROUP BY a, b WITH CUBE;

show functions   -- 查看所有函数
desc function count   -- 查看函数用法
desc function extended count  -- 详细用法，简单例子
 -- 查看在hdfs上的存储路径及建表语句
show create table dws.dws_ads_logs
DESCRIBE dws.dws_ads_logs -- 查看表结构
-- 查看分区信息（大小、创建时间、字段等）
describe formatted dws.dws_ads_logs partition(dt="20210801");
-- 开启并行模式(默认关闭)，设定并行度（默认是8）
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=14;

-- 相当于建了 a、b 临时表
with
     a as (select * from scott.emp),
     b as (select * from scott.dept)

select * from a, b where a.deptno = b.deptno;

-- 比 ORDER BY sum(b) DESC 性能好
SELECT  a, sum(b) as total_b
FROM tab
GROUP BY a
ORDER BY total_b DESC limit 5;