5個(gè)編寫(xiě)SQL查詢時(shí)常出現(xiàn)的錯(cuò)誤分別是什么

這篇文章將為大家詳細(xì)講解有關(guān)5個(gè)編寫(xiě)SQL查詢時(shí)常出現(xiàn)的錯(cuò)誤分別是什么，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

SQL被廣泛應(yīng)用于數(shù)據(jù)分析和數(shù)據(jù)提取。易上手，受到業(yè)內(nèi)人士的一致好評(píng)

盡管剛開(kāi)始編寫(xiě)SQL相當(dāng)容易，但是出錯(cuò)率也是相當(dāng)?shù)母摺?/p>

下面是小芯整理的，在編寫(xiě)SQL查詢代碼時(shí)大家經(jīng)常犯的5個(gè)錯(cuò)誤。

示例很短，可能看起來(lái)很簡(jiǎn)單。但是，在處理更大的查詢時(shí)，這些錯(cuò)誤可就不會(huì)一目了然了。其中一些示例是特定于AWS  Redshift的，而另一些則會(huì)出現(xiàn)在其他SQL數(shù)據(jù)庫(kù)(Postgres、MySQL等)。這些示例應(yīng)該在本地?cái)?shù)據(jù)庫(kù)上運(yùn)行，或者可以使用SQLFiddle在線運(yùn)行。

示例SQL查詢可下載。

設(shè)定

創(chuàng)建兩個(gè)臨時(shí)表，其中有幾個(gè)條目有助于處理示例。

Sales表

該表包含帶有時(shí)間戳、產(chǎn)品、價(jià)格等的銷售條目。請(qǐng)注意，key列是唯一的，其他列中的值可以重復(fù)(例如ts列)。

DROP TABLE IF EXISTSsales;  CREATE TEMPORARY TABLE sales  (  key varchar(6),  ts timestamp,  product integer,  completed boolean,  price float  );INSERT INTO sales  VALUES ('sale_1', '2019-11-08 00:00', 0, TRUE, 1.1),  ('sale_2', '2019-11-08 01:00', 0, FALSE,1.2),  ('sale_3', '2019-11-08 01:00', 0, TRUE,1.3),  ('sale_4', '2019-11-08 01:00', 1, FALSE,1.4),  ('sale_5', '2019-11-08 02:00', 1, TRUE,1.5),  ('sale_6', '2019-11-08 02:00', 1, TRUE,1.5);SELECT * FROM sales;

Hourly delay表

該表包含某一天每小時(shí)的延遲時(shí)間。請(qǐng)注意，ts列在下表中是唯一的。

DROP TABLE IF EXISTShourly_delay;  CREATE TEMPORARY TABLE hourly_delay  (  ts timestamp,  delay float  );  INSERT INTO hourly_delay  VALUES ('2019-11-08 00:00', 80.1),  ('2019-11-08 01:00', 100.2),  ('2019-11-08 02:00', 70.3);SELECT* FROM hourly_delay;

1.按相同時(shí)間戳排序

檢索每種產(chǎn)品最近一次的售價(jià):

SELECT price  FROM (SELECT price, row_number() OVER (PARTITION BYproduct ORDER BY ts DESC) AS ix FROM sales) ASq1  WHERE ix = 1;

以上查詢的問(wèn)題是多個(gè)銷售具有相同的時(shí)間戳。此查詢?cè)谙嗤瑪?shù)據(jù)上的連續(xù)運(yùn)行可能得出不同的結(jié)果。下圖可見(jiàn)，產(chǎn)品0在2019-11-11-08  01:00有兩次銷售，價(jià)格分別為1.2和1.3。

用下一個(gè)錯(cuò)誤修復(fù)這個(gè)查詢:)

2. 根據(jù)條件計(jì)算平均值

計(jì)算完成銷售的產(chǎn)品的平均價(jià)格。值是(1.1 + 1.3 + 1.5 + 1.5)/ 4，即1.35。

