如何通过格式良好的SQL提高效率和准确性

日期:2015-4-27作者:Mpumelelo Msimanga翻译:冯昀辉 来源:TechTarget中国

SQL   格式化   查询   

【TechTarget中国原创】

背景

格式良好的SQL并不会比乱七八糟的SQL运行效果更好。数据库其实不怎么关心SQL语句中你把逗号放到了字段名的前面还是后面。为了你自己思路清楚,应该做一个有效率的SQL编写者,我建议你遵守以下这些格式规则。在本文中我将分享如何通过格式良好的SQL语句提升生产率。我定义的效率指的是能从SQL输出准确的结果,并且代码清晰易于理解、修改和调试。我只列出了“SELECT”语句,因为我写的SQL语句99%都是查询语句。格式化SQL代码是非常个性化的事,我也很清楚因人而异,开发者都认为自己的格式化规则是最合理的。

样例问题

下面是一个典型的SQL应用场景,业务报表的数据来自三张表,客户表、销售表和地域表。基于2015年一月份的数据,该报表需要展示在每个行政区内的客户总数和销量总数。该需求通过一个简单的SQL语句就可以实现,需要关联查询三张表。

数据可能出现的问题

虽然SQL很简单,但保证你的结果正确仍然是真正的关键,因为有下面一些原因可能导致错误:

数据可能来自不同的数据源。也就是说你不能保证这几个表之间的完整性。具体举例来说,你不能假定客户表中所有的邮政编码都是有效的邮政编码,并且一定在地域表中存在。

录入客户表数据的应用可能捕获到未经验证的地点数据,可能会包括错误的邮政编码。

邮政编码表可能不是完整的。新发布的邮政编码可能没有在发布后及时导入到表中。


第一原则

对我来说,相比于编写清晰易读的SQL,从SQL得到正确的结果才是第一要务。我要做的第一件事就是编写下面的SQL语句来获取客户总数。在我写完整个语句之后我会再调整它。

我写的第一个语句是这样的:


SELECTCOUNT(DISTINCT cust_id) as count_customersFROMcustomers

Result:

count_customers
“10”


这个查询很重要,因为它紧紧围绕第一原则。因为没有SQL管理查询,也就没有依赖,我知道这就是客户数量的正确结果。我把这个结果记下来,因为我总需要拿这个数字来衡量后面的SQL(是否正确),在本文后面也会多次提到。

下一步要做的事就是添加必要的字段和表完成查询。我特意把“添加”这个词高亮标注出来,因为根据我的规则,我会在应用第一原则时把能获取相同结果的查询注释掉。下面就是我最终格式化的查询语句。

格式化SQL

下面就是根据我的格式化思路推荐的格式化SQL。

 

01.SELECT
02.0
03.,c.cust_post_code
04.,p.location
05.,COUNT(DISTINCT c.cust_id) number_customers
06.,SUM(s.total_amount) as total_sales
07.FROM
08.customers c
09.JOIN post_codes p ON c.cust_post_code = p.post_code
10.JOIN sales s ON c.cust_id = s.cust_id
11.WHERE
12.1=1
13.AND s.sales_date BETWEEN ‘2015-01-01’ AND ‘2015-01-31’
14.—AND s.order_id = 5
15.GROUP BY
16.c.cust_post_code
17.,p.location


总是使用表别名

时间会证明这么做是有必要的。如果你没有对SQL语句中用到的每个字段使用别名,在将来某个时候可能会给这个查询语句添加进来别的同名字段。到那时候你的查询乃至报表就会产生错误(出现了重名字段名)。

逗号放到字段之前

在调试或者测试我的查询语句时,这么做可以方便地注释掉某个字段,而不需要修改其它行,所有的逗号都没有缺少或多余。不这么做的话你可能总要调整逗号才能保证语句正确。如果你经常要调试语句,这么做会带来极大方便,效率会更高。这个做法对“SELECT”部分和“GROUP BY”子句部分同样适用。

在开发时我使用“SELECT 0”作为语句的开始,迁移到正式环境时它很容易删除掉。这样我们就可以在后面所有字段前面都写都好了。没有这个“0”的话,如果我想注释掉第一个字段(本例中是“c.cust_post_code”),我就必须处理后面的逗号问题。我必须临时注释掉它,将来还要加回来。在“GROUP BY”语句中也是一样的。这个“0”是额外加的。

