케이스 유니온 마지막 도와주세요 0 1 586

by 스티브1조 [2018.03.08 19:59:53]


WITH TEMP AS 
(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 areaInfo 
     , TO_CHAR(SUM(reqCnt), '999,999') reqCnt
     , TO_CHAR(SUM(winCnt), '999,999') winCnt
     , RTRIM(TO_CHAR(
       RATIO_TO_REPORT(SUM(winCnt)) OVER(PARTITION BY GROUPING(age)) * 100
       , 'fm990.99'), '.') || '%' winAvg
     , RTRIM(TO_CHAR(SUM(winCnt) / SUM(reqCnt) * 100, 'fm990.99'), '.') || '%' ageAvg
  FROM (SELECT age
             , evt_req_cnt reqCnt
             , evt_win_cnt winCnt
          FROM summary_btpoint
         UNION ALL
        SELECT age
             , evt_req_cnt1 + evt_req_cnt2 + evt_req_cnt3 reqCnt
             , evt_win_cnt1 + evt_win_cnt2 + evt_win_cnt3 winCnt
          FROM summary_aprilbox
         UNION ALL
        SELECT age
             , evt_req_cnt reqCnt
             , evt_win_cnt winCnt
          FROM summary_scratch
          
        )a
 GROUP BY (age))SELECT  areaInfo,reqCnt,winCnt,winAvg,ageAvg FROM TEMP
;


결과가 
60세 이상	  37,696	     975	4.17%	2.59%
60세 이상	   2,418	      50	0.21%	2.07%
25세~29세	   2,461	      67	0.29%	2.72%
15세~19세	      20	       8	0.03%	40%
60세 이상	      20	       5	0.02%	25%
40세~49세	   9,278	      43	0.18%	0.46%
15세 미만	  73,562	   3,562	15.25%	4.84%
40세~49세	  27,541	   2,574	11.02%	9.35%
60세 이상	   4,493	      50	0.21%	1.11%
25세~29세	   2,623	     116	0.5%	4.42%
50세~59세	   1,751	      36	0.15%	2.06%
50세~59세	   5,635	      72	0.31%	1.28%

나이별로 그룹으로 묵고 그룹핑해서 맨위에 총응모자 당첨자 비율 보이게 하고싶어요 도와주세요

 

by 마농 [2018.03.09 09:34:08]
SELECT NVL(age, '총인원') ageInfo
     , TO_CHAR(SUM(reqCnt), '999,999') reqCnt
     , TO_CHAR(SUM(winCnt), '999,999') winCnt
     , RTRIM(TO_CHAR(
       RATIO_TO_REPORT(SUM(winCnt)) OVER(PARTITION BY GROUPING(age)) * 100
       , 'fm990.99'), '.') || '%' winAvg
     , RTRIM(TO_CHAR(SUM(winCnt) / SUM(reqCnt) * 100, 'fm990.99'), '.') || '%' ageAvg
  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 age
             , reqCnt
             , winCnt
          FROM (SELECT age
                     , evt_req_cnt reqCnt
                     , evt_win_cnt winCnt
                  FROM summary_btpoint
                 UNION ALL
                SELECT age
                     , evt_req_cnt1 + evt_req_cnt2 + evt_req_cnt3 reqCnt
                     , evt_win_cnt1 + evt_win_cnt2 + evt_win_cnt3 winCnt
                  FROM summary_aprilbox
                 UNION ALL
                SELECT age
                     , evt_req_cnt reqCnt
                     , evt_win_cnt winCnt
                  FROM summary_scratch
                ) a
        ) a
 GROUP BY ROLLUP(age)
 ORDER BY a.age NULLS FIRST
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입