一次查询优化...

部门同事来找我说现在用的几个存储过程(离职同事写的)查询太慢了, 要几个小时才能出结果, 于是我打开后看到了如此的内容...

我也不是说有什么优越感, 但是这槽点也太大了...

以下是部分片段, 该存储过程总计 900 行, 类似的计算部分大概有四百行多, 剩余部分全是字符串拼接

该同事还留下了另外几个存储过程等待修改, 看到五百行往上的满篇 left join, with 套 with

十几个表 left join, 还是 on a.col1 >= b.col2 and a.col2 < b.col2 这种连接

现在只想摸鱼


-- GMV
SET
    @start_gmv = (
        SELECT
            SUM(`客户付款总金额(￥)`)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = start_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

SET
    @end_gmv = (
        SELECT
            SUM(`客户付款总金额(￥)`)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = end_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

-- 订单量
SET
    @start_order = (
        SELECT
            COUNT(*)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = start_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

SET
    @end_order = (
        SELECT
            COUNT(*)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = end_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

-- 产品销量
SET
    @start_sale = (
        SELECT
            SUM(`产品销量`)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = start_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

SET
    @end_sale = (
        SELECT
            SUM(`产品销量`)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = end_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

-- 客单价
SET
    @start_atv = (
        SELECT
            SUM(`客户付款总金额(￥)`) / COUNT(*)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = start_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

SET
    @end_atv = (
        SELECT
            SUM(`客户付款总金额(￥)`) / COUNT(*)
        FROM
            `历史订单明细`
        WHERE
            `测算日期范围` = end_estimate_date
            AND `部门` LIKE CONCAT('%', @department, '%')
            AND `经营团队` LIKE CONCAT('%', @management_team, '%')
            AND `组别` LIKE CONCAT('%', @groups, '%')
    );

-- 产品成本占比
...