背景
业务方要求获取2018年,系统中待流失客户的交易额
数据模型
t_customer_pool
表存储的是待流失客户,每个月1号都会有定时任务按照规则刷新表中的数据- 如果一个客户,一年到头都是待流失客户,那么这个客户在表中会有12条记录
查询SQL
错误例子 V1.0
SELECT
c.cust_id "客户ID",
c.cust_name "客户名称",
sum(o.amount) "交易金额"
FROM
t_order o
INNER JOIN t_customer c ON o.cust_id = c.cust_id
WHERE
o.pay_time >= '2018-01-01 00:00:00'
AND o.pay_time <= '2018-12-31 23:59:59'
AND c.cust_id IN (
SELECT DISTINCT
cust_id
FROM
t_customer_pool
WHERE
create_time >= '2018-01-01 00:00:00'
AND create_time <= '2018-12-31 23:59:59'
)
GROUP BY
c.cust_id;
复制代码
问题:
- 不应该使用t_order表驱动t_customer表(大表join小表),应该使用t_customer表驱动t_order表(小表join大表)
- 待流失客户有可能是没有交易的,在t_order表中没有记录,所以应该使用left join
按照存在的问题,去修改,得出错误例子 V2.0
错误例子 V2.0
SELECT
c.cust_id "客户ID",
c.cust_name "客户名称",
sum(o.amount) "交易金额"
FROM
t_customer c
LEFT JOIN t_order o ON o.cust_id = c.cust_id
AND o.pay_time >= '2018-01-01 00:00:00'
AND o.pay_time <= '2018-12-31 23:59:59'
WHERE
c.cust_id IN (
SELECT DISTINCT
cust_id
FROM
t_customer_pool
WHERE
create_time >= '2018-01-01 00:00:00'
AND create_time <= '2018-12-31 23:59:59'
)
GROUP BY
c.cust_id;
复制代码
备注
- left join某个表后,该表的筛选条件不要写在where条件后面,不然left join 会变成 inner join
- 信心满满把这条SQL交给DBA执行(测试环境验证通过),结果DBA说SQL执行了很长时间没得出结果,只能kill掉
- 线上环境的数据量是测试环境的几十倍,数据量一上来,隐藏问题就被放大了
问题:
- t_order表会被全表扫描,应该使用子查询,先查出2018年内的订单
- join表的on语句上最好只添加连接表的条件,筛选条件写在where后面,避免写在on条件后面
- group by 是先分组,后排序,应该加上order by null
正确例子
SELECT
c.cust_id "客户ID",
c.cust_name "客户名称",
sum(o.amount) "交易金额"
FROM
t_customer c
LEFT JOIN (
SELECT
order_id,
amount,
cust_id
FROM
t_order
WHERE
pay_time >= '2018-01-01 00:00:00'
AND pay_time <= '2018-12-31 23:59:59'
) o ON o.cust_id = c.cust_id
WHERE
c.cust_id IN (
SELECT DISTINCT
cust_id
FROM
t_customer_pool
WHERE
create_time >= '2018-01-01 00:00:00'
AND create_time <= '2018-12-31 23:59:59'
)
GROUP BY
c.cust_id
ORDER BY
NULL;
复制代码
如果大家有更好的方法,欢迎在文章下面评论