SQL 語句寫的爛怎麼辦？我幫你解決?

pureyb發表於2019-09-05
SQL

使用規範語法和格式，不僅可以使得程式碼簡潔易懂。更有利於別人後來對程式碼的增加迭代。本文出於對MySQL在程式碼中的語法規範和格式做了如下梳理。

1. 使用小寫的SQL

它與大寫SQL一樣易讀，而且不必一直按住shift鍵。

-- Good
select * from users

-- Bad
SELECT * FROM users

-- Bad
Select * From users

2. 單行查詢 和 多行查詢

只有當你選擇:

  • 所有列(*)或選擇1或2列
  • 您的查詢沒有額外的複雜性
-- Good
select * from users

-- Good
select id from users

-- Good
select id, email from users

-- Good
select count(*) from users

這樣做的原因很簡單，當所有內容都在一行時，仍然很容易閱讀。但一旦你開始新增更多的列或更復雜的程式碼，如果是多行程式碼就更容易閱讀:

-- Good
select
    id,
    email,
    created_at
from users

-- Good
select *
from users
where email = 'example@domain.com'

對於具有1或2列的查詢，可以將這些列放在同一行上。對於3+列，將每個列名放在它自己的行上，包括第一項:

-- Good
select id, email
from users
where email like '%@gmail.com'

-- Good
select user_id, count(*) as total_charges
from charges
group by user_id

-- Good
select
    id,
    email,
    created_at
from users

-- Bad
select id, email, created_at
from users

-- Bad
select id,
    email
from users

3. 左對齊

-- Good
select id, email
from users
where email like '%@gmail.com'

-- Bad
select id, email
  from users
 where email like '%@gmail.com'

4. 使用單引號

一些SQL語法，如BigQuery支援使用雙引號，但是對於大多數語法，雙引號最終將引用列名。因此，單引號更可取:

-- Good
select *
from users
where email = 'example@domain.com'

-- Bad
select *
from users
where email = "example@domain.com"

5.!= 優於 <>

因為!=讀起來像“not equal”，更接近我們大聲說出來的方式。

-- Good
select count(*) as paying_users_count
from users
where plan_name != 'free'

6. 逗號放在行尾

-- Good
select
    id,
    email
from users

-- Bad
select
    id
    , email
from users

7.縮排條件

如果只有一個條件，請將其保留在與以下相同的行

select email
from users
where id = 1234

當有多個縮排時，將每個縮排比where更深一層。將邏輯運算子放在前一個條件的末尾:

select id, email
from users
where 
    created_at >= '2019-03-01' and 
    vertical = 'work'

8.避免在括號內使用空格

-- Good
select *
from users
where id in (1, 2)

-- Bad
select *
from users
where id in ( 1, 2 )

9.in 查詢 將值多個縮排行

-- Good
select *
from users
where email in (
    'user-1@example.com',
    'user-2@example.com',
    'user-3@example.com',
    'user-4@example.com'
)

10.表名應該是名詞的複數形式

-- Good
select * from users
select * from visit_logs

-- Bad
select * from user
select * from visitLog

11. 欄位使用 snake_case名字

-- Good
select
    id,
    email,
    timestamp_trunc(created_at, month) as signup_month
from users

-- Bad
select
    id,
    email,
    timestamp_trunc(created_at, month) as SignupMonth
from users

12.列名約定

  • 布林欄位 的字首應該是 is_ 、 has_  或   does_ 。例如，is_customer、has_unsubscribe等
  • 日期的欄位 應該以 _date 作為字尾。例如,report_date。
  • 日期+時間欄位 應以 _at 為字尾。例如，created_at，posted_at等。

13. 列順序約定

首先放置主鍵，然後是外來鍵，然後是所有其他列。如果表有任何系統列（created_at，updated_at，is_deleted等），將它們放在最後。


-- Good
select
    id,
    name,
    created_at
from users

-- Bad
select
    created_at,
    name,
    id,
from users

14. Include inner for inner joins

最好顯式，以便連線型別非常清楚:

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id

-- Bad
select
    email,
    sum(amount) as total_revenue
from users
join charges on users.id = charges.user_id

15.對於連線條件，設定後立即放置第一個引用的表

通過這種方式，它可以更容易地確定您的連線是否會導致結果散開

-- Good
select
    ...
from users
left join charges on users.id = charges.user_id
-- primary_key = foreign_key --> one-to-many --> fanout

select
    ...
from charges
left join users on charges.user_id = users.id
-- foreign_key = primary_key --> many-to-one --> no fanout

