데이터베이스 질문과 답변
SELECT DISTINCT A.DT
, A.PS1H_INCT
, A.PS2H_INCT
, A.PS6H_INCT
, A.PS24_INCT
, A.PS1H_OTCT
, A.PS2H_OTCT
, A.PS6H_OTCT
, A.PS24_OTCT
, SUM(NVL(A.PS1H_INCT, 0) - NVL(A.PS1H_OTCT, 0)) OVER(ORDER BY A.DT) AS PS1H_DATA
, SUM(NVL(A.PS2H_INCT, 0) - NVL(A.PS2H_OTCT, 0)) OVER(ORDER BY A.DT) AS PS2H_DATA
, SUM(NVL(A.PS6H_INCT, 0) - NVL(A.PS6H_OTCT, 0)) OVER(ORDER BY A.DT) AS PS6H_DATA
, SUM(NVL(A.PS24_INCT, 0) - NVL(A.PS24_OTCT, 0)) OVER(ORDER BY A.DT) AS PS24_DATA
FROM (
/*발급*/
SELECT DISTINCT
ISSU_DATE AS DT
, SUM(NVL(PS1H_INCT,0)) AS PS1H_INCT
, SUM(NVL(PS2H_INCT,0)) AS PS2H_INCT
, SUM(NVL(PS6H_INCT,0)) AS PS6H_INCT
, SUM(NVL(PS24_INCT,0)) AS PS24_INCT
,0 AS PS1H_OTCT
,0 AS PS2H_OTCT
,0 AS PS6H_OTCT
,0 AS PS24_OTCT
FROM GA_PASSCT
GROUP BY ISSU_DATE
UNION ALL
/*지급*/
SELECT
NVL(B.VSIC_DATE, APPL_DATE) AS DT
,0 AS PS1H_INCT
,0 AS PS2H_INCT
,0 AS PS6H_INCT
,0 AS PS24_INCT
, SUM(NVL(A.PS1H_OTCT,0)) AS PS1H_OTCT
, SUM(NVL(A.PS2H_OTCT,0)) AS PS2H_OTCT
, SUM(NVL(A.PS6H_OTCT,0)) AS PS6H_OTCT
, SUM(NVL(A.PS24_OTCT,0)) AS PS24_OTCT
FROM GA_PARKPS A
LEFT JOIN GA_VSITOR B ON A.APPL_NUMB = B.APPL_NUMB
WHERE NVL(A.APCN_CODE,'1')='1'
AND B.VSIC_DATE IS NOT NULL
GROUP BY NVL(B.VSIC_DATE, APPL_DATE)
) A
WHERE A.DT IS NOT NULL
ORDER BY A.DT
;
위와 같은 쿼리를 사용하였을 때 아래와 같은 결과 값이 나옵니다.
20190118 15 15 15 10 0 0 0 0 15 13 15 10
20190118 0 0 0 0 0 2 0 0 15 13 15 10
20190121 10 10 10 10 0 0 0 0 25 23 25 20
유니온을 사용하였지만 같은 날짜일때 각 컬럼 들이 따로 나오게 됩니다...
아래와 같이 같은 날짜가 한번에 나오면서 조회될 방법은 없을까요...?
20190118 15 15 15 10 2 0 0 15 15 13 15 10
by 마농
[2019.01.22 16:10:49]
불필요한 군더더기 코드 및 잘못 작성된 코드들이 많네요.
Distinct, Left Join, NVL 등등.
한줄로 합치는 것은 한번 더 그룹바이 하세요.
SELECT dt
, SUM(ps1h_inct) ps1h_inct
, SUM(ps2h_inct) ps2h_inct
, SUM(ps6h_inct) ps6h_inct
, SUM(ps24_inct) ps24_inct
, SUM(ps1h_otct) ps1h_otct
, SUM(ps2h_otct) ps2h_otct
, SUM(ps6h_otct) ps6h_otct
, SUM(ps24_otct) ps24_otct
, SUM(SUM(ps1h_inct - ps1h_otct)) OVER(ORDER BY dt) ps1h_data
, SUM(SUM(ps2h_inct - ps2h_otct)) OVER(ORDER BY dt) ps2h_data
, SUM(SUM(ps6h_inct - ps6h_otct)) OVER(ORDER BY dt) ps6h_data
, SUM(SUM(ps24_inct - ps24_otct)) OVER(ORDER BY dt) ps24_data
FROM (SELECT issu_date dt
, NVL(SUM(ps1h_inct), 0) ps1h_inct
, NVL(SUM(ps2h_inct), 0) ps2h_inct
, NVL(SUM(ps6h_inct), 0) ps6h_inct
, NVL(SUM(ps24_inct), 0) ps24_inct
, 0 ps1h_otct
, 0 ps2h_otct
, 0 ps6h_otct
, 0 ps24_otct
FROM gapassct
WHERE issu_date IS NOT NULL
GROUP BY issu_date
UNION ALL
SELECT b.vsic_date dt
, 0 ps1h_inct
, 0 ps2h_inct
, 0 ps6h_inct
, 0 ps24_inct
, NVL(SUM(a.ps1h_otct), 0) ps1h_otct
, NVL(SUM(a.ps2h_otct), 0) ps2h_otct
, NVL(SUM(a.ps6h_otct), 0) ps6h_otct
, NVL(SUM(a.ps24_otct), 0) ps24_otct
FROM ga_parkps a
INNER JOIN ga_visitor b
ON a.appl_numb = b.appl_numb
WHERE NVL(a.apcn_code, '1') = '1'
AND b.vsic_date IS NOT NULL
GROUP BY b.vsic_date
)
GROUP BY dt
ORDER BY dt
;
by 제블리
[2019.01.22 16:34:58]
감사합니다! 한번 더 그룹바이를 생각했어야 했는데...
불필요한 코드는 지우고 다시 적용해 보겠습니다 . 감사합니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.