데이터베이스 질문과 답변
SELECT T2.ageInfo AS ageInfo,
TO_CHAR(NVL(T2.BUY_CNT1,0),'999,999') as buy1Cnt,
TO_CHAR(NVL(T2.BUY_CNT2,0),'999,999') as buy2Cnt,
TO_CHAR(NVL(T2.BUY_CNT3,0),'999,999') as buy3Cnt,
NVL( T1.req1Cnt,0) as req1Cnt,
NVL( T1.win1Cnt,0) as win1Cnt,
NVL( T1.age1avg,0) as age1avg,
NVL( T1.req2Cnt,0) as req2Cnt,
NVL( T1.win2Cnt,0) as win2Cnt,
NVL( T1.age2avg,0) as age2avg,
NVL( T1.req2Cnt,0) as req3Cnt,
NVL( T1.win3Cnt,0) as win3Cnt,
NVL( T1.age3avg,0) as age3avg
FROM
(SELECT
' 총인원' AS ageInfo,
TO_CHAR(SUM(evt_req_cnt1),'999,999') AS req1Cnt,
TO_CHAR(SUM(evt_win_cnt1),'999,999') AS win1Cnt,
CONCAT(TO_CHAR(round( SUM(evt_win_cnt1) *100 / DECODE(SUM(evt_req_cnt1),0,NULL,SUM(evt_req_cnt1)),2 ),'fm990.99'),'%') AS age1avg,
TO_CHAR(SUM(evt_req_cnt1) + SUM(evt_req_cnt2),'999,999') AS req2Cnt,
TO_CHAR(SUM(evt_win_cnt2),'999,999') AS win2Cnt,
CONCAT(TO_CHAR(round( SUM(evt_win_cnt2) *100 / DECODE(SUM(evt_req_cnt1) + SUM(evt_req_cnt2),0,NULL,SUM(evt_req_cnt1) + SUM(evt_req_cnt2)),2 ),'fm990.99'),'%') AS age2avg,
TO_CHAR(SUM(evt_req_cnt1) + SUM(evt_req_cnt2) + SUM(evt_req_cnt3),'999,999') AS req3Cnt,
TO_CHAR(SUM(evt_win_cnt3),'999,999') AS win3Cnt,
CONCAT(TO_CHAR(round( SUM(evt_win_cnt3) *100 / DECODE(SUM(evt_req_cnt1) + SUM(evt_req_cnt2) + SUM(evt_req_cnt3),0,NULL,SUM(evt_req_cnt1) + SUM(evt_req_cnt2) + SUM(evt_req_cnt3)),2 ),'fm990.99'),'%') AS age3avg
FROM SUMMARY_APRILBOX
where evt_dt BETWEEN REPLACE('2018-04-01','-','') AND REPLACE('2018-04-01','-','')
UNION all
SELECT
CASE WHEN age < 15 THEN '15세 미만'
WHEN age >= 15 AND age < 20 THEN '15세~19세'
WHEN age >= 20 AND age < 25 THEN '20세~24세'
WHEN age >= 25 AND age < 30 THEN '25세~29세'
WHEN age >= 30 AND age < 35 THEN '30세~34세'
WHEN age >= 35 AND age < 40 THEN '35세~39세'
WHEN age >= 40 AND age < 50 THEN '40세~49세'
WHEN age >= 50 AND age < 60 THEN '50세~59세'
WHEN age >= 60 THEN '60세 이상'
END AS ageInfo,
TO_CHAR(SUM(evt_req_cnt1),'999,999') AS req1Cnt,
TO_CHAR(SUM(evt_win_cnt1),'999,999') AS win1Cnt,
CONCAT(TO_CHAR(round( SUM(evt_win_cnt1) *100 / DECODE(SUM(evt_req_cnt1),0,NULL,SUM(evt_req_cnt1)),2 ),'fm990.99'),'%') AS age1avg,
TO_CHAR(SUM(evt_req_cnt1) + SUM(evt_req_cnt2),'999,999') AS req2Cnt,
TO_CHAR(SUM(evt_win_cnt2),'999,999') AS win2Cnt,
CONCAT(TO_CHAR(round( SUM(evt_win_cnt2) *100 / DECODE(SUM(evt_req_cnt1) + SUM(evt_req_cnt2),0,NULL,SUM(evt_req_cnt1) + SUM(evt_req_cnt2)),2 ),'fm990.99'),'%') AS age2avg,
TO_CHAR(SUM(evt_req_cnt1) + SUM(evt_req_cnt2) + SUM(evt_req_cnt3),'999,999') AS req3Cnt,
TO_CHAR(SUM(evt_win_cnt3),'999,999') AS win3Cnt,
CONCAT(TO_CHAR(round( SUM(evt_win_cnt3) *100 / DECODE(SUM(evt_req_cnt1) + SUM(evt_req_cnt2) + SUM(evt_req_cnt3),0,NULL,SUM(evt_req_cnt1) + SUM(evt_req_cnt2) + SUM(evt_req_cnt3)),2 ),'fm990.99'),'%') AS age3avg
FROM SUMMARY_APRILBOX
where evt_dt BETWEEN REPLACE('2018-04-01','-','') AND REPLACE('2018-04-01','-','')
GROUP BY
CASE WHEN age < 15 THEN '15세 미만'
WHEN age >= 15 AND age < 20 THEN '15세~19세'
WHEN age >= 20 AND age < 25 THEN '20세~24세'
WHEN age >= 25 AND age < 30 THEN '25세~29세'
WHEN age >= 30 AND age < 35 THEN '30세~34세'
WHEN age >= 35 AND age < 40 THEN '35세~39세'
WHEN age >= 40 AND age < 50 THEN '40세~49세'
WHEN age >= 50 AND age < 60 THEN '50세~59세'
WHEN age >= 60 THEN '60세 이상'
END ORDER BY ageInfo
)T1,
(SELECT CASE WHEN age < 15 THEN '15세 미만'
WHEN age >= 15 AND age < 20 THEN '15세~19세'
WHEN age >= 20 AND age < 25 THEN '20세~24세'
WHEN age >= 25 AND age < 30 THEN '25세~29세'
WHEN age >= 30 AND age < 35 THEN '30세~34세'
WHEN age >= 35 AND age < 40 THEN '35세~39세'
WHEN age >= 40 AND age < 50 THEN '40세~49세'
WHEN age >= 50 AND age < 60 THEN '50세~59세'
WHEN age >= 60 THEN '60세 이상'
END AS ageInfo,
COUNT(DECODE(BUY_CNT, 1, 1, NULL)) AS BUY_CNT1, COUNT(DECODE(BUY_CNT, 2, 1, NULL)) AS BUY_CNT2, COUNT(CASE WHEN BUY_CNT >= 3 THEN 1 ELSE NULL END) AS BUY_CNT3
FROM (
SELECT MEMBER_NO, age, SUM(BUY_CNT)-SUM(RETURN_CNT) AS BUY_CNT
from SUMMARY_MBO_ORDER GROUP BY MEMBER_NO, age
) GROUP BY (age)
ORDER BY age )T2
WHERE T2.ageInfo = T1.ageInfo(+)
ORDER BY ageInfo
코드는이거구요 롤업해서 합계구하고싶은데 잘안대네요 조인이 잘못되서그런건지 도와주세요
by 마농
[2018.03.21 14:10:17]
SELECT a.ageInfo
, TO_CHAR(NVL(a.buy1Cnt, 0), 'fm999,990') AS buy1Cnt
, TO_CHAR(NVL(a.buy2Cnt, 0), 'fm999,990') AS buy2Cnt
, TO_CHAR(NVL(a.buy3Cnt, 0), 'fm999,990') AS buy3Cnt
, TO_CHAR(NVL(b.req1Cnt, 0), 'fm999,990') AS req1Cnt
, TO_CHAR(NVL(b.win1Cnt, 0), 'fm999,990') AS win1Cnt
, TO_CHAR(NVL(b.age1avg, 0), 'fm990.00' )||'%' AS age1avg
, TO_CHAR(NVL(b.req2Cnt, 0), 'fm999,990') AS req2Cnt
, TO_CHAR(NVL(b.win2Cnt, 0), 'fm999,990') AS win2Cnt
, TO_CHAR(NVL(b.age2avg, 0), 'fm990.00' )||'%' AS age2avg
, TO_CHAR(NVL(b.req2Cnt, 0), 'fm999,990') AS req3Cnt
, TO_CHAR(NVL(b.win3Cnt, 0), 'fm999,990') AS win3Cnt
, TO_CHAR(NVL(b.age3avg, 0), 'fm990.00' )||'%' AS age3avg
FROM (SELECT NVL(ageInfo, ' 총인원') AS ageInfo
, COUNT(CASE WHEN buy_cnt = 1 THEN 1 END) AS buy1Cnt
, COUNT(CASE WHEN buy_cnt = 2 THEN 1 END) AS buy2Cnt
, COUNT(CASE WHEN buy_cnt >= 3 THEN 1 END) AS buy3Cnt
FROM (SELECT CASE WHEN age < 15 THEN '15세 미만'
WHEN age >= 15 AND age < 20 THEN '15세~19세'
WHEN age >= 20 AND age < 25 THEN '20세~24세'
WHEN age >= 25 AND age < 30 THEN '25세~29세'
WHEN age >= 30 AND age < 35 THEN '30세~34세'
WHEN age >= 35 AND age < 40 THEN '35세~39세'
WHEN age >= 40 AND age < 50 THEN '40세~49세'
WHEN age >= 50 AND age < 60 THEN '50세~59세'
WHEN age >= 60 THEN '60세 이상'
END AS ageInfo
, SUM(buy_cnt) - SUM(return_cnt) AS buy_cnt
FROM summary_mbo_order
GROUP BY member_no, age
)
GROUP BY ROLLUP(ageInfo)
) a
, (SELECT NVL(ageInfo, ' 총인원') AS ageInfo
, SUM(req1Cnt) AS req1Cnt
, SUM(req2Cnt) AS req2Cnt
, SUM(req3Cnt) AS req3Cnt
, SUM(win1Cnt) AS win1Cnt
, SUM(win2Cnt) AS win2Cnt
, SUM(win3Cnt) AS win3Cnt
, ROUND(SUM(win1Cnt) / NULLIF(SUM(req1Cnt), 0) * 100, 2) AS age1avg
, ROUND(SUM(win2Cnt) / NULLIF(SUM(req2Cnt), 0) * 100, 2) AS age2avg
, ROUND(SUM(win3Cnt) / NULLIF(SUM(req3Cnt), 0) * 100, 2) AS age3avg
FROM (SELECT CASE WHEN age < 15 THEN '15세 미만'
WHEN age >= 15 AND age < 20 THEN '15세~19세'
WHEN age >= 20 AND age < 25 THEN '20세~24세'
WHEN age >= 25 AND age < 30 THEN '25세~29세'
WHEN age >= 30 AND age < 35 THEN '30세~34세'
WHEN age >= 35 AND age < 40 THEN '35세~39세'
WHEN age >= 40 AND age < 50 THEN '40세~49세'
WHEN age >= 50 AND age < 60 THEN '50세~59세'
WHEN age >= 60 THEN '60세 이상'
END AS ageInfo
, evt_req_cnt1 AS req1Cnt
, evt_req_cnt1 + evt_req_cnt2 AS req2Cnt
, evt_req_cnt1 + evt_req_cnt2 + evt_req_cnt3 AS req3Cnt
, evt_win_cnt1 AS win1Cnt
, evt_win_cnt2 AS win2Cnt
, evt_win_cnt3 AS win3Cnt
FROM summary_aprilbox
WHERE evt_dt BETWEEN REPLACE('2018-04-01','-','') AND REPLACE('2018-04-01','-','')
)
GROUP BY ROLLUP(ageInfo)
) b
WHERE a.ageInfo = b.ageInfo(+)
ORDER BY ageInfo
;
by 스티브1조
[2018.03.21 15:18:09]
3시간 삽질했는데 감사합니다 ㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.