关于SQL的一些非SELECT知识点

2021-01-04

复习一波分布式SQL的基础语法,其中有一些之前都没怎么用到过。

运算符

BETWWEN A AND B,判断 A ≤ x ≤ B。

a || b || c,字符串连接符,相当于concat(a,b,c)。

key IN (1,2,3),判断key是否在集合中,后边跟的必须是个集合,和Python的in不太一样。

a<>b,不等于,等价于a!=b

a<=>b,判断a不等于b是否非空,等价于ifNull(a!=b, false)

SELECT

SELECT可以通过正则来选择符合特定规则的列名。

1
SELECT `(ds_1|dss_1)?+.+` FROM A t;

可以通过表名后边跟一个别名来为查询表取别名。

1
SELECT * FROM A VALUES (1,2) (2,3) t(a,b);

可以通过VALUES语句象征性得产生一个临时表。

DELETE FROM A [WHERE ...]用于删除某些行,UPDATE A SET a='a' [WHERE ...]用于更新某些行的值。

GROUP BY

GROUP BY后跟的KEY必须是输入表的列名或基于列名的表达式,不能是SELECT输出列(别名也不行,除非输出列的列名是原表的列名)。在SQL解析中,GROUP BY操作通常是先于SELECT操作的,因此GROUP BY只能接受输入表的列或表达式为KEY。

1
2
SELECT a AS b FROM A GROUP BY a;	-- 对,因为a本身是A的列名
SELECT a AS b FROM A GROUP BY b; -- 错,b是输出列

并且GROUP BYSELECT之间存在制约关系,SELECT的所有列中,没有使用聚合函数的列,必须出现在GROUP BY中。

1
2
3
SELECT a,b FROM A GROUP BY a;		-- 错,会报错说b也必须出现在GROUP BY里
SELECT a,SUM(b) FROM A GROUP BY a; -- 对,因为b进行了聚合操作
SELECT a FROM A GROUP BY a; -- 对,因为只有一个变量

ORDER BY

ORDER BY必须与LIMIT联用(因为是全局排序),且KEY必须是SELECT的输出列的列名或者别名。

对于NULL,在mysql中认为比任何值都小,在oracle中则不是这样。

排序的关键字:DESC是降序,ASC是升序,默认是DESC

1
2
SELECT * FROM A GROUP BY a;					-- 错,因为没和LIMIT连用
SELECT * FROM A ORDER BY a DESC, b ASC; -- 对,分别排序规则

DISTRIBUTE BY

对数据按照某几列的值做hash分片,必须使用SELECT的输出列别名。

1
SELECT a FROM A DISTRIBUTE BY a;

SORT BY:局部排序,似乎主要在诸如MapReduce-based的分布式数据库查询中见到。如果SORT BY语句前有DISTRIBUTE BYSORT BY即对DISTRIBUTE BY的结果进行局部排序。DISTRIBUTE BY控制 map 的输出在reduer中是如何划分的。

如果SORT BY语句前没有DISTRIBUTE BYSORT BY即对每个reduce中的数据进行排序,同样是执行一个局部排序过程。这可以保证每个reduce的输出落盘数据都是有序的,从而能够增加存储压缩率,同时读取的时候如果有过滤,能够利用这个信息减少真正从磁盘读取的数据量,提高后面进行的全局排序的效率。

CLUSTER BY

CLUSTER BY相当于DISTRIBUTE BYSORT BY合用,但是CLUSTER BY不能指定排序为asc或 desc 的规则。

ORDER BY不和DISTRIBUTE BY/SORT BY共用,同时GROUP BY也不和DISTRIBUTE BY/SORT BY共用。

ORDER BY/DISTRIBUTE BY/SORT BY是后于SELECT操作的,因此它们只能接受SELECT语句的输出列为key。

UNION/INTERSECT/EXCEPT

三者相当于对集合进行的交并补操作。三种语法均有ALLDISTINCT两种方式,其中ALL方式下不做去重,而DISTINCT方式下会做去重。默认是DISTINCT方式,因此DISTINCT关键字可以省略。

  • UNION: 求两个数据集的并集。即将两个数据集合并成一个数据集。UNION可以把多个SELECT操作返回的结果,联合成一个数据集。但是连接的两个SELECT查询语句,两个SELECT的列个数、列名称、列类型必须严格一致。如果原名称不一致,可以通过别名设置成相同的名称。
  • INTERSECT: 求两个数据集的交集。即输出两个数据集均包含的记录。
  • EXCEPT: 求第二个数据集在第一个数据集中的补集。即输出第一个数据集包含而第二个数据集不包含的记录。
  • MINUS: 等同于EXCEPT。
1
2
3
SELECT * FROM A
UNION|INTERSECT|EXCEPT [ALL|DISTINCT]
SELECT * FROM B;

