全文共3063字,预计学习时长9分钟
![26cff0931025a3cc53e7bfd12870f063.png](https://i-blog.csdnimg.cn/blog_migrate/d3c643af233b64369f854cbc09c7ee71.jpeg)
来源:sunsplash
SQL被广泛应用于数据分析和数据提取。易上手,受到业内人士的一致好评
尽管刚开始编写SQL相当容易,但是出错率也是相当的高。
下面是小芯整理的,在编写SQL查询代码时大家经常犯的5个错误。
示例很短,可能看起来很简单。但是,在处理更大的查询时,这些错误可就不会一目了然了。其中一些示例是特定于AWS Redshift的,而另一些则会出现在其他SQL数据库(Postgres、MySQL等)。这些示例应该在本地数据库上运行,或者可以使用SQLFiddle在线运行。
示例SQL查询可下载。
![347d636bbbf0acc0f2ae306977b90e23.png](https://i-blog.csdnimg.cn/blog_migrate/30b7bf1d74ab87ea7d7a1ff7f7850c9f.jpeg)
来源: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;
![eeb4ac72c4c540ca441f544fd6239fd8.png](https://i-blog.csdnimg.cn/blog_migrate/23f05e25172cef0bfb782f1e5699b87b.jpeg)
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;
![b0cd6d0866b5f5018eecef1b4b215dc2.png](https://i-blog.csdnimg.cn/blog_migrate/37bdc61c2550b3e8f467c469f64652f6.jpeg)
1.按相同时间戳排序
检索每种产品最近一次的售价:
SELECT price
FROM (SELECT price, row_number() OVER (PARTITION BYproduct ORDER BY ts DESC) AS ix FROM sales) ASq1
WHERE ix = 1;
![2c65e713b444b0bf68f8f0492f6d4bf0.png](https://i-blog.csdnimg.cn/blog_migrate/258b671dcb720cbf71d920309cbcfc3a.jpeg)
以上查询的问题是多个销售具有相同的时间戳。此查询在相同数据上的连续运行可能得出不同的结果。下图可见,产品0在2019-11-11-08 01:00有两次销售,价格分别为1.2和1.3。
![ac03034b20674bc70547f2c2a9e1133e.png](https://i-blog.csdnimg.cn/blog_migrate/7d20bc47f12eb95fafe6343760b2e5d8.jpeg)
用下一个错误修复这个查询:)
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;
![84f214ff86c17b6f52b7d94a7aced0e0.png](https://i-blog.csdnimg.cn/blog_migrate/04e3c99c589f937c3206644f2987feea.jpeg)
结果是错误的!以上查询将hourly_delay表中的delay列乘以倍数,如下图所示。这是因为按时间戳连接,该时间戳在hourly_delay表中是唯一的,但在sales表中会重复。
![0b19381fe66d2febc2c8bb5344dbb239.png](https://i-blog.csdnimg.cn/blog_migrate/be1355c935c518bb677584fd5aeca6cb.jpeg)
为了修复这个问题,要在一个单独的子查询中为每个表计算统计信息,然后连接汇总。这使得时间戳在两个表中都是唯一的。
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;
![0e00dc9b18d54949a5f039669325ff71.png](https://i-blog.csdnimg.cn/blog_migrate/ab30d96d6e1f3266bd5634095a4da5d5.jpeg)
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;
![08f2118d2c325c1a9e5e48f27a633856.png](https://i-blog.csdnimg.cn/blog_migrate/cde89035db48c5242656a4e1e101c8ac.jpeg)
为什么查询结果不同于上一次运行?在进行“快速修复”时,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;
![ffa163986b6635c39f25c1520691d5e8.png](https://i-blog.csdnimg.cn/blog_migrate/199cc045ea61e6f69a0c6c72def0136f.jpeg)
本次修复使结果可重复。
![f59b0069c173c0f35a36b8422e0ee3ba.png](https://i-blog.csdnimg.cn/blog_migrate/2ade6cf8a661c55faccf18e34721ebb1.jpeg)
来源:Pexels
这些都是大家经常踩雷的SQL错误和解决方案。不知道你是否也感同身受,或者还有其他有关SQL查询的趣事?记得给小芯分享哟~
![fb3fed72d47ccd512d1fc432f1b19ae6.gif](https://i-blog.csdnimg.cn/blog_migrate/3d869b55d924f57ebc2560e024d8238d.gif)
![bec03c97432441c632b70a07476cf475.png](https://i-blog.csdnimg.cn/blog_migrate/15f13912bb5623bbfdabef3f6d7c5bb1.jpeg)
留言点赞关注
我们一起分享AI学习与发展的干货
如转载,请后台留言,遵守转载规范