实践分享 - MySQL优化 - 查询去年待流失客户的交易额

t_customer_pool

查询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;
复制代码

问题：

1. 不应该使用t_order表驱动t_customer表（大表join小表）， 应该使用t_customer表驱动t_order表（小表join大表）
2. 待流失客户有可能是没有交易的，在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;
复制代码

问题：

1. t_order表会被全表扫描 ，应该使用子查询，先查出2018年内的订单
2. join表的on语句上最好只添加连接表的条件， 筛选条件写在where后面，避免写在on条件后面
3. 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;
复制代码

如果大家有更好的方法，欢迎在文章下面评论