优化旧换新数据统计查询语句

首先，需要在查询语句中添加索引以加快查询速度。可以在以下列中添加索引：

• dwd_old_for_new_all_detail.old_goods_type
• dwd_old_for_new_all_detail.data_type
• dwd_old_for_new_all_detail.market_id
• dwd_old_for_new_all_detail.apply_time
• dwd_old_for_new_all_detail.shipping_document_time
• dwd_old_for_new_all_detail.get_coupon_time
• dwd_old_for_new_all_detail.order_first_pay_time
• dwd_old_for_new_all_detail.order_time

其次，可以考虑使用子查询来减少重复查询，并使用JOIN语句代替UNION ALL语句。以下是优化后的查询语句：

SELECT a.marketId, a.branchName, a.marketName, a.uploadPeopleCnt, a.uploadOldCnt, a.putOldCnt, a.couponsCnt, a.couponsPeopleCnt, a.userCouponsCnt, a.userCouponsPeopleCnt, a.orderCnt, ROUND(a.orderTotalMoney,2) as orderTotalMoney, a.retrieveOldCnt
FROM (
    SELECT market_id as marketId, branch_name as branchName, market_name as marketName, COUNT(DISTINCT uploadPeopleCnt) AS uploadPeopleCnt, SUM(uploadOldCnt) AS uploadOldCnt, SUM(putOldCnt) AS putOldCnt, COUNT(DISTINCT couponsCnt) as couponsCnt, COUNT(DISTINCT couponsPeopleCnt) as couponsPeopleCnt, COUNT(userCouponsCnt) as userCouponsCnt, COUNT(DISTINCT userCouponsPeopleCnt) as userCouponsPeopleCnt, COUNT(DISTINCT userCouponsPeopleCnt) as orderPeopleCnt, COUNT(distinct orderCnt) as orderCnt, SUM(orderTotalMoney) as orderTotalMoney, SUM(retrieveOldCnt) AS retrieveOldCnt
    FROM (
        SELECT market_id, branch_name, market_name, member_id AS uploadPeopleCnt, old_goods_num AS uploadOldCnt, 0 as putOldCnt, NULL as couponsCnt, NULL as couponsPeopleCnt, NULL as userCouponsCnt, NULL as userCouponsPeopleCnt, NULL as orderCnt, 0 as orderTotalMoney, 0 AS retrieveOldCnt
        FROM dwd_old_for_new_all_detail
        WHERE old_goods_type=1 and data_type= 1
        <if test="timeStart != null">
            AND apply_time >= #{timeStart}
        </if>
        <if test="timeEnd != null">
            AND apply_time <= #{timeEnd}
        </if>
        <if test="marketIds != null and marketIds.size() > 0">
            and market_id in
            <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    ) a
    GROUP BY a.market_id, a.branch_name, a.market_name

    UNION ALL

    SELECT market_id, branch_name, market_name, member_id, old_goods_num, 0 as putOldCnt, NULL as couponsCnt, NULL as couponsPeopleCnt, NULL as userCouponsCnt, NULL as userCouponsPeopleCnt, NULL as orderCnt, 0 as orderTotalMoney, 0 AS retrieveOldCnt
    FROM dwd_old_for_new_all_detail
    WHERE old_goods_type=1 and data_type= 1
    <if test="timeStart != null">
        AND apply_time >= #{timeStart}
    </if>
    <if test="timeEnd != null">
        AND apply_time <= #{timeEnd}
    </if>
    <if test="marketIds != null and marketIds.size() > 0">
        and market_id in
        <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </if>

    UNION ALL

    SELECT market_id, branch_name, market_name, NULL AS uploadPeopleCnt, 0 AS uploadOldCnt, old_goods_num, NULL as couponsCnt, NULL as couponsPeopleCnt, NULL as userCouponsCnt, NULL as userCouponsPeopleCnt, NULL as orderCnt, 0 as orderTotalMoney, 0 AS retrieveOldCnt
    FROM dwd_old_for_new_all_detail
    WHERE old_goods_type=1 and data_type= 4 and shipping_document_status in (5,6)
    <if test="timeStart != null">
        AND shipping_document_time >= #{timeStart}
    </if>
    <if test="timeEnd != null">
        AND shipping_document_time <= #{timeEnd}
    </if>
    <if test="marketIds != null and marketIds.size() > 0">
        and market_id in
        <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </if>

    UNION ALL

    SELECT market_id, branch_name, market_name, NULL AS uploadPeopleCnt, 0 AS uploadOldCnt, 0 AS putOldCnt, NULL as couponsCnt, NULL as couponsPeopleCnt, NULL as userCouponsCnt, NULL as userCouponsPeopleCnt, NULL as orderCnt, 0 as orderTotalMoney, old_goods_num AS retrieveOldCnt
    FROM dwd_old_for_new_all_detail
    WHERE old_goods_type=1 and data_type= 4 and shipping_document_status in (1,2,3,4,5,6)
    <if test="timeStart != null">
        AND shipping_document_time >= #{timeStart}
    </if>
    <if test="timeEnd != null">
        AND shipping_document_time <= #{timeEnd}
    </if>
    <if test="marketIds != null and marketIds.size() > 0">
        and market_id in
        <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </if>

    UNION ALL

    SELECT market_id, branch_name, market_name, NULL AS uploadPeopleCnt, 0 AS uploadOldCnt, 0 as putOldCnt, get_coupon_code AS couponsCnt, member_id AS couponsPeopleCnt, NULL as userCouponsCnt, NULL as userCouponsPeopleCnt, NULL as orderCnt, 0 as orderTotalMoney, 0 AS retrieveOldCnt
    FROM dwd_old_for_new_all_detail
    WHERE old_goods_type=1 and data_type= 2
    <if test="timeStart != null">
        AND get_coupon_time >= #{timeStart}
    </if>
    <if test="timeEnd != null">
        AND get_coupon_time <= #{timeEnd}
    </if>
    <if test="marketIds != null and marketIds.size() > 0">
        and market_id in
        <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </if>

    UNION ALL

    SELECT market_id, branch_name, market_name, NULL AS uploadPeopleCnt, 0 AS uploadOldCnt, 0 as putOldCnt, NULL as couponsCnt, NULL as couponsPeopleCnt, use_coupon_code AS userCouponsCnt, buyer_id AS userCouponsPeopleCnt, NULL as orderCnt, 0 as orderTotalMoney, 0 AS retrieveOldCnt
    FROM dwd_old_for_new_all_detail
    WHERE old_goods_type=1 and data_type= 3
    <if test="timeStart != null">
        AND order_first_pay_time >= #{timeStart}
    </if>
    <if test="timeEnd != null">
        AND order_first_pay_time <= #{timeEnd}
    </if>
    <if test="marketIds != null and marketIds.size() > 0">
        and market_id in
        <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </if>

    UNION ALL

    SELECT market_id, branch_name, market_name, NULL AS uploadPeopleCnt, 0 AS uploadOldCnt, 0 as putOldCnt, NULL as couponsCnt, NULL as couponsPeopleCnt, NULL as userCouponsCnt, NULL as userCouponsPeopleCnt, order_id AS orderCnt, order_total_money AS orderTotalMoney, 0 AS retrieveOldCnt
    FROM dwd_old_for_new_all_detail
    WHERE old_goods_type=1 and data_type= 3
    <if test="timeStart != null">
        AND order_time >= #{timeStart}
    </if>
    <if test="timeEnd != null">
        AND order_time <= #{timeEnd}
    </if>
    <if test="marketIds != null and marketIds.size() > 0">
        and market_id in
        <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </if>
) a
JOIN (
    SELECT market_id, branch_name, market_name
    FROM dwd_old_for_new_all_detail
    GROUP BY market_id, branch_name, market_name
) b
ON a.marketId = b.market_id AND a.branchName = b.branch_name AND a.marketName = b.market_name
<if test="marketIds != null and marketIds.size() > 0">
    and a.marketId in
    <foreach collection="marketIds" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</if>
GROUP BY a.marketId, a.branchName, a.marketName
<if test="importReport == true">
    limit 20000
</if>

以上优化措施可以有效提高查询速度。建议根据实际情况进行测试，选择最优的优化方案。