避免！5个编写SQL查询时常出现的错误

全文共3063字，预计学习时长9分钟

来源：sunsplash

 

SQL被广泛应用于数据分析和数据提取。易上手，受到业内人士的一致好评

尽管刚开始编写SQL相当容易，但是出错率也是相当的高。

 

下面是小芯整理的，在编写SQL查询代码时大家经常犯的5个错误。

 

示例很短，可能看起来很简单。但是，在处理更大的查询时，这些错误可就不会一目了然了。其中一些示例是特定于AWS Redshift的，而另一些则会出现在其他SQL数据库（Postgres、MySQL等）。这些示例应该在本地数据库上运行，或者可以使用SQLFiddle在线运行。

 

示例SQL查询可下载。

来源：Pexels

 

设定

 

创建两个临时表，其中有几个条目有助于处理示例。

 

Sales表

 

该表包含带有时间戳、产品、价格等的销售条目。请注意，key列是唯一的，其他列中的值可以重复（例如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表

 

 

该表包含某一天每小时的延迟时间。请注意，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.按相同时间戳排序

 

检索每种产品最近一次的售价:

 

SELECT price

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

WHERE ix = 1;

 

以上查询的问题是多个销售具有相同的时间戳。此查询在相同数据上的连续运行可能得出不同的结果。下图可见，产品0在2019-11-11-08 01:00有两次销售，价格分别为1.2和1.3。

 

用下一个错误修复这个查询:)

 

 

 

2. 根据条件计算平均值

 

计算完成销售的产品的平均价格。值是（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;

当运行查询时，值为0.9。为什么？因为发生了这一计算：（1.1+0+1.3+0+1.5+1.5）/6是0.9。查询中的错误是，将0设置为不应包含的项。应使用NULL而不是0。

SELECT avg(price)

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

当前，输出和预计一样是1.35。

 

3.计算整数列的平均值

计算含有整数的product列的平均值。

SELECT avg(product)

FROM sales;

Product列中有3个0和3个1，预估平均值为0.5。大多数数据库（例如最新版本的Postgres）将返回0.5，但是Redshift将返回0，因为它不会自动将product列强制转换为float。因此需要将其强制转换为float类型：

 

SELECT avg(product::FLOAT)

FROM sales;

 

4. 内连接

假设要对每天的所有销售延迟进行汇总，并计算每天的平均销售价格。

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;

 

结果是错误的！以上查询将hourly_delay表中的delay列乘以倍数，如下图所示。这是因为按时间戳连接，该时间戳在hourly_delay表中是唯一的，但在sales表中会重复。

 

 

 

为了修复这个问题，要在一个单独的子查询中为每个表计算统计信息，然后连接汇总。这使得时间戳在两个表中都是唯一的。

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

对上述错误的补救是显而易见的。将key列添加到ORDER BY，这样一来，查询结果就可以在相同数据上重复出现——快速修复。

 

SELECT price

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

WHERE ix = 1;

 

 

为什么查询结果不同于上一次运行？在进行“快速修复”时，key列被放在了ORDER BY中的错误位置。它应该在DESC语句之后，而不是之前。查询现在将返回第一笔销售，而不是最后一笔销售。再进行一次修正。

 

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;

 

 

本次修复使结果可重复。

来源：Pexels

 

这些都是大家经常踩雷的SQL错误和解决方案。不知道你是否也感同身受，或者还有其他有关SQL查询的趣事？记得给小芯分享哟~

 

 

 

留言 点赞 关注

我们一起分享AI学习与发展的干货
欢迎关注全平台AI垂类自媒体 “读芯术”

（添加小编微信：dxsxbb，加入读者圈，一起讨论最新鲜的人工智能科技哦～）