包裹实时统计sql优化

优化前的sql

 <select id="realTimeStatistics" resultType="com.fpx.acs.pojo.vo.manage.PackageRealTimeStatisticsVo">
        SELECT
        COUNT(
        CASE
        WHEN create_time &gt;= #{statisticalTimeBegin} and create_time &lt; #{statisticalTimeEnd}
        THEN 1
        else null
        END
        ) as forecast_count ,

        COUNT(
        CASE
        WHEN arrived_time &gt;= #{statisticalTimeBegin} and arrived_time &lt; #{statisticalTimeEnd}
        THEN 1
        else null
        END
        ) as in_storage_count ,

        COUNT(
        CASE
        WHEN on_shelves_time &gt;= #{statisticalTimeBegin} and on_shelves_time &lt; #{statisticalTimeEnd}
        THEN 1
        else null
        END
        ) as on_shelves_count ,

        COUNT(
        CASE
        WHEN outbound_time &gt;= #{statisticalTimeBegin} and outbound_time &lt; #{statisticalTimeEnd}
        THEN 1
        else null
        END
        ) as out_storage_count
        FROM acs_package ap
        WHERE warehouse_code = #{warehouseCode}
    </select>

explain结果

优化后的sql

 select
        A.forecast_count,
        B.in_storage_count,
        C.on_shelves_count,
        D.out_storage_count
        from
        (
        SELECT
        count(1) as forecast_count
        FROM
        acs_package ap
        where
        warehouse_code = #{warehouseCode}
        and create_time >= #{statisticalTimeBegin}
        and create_time <= #{statisticalTimeEnd}
        ) A,
        (
        SELECT
        count(1) as in_storage_count
        FROM
        acs_package ap
        where
        warehouse_code = #{warehouseCode}
        and arrived_time >= #{statisticalTimeBegin}
        and arrived_time <= #{statisticalTimeEnd}
        ) B,
        (
        SELECT
        count(1) as on_shelves_count
        FROM
        acs_package ap
        where
        warehouse_code = #{warehouseCode}
        and on_shelves_time >= #{statisticalTimeBegin}
        and on_shelves_time <= #{statisticalTimeEnd}
        ) C,
        (
        SELECT
        count(1) as out_storage_count
        FROM
        acs_package ap
        where
        warehouse_code = #{warehouseCode}
        and outbound_time >= #{statisticalTimeBegin}
        and outbound_time <= #{statisticalTimeEnd}
        ) D

explain结果

explain用法