SELECT avg(price)  FROM (SELECT CASE WHEN completed = TRUETHEN price else 0 END AS price FROM sales) ASq1;

當(dāng)運(yùn)行查詢時(shí)，值為0.9。為什么?因?yàn)榘l(fā)生了這一計(jì)算：(1.1+0+1.3+0+1.5+1.5)/6是0.9。查詢中的錯(cuò)誤是，將0設(shè)置為不應(yīng)包含的項(xiàng)。應(yīng)使用NULL而不是0。

SELECT avg(price)  FROM (SELECT CASE WHEN completed = TRUETHEN price else NULL END AS price FROMsales) AS q1;

當(dāng)前，輸出和預(yù)計(jì)一樣是1.35。

3.計(jì)算整數(shù)列的平均值

計(jì)算含有整數(shù)的product列的平均值。

SELECT avg(product)  FROM sales;

Product列中有3個(gè)0和3個(gè)1，預(yù)估平均值為0.5。大多數(shù)數(shù)據(jù)庫(kù)(例如最新版本的Postgres)將返回0.5，但是Redshift將返回0，因?yàn)樗粫?huì)自動(dòng)將product列強(qiáng)制轉(zhuǎn)換為float。因此需要將其強(qiáng)制轉(zhuǎn)換為float類型：

SELECT avg(product::FLOAT)  FROM sales;

4. 內(nèi)連接

假設(shè)要對(duì)每天的所有銷售延遲進(jìn)行匯總，并計(jì)算每天的平均銷售價(jià)格。

SELECT t2.ts::DATE, sum(t2.delay),avg(t1.price)  FROM hourly_delay AS t2  INNER JOIN sales ASt1 ON t1.ts = t2.ts  GROUP BY t2.ts::DATE;

結(jié)果是錯(cuò)誤的!以上查詢將hourly_delay表中的delay列乘以倍數(shù)，如下圖所示。這是因?yàn)榘磿r(shí)間戳連接，該時(shí)間戳在hourly_delay表中是唯一的，但在sales表中會(huì)重復(fù)。

為了修復(fù)這個(gè)問(wèn)題，要在一個(gè)單獨(dú)的子查詢中為每個(gè)表計(jì)算統(tǒng)計(jì)信息，然后連接匯總。這使得時(shí)間戳在兩個(gè)表中都是唯一的。

SELECT t1.ts, daily_delay, avg_price  FROM (SELECT t2.ts::DATE, sum(t2.delay) ASdaily_delay FROM hourly_delay AS t2 GROUP BYt2.ts::DATE) AS t2  INNER JOIN (SELECTts::DATE AS ts, avg(price) AS avg_price FROM sales GROUPBY ts::DATE) AS t1 ON t1.ts = t2.ts;

5.將列添加到ORDER BY

對(duì)上述錯(cuò)誤的補(bǔ)救是顯而易見(jiàn)的。將key列添加到ORDER BY，這樣一來(lái)，查詢結(jié)果就可以在相同數(shù)據(jù)上重復(fù)出現(xiàn)——快速修復(fù)。

SELECT price  FROM (SELECT price, row_number() OVER (PARTITION BYproduct ORDER BY ts, key DESC) AS ix FROMsales) AS q1  WHERE ix = 1;

為什么查詢結(jié)果不同于上一次運(yùn)行?在進(jìn)行“快速修復(fù)”時(shí)，key列被放在了ORDER  BY中的錯(cuò)誤位置。它應(yīng)該在DESC語(yǔ)句之后，而不是之前。查詢現(xiàn)在將返回第一筆銷售，而不是最后一筆銷售。再進(jìn)行一次修正。

SELECT product, price  FROM (SELECT product, price, row_number() OVER (PARTITION BYproduct ORDER BY ts DESC, key) AS ix FROMsales) AS q1  WHERE ix = 1;

本次修復(fù)使結(jié)果可重復(fù)。

關(guān)于5個(gè)編寫(xiě)SQL查詢時(shí)常出現(xiàn)的錯(cuò)誤分別是什么就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。