超累人的sql（2）

SELECT
    P.DC_CODE,
    P.DC_NAME,
    P.PARK_NAME,
    A.PARK_CODE,
    A.ALC_DATE,
    L.LUWP_TOTAL_NUM,
    A.LUWP_USE_TOTAL_NUM,
    A.LUWP_WORK_NUM,
    CONCAT( TO_CHAR( ROUND( 100 * A.LUWP_USE_TOTAL_NUM / L.LUWP_TOTAL_NUM, 2 ), 'fm9999999990.00' ), '%' ) LUWP_USAGE,
    ROUND( A.LUWP_USE_TOTAL_NUM / NULLIF( A.LUWP_WORK_NUM, 0 ), 2 ) LUWP_TIMES,
    ROUND(LUWP_TOTAL_NUM * 24) TIMECOUNT,
    A.WORK_TOTAL_TIME,
    CONCAT( TO_CHAR( ROUND( 100 * A.LUWP_WORK_NUM / l.LUWP_TOTAL_NUM, 2 ), 'fm9999999990.00' ), '%' ) LUWP_WORK_USAGE,
    A.WAIT_AVG_TIME,
    A.WOTK_AVG_TIME 
FROM
    (
SELECT
    PARK_CODE,
    ALC_DATE,
    COUNT( DISTINCT OPER_LUWP_CODE ) LUWP_WORK_NUM,
    COUNT( OPER_LUWP_CODE ) LUWP_USE_TOTAL_NUM,
    nvl( TO_CHAR( ROUND( SUM(( DAC_END_TIME - DAC_START_TIME ) * 24 ), 2 ), 'fm9999999990.00' ), 0 ) WORK_TOTAL_TIME,
    AVG(
    ROUND(( DAC_START_TIME - ALC_LUWP_TIME ) * 24, 2 )) WAIT_AVG_TIME,
    AVG(
    ROUND(( DAC_END_TIME - DAC_START_TIME ) * 24, 2 )) WOTK_AVG_TIME 
FROM
    (
SELECT
    D.ALC_BY,
    D.ALC_LUWP_CODE,
    D.OPER_LUWP_CODE,
    D.ALC_LUWP_TIME,
    D.SIGN_START_TIME,
    D.DAC_START_TIME,
    D.DAC_END_TIME,
CASE
    
    WHEN to_char( D.SIGN_START_TIME, 'hh24mi' ) BETWEEN '1030' 
    AND '2400' THEN
        to_char( D.SIGN_START_TIME, 'yyyy-mm-dd' ) 
        WHEN to_char( D.SIGN_START_TIME, 'hh24mi' ) BETWEEN '0000' 
        AND '1029' THEN
            to_char( D.SIGN_START_TIME - 1, 'yyyy-mm-dd' ) ELSE '' 
        END AS ALC_DATE,
        D.park_Code 
    FROM
        GQ_RPT_ACT_DETAIL D
        LEFT JOIN GQ_BASE_PARK P ON D.PARK_CODE = P.PARK_CODE 
    WHERE
        D.STATUS = 1 
        <if test="dcCode != null and dcCode != ''" > and P.DC_CODE = #{dcCode}</if>
            <if test="parkCode != null and parkCode != ''" > and D.PARK_CODE = #{parkCode}</if>
                AND SIGN_START_TIME 
                BETWEEN
                to_date(#{alcDateStart},'yy-mm-dd hh24:mi:ss')    
                AND
                to_date(#{alcDateEnd},'yy-mm-dd hh24:mi:ss')) B 
        GROUP BY
            ALC_DATE,
            PARK_CODE 
            ) A
            
            LEFT JOIN ( SELECT PARK_CODE, COUNT( 1 ) LUWP_TOTAL_NUM FROM GQ_BASE_LUWP WHERE STATUS = 1 GROUP BY PARK_CODE ) L ON L.PARK_CODE = A.PARK_CODE
            LEFT JOIN GQ_BASE_PARK P ON A.PARK_CODE = P.PARK_CODE where p.status = 1
        
        
        ORDER BY A.ALC_DATE DESC