服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - 数据库技术 - 可维护、可阅读的SQL代码的十个优秀实践

可维护、可阅读的SQL代码的十个优秀实践

2021-08-08 23:54今日头条闻数起舞 数据库技术

SQL很棒，它是数据分析，数据科学，数据工程甚至软件开发的基础之一。在本文中，我总结了编写SQL的最常见的优秀实践。

没有正确的指导，它很容易混淆SQL。由于团队中的每个人都可能有自己的写作SQL习惯，因此您可以快速结束一个没有人理解的令人困惑的代码。

您可能意识到遵循一套良好实践的重要性..这篇文章为您提供了您正在寻找的指导!

可维护、可阅读的SQL代码的十个优秀实践

1. 使用大写的关键字

让我们从一个基本开始：使用大写的SQL关键字，以及小写的表和列。使用SQL函数的大写(First_Value()，date_trunc()等)也是一个很好的做法。

避免：

  1. select id, name from company.customers 

而是：

  1. SELECT id, name FROM company.customers 

2. 使用Snake Case进行Schema，表，列的编写

编程语言在案例类型时具有最佳实践：Camelcase，Pascalcase，Kebabuic和Snake_Case是最常见的。

涉及SQL，Snake Case(有时称为下划线Case)是最广泛使用的约定。

避免：

  1. SELECT Customers.id,  
  2.        Customers.name,  
  3.        COUNT(WebVisit.id) as nbVisit 
  4. FROM COMPANY.Customers 
  5. JOIN COMPANY.WebVisit ON Customers.id = WebVisit.customerId 
  6. WHERE Customers.age <= 30 
  7. GROUP BY Customers.id, Customers.name 

而是：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        COUNT(web_visit.id) as nb_visit 
  4. FROM company.customers 
  5. JOIN company.web_visit ON customers.id = web_visit.customer_id 
  6. WHERE customers.age <= 30 
  7. GROUP BY customers.id, customers.name 

虽然有些人喜欢包括区分Schema，表和列的变体，但我建议使用Snake Case。

3. 在提高可读性时使用别名

众所周知，别名是重命名表或列没有意义的表格或列的便捷方式。当他们的名字并不有意义时，请随时向您的表和列提供别名，并别名。

避免：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        customers.context_col1, 
  4.        nested.f0_ 
  5. FROM company.customers 
  6. JOIN ( 
  7.           SELECT customer_id, 
  8.                  MIN(date) 
  9.           FROM company.purchases 
  10.           GROUP BY customer_id 
  11.       ) ON customer_id = customers.id 

而是：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        customers.context_col1 as ip_address, 
  4.        first_purchase.date    as first_purchase_date 
  5. FROM company.customers 
  6. JOIN ( 
  7.           SELECT customer_id, 
  8.                  MIN(date) as date 
  9.           FROM company.purchases 
  10.           GROUP BY customer_id 
  11.       ) AS first_purchase  
  12.         ON first_purchase.customer_id = customers.id 

我通常用小写的列别名，以及具有大写的表。

4. 格式化：仔细使用缩进和空格

即使它是一个基本的原则，使您的代码更具可读性是一个快速的胜利。正如您与Python一样，您应该标识您的SQL代码。

关键字后，以及使用子查询或派生表后。

避免：

  1. SELECT customers.id, customers.name, customers.age, customers.gender, customers.salary, first_purchase.date 
  2. FROM company.customers 
  3. LEFT JOIN ( SELECT customer_id, MIN(date) as date FROM company.purchases GROUP BY customer_id ) AS first_purchase  
  4. ON first_purchase.customer_id = customers.id  
  5. WHERE customers.age<=30 

而是：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        customers.age,  
  4.        customers.gender,  
  5.        customers.salary, 
  6.        first_purchase.date 
  7. FROM company.customers 
  8. LEFT JOIN ( 
  9.               SELECT customer_id, 
  10.                      MIN(date) as date  
  11.               FROM company.purchases 
  12.               GROUP BY customer_id 
  13.           ) AS first_purchase  
  14.             ON first_purchase.customer_id = customers.id 
  15. WHERE customers.age <= 30 

