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

人间有味茶清欢 發表於 2023-11-29 00:00:00

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

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

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

<img title="可维护、可阅读的SQL代码的十个优秀实践" alt="可维护、可阅读的SQL代码的十个优秀实践" border="0" height="auto" src="https://zhuji.jb51.net/uploads/img/202305/2bed427b2592d7c0d8f8a40fcc0adc6b.jpg" width="auto">
<h3>
1. 使用大写的关键字</h3>

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

避免：
<ol class="dp-xml">
<li class="alt">
select id, name from company.customers 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
SELECT id, name FROM company.customers 
</li>
</ol>
<h3>
2. 使用Snake Case进行Schema，表，列的编写</h3>

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

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

避免：
<ol class="dp-xml">
<li class="alt">
SELECT Customers.id, 
</li>
<li>
 Customers.name, 
</li>
<li class="alt">
 COUNT(WebVisit.id) as nbVisit 
</li>
<li>
FROM COMPANY.Customers 
</li>
<li class="alt">
JOIN COMPANY.WebVisit ON Customers.id = WebVisit.customerId 
</li>
<li>
WHERE Customers.age <= 30 
</li>
<li class="alt">
GROUP BY Customers.id, Customers.name 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 COUNT(web_visit.id) as nb_visit 
</li>
<li>
FROM company.customers 
</li>
<li class="alt">
JOIN company.web_visit ON customers.id = web_visit.customer_id 
</li>
<li>
WHERE customers.age <= 30 
</li>
<li class="alt">
GROUP BY customers.id, customers.name 
</li>
</ol>

虽然有些人喜欢包括区分Schema，表和列的变体，但我建议使用Snake Case。
<h3>
3. 在提高可读性时使用别名</h3>

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

避免：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 customers.context_col1, 
</li>
<li>
 nested.f0_ 
</li>
<li class="alt">
FROM company.customers 
</li>
<li>
JOIN ( 
</li>
<li class="alt">
 SELECT customer_id, 
</li>
<li>
 MIN(date) 
</li>
<li class="alt">
 FROM company.purchases 
</li>
<li>
 GROUP BY customer_id 
</li>
<li class="alt">
 ) ON customer_id = customers.id 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 customers.context_col1 as ip_address, 
</li>
<li>
 first_purchase.date as first_purchase_date 
</li>
<li class="alt">
FROM company.customers 
</li>
<li>
JOIN ( 
</li>
<li class="alt">
 SELECT customer_id, 
</li>
<li>
 MIN(date) as date 
</li>
<li class="alt">
 FROM company.purchases 
</li>
<li>
 GROUP BY customer_id 
</li>
<li class="alt">
 ) AS first_purchase 
</li>
<li>
 ON first_purchase.customer_id = customers.id 
</li>
</ol>

我通常用小写的列别名，以及具有大写的表。
<h3>
4. 格式化：仔细使用缩进和空格</h3>

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

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

避免：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, customers.name, customers.age, customers.gender, customers.salary, first_purchase.date 
</li>
<li>
FROM company.customers 
</li>
<li class="alt">
LEFT JOIN ( SELECT customer_id, MIN(date) as date FROM company.purchases GROUP BY customer_id ) AS first_purchase 
</li>
<li>
ON first_purchase.customer_id = customers.id 
</li>
<li class="alt">
WHERE customers.age<=30 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 customers.age, 
</li>
<li>
 customers.gender, 
</li>
<li class="alt">
 customers.salary, 
</li>
<li>
 first_purchase.date 
</li>
<li class="alt">
FROM company.customers 
</li>
<li>
LEFT JOIN ( 
</li>
<li class="alt">
 SELECT customer_id, 
</li>
<li>
 MIN(date) as date 
</li>
<li class="alt">
 FROM company.purchases 
</li>
<li>
 GROUP BY customer_id 
</li>
<li class="alt">
 ) AS first_purchase 
</li>
<li>
 ON first_purchase.customer_id = customers.id 
</li>
<li class="alt">
WHERE customers.age <= 30 
</li>
</ol>

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

