|
|
51CTO旗下网站
|
|
移动端

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

SQL被广泛应用于数据分析和数据提取。易上手,受到业内人士的一致好评。尽管刚开始编写SQL相当容易,但是出错率也是相当的高。

作者:读芯术来源:今日头条|2019-12-03 18:51

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

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

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

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

示例SQL查询可下载。

设定

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

Sales表

该表包含带有时间戳、产品、价格等的销售条目。请注意,key列是唯一的,其他列中的值可以重复(例如ts列)。

  1. DROP TABLE IF EXISTSsales; 
  2.  
  3. CREATE TEMPORARY TABLE sales 
  4.  
  5.  
  6. key varchar(6), 
  7.  
  8. ts timestamp
  9.  
  10. product integer
  11.  
  12. completed boolean, 
  13.  
  14. price float 
  15.  
  16. );INSERT INTO sales 
  17.  
  18. VALUES ('sale_1''2019-11-08 00:00', 0, TRUE, 1.1), 
  19.  
  20. ('sale_2''2019-11-08 01:00', 0, FALSE,1.2), 
  21.  
  22. ('sale_3''2019-11-08 01:00', 0, TRUE,1.3), 
  23.  
  24. ('sale_4''2019-11-08 01:00', 1, FALSE,1.4), 
  25.  
  26. ('sale_5''2019-11-08 02:00', 1, TRUE,1.5), 
  27.  
  28. ('sale_6''2019-11-08 02:00', 1, TRUE,1.5);SELECT * FROM sales; 
避免!5个编写SQL查询时常出现的错误

Hourly delay表

该表包含某一天每小时的延迟时间。请注意,ts列在下表中是唯一的。

  1. DROP TABLE IF EXISTShourly_delay; 
  2.  
  3. CREATE TEMPORARY TABLE hourly_delay 
  4.  
  5.  
  6. ts timestamp
  7.  
  8. delay float 
  9.  
  10. ); 
  11.  
  12. INSERT INTO hourly_delay 
  13.  
  14. VALUES ('2019-11-08 00:00', 80.1), 
  15.  
  16. ('2019-11-08 01:00', 100.2), 
  17.  
  18. ('2019-11-08 02:00', 70.3);SELECTFROM hourly_delay; 
避免!5个编写SQL查询时常出现的错误

1.按相同时间戳排序

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

  1. SELECT price 
  2.  
  3. FROM (SELECT price, row_number() OVER (PARTITION BYproduct ORDER BY ts DESCAS ix FROM sales) ASq1 
  4.  
  5. WHERE ix = 1; 
避免!5个编写SQL查询时常出现的错误

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

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

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

2. 根据条件计算平均值

计算完成销售的产品的平均价格。值是(1.1 + 1.3 + 1.5 + 1.5)/ 4,即1.35。

  1. SELECT avg(price) 
  2.  
  3. 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。

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

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

3.计算整数列的平均值

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

  1. SELECT avg(product) 
  2.  
  3. FROM sales; 

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

  1. SELECT avg(product::FLOAT
  2.  
  3. FROM sales; 

4. 内连接

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

  1. SELECT t2.ts::DATEsum(t2.delay),avg(t1.price) 
  2.  
  3. FROM hourly_delay AS t2 
  4.  
  5. INNER JOIN sales ASt1 ON t1.ts = t2.ts 
  6.  
  7. GROUP BY t2.ts::DATE
避免!5个编写SQL查询时常出现的错误

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

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

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

  1. SELECT t1.ts, daily_delay, avg_price 
  2.  
  3. FROM (SELECT t2.ts::DATEsum(t2.delay) ASdaily_delay FROM hourly_delay AS t2 GROUP BYt2.ts::DATEAS t2 
  4.  
  5. INNER JOIN (SELECTts::DATE AS ts, avg(price) AS avg_price FROM sales GROUPBY ts::DATEAS t1 ON t1.ts = t2.ts; 
避免!5个编写SQL查询时常出现的错误

5.将列添加到ORDER BY

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

  1. SELECT price 
  2.  
  3. FROM (SELECT price, row_number() OVER (PARTITION BYproduct ORDER BY ts, key DESCAS ix FROMsales) AS q1 
  4.  
  5. WHERE ix = 1; 
避免!5个编写SQL查询时常出现的错误

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

  1. SELECT product, price 
  2.  
  3. FROM (SELECT product, price, row_number() OVER (PARTITION BYproduct ORDER BY ts DESCkeyAS ix FROMsales) AS q1 
  4.  
  5. WHERE ix = 1; 
避免!5个编写SQL查询时常出现的错误

本次修复使结果可重复。

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

【编辑推荐】

  1. 分享一份MySQL一键优化脚本,值得收藏
  2. 一文看懂SQL Server数据库触发器概念、原理及案例
  3. 工作中遇到的99%SQL优化,这里都能给你解决方案
  4. 12 月 DB-Engines 数据库排行:SQLite 角逐前 10
  5. 详解Oracle数据库并行操作常见等待事件及脚本
【责任编辑:华轩 TEL:(010)68476606】

点赞 0
分享:
-->
大家都在看
猜你喜欢

订阅专栏+更多

服务器选型从入门到实战

服务器选型从入门到实战

政务云规划设计实战
共16章 | 51CTOsummer

425人订阅学习

骨干网与数据中心建设案例

骨干网与数据中心建设案例

高级网工必会
共20章 | 捷哥CCIE

417人订阅学习

中间件葡京游戏大厅平台安全防护攻略

中间件葡京游戏大厅平台安全防护攻略

4类葡京游戏大厅平台安全防护
共4章 | hack_man

157人订阅学习

读 书 +更多

网管员必读—网络应用

本书是一本介绍当前主流计算机网络应用技术的工具图书,全面总结了当前最主流、最基础的计算机网络应用,包括局域网和互联网应用两方面。在...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微