SQL常见错误总结

SQL是数据分析中最高频的操作之一，本文梳理常见的SQL错误，可以归为三大类：

• 语法类；
• 函数类；
• 逻辑类；

语法错误

标点错漏

e.g. 逗号多或缺，括号等不成对，漏写引号、多余的空格等；

e.g. case when … end函数，有时候少写end；

e.g. select含有 聚合函数(count, sum, avg)时，相应字段都要放入group by 后面；

重命名

如果有子查询，那么需要对子查询进行重命名；

表的重命名不要搞混；

数据拼接

e.g. union all时要求字段的名称和顺序都要保持一致；

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

e.g. join关联的时候注意是1对1映射还是1对多映射，小心出现笛卡尔积的情况；

null值

• 正常的数值和null值做四则运算，得到的结果还是null，建议用isnull、coalesce之类的函数对null值进行处理，或者计算的时候在where字句中过滤null值；
• sum/avg(case when end)操作时要加 else 0 不然会出现null的情况；
• join操作是最常见的出现null的情形(无匹配时)；
• join操作可能会因为null值产生数据倾斜。

函数错误

参数数量

e.g. 某函数需输入2个参数，结果只有1个

参数格式

e.g. to_date(string timestamp)，select to_date('20161125') 返回值为null，因为数据格式不是日期时间

e.g. 使用between and时还要注意字段和条件的颗粒度匹配，比如对某个timestamp字段(日期时间格式，带有时分秒的)时，如下代码

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

判断条件给到的格式是日期，而字段是日期时间格式，2010-09-15对应的日期时间格式是2020-09-15 00:00:00，那么实际上9月15号0点后的数据实际是没有被选中的，对于这种情况，可以将原有的日期时间字段用to_date或者substr处理一下。

函数逻辑

e.g. between 小值 and 大值, 注意最小值在前,最大值在后，这个含义是[小值,大值]，是包含边界的；

e.g. 函数datediff中第1个参数是起始日期(通常是较小值)，第2个参数是结束日期(通常为较大值)

逻辑错误

数据重复

对于存在一对多关系的数据表关联后会产生数据重复，这种重复对于sum/avg等非去重的统计计算操作有影响，对count(distinct *)等去重计数操作没影响
e.g. 一张母订单可以对应多张子订单；
e.g. 一个用户可以对应多条交易记录；

无效筛选

隐藏前提

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

实际上b.tag='1' 这个筛选条件已经带有b.tag is not null 的“隐藏前提”了，所以这里用left join 和 join的效果是一样的。

涉及到转化率的时候，表的顺序和转化率的顺序是一致的，且不能在where子句中添加后续流程的筛选条件，不然“隐藏前提”会过滤掉一部分数据而导致结果有误。

标签重叠

建立标签的时候要符合MECE原则(相互独立，完全穷尽)；

一般来说建立标签的时候使用简单的逻辑，每个维度单独成列(基础标签)；e.g. 性别区分：男、女、未知；

编写sql进行分组统计时，不建议使用“复合逻辑”标签，复合标签不仅逻辑上容易出错(标签重叠)，维护成本也更高。e.g. 同时考虑会员等级和性别，然后对应的标签值就会是：(铁牌、铜牌、银牌、金牌、钻石、皇冠)*(男，女，未知)；

计算用户数量时，同一用户可能会有多个标签(行为标签、属性标签、不同时间段等)，这样同一用户会分别存在多个标签中，对各标签求和会大于实际用户数量。

此外，一个用户有多个标签时，可能会涉及到多个标签的“或、且、非”运算。

e.g. 一个用户在某一时刻，可能有多张优惠券，优惠券的状态可能是【已使用、已过期、未使用】等，现在要判断当前有“未使用”的优惠券。

时间错位

即数据匹配时要在时间维度上要对齐。

e.g. T+1的用户标签匹配时，昨日的标签匹配今日的交易情况；

多行判断

假设订单表order_info有如下字段

注：

• 实际支付金额=订单金额-优惠金额
• 订单有使用优惠则fav_amt>0,否则其值为0

筛选第一单使用优惠且第二单没有使用优惠的用户ID，其中可能用到如下逻辑

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

然后筛选rn in (1,2) 然后对符合条件的订单去重计数=2

筛选条件是针对一行一行的数据去匹配的，所以要注意多行条件判断时行与行之间的or关系。