SQL常见错误指南

2023-03-06 00:00:00 字段 多个 日期 标签 匹配


以下文章来源于公众号-dataxon ,作者Ahong

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,建议用isnullcoalesce之类的函数对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 joinjoin的效果是一样的。

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

标签重叠

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

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

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

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

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

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

时间错位

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

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

多行判断

假设订单表order_info有如下字段

字段名(En) 字段名
order_id 订单号
user_id 用户ID
create_time 订单生成时间
order_amt 订单金额(优惠前)
fav_amt 优惠金额
pay_amt 实际支付金额=订单金额-优惠金额

注:

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

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

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

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

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



相关文章