没有正确的指导,它很容易混淆SQL。由于团队中的每个人都可能有自己的写作SQL习惯,因此您可以快速结束一个没有人理解的令人困惑的代码。
您可能意识到遵循一套良好实践的重要性..这篇文章为您提供了您正在寻找的指导!
1. 使用大写的关键字
让我们从一个基本开始:使用大写的SQL关键字,以及小写的表和列。使用SQL函数的大写(First_Value(),date_trunc()等)也是一个很好的做法。
避免:
select id, name from company.customers
- 1.
而是:
SELECT id, name FROM company.customers
- 1.
2. 使用Snake Case进行Schema,表,列的编写
编程语言在案例类型时具有最佳实践:Camelcase,Pascalcase,Kebabuic和Snake_Case是最常见的。
涉及SQL,Snake Case(有时称为下划线Case)是最广泛使用的约定。
避免:
SELECT Customers.id,
Customers.name,
COUNT(WebVisit.id) as nbVisit
FROM COMPANY.Customers
JOIN COMPANY.WebVisit ON Customers.id = WebVisit.customerId
WHERE Customers.age <= 30
GROUP BY Customers.id, Customers.name
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
而是:
SELECT customers.id,
customers.name,
COUNT(web_visit.id) as nb_visit
FROM company.customers
JOIN company.web_visit ON customers.id = web_visit.customer_id
WHERE customers.age <= 30
GROUP BY customers.id, customers.name
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
虽然有些人喜欢包括区分Schema,表和列的变体,但我建议使用Snake Case。
3. 在提高可读性时使用别名
众所周知,别名是重命名表或列没有意义的表格或列的便捷方式。当他们的名字并不有意义时,请随时向您的表和列提供别名,并别名。
避免:
SELECT customers.id,
customers.name,
customers.context_col1,
nested.f0_
FROM company.customers
JOIN (
SELECT customer_id,
MIN(date)
FROM company.purchases
GROUP BY customer_id
) ON customer_id = customers.id
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
而是:
SELECT customers.id,
customers.name,
customers.context_col1 as ip_address,
first_purchase.date as first_purchase_date
FROM company.customers
JOIN (
SELECT customer_id,
MIN(date) as date
FROM company.purchases
GROUP BY customer_id
) AS first_purchase
ON first_purchase.customer_id = customers.id
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
我通常用小写的列别名,以及具有大写的表。
4. 格式化:仔细使用缩进和空格
即使它是一个基本的原则,使您的代码更具可读性是一个快速的胜利。正如您与Python一样,您应该标识您的SQL代码。
关键字后,以及使用子查询或派生表后。
避免:
SELECT customers.id, customers.name, customers.age, customers.gender, customers.salary, first_purchase.date
FROM company.customers
LEFT JOIN ( SELECT customer_id, MIN(date) as date FROM company.purchases GROUP BY customer_id ) AS first_purchase
ON first_purchase.customer_id = customers.id
WHERE customers.age<=30
- 1.
- 2.
- 3.
- 4.
- 5.
而是:
SELECT customers.id,
customers.name,
customers.age,
customers.gender,
customers.salary,
first_purchase.date
FROM company.customers
LEFT JOIN (
SELECT customer_id,
MIN(date) as date
FROM company.purchases
GROUP BY customer_id
) AS first_purchase
ON first_purchase.customer_id = customers.id
WHERE customers.age <= 30
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
此外,请注意我们如何在where子句中使用白空格。
避免:
SELECT id WHERE customers.age<=30
- 1.
而是:
SELECT id WHERE customers.age <= 30
- 1.
5. 避免 Select *
值得提醒这种良好的做法。您应该明确关于要选择的内容,因此避免使用SELECT *。
选择使您的请求不清楚,因为它隐藏了查询背后的意图。另外,请记住,您的表可能会发展和影响选择。这就是为什么我不是除()教学的除外粉丝。
避免:
SELECT * EXCEPT(id) FROM company.customers
- 1.
更喜欢:
SELECT name,
age,
salary
FROM company.customers
- 1.
- 2.
- 3.
- 4.
6. 使用ANSI-92 Join 语法
…而不是Join表的SQL where子句。虽然您可以使用where子句和join子句来联结表,但它是使用Join / ansi-92语法的最佳实践。
虽然性能方面没有差异,但是Join子句将关系逻辑与过滤器分开并提高可读性。
避免:
SELECT customers.id,
customers.name,
COUNT(transactions.id) as nb_transaction
FROM company.customers, company.transactions
WHERE customers.id = transactions.customer_id
AND customers.age <= 30
GROUP BY customers.id, customers.name
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
而是:
SELECT customers.id,
customers.name,
COUNT(transactions.id) as nb_transaction
FROM company.customers
JOIN company.transactions ON customers.id = transactions.customer_id
WHERE customers.age <= 30
GROUP BY customers.id, customers.name
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
“Where基于条款”语法 - 也称为ANSI-89 - 比新的ANSI-92大,这就是为什么它仍然很常见。如今,大多数开发人员和数据分析师都使用Join语法。
7. 使用公共表表达式(CTE)
CTE允许您定义和执行查询,其中结果暂时存在,并且可以在更大的查询中使用。CTE可在大多数现代数据库上获得。
它类似于派生表,具有2个优点:
- 使用CTE提高了查询的可读性
- CTE是定义一次,然后可以多次参考
您用 WITH … AS:
WITH my_cte AS( SELECT col1, col2 FROM table)SELECT * FROM my_cte
- 1.
避免:
SELECT customers.id,
customers.name,
customers.age,
customers.gender,
customers.salary,
persona_salary.avg_salary as persona_avg_salary,
first_purchase.date
FROM company.customers
JOIN (
SELECT customer_id,
MIN(date) as date
FROM company.purchases
GROUP BY customer_id
) AS first_purchase
ON first_purchase.customer_id = customers.id
JOIN (
SELECT age,
gender,
AVG(salary) as avg_salary
FROM company.customers
GROUP BY age, gender
) AS persona_salary
ON persona_salary.age = customers.age
AND persona_salary.gender = customers.gender
WHERE customers.age <= 30
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
而是:
WITH first_purchase AS
(
SELECT customer_id,
MIN(date) as date
FROM company.purchases
GROUP BY customer_id
),
persona_salary AS
(
SELECT age,
gender,
AVG(salary) as avg_salary
FROM company.customers
GROUP BY age, gender
)
SELECT customers.id,
customers.name,
customers.age,
customers.gender,
customers.salary,
persona_salary.avg_salary as persona_avg_salary,
first_purchase.date
FROM company.customers
JOIN first_purchase ON first_purchase.customer_id = customers.id
JOIN persona_salary ON persona_salary.age = customers.age
AND persona_salary.gender = customers.gender
WHERE customers.age <= 30
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
8. 有时,它可能值得分成多个查询
小心这个。让我们给出一些背景:
我经常在BigQuery上使用Airflow,转换数据和准备数据可视化上执行SQL查询。我们有一个工作流Orchestrator(气流),以定义的顺序执行请求。在某些情况下,我们选择将复杂查询拆分为多个较小的查询。
代替:
CREATE TABLE customers_infos AS
SELECT customers.id,
customers.salary,
traffic_info.weeks_since_last_visit,
category_info.most_visited_category_id,
purchase_info.highest_purchase_value
FROM company.customers
LEFT JOIN ([..]) AS traffic_info
LEFT JOIN ([..]) AS category_info
LEFT JOIN ([..]) AS purchase_info
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
你可以使用:
## STEP1: Create initial table
CREATE TABLE public.customers_infos AS
SELECT customers.id,
customers.salary,
0 as weeks_since_last_visit,
0 as most_visited_category_id,
0 as highest_purchase_value
FROM company.customers
## STEP2: Update traffic infos
UPDATE public.customers_infos
SET weeks_since_last_visit = DATE_DIFF(CURRENT_DATE,
last_visit.date, WEEK)
FROM (
SELECT customer_id, max(visit_date) as date
FROM web.traffic_info
GROUP BY customer_id
) AS last_visit
WHERE last_visit.customer_id = customers_infos.id
## STEP3: Update category infos
UPDATE public.customers_infos
SET most_visited_category_id = [...]
WHERE [...]
## STEP4: Update purchase infos
UPDATE public.customers_infos
SET highest_purchase_value = [...]
WHERE [...]
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
警告:即使这种方法在简化复杂查询时,它可能会带来可读性/性能权衡。
如果您使用OLAP或任何面向列的数据库,则尤其如此,针对聚合和分析查询(选择,AVG,MIN,MAX,…)进行优化,但在交谈时更少的性能(更新)。
虽然在某些情况下,它也可能提高您的表现。即使使用现代面向列的数据库,也会导致内存或性能问题太多。在这些情况下,拆分您的请求通常有助于性能和内存。
此外,值得一提的是,您需要某种程序或Orchestrator以定义的顺序执行查询。
9. 基于您自己的约定的有意义的名称
正确地命名你的模式和表格很难。使用哪些命名约定是值得难得的,但选择一个并坚持下来并非。您应该定义自己的惯例,并通过您的团队通过它。
计算机科学中只有两个难题:缓存失效和命名的东西。 - 菲尔卡尔顿 |
以下是我使用的约定示例:
(1) Schema
如果您使用多种目的的分析数据库,则是在有意义的模式下组织表的良好做法。
在我们的BigQuery数据库中,我们每个数据源的一个架构。更重要的是,我们根据其目的输出不同模式的结果。
- 任何应由第三方工具可访问的任何表都在公共架构中奠定。Dataviz工具,如DataSudio或Tableau从这里获取他们的数据。
- 自从我们使用BQML使用机器学习以来,我们有一个专用的Machine_Learning架构。
(2) 表
表格本身应该根据惯例,我们有几个数据可视化的仪表板,每个仪表板都有自己的目的:营销仪表板,一个产品仪表板,一个行政仪表板,名称为几个。
我们的公共模式中的每个表都由仪表板的名称前缀。有些例子可能包括:
product_inbox_usage
product_addon_competitor_stats
marketing_acquisition_agencies
executive_funnel_overview
- 1.
- 2.
- 3.
- 4.
在与团队合作时,值得花时间定义您的约定。谈到命名一个新表时,永远不要使用快速和肮脏的名称,你会“更改”:你可能不会。
随意使用这些示例来定义您的约定。
10. 最后,写下有用的注释……但不是太多
我同意良好的写作和理所当然地命名的代码不应需要评论的想法。读取代码的人甚至应该在代码本身之前了解逻辑和意图。
仍然,评论可能在某些情况下很有用。但你肯定应该避免评论的陷阱太多。
避免:
WITH fp AS
(
SELECT c_id, # customer id
MIN(date) as dt # date of first purchase
FROM company.purchases
GROUP BY c_id
),
ps AS
(
SELECT age,
gender,
AVG(salary) as avg
FROM company.customers
GROUP BY age, gender
)
SELECT customers.id,
ct.name,
ct.c_age, # customer age
ct.gender,
ct.salary,
ps.avg, # average salary of a similar persona
fp.dt # date of first purchase for this client
FROM company.customers ct
# join the first purchase on client id
JOIN fp ON c_id = ct.id
# match persona based on same age and genre
JOIN ps ON ps.age = c_age
AND ps.gender = ct.gender
WHERE c_age <= 30
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
而是:
WITH first_purchase AS
(
SELECT customer_id,
MIN(date) as date
FROM company.purchases
GROUP BY customer_id
),
persona_salary AS
(
SELECT age,
gender,
AVG(salary) as avg_salary
FROM company.customers
GROUP BY age, gender
)
SELECT customers.id,
customers.name,
customers.age,
customers.gender,
customers.salary,
persona_salary.avg_salary as persona_avg_salary,
first_purchase.date
FROM company.customers
JOIN first_purchase ON first_purchase.customer_id = customers.id
JOIN persona_salary ON persona_salary.age = customers.age
AND persona_salary.gender = customers.gender
WHERE customers.age <= 30
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
结论
SQL很棒。它是数据分析,数据科学,数据工程甚至软件开发的基础之一:它不会等待。它的灵活性是一种力量,但可以是陷阱。
您可能不会最初意识到这一点,特别是如果您是唯一负责自己代码的人。但在某些时候,在与团队合作或者有人继续工作时,没有一系列最佳实践的SQL代码将成为负担。
在本文中,我总结了编写SQL的最常见的优秀实践。当然,有些是争辩或基于个人意见:您可能希望从这里获得灵感,并定义与您的团队不同的东西。
我希望它能帮助您将您的SQL质量带到一个下一个高度!
原文链接:
https://towardsdatascience.com/10-best-practices-to-write-readable-and-maintainable-sql-code-427f6bb98208