SQL常見錯誤指南

點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，

設(shè)為“置頂或星標(biāo)”，第一時間送達(dá)干貨

SQL是數(shù)據(jù)分析中最高頻的操作之一，本文梳理常見的SQL錯誤，可以歸為三大類：

• 語法類；
• 函數(shù)類；
• 邏輯類；

語法錯誤

標(biāo)點(diǎn)錯漏

e.g. 逗號多或缺，括號等不成對，漏寫引號、多余的空格等；

e.g. case when … end函數(shù)，有時候少寫end；

e.g. select含有 聚合函數(shù)(count, sum, avg)時，相應(yīng)字段都要放入group by 后面；

重命名

如果有子查詢，那么需要對子查詢進(jìn)行重命名；

表的重命名不要搞混；

數(shù)據(jù)拼接

e.g. union all時要求字段的名稱和順序都要保持一致；

e.g. join操作要求兩邊的字段格式一致；

e.g. join關(guān)聯(lián)的時候注意是1對1映射還是1對多映射，小心出現(xiàn)笛卡爾積的情況；

null值

• 正常的數(shù)值和null值做四則運(yùn)算，得到的結(jié)果還是null，建議用isnull、coalesce之類的函數(shù)對null值進(jìn)行處理，或者計算的時候在where字句中過濾null值；
• sum/avg(case when end)操作時要加 else 0 不然會出現(xiàn)null的情況；
• join操作是最常見的出現(xiàn)null的情形(無匹配時)；
• join操作可能會因?yàn)閚ull值產(chǎn)生數(shù)據(jù)傾斜。

函數(shù)錯誤

參數(shù)數(shù)量

e.g. 某函數(shù)需輸入2個參數(shù)，結(jié)果只有1個

參數(shù)格式

e.g. to_date(string timestamp)，select to_date('20161125') 返回值為null，因?yàn)閿?shù)據(jù)格式不是日期時間

e.g. 使用between and時還要注意字段和條件的顆粒度匹配，比如對某個timestamp字段(日期時間格式，帶有時分秒的)時，如下代碼

where?order_time?between?'2020-09-01'?and?'2020-09-15'

判斷條件給到的格式是日期，而字段是日期時間格式，2010-09-15對應(yīng)的日期時間格式是2020-09-15 00:00:00，那么實(shí)際上9月15號0點(diǎn)后的數(shù)據(jù)實(shí)際是沒有被選中的，對于這種情況，可以將原有的日期時間字段用to_date或者substr處理一下。

函數(shù)邏輯

e.g. between 小值 and 大值, 注意最小值在前,最大值在后，這個含義是[小值,大值]，是包含邊界的；

e.g. 函數(shù)datediff中第1個參數(shù)是起始日期(通常是較小值)，第2個參數(shù)是結(jié)束日期(通常為較大值)

邏輯錯誤

數(shù)據(jù)重復(fù)

對于存在一對多關(guān)系的數(shù)據(jù)表關(guān)聯(lián)后會產(chǎn)生數(shù)據(jù)重復(fù)，這種重復(fù)對于sum/avg等非去重的統(tǒng)計計算操作有影響，對count(distinct *)等去重計數(shù)操作沒影響
e.g. 一張母訂單可以對應(yīng)多張子訂單；
e.g. 一個用戶可以對應(yīng)多條交易記錄；

無效篩選

隱藏前提

select?a.col1,b.col2
from?a
left?join?b?on(a.id?=?b.id)
where?b.tag?=?'1'

實(shí)際上b.tag='1' 這個篩選條件已經(jīng)帶有b.tag is not null 的“隱藏前提”了，所以這里用left join 和 join的效果是一樣的。

涉及到轉(zhuǎn)化率的時候，表的順序和轉(zhuǎn)化率的順序是一致的，且不能在where子句中添加后續(xù)流程的篩選條件，不然“隱藏前提”會過濾掉一部分?jǐn)?shù)據(jù)而導(dǎo)致結(jié)果有誤。

標(biāo)簽重疊

建立標(biāo)簽的時候要符合MECE原則(相互獨(dú)立，完全窮盡)；

一般來說建立標(biāo)簽的時候使用簡單的邏輯，每個維度單獨(dú)成列(基礎(chǔ)標(biāo)簽)；e.g. 性別區(qū)分：男、女、未知；

編寫sql進(jìn)行分組統(tǒng)計時，不建議使用“復(fù)合邏輯”標(biāo)簽，復(fù)合標(biāo)簽不僅邏輯上容易出錯(標(biāo)簽重疊)，維護(hù)成本也更高。e.g. 同時考慮會員等級和性別，然后對應(yīng)的標(biāo)簽值就會是：(鐵牌、銅牌、銀牌、金牌、鉆石、皇冠)*(男，女，未知)；

計算用戶數(shù)量時，同一用戶可能會有多個標(biāo)簽(行為標(biāo)簽、屬性標(biāo)簽、不同時間段等)，這樣同一用戶會分別存在多個標(biāo)簽中，對各標(biāo)簽求和會大于實(shí)際用戶數(shù)量。

此外，一個用戶有多個標(biāo)簽時，可能會涉及到多個標(biāo)簽的“或、且、非”運(yùn)算。

e.g. 一個用戶在某一時刻，可能有多張優(yōu)惠券，優(yōu)惠券的狀態(tài)可能是【已使用、已過期、未使用】等，現(xiàn)在要判斷當(dāng)前有“未使用”的優(yōu)惠券。

時間錯位

即數(shù)據(jù)匹配時要在時間維度上要對齊。

e.g. T+1的用戶標(biāo)簽匹配時，昨日的標(biāo)簽匹配今日的交易情況；

多行判斷

假設(shè)訂單表order_info有如下字段

注：

• 實(shí)際支付金額=訂單金額-優(yōu)惠金額
• 訂單有使用優(yōu)惠則fav_amt>0,否則其值為0

篩選第一單使用優(yōu)惠且第二單沒有使用優(yōu)惠的用戶ID，其中可能用到如下邏輯

(rn=1?and?fav_amt>0)
or?
(rn=2?and?fav_amt=0)

然后篩選rn in (1,2) 然后對符合條件的訂單去重計數(shù)=2

篩選條件是針對一行一行的數(shù)據(jù)去匹配的，所以要注意多行條件判斷時行與行之間的or關(guān)系。