此外，请注意我们如何在where子句中使用白空格。

避免：

  1. SELECT id WHERE customers.age<=30 

而是：

  1. SELECT id WHERE customers.age <= 30 

5. 避免 Select *

值得提醒这种良好的做法。您应该明确关于要选择的内容，因此避免使用SELECT *。

选择使您的请求不清楚，因为它隐藏了查询背后的意图。另外，请记住，您的表可能会发展和影响选择。这就是为什么我不是除()教学的除外粉丝。

避免：

  1. SELECT * EXCEPT(id) FROM company.customers 

更喜欢：

  1. SELECT name, 
  2.        age, 
  3.        salary 
  4. FROM company.customers 

6. 使用ANSI-92 Join 语法

…而不是Join表的SQL where子句。虽然您可以使用where子句和join子句来联结表，但它是使用Join / ansi-92语法的最佳实践。

虽然性能方面没有差异，但是Join子句将关系逻辑与过滤器分开并提高可读性。

避免：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        COUNT(transactions.id) as nb_transaction 
  4. FROM company.customers, company.transactions 
  5. WHERE customers.id = transactions.customer_id 
  6.       AND customers.age <= 30 
  7. GROUP BY customers.id, customers.name 

而是：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        COUNT(transactions.id) as nb_transaction 
  4. FROM company.customers 
  5. JOIN company.transactions ON customers.id = transactions.customer_id 
  6. WHERE customers.age <= 30 
  7. GROUP BY customers.id, customers.name 

“Where基于条款”语法 - 也称为ANSI-89 - 比新的ANSI-92大，这就是为什么它仍然很常见。如今，大多数开发人员和数据分析师都使用Join语法。

7. 使用公共表表达式(CTE)

CTE允许您定义和执行查询，其中结果暂时存在，并且可以在更大的查询中使用。CTE可在大多数现代数据库上获得。

它类似于派生表，具有2个优点：

  • 使用CTE提高了查询的可读性
  • CTE是定义一次，然后可以多次参考

您用 WITH … AS：

  1. WITH my_cte AS( SELECT col1, col2 FROM table)SELECT * FROM my_cte 

避免：

  1. SELECT customers.id,  
  2.        customers.name,  
  3.        customers.age,  
  4.        customers.gender,  
  5.        customers.salary, 
  6.        persona_salary.avg_salary as persona_avg_salary, 
  7.        first_purchase.date 
  8. FROM company.customers 
  9. JOIN ( 
  10.           SELECT customer_id, 
  11.                  MIN(date) as date  
  12.           FROM company.purchases 
  13.           GROUP BY customer_id 
  14.       ) AS first_purchase  
  15.         ON first_purchase.customer_id = customers.id 
  16. JOIN ( 
  17.           SELECT age, 
  18.              gender, 
  19.              AVG(salary) as avg_salary 
  20.          FROM company.customers 
  21.          GROUP BY age, gender 
  22.       ) AS persona_salary  
  23.         ON persona_salary.age = customers.age 
  24.            AND persona_salary.gender = customers.gender 
  25. WHERE customers.age <= 30 

而是：

  1. WITH first_purchase AS 
  2. ( 
  3.    SELECT customer_id, 
  4.           MIN(date) as date  
  5.    FROM company.purchases 
  6.    GROUP BY customer_id 
  7. ), 
  8. persona_salary AS 
  9. ( 
  10.    SELECT age, 
  11.           gender, 
  12.           AVG(salary) as avg_salary 
  13.    FROM company.customers 
  14.    GROUP BY age, gender 
  15. ) 
  16. SELECT customers.id,  
  17.        customers.name,  
  18.        customers.age,  
  19.        customers.gender,  
  20.        customers.salary, 
  21.        persona_salary.avg_salary as persona_avg_salary, 
  22.        first_purchase.date 
  23. FROM company.customers 
  24. JOIN first_purchase ON first_purchase.customer_id = customers.id 
  25. JOIN persona_salary ON persona_salary.age = customers.age 
  26.                        AND persona_salary.gender = customers.gender 
  27. WHERE customers.age <= 30 