-- Bad
select
    ...
from users
left join charges on charges.user_id = users.id

16.單個連線條件應與連線位於同一行

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id
group by email

-- Bad
select
    email,
    sum(amount) as total_revenue
from users
inner join charges
on users.id = charges.user_id
group by email

當你有多個連線條件時，將每個條件放在它們自己的縮排行中：

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on 
    users.id = charges.user_id and
    refunded = false
group by email

17.避免別名表

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id

-- Bad
select
    email,
    sum(amount) as total_revenue
from users u
inner join charges c on u.id = c.user_id

唯一的例外是，當需要多次連線到一個表並需要區分它們時。

18. 除非必須，否則不要包含表名

-- Good
select
    id,
    name
from companies

-- Bad
select
    companies.id,
    companies.name
from companies

19.始終重新命名聚合和函式包裝的引數

-- Good
select count(*) as total_users
from users

-- Bad
select count(*)
from users

-- Good
select timestamp_millis(property_beacon_interest) as expressed_interest_at
from hubspot.contact
where property_beacon_interest is not null

-- Bad
select timestamp_millis(property_beacon_interest)
from hubspot.contact
where property_beacon_interest is not null

20.明確布林條件

-- Good
select * from customers where is_cancelled = true
select * from customers where is_cancelled = false

-- Bad
select * from customers where is_cancelled
select * from customers where not is_cancelled

21. 使用as給欄位起別名

-- Good
select
    id,
    email,
    timestamp_trunc(created_at, month) as signup_month
from users

-- Bad
select
    id,
    email,
    timestamp_trunc(created_at, month) signup_month
from users

22.按列名分組，而不是按編號分組

-- Good
select user_id, count(*) as total_charges
from charges
group by user_id

-- Bad
select
    user_id,
    count(*) as total_charges
from charges
group by 1

23.使用列別名分組


-- Good
select
  timestamp_trunc(com_created_at, year) as signup_year,
  count(*) as total_companies
from companies
group by signup_year

-- Bad
select
  timestamp_trunc(com_created_at, year) as signup_year,
  count(*) as total_companies
from companies
group by timestamp_trunc(com_created_at, year)

24.首先對列進行分組

-- Good
select
  timestamp_trunc(com_created_at, year) as signup_year,
  count(*) as total_companies
from companies
group by signup_year

-- Bad
select
  count(*) as total_companies,
  timestamp_trunc(com_created_at, year) as signup_year
from mysql_helpscout.helpscout_companies
group by signup_year

25. when 宣告的案例

-- Good
select
    case
        when event_name = 'viewed_homepage' then 'Homepage'
        when event_name = 'viewed_editor' then 'Editor'
    end as page_name
from events

-- Good too
select
    case
        when event_name = 'viewed_homepage'
            then 'Homepage'
        when event_name = 'viewed_editor'
            then 'Editor'
    end as page_name
from events

-- Bad 
select
    case when event_name = 'viewed_homepage' then 'Homepage'
        when event_name = 'viewed_editor' then 'Editor'
    end as page_name
from events

26. Use CTEs, not subqueries

避免子查詢;cte將使您的查詢更容易閱讀和推理。

使用CTEs時，用新行填充查詢。

如果您使用任何CTE，請始終使用名為final的CTE，並在末尾選擇* from final。這樣，您就可以快速檢查查詢中用於除錯結果的其他cte的輸出。

關閉CTE圓括號時應使用與with和CTE名稱相同的縮排級別。

-- Good
with ordered_details as (

    select
        user_id,
        name,
        row_number() over (partition by user_id order by date_updated desc) as details_rank
    from billingdaddy.billing_stored_details

),

final as (

    select user_id, name
    from ordered_details
    where details_rank = 1

)

select * from final

-- Bad
select user_id, name
from (
    select
        user_id,
        name,
        row_number() over (partition by user_id order by date_updated desc) as details_rank
    from billingdaddy.billing_stored_details
) ranked
where details_rank = 1

27.使用有意義的CTE名稱

-- Good
with ordered_details as (

-- Bad
with d1 as (

28. Window functions

您可以將其全部保留在自己的行中，也可以根據其長度將其分解為多個：

-- Good
select
    user_id,
    name,
    row_number() over (partition by user_id order by date_updated desc) as details_rank
from billingdaddy.billing_stored_details

-- Good
select
    user_id,
    name,
    row_number() over (
        partition by user_id
        order by date_updated desc
    ) as details_rank
from billingdaddy.billing_stored_details

原文

相關文章