把“JOIN”放到独立行

把“JOIN”语句放到独立行有以下好处:

这么做很容易看到本查询语句涉及的所有表,只需要看滚动“JOIN”语句就可以了。

使用“JOIN”相比于在“WHERE”子句中列出所有表和表达式关系,可以把所有逻辑关系都放到一个地方。我们不可能总是吧“JOIN”语句放到一行中,但是至少应该放到一起。

这么做的话要注释掉“JOIN”语句也是相对容易的。这在调试时非常有用,你可能需要知道是否是“JOIN”引起了数据问题。

列模式编辑

在处理大量字段的情况时,列模式编辑非常方便。下面是我曾经做过的第一个动态GIF展示,你可以注释掉所有非聚集字段。我使用了列模式编辑,而不仅仅是注释掉字段:

创建全部索引

在使用字段较多的UNION语句时:

注释掉“GROUP BY”子句的字段清单

 

测试查询结果

我必须使用外连接“OUTER”列出所有客户,因为不是所有客户的邮政编码都在地域表里有相应的邮政编码。我可以通过包含和排除不同字段和表反复操作来确保我查询的结果与最开始那个查询(单独查询客户的那个语句)结果相同,这其实是对第一原则的遵守。


SELECT0,c.cust_post_code—,p.location,COUNT(DISTINCT c.cust_id) number_customers,SUM(s.total_amount) as total_salesFROMcustomers c—LEFT OUTER JOIN post_codes p ON c.cust_post_code = p.post_codeJOIN sales s ON c.cust_id = s.cust_idWHERE1=1AND s.sales_date BETWEEN ‘2015-01-01’ AND ‘2015-01-31’—AND c.cust_post_code = 2000—AND p.post_code = 200GROUP BYc.cust_post_code—,p.location


像这样的SQL对我来说意味着我必须写独立的测试来检查数据。通过注释掉的那几行语句我可以使用第一原则验证我查询数据的准确性。这么做提高了我的效率和报表的准确性。

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

评论
查看更多评论

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

Oracle SQL>更多

相关推荐

  • Azure数据湖分析从U-SQL中获得提升

    大数据的发展已经让许多精通SQL的数据专业人员不知所措。微软的U-SQL编程语言试图让这些人回归数据查询游戏。

  • TT百科:SQL(结构化查询语言)

    一般来说,SQL-on-Hadoop仍是一项新兴技术,但随着各个公司寻求获得拥有大数据应用程序编程SQL技能的开发和分析人员,它们正逐渐成为Hadoop部署的固定组件。

  • SQL和NoSQL数据库设计之争

    企业收集了很多大规模增长的松散结构化数据,Hadoop,Spark以及其他新技术处理这些数据非常有助于改善商业智能分析效率。

  • 全面梳理SQL和NoSQL数据库的技术差别

    诚然,在互联网上有成千上万关于选择SQL还是NoSQL的辩论。但是,这两者是不是必须站在对立面战斗呢?

技术手册>更多

  • Oracle数据库Web开发工具指南

    在进行Oracle Web开发的时候,程序员再也没有理由说自己找不到合适的开发工具了。在过去几年中,出现了许多基于Oracle数据库的Web开发工具,这些工具可以帮助程序员更加快速高效便捷地进行Web应用开发。

  • Oracle-Sun服务器硬件指南

    合格的DBA除了要了解数据库基本知识以外,当然也要掌握相关的硬件服务器的知识,因为选择合适的硬件配置是优化数据库和应用性能的关键一环。

  • SQL Server高可用性指南

    “高可用性”(High Availability)通常来描述一个系统经过专门的设计,从而减少停工时间,而保持其服务的高度可用性。身为DBA的你需要了解它们都是什么以及它们的区别都有哪些,这样才能使你针对SQL Server环境做出最佳选择。

  • Oracle DBA成功的必备手册

    本系列文章由三部分组成,为Oracle数据库管理员(DBA)面试成功的必备手册。本专题内容囊括从DBA最初的面试开始,从写“杀手简历”到求职信、到面试过程到Oracle认证再到上升到公司高层最后到你成为公司里的明星DBA。专家为你一一指点迷津,最终让你登上成功的宝座。

TechTarget

最新资源
  • 安全
  • 存储
  • CIO
  • SOA
  • 云计算
  • 商务智能