8. 有时，它可能值得分成多个查询

小心这个。让我们给出一些背景：

我经常在BigQuery上使用Airflow，转换数据和准备数据可视化上执行SQL查询。我们有一个工作流Orchestrator(气流)，以定义的顺序执行请求。在某些情况下，我们选择将复杂查询拆分为多个较小的查询。

代替：

  1. CREATE TABLE customers_infos AS 
  2. SELECT customers.id, 
  3.        customers.salary, 
  4.        traffic_info.weeks_since_last_visit, 
  5.        category_info.most_visited_category_id, 
  6.        purchase_info.highest_purchase_value 
  7. FROM company.customers 
  8. LEFT JOIN ([..]) AS traffic_info 
  9. LEFT JOIN ([..]) AS category_info 
  10. LEFT JOIN ([..]) AS purchase_info 

你可以使用：

  1. ## STEP1: Create initial table 
  2. CREATE TABLE public.customers_infos AS 
  3. SELECT customers.id, 
  4.        customers.salary, 
  5.        0 as weeks_since_last_visit, 
  6.        0 as most_visited_category_id, 
  7.        0 as highest_purchase_value 
  8. FROM company.customers 
  9. ## STEP2: Update traffic infos 
  10. UPDATE public.customers_infos 
  11. SET weeks_since_last_visit = DATE_DIFF(CURRENT_DATE, 
  12.                                        last_visit.date, WEEK) 
  13. FROM ( 
  14.          SELECT customer_id, max(visit_date) as date 
  15.          FROM web.traffic_info 
  16.          GROUP BY customer_id 
  17.      ) AS last_visit 
  18. WHERE last_visit.customer_id = customers_infos.id 
  19. ## STEP3: Update category infos 
  20. UPDATE public.customers_infos 
  21. SET most_visited_category_id = [...] 
  22. WHERE [...] 
  23. ## STEP4: Update purchase infos 
  24. UPDATE public.customers_infos 
  25. SET highest_purchase_value = [...] 
  26. WHERE [...] 

警告：即使这种方法在简化复杂查询时，它可能会带来可读性/性能权衡。

如果您使用OLAP或任何面向列的数据库，则尤其如此，针对聚合和分析查询(选择，AVG，MIN，MAX，…)进行优化，但在交谈时更少的性能(更新)。

虽然在某些情况下，它也可能提高您的表现。即使使用现代面向列的数据库，也会导致内存或性能问题太多。在这些情况下，拆分您的请求通常有助于性能和内存。

此外，值得一提的是，您需要某种程序或Orchestrator以定义的顺序执行查询。

9. 基于您自己的约定的有意义的名称

正确地命名你的模式和表格很难。使用哪些命名约定是值得难得的，但选择一个并坚持下来并非。您应该定义自己的惯例，并通过您的团队通过它。

 计算机科学中只有两个难题：缓存失效和命名的东西。 - 菲尔卡尔顿

以下是我使用的约定示例：

(1) Schema

如果您使用多种目的的分析数据库，则是在有意义的模式下组织表的良好做法。

在我们的BigQuery数据库中，我们每个数据源的一个架构。更重要的是，我们根据其目的输出不同模式的结果。

  • 任何应由第三方工具可访问的任何表都在公共架构中奠定。Dataviz工具，如DataSudio或Tableau从这里获取他们的数据。
  • 自从我们使用BQML使用机器学习以来，我们有一个专用的Machine_Learning架构。

(2) 表

表格本身应该根据惯例，我们有几个数据可视化的仪表板，每个仪表板都有自己的目的：营销仪表板，一个产品仪表板，一个行政仪表板，名称为几个。

我们的公共模式中的每个表都由仪表板的名称前缀。有些例子可能包括：

  1. product_inbox_usage 
  2. product_addon_competitor_stats 
  3. marketing_acquisition_agencies 
  4. executive_funnel_overview 

