背景
业务方要求获取 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;
如果大家有更好的方法, 欢迎在文章下面评论
来源: https://juejin.im/post/5c5167a2f265da61587787bf