SELECT
P.DC_CODE,
P.DC_NAME,
P.PARK_NAME,
A.PARK_CODE,
A.ALC_DATE,
A.ALC_TOTAL_NUM,
A.ALC_SYSTEM_NUM,
CONCAT( TO_CHAR(ROUND( 100 * A.ALC_SYSTEM_NUM / A.ALC_TOTAL_NUM, 2 ),'fm9999999990.00'), '%' ) SYSTEM_PERCENT,
A.ALC_USER_NUM,
CONCAT( TO_CHAR(ROUND( 100 * A.ALC_USER_NUM / A.ALC_TOTAL_NUM, 2 ),'fm9999999990.00'), '%' ) USER_PERCENT,
A.ALC_JUMP_NUM,
CONCAT( TO_CHAR(ROUND( 100 * A.ALC_JUMP_NUM / A.ALC_TOTAL_NUM, 2 ),'fm9999999990.00'), '%' ) JUMP_PERCENT,
CASE WHEN A.ALC_AVG_DURATION is NULL THEN 0 ELSE A.ALC_AVG_DURATION end ALC_AVG_DURATION
FROM (
SELECT
PARK_CODE,
ALC_DATE,
COUNT( 1 ) ALC_TOTAL_NUM,
SUM( CASE WHEN ALC_BY != 'SYS' THEN 1 ELSE 0 END ) ALC_USER_NUM,
SUM( CASE WHEN OPER_LUWP_CODE = ALC_LUWP_CODE AND ALC_BY = 'SYS' THEN 1 ELSE 0 END ) ALC_SYSTEM_NUM,
SUM( CASE WHEN (ALC_BY ='SYS' or ALC_BY IS NULL) AND OPER_LUWP_CODE != ALC_LUWP_CODE or ALC_LUWP_CODE IS null and OPER_LUWP_CODE is not null THEN 1 ELSE 0 END )ALC_JUMP_NUM,
ROUND(AVG(
CEIL(( ALC_LUWP_TIME - SIGN_START_TIME ) * 24 * 60 )),0) ALC_AVG_DURATION
FROM
(
SELECT
ALC_BY,
ALC_LUWP_CODE,
OPER_LUWP_CODE,
ALC_LUWP_TIME,
SIGN_START_TIME,
CASE
WHEN to_char( SIGN_START_TIME, 'hh24mi' ) BETWEEN '1030'
AND '2400' THEN
to_char( SIGN_START_TIME, 'yyyy-mm-dd' )
WHEN to_char( SIGN_START_TIME, 'hh24mi' ) BETWEEN '0000'
AND '1029' THEN
to_char( SIGN_START_TIME - 1, 'yyyy-mm-dd' ) ELSE ''
END AS ALC_DATE,
park_Code
FROM
GQ_RPT_ACT_DETAIL
WHERE
STATUS = 1
<if test="parkCode != null and parkCode != ''" > and 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 GQ_BASE_PARK P ON A.PARK_CODE=P.PARK_CODE
WHERE P.STATUS=1
<if test="dcCode != null and dcCode != ''" > and DC_CODE = #{dcCode}</if>
ORDER BY A.ALC_DATE DESC
超累人的SQL
最新推荐文章于 2024-05-19 03:18:57 发布