沒有正確的指導(dǎo)，它很容易混淆SQL。由于團(tuán)隊(duì)中的每個人都可能有自己的寫作SQL習(xí)慣，因此您可以快速結(jié)束一個沒有人理解的令人困惑的代碼。

您可能意識到遵循一套良好實(shí)踐的重要性..這篇文章為您提供了您正在尋找的指導(dǎo)!

[[416033]]

1. 使用大寫的關(guān)鍵字

讓我們從一個基本開始：使用大寫的SQL關(guān)鍵字，以及小寫的表和列。使用SQL函數(shù)的大寫(First_Value()，date_trunc()等)也是一個很好的做法。

避免：

select id, name from company.customers 

而是：

SELECT id, name FROM company.customers 

2. 使用Snake Case進(jìn)行Schema，表，列的編寫

編程語言在案例類型時具有最佳實(shí)踐：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 

而是：

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 

雖然有些人喜歡包括區(qū)分Schema，表和列的變體，但我建議使用Snake Case。

3. 在提高可讀性時使用別名

眾所周知，別名是重命名表或列沒有意義的表格或列的便捷方式。當(dāng)他們的名字并不有意義時，請隨時向您的表和列提供別名，并別名。

避免：

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 

而是：

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 

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

4. 格式化：仔細(xì)使用縮進(jìn)和空格

即使它是一個基本的原則，使您的代碼更具可讀性是一個快速的勝利。正如您與Python一樣，您應(yīng)該標(biāo)識您的SQL代碼。

關(guān)鍵字后，以及使用子查詢或派生表后。

避免：

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 

而是：

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 

此外，請注意我們?nèi)绾卧趙here子句中使用白空格。

避免：

SELECT id WHERE customers.age<=30 

而是：

SELECT id WHERE customers.age <= 30 

5. 避免 Select *

值得提醒這種良好的做法。您應(yīng)該明確關(guān)于要選擇的內(nèi)容，因此避免使用SELECT *。

