本文轉(zhuǎn)載自公眾號“讀芯術(shù)”(ID：AI_Discovery)

 本文將指出一些常見但卻總是被忽略的錯(cuò)誤，請靜下心來，準(zhǔn)備好提升查詢技能吧!

[[377879]]

讓我們以一個(gè)虛構(gòu)的業(yè)務(wù)為例。假設(shè)你是亞馬遜電子商務(wù)分析團(tuán)隊(duì)的一員，需要運(yùn)行幾個(gè)簡單的查詢。你手頭有兩個(gè)表，分別為“product(產(chǎn)品)”和“discount (折扣)”。

 

 

1.計(jì)算NULL字段的數(shù)目

為了計(jì)算null字段的數(shù)目，要掌握COUNT函數(shù)的工作原理。假設(shè)計(jì)算產(chǎn)品數(shù)量時(shí)，要求計(jì)入表格“product”的“product id”主鍵列中遺漏的字段。

 

SELECT COUNT(product_id) 
FROM product;Result: 3 

 

由于要求計(jì)入“product id”列中的null值，查詢結(jié)果應(yīng)該為4，但COUNT()函數(shù)不會將null值計(jì)數(shù)。

解決方法：使用COUNT(*)函數(shù)。該函數(shù)可以將null值計(jì)數(shù)。

 

Select Count(*) 
From product;Result: 4 

 

這個(gè)操作很簡單，但是在編寫復(fù)雜的查詢時(shí)總會被忽略。

2.使用保留字作為列名

 

SELECT product_id, 
RANK() OVER (ORDER BY price desc) As Rank 
FROM product; 

 

由于列名“Rank”是Rank函數(shù)的保留字，該查詢結(jié)果出錯(cuò)。

解決方法：

 

SELECT product_id, 
RANK() OVER (ORDER BY price desc) As ‘Rank’ 
FROM product; 

 

加上單引號，即可得到想要的結(jié)果。

3.NULL的比較運(yùn)算

 

SELECT product_name 
FROM product 
WHERE product_id=NULL; 

 

由于使用了比較運(yùn)算符“=”，此處運(yùn)算會出現(xiàn)異常，使用另一比較運(yùn)算符“!=”運(yùn)算也會出現(xiàn)異常。這里的邏輯問題在于，你編寫的查詢得出的是“product id”列的值是否未知，而無法得出這一列的值是否是未知的產(chǎn)品。

解決方法：

 

SELECT product_name 
FROM product 
WHERE product_id ISNULL; 

 

4.ON子句過濾和WHERE子句過濾的區(qū)別

這是一個(gè)非常有趣的概念，會提高你對于ON子句過濾和WHERE子句過濾之間區(qū)別的基本理解。這并不完全是一個(gè)錯(cuò)誤，只是演示了兩者的用法，你可以根據(jù)業(yè)務(wù)需求選擇最佳方案。

 

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; 

 

結(jié)果：

 

 

在這種情況下，過濾條件在兩個(gè)表格連接之后生效。因此，所得結(jié)果不包含d.product_id≤1的行(同理，顯然也不包含p.product≤1的行)。

解決方法：使用AND，注意結(jié)果上的不同。

 

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; 

 

結(jié)果：

 

 

在這里，條件語句AND在兩個(gè)表格連接發(fā)生之前計(jì)算。可以把此查詢看作只適用于一個(gè)表(“product”表)的WHERE子句。現(xiàn)在，由于右連接，結(jié)果中出現(xiàn)了d.product_id≤1的行(顯然還有p.product_id>1的行)。

請注意，ON子句過濾和WHERE子句過濾只在左/右/外連接時(shí)不同，而在內(nèi)連接時(shí)相同。

5.在同一查詢的WHERE子句中使用Windows函數(shù)生成的列&使用CASE WHEN子句

注意，不要在同一查詢的WHERE子句中使用通過Windows函數(shù)生成的列名以及CASE WHEN子句。

 

SELECT product_id, 
RANK() OVER (ORDER BY price desc) AS rk 
FROM product 
WHERE rk=2; 

 

因?yàn)榱衦k由Windows函數(shù)生成，并且在同一查詢的WHERE子句中使用了該列，這個(gè)查詢結(jié)果會出現(xiàn)異常。

解決方法：這一問題可以通過使用臨時(shí)表或者子查詢解決。

 

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子句創(chuàng)建的列。請記住，Windows函數(shù)只能出現(xiàn)在SELECT或ORDER BY子句中。

6.BETWEEN的使用不正確

如果不清楚BETWEEN的有效范圍，也許會得不到想要的查詢結(jié)果。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; 

 

結(jié)果：

 

 

在查詢中，也許我們只想得到2019年的所有日期，但是結(jié)果中還包含了2020年1月1日。這是因?yàn)锽ETWEEN語句的有效范圍包含2019/01/01和2020/01/01。

解決方法：相應(yīng)地調(diào)整范圍可以解決這個(gè)問題。

 

SELECT * 
FROM discount 
WHERE offer_valid_till BETWEEN ‘2019/01/01’ AND ‘2019/12/31’ 
ORDER BY offer_valid_till; 

 

結(jié)果：

 

 

現(xiàn)在，所有查詢結(jié)果均為2019年的日期。

7.在GROUP BY語句后使用WHERE子句

在編寫GROUP BY語句時(shí)，請注意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語句后，此查詢是錯(cuò)誤的。這是為什么呢?

WHERE子句用于過濾查詢結(jié)果，這一步要在將查詢結(jié)果分組之前實(shí)現(xiàn)，而不是先分組再過濾。正確的做法是先應(yīng)用WHERE條件過濾減少數(shù)據(jù)，再使用GROUP BY子句通過聚合函數(shù)將數(shù)據(jù)分組(此處使用聚合函數(shù)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語句的執(zhí)行順序：

• FROM 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• SELECT 子句
• ORDER BY 子句

以上包含了大部分讓人不解的錯(cuò)誤，尤其是對初學(xué)者而言。正如亨利·福特所說：“唯一的錯(cuò)誤是我們從中學(xué)不到任何東西”，希望這篇文章能幫助你精進(jìn)查詢技能。