SQL规范与技巧(持续更新)

年末, 发现今年也没写几篇博客, 像赶暑假作业一样赶紧写几篇. 还没放假前写了sql, 真的是跟屎一样难看, 幸好同事有耐心帮我检查代码和规范, 所以趁着还没到2021把这个没整理的知识梳理总结一遍.

书写规范

查询表的时候表名的别名最好是大写, 查询表中的属性名最好是小写(也可以反过来), 这样利于区分.
关于缩进和标点符号的位置, 缩进部分最好按照下面的方式来写, 在一些编辑器比如sublime text 或者 notepad++中都会有固定的缩进, 这里有个小技巧是把查询多个属性后的逗号放在前面,这样可以方便检查是否有遗漏逗号. 最后就是空格, ’=’之间最好有空格, 这样看起来比较清楚.

SELECT col_1 ,col_2 ,col_3 ,COUNT(*) FROM A WHERE col_1 = ‘a’ AND col_2 = ( SELECT MAX(col_2) FROM B WHERE col_3 = 100 ) GROUP BY col_1 ,col_2 ,col_3

基础查询技巧

如果只是在普通的查询时候,建议使用LIMIT来控制返回结果的数量, 减少查询的开销
*避免使用 SELECT **, 这其实是一条非常基本的原则, 你在查询前最好是知道你要什么, 如果你的目的不是看这个表有哪些列的话, 在生产过程不应该使用这条语句开头.
善用变量, 因为在一个在一个query文件中, 你会用到不止一次attribute = value的语句,那么此时就可以用变量. 我在公司用的HUE, 因此可以用attribute = ‘${variable_name}’来设定变量值. 如果是用shell来跑sql文件, 无论是表还是值, 都可以用${}来得到定义好的变量.
如果可以用’=’就少用’<>’
WITH AS短语，也叫做子查询部分（subquery factoring), 可以让代码的可读性更高.
对于经常用JOIN语句的人来说, 写关于属性的条件WHERE中的条件最好是主表的就是select中的表的, ON 中的条件就是连接的那个表的条件.

几个我在工作时候看到的但以前没用过的语句

ROW_NUMBER() OVER (PARTITION BY ## ORDER BY ## ASC/DESC) AS . 这个语句是返回一个新的列, partition by用于给结果集分组, 并且对结果进行排序, 一般用到这个语句的时候是在后续的查询中只需要用结果中的其中一列(通常是第一列, 取决与order by).

ROW_Number	It assigns the sequential rank number to each record, same value will have different values
RANK	It assigns the rank number to each row in a partition. same value will have same id, It skips the number for similar values. means could have gap.
Dense_RANK	It assigns the rank number to each row in a partition. It does not skip the number for similar values. no gap there.

SELECT * ,ROW_NUMBER() OVER (PARTITION BY Grade ORDER BY Score ASC) As row_id FROM Student

LAG是用来访问当前行之前的特定物理偏移量的行. [1]
- return_value - 基于指定偏移量的前一行的返回值。
- offset - 从当前行返回的行数，用于访问数据。如果未明确指定offset，则它的默认值为1。
- default - 是当offset超出分区范围时要返回的值。如果未指定，则默认为NULL。
- PARTITION BY子句将结果集的行分配到应用LAG()函数的分区。如果省略PARTITION BY子句，该函数会将整个结果集视为单个分区。
- ORDER BY子句指定应用LAG()函数的每个分区中行的逻辑顺序。

lead和lag函数，这两个函数一般用于计算差值，最适用的场景是计算花费时间。举个例子，有数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。

lead是用于统计窗口内往下第n行值，lag是用于统计窗口内往上第n行值。

LAG(return_value ,offset [,default]) OVER ( [PARTITION BY partition_expression, … ] ORDER BY sort_expression [ASC

DESC], … )

与字符串有关的函数
- STRLEFT(string, number_of_chars)
- CHAR_LENGTH(string)
- REVERSE(string)
- CONCAT ( input_string1, input_string2 [, input_stringN ] ) 最多可以连接255个字符串
- GROUP_CONCAT(colum, sep). 特别像把SELECT v FROM t GROUP BY v语句的结果串接起来.
IF函数
- IF(500<1000, ”YES”, ”NO”) True then YES and False for NO. could also be used with OR.
能写在 WHERE 子句里的条件不要写在 HAVING 子句里.[2]
- 使用 GROUP BY 子句进行聚合时会进行排序，如果事先通过 WHERE 子句能筛选出一部分行，能减轻排序的负担.
- 在 WHERE 子句中可以使用索引，而 HAVING 子句是针对聚合后生成的视图进行筛选的，但很多时候聚合后生成的视图并没有保留原表的索引结构
CASE WHEN . 可以用在select 之后设定选某个attribute (则这个选择之后的注意要写上别名 END AS ##), 或者放在order by里面

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;

DATEDIFF(recordDate, LAG(recordDate) OVER (ORDER BY recordDate)) AS date_diff

REFERENCE

Enjoy Reading This Article?