注意事项:

  • 集合操作的结果不保证顺序。
  • 集合操作左右两个分支要求列个数必须一致。如果类型不一致,可能会插入隐式类型转换。
  • 支持通过括号指定优先级。
  • UNION后如果有ORDER BY | LIMIT等子句,则其将作用于前面最后一个UNION的结果。也可以通过一些语句进行设置,使之作用于前面所有UNION。

JOIN

{LEFT OUTER | RIGHT OUTER | FULL OUTER | INNER} JOIN,四种JOIN操作。这里忽视CROSS JOIN 笛卡尔积的方式。

  • INNER JOIN:如果表中有至少一个匹配,则返回行,关键字INNER可省。
  • LEFT OUTER JOIN:即使右表中没有匹配,也从左表返回所有的行。
  • RIGHT OUTER JOIN:即使左表中没有匹配,也从右表返回所有的行。
  • FULL OUTER JOIN:只要其中一个表中存在匹配,则返回行。

连接条件,只允许AND连接的等值条件。只有在诸如MapReduct-based的分布式查询中可以使用不等值连接或者使用OR连接多个条件。支持通过括号指定JOIN的优先级。

NATURAL JOIN(自然连接) ,参与JOIN的两张表根据字段名字自动决定连接字段。

1
2
3
4
--表A的字段(key1, a1),表B的字段(key1, b1)
SELECT * FROM A NATURAL JOIN B;
-- 等价于
SELECT A.key1 as key1, A.a1, B.b1 FROM A INNER JOIN B ON A.key1 = B.key1;

LEFT SEMI JOIN(半连接),右表只用来过滤左表的数据而不出现在结果集中。

1
2
SELECT * FROM A LEFT SEMI JOIN B ON A.id=B.id;
-- 只要A中某行的id在B的所有id中出现过,此行就保留在结果集中

LEFT ANTI JOIN(反连接),当JOIN条件不成立时返回左表中的数据。

1
2
SELECT * FROM A LEFT ANTI JOIN B ON A.id=B.id;
-- 只要A中某行的id在B的所有id中都没有出现过,此行就保留在结果集中

HAVING

聚合函数无法和WHERE合用,但是可以和HAVING合用来做条件过滤。

1
2
3
SELECT a, SUM(b) FROM A
GROUP BY a
HAVING SUM(b)<2000;

子查询SUBQUERY

普通的select是从几张表中读数据,但查询的对象也可以是另外一个SELECT操作。

1
SELECT A.a, A.b FROM (SELECT * FROM AA) A JOIN B ON A.a=B.a;

因为SELECT的返回值实际上是一个集合,所以也可以联合IN操作来进行数据判断。

1
SELECT * FROM A WHERE a NOT IN (SELECT a FROM B);

NOT IN SUBQUERY类似于LEFT ANTI JOIN,但是不同的一点是,如果B中有任何为NULL的列,则 NOT IN表达式会为NULL,导致WHERE条件不成立,无数据返回,此时与LEFT ANTI JOIN不同。

当SUBQUERY的输出结果为单行单列的时候(例如SUBQUERY的SELECT是COUNT函数),可以当做标量来使用。以SELECT COUNT()为例,输出结果是一个row set,但可以判断这条语句的输出有且仅有一行一列,因此可以将其当做标量使用。例如:

1
2
SELCT * FROM A WHERE (SELECT COUNT(*) FROM B WHERE A.a = B.a) > 1;
-- 这里的COUNT的结果直接作为标量与1进行计算

注意,能当成标量来使用的SUBQUERY必须是在编译阶段就能够确认返回结果只有一行一列的查询,如果一条语句,即使能够确定在实际运行过程中只会产生一行数据,但是编译过程中确定不了,编译器也是会报错。也就是说并不是根据返回数据的条数判断,而是根据函数本身的返回值判断的。该使用方式只能发生在WHERE中。

窗口函数

可以使用窗口函数进行灵活的分析处理工作,窗口函数只能出现在select子句中,窗口函数中不能嵌套使用窗口函数和聚合函数,且不可以和同级别的聚合函数一起使用。窗口函数的作用是开某扇窗,从某个角度看数据

1
2
3
4
window_func() OVER (PARTITION BY a ORDER BY b [windowing_clause]) as name
-- partition by部分用来指定开窗的列。分区列的值相同的行被视为在同一个窗口内。
-- order by用来指定数据在一个窗口内如何排序。
-- windowing_clause部分可以用rows指定开窗方式
  • COUNT,计数。
  • AVG,取均值。
  • MAX,取最大值。
  • MIN,取最小值。
  • MEDIAN,取中位数。
  • STDDEV,计算总体标准差。
  • STDDEV_SAMP,计算样本标准差。
  • SUM,求和。
  • DENSE_RANK,计算连续排名。
  • RANK,计算排名。
  • LAG,按偏移量取当前行之前第几行的值,如当前行号为rn,则取行号为rn-offset的值。
  • LEAD,按偏移量取当前行之后第几行的值,如当前行号为rn则取行号为rn+offset的值。
  • PERCENT_RANK,计算一组数据中某行的相对排名。
  • ROW_NUMBER,用于计算行号,从 1 开始。
  • CLUSTER_SAMPLE,分组抽样。
  • NTILE,将分组数据按照顺序切分成n片,并返回当前切片值,如果切片不均匀,默认增加第一个切片的分布。
  • cume_dist,用于取得累计分布,相当于求分组中值小于等于当前值的行数占分组总行数的比例。

