Hive SQL常用语法使用及示例

背景

在SQL的使用过程中，会遇到一些常用但不熟悉的语法，现本人分享日常工作中常用的语法及示例，供大家不定时查阅和引用。以下内容均基于Hive SQL语法。

常用SQL及说明示例

• 1. 移动窗口函数

row_number() over(partition by A,B order by C desc) rank
--对A和B聚合，然后按照C降序排列（ASC为升序），类似于Group BY 函数

这个函数可谓超级常用的了，有时需要按照同一个固有属性选择里面排名靠前或者殿后的，比如同一类产品最早生产时间、同一个班级排名NO.1、同一个男明星最早出轨的时间等等。同时这些目的还不是我们最终目的，只是需要作为某种筛选条件，得到结果后还需要再去筛选、关联、聚合等等，此时就可用移动窗口函数。
举个栗子，如下表：用户ID、用户对应的分数score字段

id	score
1	54
1	80
1	20
2	80
2	78
3	88
3	96
3	79

经过如下命令后，会在原表增加一列rank，

row_number() over(partition by id order by score desc) rank
--对A和B聚合，然后按照C降序排列（ASC为升序），类似于Group BY 函数

id	score	rank
1	80	1
1	54	2
1	20	3
2	80	1
2	78	2
3	96	1
3	88	2
3	79	3

• 2. 字符串截取函数

substring(name,2,3)
--从第2个位置开始，截取3个文本

举个栗子：下表只有一个name字段

name
天上掉下个林妹妹
我爱华农
你好我是你大爷

利用上述substring命令截取后，

name
上掉下
爱华农
好我是

有时截取的位置不知道从几开始，只知道需要从某个字后开始截取，那么，此时还需要对文本中文字进行定位。

instr(memo,'我爱你')
charindex('我爱你',memo)
locate("我爱你",memo)
--定位字符串所在位置，多个选择，视不同SQL支持与否情况而定

memo	转换后→	memo
冷冷我爱你你爱我吗	→	3
我爱你你知道吗	→	1
你为什么不说”我爱你“	→	8

有了定位和截取函数就可以随意组合和截取字段了~

• 3. 选取每月最后一天的小技巧

1°. 当前日期加1天得到的日期的月份，与原日期的月份正好相差1，即为月底那一天：（月末数据有的情况下）

add_months(trunc(dt,'MM'),1) = trunc(date_add(dt,1),'MM')

2°. 如果数据未更新到月末这一天，那么就取每个月的最大日期：

where dt in (
  select dt from
  (select trunc(dt,'MM'),max(dt) as dt from table
   group by trunc(dt,'MM') a
)

• 4. 列与列、行与行之间的拼接

1°. 同一行两列拼接成一个字段：concat

concat(a,b) as name
--将a和b两个字段进行拼接，生成的新的字段命名为name

举个栗子，下面表三个字段分别为ID、姓a、名b：

id	a	b
1	张	三
1	李	四
2	王	麻子
2	吴	欢

列经过拼接后，得到下表table1：

id	name
1	张三
1	李四
2	王麻子
2	吴欢

2°. 不同行拼接成一行：concat_ws

select id,concat_ws(',', collect_set(name)) label
from table1
group by id
--将同一个id下的多行name字段按照间隔为逗号进行拼接，成为一行，新的字段命名为label

如果按照上述命令将table中name字段进行拼接，将得到：

id	lable
1	张三,李四
2	王麻子,吴欢

• 5. 转义符号“\”

右斜杠“\”在筛选某些特殊字符时，特别有用，最为典型的是对“_”下划线的转移。如果直接在where筛选条件里写下划线，则会导致筛选条件失效。

"%1\_1\_%"  
--相当于是 查找文本中含有"1_1_"的数据，需要在“_”前面加上“\”

• 6. 字符串分割函数：split

split(str, regex) 
--将str字段，按照regex分割开成独立的字符串
split('a,b,c,d',',')  --例子1
["a","b","c","d"]  --例子1输出结果
split('a,b,c,d',',')[0]   --例子2
a  --例子2输出结果

• 7. 非空函数：nvl、coalesce

SQL中经常会有要么A是NULL则选取B值、否则选取A值的这种情况，最直接想到的是利用case when函数来实现，实际上SQL有现成的命令来实现这个目的：

NVL(expr1, expr2)
--返回参数中的第一个非空值，支持2个参数
--1、空值转换函数； 
--2、类似于mysql-nullif(expr1, expr2)，sqlserver-ifnull(expr1, expr2)

Coalesce(expr1,expr2,expr3,...)
--返回参数中的第一个非空值，支持2个以上参数

结语

以上命令都是自己在实际工作中用到的函数，由于每次用每次都要上网去搜索，后来干脆自己将常用的命令整理下来，需要使用时到这个文件中查找，节省时间和精力，也不失为一个好办法。后续如果遇到更多有用的函数，博主也将更新到此文中。