選擇使您的請求不清楚，因?yàn)樗[藏了查詢背后的意圖。另外，請記住，您的表可能會發(fā)展和影響選擇。這就是為什么我不是除()教學(xué)的除外粉絲。

避免：

SELECT * EXCEPT(id) FROM company.customers 

更喜歡：

SELECT name, 
       age, 
       salary 
FROM company.customers 

6. 使用ANSI-92 Join 語法

…而不是Join表的SQL where子句。雖然您可以使用where子句和join子句來聯(lián)結(jié)表，但它是使用Join / ansi-92語法的最佳實(shí)踐。

雖然性能方面沒有差異，但是Join子句將關(guān)系邏輯與過濾器分開并提高可讀性。

避免：

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 

而是：

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 

“Where基于條款”語法 - 也稱為ANSI-89 - 比新的ANSI-92大，這就是為什么它仍然很常見。如今，大多數(shù)開發(fā)人員和數(shù)據(jù)分析師都使用Join語法。

7. 使用公共表表達(dá)式(CTE)

CTE允許您定義和執(zhí)行查詢，其中結(jié)果暫時存在，并且可以在更大的查詢中使用。CTE可在大多數(shù)現(xiàn)代數(shù)據(jù)庫上獲得。

它類似于派生表，具有2個優(yōu)點(diǎn)：

• 使用CTE提高了查詢的可讀性
• CTE是定義一次，然后可以多次參考

您用 WITH … AS：

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

避免：

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 

而是：

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 

8. 有時，它可能值得分成多個查詢

小心這個。讓我們給出一些背景：

我經(jīng)常在BigQuery上使用Airflow，轉(zhuǎn)換數(shù)據(jù)和準(zhǔn)備數(shù)據(jù)可視化上執(zhí)行SQL查詢。我們有一個工作流Orchestrator(氣流)，以定義的順序執(zhí)行請求。在某些情況下，我們選擇將復(fù)雜查詢拆分為多個較小的查詢。

代替：

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 

你可以使用：

## 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 [...] 

警告：即使這種方法在簡化復(fù)雜查詢時，它可能會帶來可讀性/性能權(quán)衡。

如果您使用OLAP或任何面向列的數(shù)據(jù)庫，則尤其如此，針對聚合和分析查詢(選擇，AVG，MIN，MAX，…)進(jìn)行優(yōu)化，但在交談時更少的性能(更新)。

雖然在某些情況下，它也可能提高您的表現(xiàn)。即使使用現(xiàn)代面向列的數(shù)據(jù)庫，也會導(dǎo)致內(nèi)存或性能問題太多。在這些情況下，拆分您的請求通常有助于性能和內(nèi)存。

此外，值得一提的是，您需要某種程序或Orchestrator以定義的順序執(zhí)行查詢。

9. 基于您自己的約定的有意義的名稱

正確地命名你的模式和表格很難。使用哪些命名約定是值得難得的，但選擇一個并堅持下來并非。您應(yīng)該定義自己的慣例，并通過您的團(tuán)隊(duì)通過它。

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

(1) Schema

如果您使用多種目的的分析數(shù)據(jù)庫，則是在有意義的模式下組織表的良好做法。

在我們的BigQuery數(shù)據(jù)庫中，我們每個數(shù)據(jù)源的一個架構(gòu)。更重要的是，我們根據(jù)其目的輸出不同模式的結(jié)果。

• 任何應(yīng)由第三方工具可訪問的任何表都在公共架構(gòu)中奠定。Dataviz工具，如DataSudio或Tableau從這里獲取他們的數(shù)據(jù)。
• 自從我們使用BQML使用機(jī)器學(xué)習(xí)以來，我們有一個專用的Machine_Learning架構(gòu)。

(2) 表

表格本身應(yīng)該根據(jù)慣例，我們有幾個數(shù)據(jù)可視化的儀表板，每個儀表板都有自己的目的：營銷儀表板，一個產(chǎn)品儀表板，一個行政儀表板，名稱為幾個。

我們的公共模式中的每個表都由儀表板的名稱前綴。有些例子可能包括：

product_inbox_usage 
product_addon_competitor_stats 
marketing_acquisition_agencies 
executive_funnel_overview 

在與團(tuán)隊(duì)合作時，值得花時間定義您的約定。談到命名一個新表時，永遠(yuǎn)不要使用快速和骯臟的名稱，你會“更改”：你可能不會。

隨意使用這些示例來定義您的約定。

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

我同意良好的寫作和理所當(dāng)然地命名的代碼不應(yīng)需要評論的想法。讀取代碼的人甚至應(yīng)該在代碼本身之前了解邏輯和意圖。

仍然，評論可能在某些情況下很有用。但你肯定應(yīng)該避免評論的陷阱太多。

避免：

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 

而是：

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 

結(jié)論

SQL很棒。它是數(shù)據(jù)分析，數(shù)據(jù)科學(xué)，數(shù)據(jù)工程甚至軟件開發(fā)的基礎(chǔ)之一：它不會等待。它的靈活性是一種力量，但可以是陷阱。

您可能不會最初意識到這一點(diǎn)，特別是如果您是唯一負(fù)責(zé)自己代碼的人。但在某些時候，在與團(tuán)隊(duì)合作或者有人繼續(xù)工作時，沒有一系列最佳實(shí)踐的SQL代碼將成為負(fù)擔(dān)。

在本文中，我總結(jié)了編寫SQL的最常見的優(yōu)秀實(shí)踐。當(dāng)然，有些是爭辯或基于個人意見：您可能希望從這里獲得靈感，并定義與您的團(tuán)隊(duì)不同的東西。

我希望它能幫助您將您的SQL質(zhì)量帶到一個下一個高度!

原文鏈接：

https://towardsdatascience.com/10-best-practices-to-write-readable-and-maintainable-sql-code-427f6bb98208