避免：
<ol class="dp-xml">
<li class="alt">
SELECT id WHERE customers.age<=30 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
SELECT id WHERE customers.age <= 30 
</li>
</ol>
<h3>
5. 避免 Select *</h3>

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

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

避免：
<ol class="dp-xml">
<li class="alt">
SELECT * EXCEPT(id) FROM company.customers 
</li>
</ol>

更喜欢：
<ol class="dp-xml">
<li class="alt">
SELECT name, 
</li>
<li>
 age, 
</li>
<li class="alt">
 salary 
</li>
<li>
FROM company.customers 
</li>
</ol>
<h3>
6. 使用ANSI-92 Join 语法</h3>

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

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

避免：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 COUNT(transactions.id) as nb_transaction 
</li>
<li>
FROM company.customers, company.transactions 
</li>
<li class="alt">
WHERE customers.id = transactions.customer_id 
</li>
<li>
 AND customers.age <= 30 
</li>
<li class="alt">
GROUP BY customers.id, customers.name 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 COUNT(transactions.id) as nb_transaction 
</li>
<li>
FROM company.customers 
</li>
<li class="alt">
JOIN company.transactions ON customers.id = transactions.customer_id 
</li>
<li>
WHERE customers.age <= 30 
</li>
<li class="alt">
GROUP BY customers.id, customers.name 
</li>
</ol>

“Where基于条款”语法 - 也称为ANSI-89 - 比新的ANSI-92大，这就是为什么它仍然很常见。如今，大多数开发人员和数据分析师都使用Join语法。
<h3>
7. 使用公共表表达式(CTE)</h3>

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

它类似于派生表，具有2个优点：
<ul>
<li>
使用CTE提高了查询的可读性</li>
<li>
CTE是定义一次，然后可以多次参考</li>
</ul>

您用 WITH … AS：
<ol class="dp-xml">
<li class="alt">
WITH my_cte AS( SELECT col1, col2 FROM table)SELECT * FROM my_cte 
</li>
</ol>

避免：
<ol class="dp-xml">
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.name, 
</li>
<li class="alt">
 customers.age, 
</li>
<li>
 customers.gender, 
</li>
<li class="alt">
 customers.salary, 
</li>
<li>
 persona_salary.avg_salary as persona_avg_salary, 
</li>
<li class="alt">
 first_purchase.date 
</li>
<li>
FROM company.customers 
</li>
<li class="alt">
JOIN ( 
</li>
<li>
 SELECT customer_id, 
</li>
<li class="alt">
 MIN(date) as date 
</li>
<li>
 FROM company.purchases 
</li>
<li class="alt">
 GROUP BY customer_id 
</li>
<li>
 ) AS first_purchase 
</li>
<li class="alt">
 ON first_purchase.customer_id = customers.id 
</li>
<li>
JOIN ( 
</li>
<li class="alt">
 SELECT age, 
</li>
<li>
 gender, 
</li>
<li class="alt">
 AVG(salary) as avg_salary 
</li>
<li>
 FROM company.customers 
</li>
<li class="alt">
 GROUP BY age, gender 
</li>
<li>
 ) AS persona_salary 
</li>
<li class="alt">
 ON persona_salary.age = customers.age 
</li>
<li>
 AND persona_salary.gender = customers.gender 
</li>
<li class="alt">
WHERE customers.age <= 30 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
WITH first_purchase AS 
</li>
<li>
( 
</li>
<li class="alt">
 SELECT customer_id, 
</li>
<li>
 MIN(date) as date 
</li>
<li class="alt">
 FROM company.purchases 
</li>
<li>
 GROUP BY customer_id 
</li>
<li class="alt">
), 
</li>
<li>
persona_salary AS 
</li>
<li class="alt">
( 
</li>
<li>
 SELECT age, 
</li>
<li class="alt">
 gender, 
</li>
<li>
 AVG(salary) as avg_salary 
</li>
<li class="alt">
 FROM company.customers 
</li>
<li>
 GROUP BY age, gender 
</li>
<li class="alt">
) 
</li>
<li>
SELECT customers.id, 
</li>
<li class="alt">
 customers.name, 