在与团队合作时，值得花时间定义您的约定。谈到命名一个新表时，永远不要使用快速和肮脏的名称，你会“更改”：你可能不会。

随意使用这些示例来定义您的约定。

10. 最后，写下有用的注释……但不是太多

我同意良好的写作和理所当然地命名的代码不应需要评论的想法。读取代码的人甚至应该在代码本身之前了解逻辑和意图。

仍然，评论可能在某些情况下很有用。但你肯定应该避免评论的陷阱太多。

避免：

  1. WITH fp AS 
  2. ( 
  3.    SELECT c_id,               # customer id 
  4.           MIN(date) as dt     # date of first purchase 
  5.    FROM company.purchases 
  6.    GROUP BY c_id 
  7. ), 
  8. ps AS 
  9. ( 
  10.    SELECT age, 
  11.           gender, 
  12.           AVG(salary) as avg 
  13.    FROM company.customers 
  14.    GROUP BY age, gender 
  15. ) 
  16. SELECT customers.id,  
  17.        ct.name,  
  18.        ct.c_age,            # customer age 
  19.        ct.gender, 
  20.        ct.salary, 
  21.        ps.avg,              # average salary of a similar persona 
  22.        fp.dt                # date of first purchase for this client 
  23. FROM company.customers ct 
  24. # join the first purchase on client id 
  25. JOIN fp ON c_id = ct.id 
  26. # match persona based on same age and genre 
  27. JOIN ps ON ps.age = c_age 
  28.            AND ps.gender = ct.gender 
  29. WHERE c_age <= 30 

而是：

  1. WITH first_purchase AS 
  2. ( 
  3.    SELECT customer_id, 
  4.           MIN(date) as date  
  5.    FROM company.purchases 
  6.    GROUP BY customer_id 
  7. ), 
  8. persona_salary AS 
  9. ( 
  10.    SELECT age, 
  11.           gender, 
  12.           AVG(salary) as avg_salary 
  13.    FROM company.customers 
  14.    GROUP BY age, gender 
  15. ) 
  16. SELECT customers.id,  
  17.        customers.name,  
  18.        customers.age,  
  19.        customers.gender,  
  20.        customers.salary, 
  21.        persona_salary.avg_salary as persona_avg_salary, 
  22.        first_purchase.date 
  23. FROM company.customers 
  24. JOIN first_purchase ON first_purchase.customer_id = customers.id 
  25. JOIN persona_salary ON persona_salary.age = customers.age 
  26.                        AND persona_salary.gender = customers.gender 
  27. WHERE customers.age <= 30 

结论

SQL很棒。它是数据分析，数据科学，数据工程甚至软件开发的基础之一：它不会等待。它的灵活性是一种力量，但可以是陷阱。

您可能不会最初意识到这一点，特别是如果您是唯一负责自己代码的人。但在某些时候，在与团队合作或者有人继续工作时，没有一系列最佳实践的SQL代码将成为负担。

在本文中，我总结了编写SQL的最常见的优秀实践。当然，有些是争辩或基于个人意见：您可能希望从这里获得灵感，并定义与您的团队不同的东西。

我希望它能帮助您将您的SQL质量带到一个下一个高度!

原文链接：https://towardsdatascience.com/10-best-practices-to-write-readable-and-maintainable-sql-code-427f6bb98208

原文链接：https://www.toutiao.com/a6993549976768905732/

  • 数据库
  • 代码
  • SQL

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    3680 　 10038 　 压缩解压 　 汇总 　 连续序号 　 隐藏开销 　 datastage 　 dsjob 　 关系连接 　 移动加权 　 平均算法 　 DB驱动 　 DB连接串 　 MDAC 　 规范化 　 TSDB 　 列式数据库 　 Trafodion 　 异构数据库 　 存储系统 　 交付 　 LSM 　 CAP定理 　 iwms 　 数据库平台 　 合区 　 where 1=1 　 dba_indexes 　 rpm-ostree 　 更新日志 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图