工作中遇到的慢查询sql问题演变成面试题
背景:
有一张数据量为500w条记录的订单记录表invoke_log,有多个字段(记录创建时间、订单编号,调用状态…),其中调用状态由两个值1(失败)和2(成功)。业务代码有一个cron会一直对订单进行检查,如果失败会插入一条状态为1的数据,直到检查成功插入一条记录为2的数据。
问题:请写出你认为效率最高的SQL(可以创建适当索引)找出满足如下要求的订单编号:
- 要求订单数据必须是在2019/1/2号到2019/1/5这四天内新产生数据,如果之前有订单记录则过滤掉。例如:11111这个订单,在2019/1/1第一次产生,所以应该过滤掉。
- 最终状态为失败的数据(invoke_status=1)。例如:222222这条记录有两条记录,第一条是失败,第二条是成功,则认为最终为成功,过滤掉该订单。
sql正确结果为:order_code = 777777
SELECT
(CASE WHEN sb3.result > 0 THEN '0' ELSE '1' END) AS invokeStatus,
sb3.order_code AS orderCode
FROM
(
SELECT
sb1.order_code,
sb1.result
FROM
(
SELECT
sb.order_code,
SUM(CASE WHEN sb.invoke_status = '1' THEN -1 ELSE 100000 END) AS result
FROM
sw_biz_invoke_log sb
WHERE
sb.invoke_type = '3'
AND DATE_SUB(NOW(), INTERVAL 7 DAY) <= sb.create_time
GROUP BY
sb.order_code
) sb1
LEFT JOIN sw_biz_invoke_log sb2 ON sb2.order_code = sb1.order_code
AND DATE_SUB(NOW(), INTERVAL 7 DAY) > sb2.create_time
WHERE
sb2.order_code IS NULL
) sb3
WHERE
sb3.result < 0