</li>
<li>
 customers.age, 
</li>
<li class="alt">
 customers.gender, 
</li>
<li>
 customers.salary, 
</li>
<li class="alt">
 persona_salary.avg_salary as persona_avg_salary, 
</li>
<li>
 first_purchase.date 
</li>
<li class="alt">
FROM company.customers 
</li>
<li>
JOIN first_purchase ON first_purchase.customer_id = customers.id 
</li>
<li class="alt">
JOIN persona_salary ON persona_salary.age = customers.age 
</li>
<li>
 AND persona_salary.gender = customers.gender 
</li>
<li class="alt">
WHERE customers.age <= 30 
</li>
</ol>
<h3>
8. 有时，它可能值得分成多个查询</h3>

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

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

代替：
<ol class="dp-xml">
<li class="alt">
CREATE TABLE customers_infos AS 
</li>
<li>
SELECT customers.id, 
</li>
<li class="alt">
 customers.salary, 
</li>
<li>
 traffic_info.weeks_since_last_visit, 
</li>
<li class="alt">
 category_info.most_visited_category_id, 
</li>
<li>
 purchase_info.highest_purchase_value 
</li>
<li class="alt">
FROM company.customers 
</li>
<li>
LEFT JOIN ([..]) AS traffic_info 
</li>
<li class="alt">
LEFT JOIN ([..]) AS category_info 
</li>
<li>
LEFT JOIN ([..]) AS purchase_info 
</li>
</ol>

你可以使用：
<ol class="dp-xml">
<li class="alt">
## STEP1: Create initial table 
</li>
<li>
CREATE TABLE public.customers_infos AS 
</li>
<li class="alt">
SELECT customers.id, 
</li>
<li>
 customers.salary, 
</li>
<li class="alt">
 0 as weeks_since_last_visit, 
</li>
<li>
 0 as most_visited_category_id, 
</li>
<li class="alt">
 0 as highest_purchase_value 
</li>
<li>
FROM company.customers 
</li>
<li class="alt">
## STEP2: Update traffic infos 
</li>
<li>
UPDATE public.customers_infos 
</li>
<li class="alt">
SET weeks_since_last_visit = DATE_DIFF(CURRENT_DATE, 
</li>
<li>
 last_visit.date, WEEK) 
</li>
<li class="alt">
FROM ( 
</li>
<li>
 SELECT customer_id, max(visit_date) as date 
</li>
<li class="alt">
 FROM web.traffic_info 
</li>
<li>
 GROUP BY customer_id 
</li>
<li class="alt">
 ) AS last_visit 
</li>
<li>
WHERE last_visit.customer_id = customers_infos.id 
</li>
<li class="alt">
## STEP3: Update category infos 
</li>
<li>
UPDATE public.customers_infos 
</li>
<li class="alt">
SET most_visited_category_id = [...] 
</li>
<li>
WHERE [...] 
</li>
<li class="alt">
## STEP4: Update purchase infos 
</li>
<li>
UPDATE public.customers_infos 
</li>
<li class="alt">
SET highest_purchase_value = [...] 
</li>
<li>
WHERE [...] 
</li>
</ol>

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

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

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

此外，值得一提的是，您需要某种程序或Orchestrator以定义的顺序执行查询。
<h3>
9. 基于您自己的约定的有意义的名称</h3>

正确地命名你的模式和表格很难。使用哪些命名约定是值得难得的，但选择一个并坚持下来并非。您应该定义自己的惯例，并通过您的团队通过它。
<table align="center" border="0" cellpadding="6" cellspacing="0" width="95%"><tbody><tr>
<td bgcolor="#fdfddf">
 计算机科学中只有两个难题：缓存失效和命名的东西。 - 菲尔卡尔顿</td>
</tr></tbody></table>

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

(1) Schema

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