其他

LATERAL VIEW

分布式SQL独有的,通常和SPLITEXPLODE等UDTF(User-Defined Table-Generating Functions)一起使用。它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。

  • LATERAL VIEW首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行,LATERAL VIEW再把结果聚合,产生一个支持别名表的虚拟表。

  • 相当于该函数的作用对象是一个Table,然后用explode函数来作用到具体的某一个list上,输出是一个新的row set。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT id, b FROM A LATERAL VIEW explode(A_list) T AS b;
    -- 假设原来的每行是(id, A_list),(1, [1,2,3])
    -- 打散之后的输出是(1,1),(1,2),(1,3)
    SELECT id, c FROM A LATERAL VIEW explode(SPLIT(A_string, ' ')) T AS c;
    -- 假设原来的每行是(id, A_string),(1, 'a b c')
    -- 打散之后的输出是(1,a),(1,b),(1,c)
    SELECT b, c FROM A LATERAL VIEW explode(A_list) T1 AS b
    LATERAL VIEW explode(A_string) T2 AS c;
    -- 也可以同时多条LATERAL VIEW同时打散,产生更多组合
    -- 假设原来的每行是(A_list, A_string),([1,2], 'a b')
    -- 打散之后的输出是(1,a),(1,b),(2,a),(2,b)

GROUPING SETS

如果同时要对好几列分别做聚合,传统的方法需要写很多UNION ALL语句,但是GROUPING SETS只需要一句。

1
2
3
4
5
6
7
8
9
10
11
SELECT a,b,c,COUNT(*) FROM A GROUP BY a,b,c GROUPING SETS ((a,b), (c), ());
-- 相当于逐层UNION
SELECT NULL, NULL, NULL, COUNT(*)
FROM A
UNION ALL
SELECT a, b, NULL, COUNT(*)
FROM A GROUP BY a, b
UNION ALL
SELECT NULL, NULL, c, COUNT(*)
FROM A GROUP BY c;
-- 对于不使用的变量,使用NULL充当占位符,这样可以使得结果集做UNION操作

为了区分占位符NULL和真正的NULL,GROUPING(一个列名)GROUPING_ID(一个或多个列名)函数就出现了。GROUPING(a)如果列a的NULL是占位符则返回1,否则返回0。GROUPING_ID的作用则是将参数列的GROUPING结果按照BitMap的方式组成整数。

CUBE & ROLLUP

CUBEROLLUP可以认为是特殊的GROUPING SETS

CUBE会枚举指定列的所有可能组合作为GROUPING SETS,而ROLLUP会以按层级聚合的方式产生GROUPING SETS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
GROUP BY CUBE(a, b, c) 等价于 GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())
GROUP BY ROLLUP(a, b, c) 等价于 GROUPING SETS((a,b,c),(a,b),(a), ())
GROUP BY CUBE ( (a, b), (c, d) ) 等价于 GROUPING SETS (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)
GROUP BY ROLLUP ( a, (b, c), d ) 等价于GROUPING SETS (
( a, b, c, d ),
( a, b, c ),
( a ),
( )
)
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e)) 等价于 GROUP BY GROUPING SETS (
(a, b, c, d), (a, b, c, e),
(a, b, d), (a, b, e),
(a, c, d), (a, c, e),
(a, d), (a, e)
)
GROUP BY grouping sets((b), (c),rollup(a,b,c)) 等价于 GROUP BY GROUPING SETS (
(b), (c),
(a,b,c), (a,b), (a), ()
)

执行计划查询(EXPLAIN)

用来显示对应于DML语句的最终执行计划结构的描述。所谓执行计划就是最终用来执行SQL语义的程序。

Common Table Expression(CTE)

提高SQL语句的可读性与执行效率。

视图View

略。

本文来源:「想飞的小菜鸡」的个人网站 vodkazy.cn

版权声明:本文为「想飞的小菜鸡」的原创文章,采用 BY-NC-SA 许可协议,转载请附上原文出处链接及本声明。

原文链接:https://vodkazy.cn/2021/01/04/关于SQL的一些非SELECT知识点

支付宝打赏 微信打赏

如果文章对你有帮助,欢迎点击上方按钮打赏作者,更多文章请访问想飞的小菜鸡