SQL语法常见问题（持续更新）

本人互联网大厂数据分析师，工作3年，对日常的SQL疑点进行持续梳理，希望互相学习一起进步

一、基础语句

1. 语句执行顺序

窗口函数在where、having后执行，如有partition by，在执行完select之后，在所得结果集之上进行partition by分组。

（7） select 表1.name
（1） from 表1
（3） join 表2
（2） on 表1.id = 表2.id
（4） where 表1.class = '班级A'
（5） group by 表1.name
（6） having avg（表1.score）> 80
（8） order by 表1.name
（9） limit 10;

2. where 和 having 的区别

1）where的执行顺序在having之前，先筛选再计算，效率更高；2）having接聚合函数，where不可以接；3）having前要group by，where没有限制

二、计算逻辑

1. 如何去重

1）select + distinct + 查询字段（单列或多列），distinct 会将全部数据打到一个 reducer 上执行，可能造成数据倾斜；2）group by ；3）row_number () over (partition by key1,key2 order by column asc) as rnk ，取 rnk=1。

三、日期转换（以hive为例）

1. 日期加减

date_sub('2016-12-08',10) -> '2016-11-28'

date_add('2016-12-08',10) -> '2016-12-18'

datediff('2016-12-08','2016-12-02') -> 6

date_sub(from_unixtime(unix_timestamp('2016-08-25'),'yyyy-MM-dd'),7) -> 2016-08-18

2. 格式转换

1）时间转字符串。DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')

2）字符串转时间。STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s')

3）字符串转时间戳。unix_timestamp('20160825 13:02:03','yyyyMMdd HH:mm:ss') -> 1472101323

4）时间戳转字符串。from_unixtime(1441565203,'yyyy/MM/dd HH:mm:ss') -> 2015/09/07 02:46:43

5）转年函数: year('2016-12-08 10:03:01')，转月month()， 转天day() ，转分钟 minute()  ,转秒 second() ，转周weekofyear() 

6）to_date('2016-12-08 10:03:01') -> 2016-12-08

四、窗口函数

1. 聚合窗口

常见包括AVG()、SUM()、COUNT()、MAX()、MIN()等

--ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示窗口从当前行的前2行开始，直到当前行结束
--词句亦可省略：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示窗口从当前分区第1行开始，直到当前行结束
--如果去掉ORDER BY选项，查询的窗口大小就是整个分区
SELECT k1,k2,k3,
AVG(k3) OVER(PARTITION BY k1 ORDER BY k2 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM table
ORDER BY k1,k2;

2. 排名窗口

常见包括ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()等

1）ROW_NUMBER函数。无论是否存在名次相同的数据，排名不重复且连续递增。

2）RANK函数。如果存在名次相同的数据，排名重复且跳跃递增。

3）DENSE_RANK函数。如果存在名次相同的数据，排名重复且连续递增。

3. 取值窗口

常见包括FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()等

1）FIRST_VALUE函数可以返回窗口内第一行数据。

2）LAST_VALUE函数可以返回窗口内最后一行数据。

3）LAG函数可以返回窗口内当前行之前的第N行数据。

4）LEAD函数可以返回窗口内当前行之后的第N行数据。

SELECT k1, k2, k3,
 ( 
    (k3 - LAG(k3,1) OVER (PARTITION BY k1 ORDER BY k2))/
    LAG(k3,1) OVER (PARTITION BY k1 ORDER BY k2)
 ) AS "环比增长率(%)"
FROM table
ORDER BY k1,k2;

✳注意事项

1）窗口函数和group by一起用时，因为窗口函数在group by 或者having后执行，所以窗口函数后面处理的数据不是原始数据。

2）窗口函数只能接在select 和 order by 后，不能接在where 后面。

五、正则化函数

1）like

通配符：%任意字符，_单个字符

2）regexp

模式	匹配模式的什么	例子	含义
^	匹配字符串开头	select name from 表名 where name regexp '^王'	匹配姓为王的名字
$	匹配字符串结尾	select name from 表名 where name regexp '明$'	匹配名字最后一个字为明的名字
.	匹配任意字符	select name from 表名 where name regexp '.明.'	匹配带有明的名字
[…]	匹配方括号间列出的任意字符	select name from 表名 where name regexp '^[wzs]';	匹配括号里任意字符的名字
[^…]	匹配方括号间未列出的任意字符	select name from 表名 where name regexp '^[^wzs]';	匹配未在括号里任意字符的名字
p1|p2|p2	交替：匹配任意p1或p2或p3	select performance from 表名 where performance regexp 'A-|A|A+';	匹配p1,p2,p3

3）regexp_like

regexp_like(str1, str2) ，如果字符串相同则返回，如果为真返回1，否则返回0，不区分大小写

SELECT first_name, last_name 
FROM employees 
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');
--可以匹配并输出Steven、Stephen

4）regexp_replace

regexp_replace(str,'旧字符串'，'新字符串')，通过匹配字符来替换给定的字符串

5）匹配特殊的字符

转义+特殊字符	含义
\\.	能够匹配.
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\	纵向制表

6）匹配字符类

字符	含义
[:alnum:］	任意字母和数字（通［a-zA-Z0-9］）
[:alpha:］	任意字符(同[a-zA-Z])
[:blank:]	空格和制表符(同[\\t])
[:digit:]	任意数字(同[0-9])
[:lower:]	任意小写字母
[:upper:]	任意大写字母
[:space:]	包括空格在内的任意空白字符

7）其他常见表达式

匹配由26个英文字母组成的字符串： ^[A-Za-z]+$

匹配由26个英文字母的大写组成的字符串： ^[A-Z]+$

匹配由26个英文字母的小写组成的字符串： ^[a-z]+$

匹配由数字和26个英文字母组成的字符串： ^[A-Za-z0-9]+$

匹配由数字和26个英文字母或者下划线组成的字符串： ^\w+$

匹配整数： ^-?[1-9]\d*$

匹配正整数： ^[1-9]\d*$

匹配负整数： ^-[1-9]\d*$

匹配非负整数： ^[1-9]\d*|0$

匹配非正整数： ^-[1-9]\d*|0$

电子邮箱： ^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$

URL	： ^(https?:\/\/)?([\da-z\.-]+)\.([a-z\.]{2,6})([\/\w \.-]*)*\/?$

IP地址：	^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$

HTML标签： ^<([a-z]+)([^<]+)*(?:>(.*)<\/\1>|\s+\/>)$

SQL语句： ^(select|drop|delete|create|update|insert).*$

邮政编码： ^[1-9]\d{5}(?!\d)$

Unicode编码中文字符串： ^[u4e00-u9fa5],{0,}$

空白行： \n[\s| ]*\r

首尾空格： (^\s*)|(\s*$)

双字节字符：	[^\x00-\xff]

六、经典SQL题

SQL处理连续问题的巧思，可以参考以下两位博主的文章，个人认为很有帮助。

转自链接：Hive SQL 五大经典面试题_hivesql面试题-CSDN博客

转自链接：Hive Sql中六种面试题型总结_hivesql面试必会6题经典-CSDN博客