使SQL更易於閱讀的幾個小技巧

 

無論是數倉開發還是數據分析，寫一手好的SQL是一項基本的技能。毋庸置疑，編寫性能較好的SQL是非常重要的，但是，SQL的可讀性同樣是不容小覷的。一個有著混亂格式的SQL腳本，往往需要花費較長的時間去弄清楚腳本的具體邏輯。如果你曾經被祖傳的毫無章法的SQL腳本狂虐過，你一定心有感觸。本文將分享幾個SQL格式的規範，當然仁者見仁智者見智，其實沒有嚴格的標準，如果有，那就是保證易於閱讀和易於維護。

大小寫保持一致

可以對SQL關鍵字使用不同的大小寫，但是要保持一致。看看這個：

SELECT customer_city,count(*) from dim_customer WHERE customerProvince = '上海' Group by customer_city

上面的SQL語句是不是很讓人抓狂，大小寫混用，看起來很不規範。總結起來，要注意下面幾點：

• SQL的關鍵字可以大寫，也可以小寫，但是不要大小寫混用。上面的SQL查詢既有完全大寫，也有首字母大寫，更有小寫。看似是不拘小節，但是萬萬使不得。
• 由於大小寫是混合的，因此很難區分小寫的關鍵字實際上是關鍵字還是列。此外，閱讀也很煩人。
• 字段命名要保持一致的風格，上面的SQL與中customer_city是小寫加下劃線，而customerProvince字段是駝峰命名法，這種不一致性顯然是不可取的。

進行一些規範之後後，查詢應如下所示：

SELECT customer_city,

   count(*)

FROM dim_customer
WHERE customer_province = '上海'
GROUP BY customer_city

使用縮進

再來看看下面的一條查詢語句：

SELECT dp.region_name,count(*) FROM user_behavior_log ubl JOIN dim_province dp ON ubl.province = dp.province_name WHERE ubl.province = '上海市' GROUP BY dp.region_name

將上面的SQL語句格式化下面的形式：

SELECT dp.region_name, count(*)
FROM user_behavior_log ubl
JOIN dim_province dp ON ubl.province = dp.province_name
WHERE ubl.province = '上海市'
GROUP BY dp.region_name

上面的格式化形式似乎清晰了很多，但是如果語句中包含了子查詢、多個JOIN以及窗口函數時，同樣會顯得對閱讀不是很友好。

再換一種格式化方式，如下：

SELECT

dp.region_name, 
count(*)

FROM user_behavior_log ubl

JOIN dim_province dp ON ubl.province = dp.province_name

WHERE ubl.province = '上海市'
GROUP BY

dp.region_name

-- 或者下面的形式
SELECT

dp.region_name 
,count(*)

FROM user_behavior_log ubl

JOIN dim_province dp ON ubl.province = dp.province_name

WHERE ubl.province = '上海市'
GROUP BY

dp.region_name</code></pre>

尖叫提示：對於第二種形式，在SELECT字段中，從第二個字段開始，每個字段前面添加一個逗號，而不是每個字段後面使用逗號結尾。這種方式可以很方便地識別FROM前面是否存在逗號，從而造成語法錯誤。當然，這個只是個人習慣問題，並不是硬性的規定。




另外上面的SQL語句使用了4個字符縮進，當然也可以選擇2個字符縮進，這個也是個人習慣問題。




在group by 和order by之後使用字段的排列序號




同樣，這種書寫風格也是個人的一種偏好，並不是一條硬性規定。應該有很多的初學者對此種寫法並不是很清楚。




看下面的這條SQL：




SELECT

dp.region_name, 
dp.province_name,
count(*)

FROM user_behavior_log ubl

JOIN dim_province dp ON ubl.province = dp.province_name

GROUP BY

dp.region_name,
dp.province_name

ORDER BY

count(*) desc -- Hive不支持</code></pre>

可以寫成下面的形式：




-- 注意：MySQL、Impala支持這種寫法，Hive不支持

SELECT

dp.region_name, 
dp.province_name,
count(*)

FROM user_behavior_log ubl

JOIN dim_province dp ON ubl.province = dp.province_name

GROUP BY 1,2
ORDER BY 3




這樣寫有如下的好處：



可以節省行：通過許多字段進行分組不僅會在SELECT子句中添加更多行，還會在GROUP BY和ORDER BY子句中添加更多行，甚至可能使查詢中的行數增加一倍。
可維護性：如果想改變分組字段，只需在SELECT子句中進行操作，在GROUP BY語句中不需要修改。
方便：只需要GROUP BY 1，2，3，…，n，其中n為分組列的字段序號。

使用Common Table表達式(with語句)




該方式稱之為Common Table Expressions(CTE),用來簡化複雜查詢。它們可以定義為臨時視圖，因為它們僅在整個查詢執行期間存在。




看一個簡單的例子：




-- 注意Hive、Impala支持這種語法，低版本的MySQL不支持(高版本支持)
WITH employee_by_title_count AS (

SELECT
    t.name as job_title
    , COUNT(e.id) as amount_of_employees
FROM employees e
    JOIN job_titles t on e.job_title_id = t.id
GROUP BY 1

),
salaries_by_title AS (

 SELECT
     name as job_title
     , salary
 FROM job_titles

)
SELECT *
FROM employee_by_title_count e

JOIN salaries_by_title s ON s.job_title = e.job_title</code></pre>

上面的語句中，最終的查詢使用employee_by_title和salaries_by_title的兩個結果集進行JOIN產生最終結果。這比在SELECT子句中或直接在FROM子句中進行子查詢更具可讀性和可維護性。




使用具有描述性的別名




這一點非常重要，如果查詢的列字段很多，肯能會存在一些id，count(*)等，很難辨識代表什麼含義，所以需要為每個查詢列加上可讀的、易於理解的別名，能夠讓其他人一眼就能看出代表什麼含義，這樣可以增加腳本的可維護性。




總結




文中提到的一些規範有些是必須要遵守的，有些是個人的編碼習慣，無論你是開發人員、數據分析師、數倉開發，遵循一些規範可以避免不必要的麻煩。值得注意的是，關於SQL的格式，沒有一個標準的約定，需要與團隊的其他成員達成共識，一起按照相同的約定進行開發，從而可以大大提高腳本的可讀性和可維護性。







本文作者：西貝木土