本文将指出一些常见但却总是被忽略的错误, 请静下心来, 准备好提升查询技能吧!
本文转载自公众号 "读芯术"(ID:AI_Discovery)
本文将指出一些常见但却总是被忽略的错误, 请静下心来, 准备好提升查询技能吧!
让我们以一个虚构的业务为例. 假设你是亚马逊电子商务分析团队的一员, 需要运行几个简单的查询. 你手头有两个表, 分别为 "product(产品)" 和 "discount (折扣)".
1. 计算 NULL 字段的数目
为了计算 null 字段的数目, 要掌握 COUNT 函数的工作原理. 假设计算产品数量时, 要求计入表格 "product" 的 "product id" 主键列中遗漏的字段.
- SELECT COUNT(product_id)
- FROM product;Result: 3
由于要求计入 "product id" 列中的 null 值, 查询结果应该为 4, 但 COUNT()函数不会将 null 值计数.
解决方法: 使用 COUNT(*)函数. 该函数可以将 null 值计数.
- Select Count(*)
- From product;Result: 4
这个操作很简单, 但是在编写复杂的查询时总会被忽略.
2. 使用保留字作为列名
- SELECT product_id,
- RANK() OVER (ORDER BY price desc) As Rank
- FROM product;
由于列名 "Rank" 是 Rank 函数的保留字, 该查询结果出错.
解决方法:
- SELECT product_id,
- RANK() OVER (ORDER BY price desc) As 'Rank'
- FROM product;
加上单引号, 即可得到想要的结果.
3.NULL 的比较运算
- SELECT product_name
- FROM product
- WHERE product_id=NULL;
由于使用了比较运算符 "=", 此处运算会出现异常, 使用另一比较运算符 "!=" 运算也会出现异常. 这里的逻辑问题在于, 你编写的查询得出的是 "product id" 列的值是否未知, 而无法得出这一列的值是否是未知的产品.
解决方法:
- SELECT product_name
- FROM product
- WHERE product_id ISNULL;
4.ON 子句过滤和 WHERE 子句过滤的区别
这是一个非常有趣的概念, 会提高你对于 ON 子句过滤和 WHERE 子句过滤之间区别的基本理解. 这并不完全是一个错误, 只是演示了两者的用法, 你可以根据业务需求选择最佳方案.
- SELECT d.product_id,
- price,
- discount
- FROM product p RIGHT JOIN discount d ON
- p.product_id=d.product_id
- WHERE p.product_id>1;
结果:
在这种情况下, 过滤条件在两个表格连接之后生效. 因此, 所得结果不包含 d.product_id≤1 的行(同理, 显然也不包含 p.product≤1 的行).
解决方法: 使用 AND, 注意结果上的不同.
- SELECT d.product_id,
- price,
- discount
- FROM product p RIGHT JOIN discount d ON
- p.product_id=d.product_id
- AND p.product_id>1;
结果:
在这里, 条件语句 AND 在两个表格连接发生之前计算. 可以把此查询看作只适用于一个表 ("product" 表) 的 WHERE 子句. 现在, 由于右连接, 结果中出现了 d.product_id≤1 的行(显然还有 p.product_id>1 的行).
请注意, ON 子句过滤和 WHERE 子句过滤只在左 / 右 / 外连接时不同, 而在内连接时相同.
5. 在同一查询的 WHERE 子句中使用 Windows 函数生成的列 & 使用 CASE WHEN 子句
注意, 不要在同一查询的 WHERE 子句中使用通过 Windows 函数生成的列名以及 CASE WHEN 子句.
- SELECT product_id,
- RANK() OVER (ORDER BY price desc) AS rk
- FROM product
- WHERE rk=2;
因为列 rk 由 Windows 函数生成, 并且在同一查询的 WHERE 子句中使用了该列, 这个查询结果会出现异常.
解决方法: 这一问题可以通过使用临时表或者子查询解决.
- WITH CTE AS
- (
- SELECT product_id,
- RANK() OVER (ORDER BY price desc) AS rk
- FROM product
- )
- SELECT product_id
- FROM
- CTE
- WHERE rk=2;
或
- SELECT product_id
- FROM
- (
- SELECT product_id,
- RANK() OVER (ORDER BY price desc) AS rk
- FROM product;
- )
- WHERE rk=2;
同样的方法也适用于使用 CASE WHEN 子句创建的列. 请记住, Windows 函数只能出现在 SELECT 或 ORDER BY 子句中.
6.BETWEEN 的使用不正确
如果不清楚 BETWEEN 的有效范围, 也许会得不到想要的查询结果. BETWEEN x AND y 语句的有效范围包含 x 和 y.
- SELECT *
- FROM discount
- WHERE offer_valid_till BETWEEN '2019/01/01' AND '2020/01/01'
- ORDER BY offer_valid_till;
结果:
在查询中, 也许我们只想得到 2019 年的所有日期, 但是结果中还包含了 2020 年 1 月 1 日. 这是因为 BETWEEN 语句的有效范围包含 2019/01/01 和 2020/01/01.
解决方法: 相应地调整范围可以解决这个问题.
- SELECT *
- FROM discount
- WHERE offer_valid_till BETWEEN '2019/01/01' AND '2019/12/31'
- ORDER BY offer_valid_till;
结果:
现在, 所有查询结果均为 2019 年的日期.
7. 在 GROUP BY 语句后使用 WHERE 子句
在编写 GROUP BY 语句时, 请注意 WHERE 子句的位置.
- SELECT category,
- AVG (price)
- FROM product p INNER JOIN discount d ON
- p.product_id=d.product_id
- GROUP BY category
- WHERE discount_amount>10;
由于将 WHERE 子句放在 GROUP BY 语句后, 此查询是错误的. 这是为什么呢?
WHERE 子句用于过滤查询结果, 这一步要在将查询结果分组之前实现, 而不是先分组再过滤. 正确的做法是先应用 WHERE 条件过滤减少数据, 再使用 GROUP BY 子句通过聚合函数将数据分组(此处使用聚合函数 AVG).
解决方法:
- SELECT category,
- AVG (price)
- FROM product p INNER JOIN discount d ON
- p.product_id=d.product_id
- WHERE discount_amount>10
- GROUP BY category;
请注意主要 SQL 语句的执行顺序:
FROM 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
SELECT 子句
ORDER BY 子句
以上包含了大部分让人不解的错误, 尤其是对初学者而言. 正如亨利. 福特所说:"唯一的错误是我们从中学不到任何东西", 希望这篇文章能帮助你精进查询技能.
来源: http://database.51cto.com/art/202101/642398.htm