在我们的BigQuery数据库中，我们每个数据源的一个架构。更重要的是，我们根据其目的输出不同模式的结果。
<ul>
<li>
任何应由第三方工具可访问的任何表都在公共架构中奠定。Dataviz工具，如DataSudio或Tableau从这里获取他们的数据。</li>
<li>
自从我们使用BQML使用机器学习以来，我们有一个专用的Machine_Learning架构。</li>
</ul>

(2) 表

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

我们的公共模式中的每个表都由仪表板的名称前缀。有些例子可能包括：
<ol class="dp-xml">
<li class="alt">
product_inbox_usage 
</li>
<li>
product_addon_competitor_stats 
</li>
<li class="alt">
marketing_acquisition_agencies 
</li>
<li>
executive_funnel_overview 
</li>
</ol>

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

随意使用这些示例来定义您的约定。
<h3>
10. 最后，写下有用的注释……但不是太多</h3>

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

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

避免：
<ol class="dp-xml">
<li class="alt">
WITH fp AS 
</li>
<li>
( 
</li>
<li class="alt">
 SELECT c_id, # customer id 
</li>
<li>
 MIN(date) as dt # date of first purchase 
</li>
<li class="alt">
 FROM company.purchases 
</li>
<li>
 GROUP BY c_id 
</li>
<li class="alt">
), 
</li>
<li>
ps AS 
</li>
<li class="alt">
( 
</li>
<li>
 SELECT age, 
</li>
<li class="alt">
 gender, 
</li>
<li>
 AVG(salary) as avg 
</li>
<li class="alt">
 FROM company.customers 
</li>
<li>
 GROUP BY age, gender 
</li>
<li class="alt">
) 
</li>
<li>
SELECT customers.id, 
</li>
<li class="alt">
 ct.name, 
</li>
<li>
 ct.c_age, # customer age 
</li>
<li class="alt">
 ct.gender, 
</li>
<li>
 ct.salary, 
</li>
<li class="alt">
 ps.avg, # average salary of a similar persona 
</li>
<li>
 fp.dt # date of first purchase for this client 
</li>
<li class="alt">
FROM company.customers ct 
</li>
<li>
# join the first purchase on client id 
</li>
<li class="alt">
JOIN fp ON c_id = ct.id 
</li>
<li>
# match persona based on same age and genre 
</li>
<li class="alt">
JOIN ps ON ps.age = c_age 
</li>
<li>
 AND ps.gender = ct.gender 
</li>
<li class="alt">
WHERE c_age <= 30 
</li>
</ol>

而是：
<ol class="dp-xml">
<li class="alt">
WITH first_purchase AS 
</li>
<li>
( 
</li>
<li class="alt">
 SELECT customer_id, 
</li>
<li>
 MIN(date) as date 
</li>
<li class="alt">
 FROM company.purchases 
</li>
<li>
 GROUP BY customer_id 
</li>
<li class="alt">
), 
</li>
<li>
persona_salary AS 
</li>
<li class="alt">
( 
</li>
<li>
 SELECT age, 
</li>
<li class="alt">
 gender, 
</li>
<li>
 AVG(salary) as avg_salary 
</li>
<li class="alt">
 FROM company.customers 
</li>
<li>
 GROUP BY age, gender 
</li>
<li class="alt">
) 
</li>
<li>
SELECT customers.id, 
</li>
<li class="alt">
 customers.name, 
</li>
<li>
 customers.age, 
</li>
<li class="alt">
 customers.gender, 
</li>
<li>
 customers.salary, 
</li>
<li class="alt">
 persona_salary.avg_salary as persona_avg_salary, 
</li>
<li>
 first_purchase.date 
</li>
<li class="alt">
FROM company.customers 
</li>
<li>
JOIN first_purchase ON first_purchase.customer_id = customers.id 
</li>
<li class="alt">
JOIN persona_salary ON persona_salary.age = customers.age 
</li>
<li>
 AND persona_salary.gender = customers.gender 
</li>
<li class="alt">
WHERE customers.age <= 30 
</li>
</ol>
<h3>
结论</h3>

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

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

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

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

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

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

頁: [1]

琼殿技术论坛's Archiver

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