데이터베이스 질문과 답변
SQL 기초 지식이 없다보니 ㅜㅜ 쿼리 속도가 너무 느립니다.
각 점검종류(정기, 정밀, 정밀안전진단, 수시) 의 통계 (점검계획수, 점검수, 최근점검일) 을 뽑고 싶은데요....
10초정도 걸립니다 ㅜㅜ 도움 부탁드립니다...
SELECT '정기점검' AS TITLE, '01' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT, SUM(CHK_CNT) AS CHK_CNT, MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN) A
WHERE CHCK_KND_CD IN ('100', 'C0002') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD IN ('100', 'C0002') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD IN ('100', 'C0002') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
) UNION
-- 정밀점검
SELECT '정밀점검' AS TITLE, '02' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT ,SUM(CHK_CNT) AS CHK_CNT ,MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN) A
WHERE CHCK_KND_CD IN ('202', 'C0007') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD IN ('202', 'C0007') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD IN ('202', 'C0007') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
) UNION
-- 정밀안전진단
SELECT '정밀안전진단' AS TITLE, '03' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT ,SUM(CHK_CNT) AS CHK_CNT ,MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN) A
WHERE CHCK_KND_CD IN ('401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD IN ('401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD IN ('401', '402', '403', 'C0004') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
) UNION
-- 수시점검
SELECT '수시점검' AS TITLE, '04' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT ,SUM(CHK_CNT) AS CHK_CNT ,MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN) A
WHERE CHCK_KND_CD NOT IN ('100', 'C0002', '202', 'C0007', '401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD NOT IN ('100', 'C0002', '202', 'C0007', '401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
UNION
SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK) A
WHERE CHCK_KND_CD NOT IN ('100', 'C0002', '202', 'C0007', '401', '402', '403', 'C0004') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
)
ORDER BY SORDER;
by 소주쵝오
[2018.08.20 10:59:26]
union 을 union all 로 변경해보세요~
by 쿨에어
[2018.08.20 11:13:59]
자문자답입니다. ㅎ 글 올리고 게시판 뒤지다가 답을 얻었습니다.
SELECT CHK_KND_CD,
DECODE(CHK_KND_CD, 1, '정기점검', 2, '정밀점검', 3, '정밀안전진단', 4, '수시점검') AS TITLE,
SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT, SUM(CHK_CNT) AS CHK_CNT, MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
SELECT CHK_KND_CD, COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT, 0 AS CHK_CNT, '' AS MAX_CHCK_DE
FROM (SELECT CHK_PLN_NO, FACIL_NO,
CASE WHEN CHCK_KND_CD IN ('100', 'C0002') THEN 1
WHEN CHCK_KND_CD IN ('202', 'C0007') THEN 2
WHEN CHCK_KND_CD IN ('401', '402', '403', 'C0004') THEN 3
ELSE 4
END CHK_KND_CD
, CHCK_DE FROM SCK_CHK_PLN) A
WHERE 1=1
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
AND CHCK_DE BETWEEN '20180601' AND '20180629'
GROUP BY CHK_KND_CD
UNION ALL
-- 점검
SELECT CHK_KND_CD, 0 AS CHK_PLAN_CNT, COUNT(SAFE_CHK_NO) AS CHK_CNT, '' AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO,
CASE WHEN CHCK_KND_CD IN ('100', 'C0002') THEN 1
WHEN CHCK_KND_CD IN ('202', 'C0007') THEN 2
WHEN CHCK_KND_CD IN ('401', '402', '403', 'C0004') THEN 3
ELSE 4
END CHK_KND_CD
, CHCK_DE FROM SCK_CHK) A
WHERE 1=1
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
AND CHCK_DE BETWEEN '20180601' AND '20180629'
GROUP BY CHK_KND_CD
UNION ALL
-- 최근점검일
SELECT CHK_KND_CD, 0 AS CHK_PLAN_CNT, 0 AS CHK_CNT, MAX(CHCK_DE) AS MAX_CHCK_DE
FROM (SELECT SAFE_CHK_NO, FACIL_NO,
CASE WHEN CHCK_KND_CD IN ('100', 'C0002') THEN 1
WHEN CHCK_KND_CD IN ('202', 'C0007') THEN 2
WHEN CHCK_KND_CD IN ('401', '402', '403', 'C0004') THEN 3
ELSE 4
END CHK_KND_CD
, CHCK_DE FROM SCK_CHK) A
WHERE 1=1
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
GROUP BY CHK_KND_CD
)
GROUP BY CHK_KND_CD
ORDER BY CHK_KND_CD;
by 우리집아찌
[2018.08.20 11:21:32]
/* 밑의 SQL은 예를 듭겁니다.
같은 테이블은 한번만 읽게해주세요.
아래의 조건으로 나누시거나
그룹핑할수 있으시면 GROUP BY 사용하시면 됩니다. ( 안되면 WITH 문으로 정의하셔도 됩니다)
CHCK_KND_CD IN ('100', 'C0002') -- 정기점검
CHCK_KND_CD IN ('202', 'C0007') -- 정밀점검
CHCK_KND_CD IN ('401', '402', '403', 'C0004') -- 정밀안전진단
*/
SELECT COUNT(CASE WHEN CHK_PLN_NO IN ('100', 'C0002') THEN 1 END ) AS CHK_PLAN_CNT -- 정기점검
, COUNT(CASE WHEN CHK_PLN_NO IN ('202', 'C0007') THEN 1 END ) AS CHK_PLAN_CNT -- 정기점검
, COUNT(CASE WHEN CHK_PLN_NO IN ('401', '402', '403', 'C0004') THEN 1 END ) AS CHK_PLAN_CNT -- 정기점검
, 0 AS CHK_CNT
, '' AS MAX_CHCK_DE
FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN) A
WHERE CHCK_DE BETWEEN '20180601' AND '20180629'
AND CHCK_KND_CD IN ('100', 'C0002','202', 'C0007','401', '402', '403', 'C0004' )
AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
by 마농
[2018.08.20 12:40:16]
SELECT DECODE(sorder, 1, '정기점검', 2, '정밀점검', 3, '정밀안전진단', 4, '수시점검') title
, sorder
, COUNT(chk_plan_cnt) chk_plan_cnt
, COUNT(chk_cnt) chk_cnt
, MAX(chck_de) chck_de
FROM (SELECT CASE WHEN chck_knd_cd IN ('100', 'C0002') THEN 1
WHEN chck_knd_cd IN ('202', 'C0007') THEN 2
WHEN chck_knd_cd IN ('401', '402', '403', 'C0004') THEN 3
ELSE 4 END sorder
, 1 chk_plan_cnt
, null chk_cnt
, null chck_de
FROM sck_chk_pln
WHERE facil_no IN (SELECT facil_no FROM sck_master WHERE sigungu_cd = '11500')
AND chck_de BETWEEN '20180601' AND '20180629'
AND safe_chk_no IS NOT NULL
UNION ALL
SELECT CASE WHEN chck_knd_cd IN ('100', 'C0002') THEN 1
WHEN chck_knd_cd IN ('202', 'C0007') THEN 2
WHEN chck_knd_cd IN ('401', '402', '403', 'C0004') THEN 3
ELSE 4 END sorder
, null chk_plan_cnt
, CASE WHEN chck_de BETWEEN '20180601' AND '20180629' THEN 1 END chk_cnt
, chck_de
FROM sck_chk
WHERE facil_no IN (SELECT facil_no FROM sck_master WHERE sigungu_cd = '11500')
AND chck_de <= TO_CHAR(sysdate, 'yyyymmdd')
AND safe_chk_no IS NOT NULL
)
GROUP BY sorder
ORDER BY sorder
;
by 쿨에어
[2018.08.20 14:18:58]
답변 감사합니다. 많이 배우고 있습니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.