데이터베이스 질문과 답변
by 스티브1조 [2018.03.05 19:11:28]
EVT_DT VARCHAR2(8 BYTE) No 1 집계일자
SEX VARCHAR2(10 BYTE) No 2 성별
AGE VARCHAR2(20 BYTE) No 3 연령별
AREA VARCHAR2(30 BYTE) No 4 지역별
GIFT_CD VARCHAR2(100 BYTE) No 5 경품별
EVT_WIN_CNT NUMBER(10,0) Yes 6 당첨자수
GIVE_CNT NUMBER(10,0) Yes 7 수령자수
컬럼값이구요 제가 뽑으려는거는
연령별 경품 당첨자수 구하는거입니다
select a.ageInfo from
(SELECT CASE
WHEN A.AGE < 15 THEN '15세 미만'
WHEN A.AGE < 20 THEN '15세~19세'
WHEN A.AGE < 25 THEN '20세~24세'
WHEN A.AGE < 30 THEN '25세~29세'
WHEN A.AGE < 35 THEN '30세~34세'
WHEN A.AGE < 40 THEN '35세~39세'
WHEN A.AGE < 50 THEN '40세~49세'
WHEN A.AGE < 60 THEN '50세~59세'
WHEN 59 < A.AGE THEN '60세 이상'
END AS ageInfo
FROM SUMMARY_APRILDAY A
GROUP BY A.AGE)a
group by a.ageInfo ORDER BY a.ageInfo ;
연령 별은 구했는데 경품별 당첨수량을 도저히 못구하겟네요 경품은 7가지입니다 ㅠ 경품시디는 하드코딩할수있구요
원하시는 결과표를 보여주세요.
SELECT a.ageInfo, A.s1,A.s2,A.s3,A.s4,A.s5,A.s6,A.s7,a.totalSum,CONCAT(TRIM('.' FROM TO_CHAR(ROUND(a.totalSum*100/30000,2),'fm990.99')),'%') as avg
from
(SELECT
CASE
WHEN A.AGE < 15 THEN '15세 미만'
WHEN A.AGE >= 15 AND A.AGE < 20 THEN '15세~19세'
WHEN A.AGE >= 20 AND A.AGE < 25 THEN '20세~24세'
WHEN A.AGE >= 25 AND A.AGE < 30 THEN '25세~29세'
WHEN A.AGE >= 30 AND A.AGE < 35 THEN '30세~34세'
WHEN A.AGE >= 35 AND A.AGE < 40 THEN '35세~39세'
WHEN A.AGE >= 40 AND A.AGE < 50 THEN '40세~49세'
WHEN A.AGE >= 50 AND A.AGE < 60 THEN '50세~59세'
WHEN A.AGE >= 60 THEN '60세 이상'
END AS ageInfo ,
SUM(DECODE(gift_cd,'170013935',evt_win_cnt,0)) AS s1,
SUM(DECODE(gift_cd,'110650627',evt_win_cnt,0)) AS s2,
SUM(DECODE(gift_cd,'111650343',evt_win_cnt,0)) AS s3,
SUM(DECODE(gift_cd,'111971655',evt_win_cnt,0)) AS s4,
SUM(DECODE(gift_cd,'111130402',evt_win_cnt,0)) AS s5,
SUM(DECODE(gift_cd,'111072037',evt_win_cnt,0)) AS s6,
SUM(DECODE(gift_cd,'111170102',evt_win_cnt,0)) AS s7,
(SUM(DECODE(gift_cd,'170013935',evt_win_cnt,0)) + SUM(DECODE(gift_cd,'110650627',evt_win_cnt,0))
+SUM(DECODE(gift_cd,'111650343',evt_win_cnt,0)) + SUM(DECODE(gift_cd,'111971655',evt_win_cnt,0))
+SUM(DECODE(gift_cd,'111130402',evt_win_cnt,0)) + SUM(DECODE(gift_cd,'111072037',evt_win_cnt,0))
+SUM(DECODE(gift_cd,'111170102',evt_win_cnt,0))) AS totalSum
FROM SUMMARY_APRILDAY A
GROUP BY CASE
WHEN A.AGE < 15 THEN '15세 미만'
WHEN A.AGE >= 15 AND A.AGE < 20 THEN '15세~19세'
WHEN A.AGE >= 20 AND A.AGE < 25 THEN '20세~24세'
WHEN A.AGE >= 25 AND A.AGE < 30 THEN '25세~29세'
WHEN A.AGE >= 30 AND A.AGE < 35 THEN '30세~34세'
WHEN A.AGE >= 35 AND A.AGE < 40 THEN '35세~39세'
WHEN A.AGE >= 40 AND A.AGE < 50 THEN '40세~49세'
WHEN A.AGE >= 50 AND A.AGE < 60 THEN '50세~59세'
WHEN A.AGE >= 60 THEN '60세 이상'
END
ORDER BY ageInfo)a
이거뽑으면 아래처럼나옵니다
15세 미만 1119 1177 1200 1168 1333 1200 1150 8347 27.82%
15세~19세 0 0 0 0 0 40 0 40 0.13%
20세~24세 300 300 300 300 300 260 190 1950 6.5%
30세~34세 0 0 0 0 0 0 110 110 0.37%
40세~49세 300 300 300 300 300 300 250 2050 6.83%
50세~59세 0 0 0 0 0 0 50 50 0.17%
그러나 제가뽑고싶은거는 아래요
이벤트품목 a b c d e f g h <---하드코딩해도대구요
총당점자수 a상품의 당첨자수 합계 /b상품의 당첨자수합꺠요
15세 미만 1119 1177 1200 1168 1333 1200 1150 8347 27.82%
15세~19세 0 0 0 0 0 40 0 40 0.13%
20세~24세 300 300 300 300 300 260 190 1950 6.5%
30세~34세 0 0 0 0 0 0 110 110 0.37%
40세~49세 300 300 300 300 300 300 250 2050 6.83%
50세~59세 0 0 0 0 0 0 50 50 0.17%
계산식에 사용된 30000 이라는 숫자는 고정값인가요? 혹시 테이블의 값으로 계산되어 나와야 하는 것은 아닌지?
총당첨자수 의 합이에요 그게요
SELECT ageInfo
, SUM(DECODE(gift_cd, '170013935', evt_win_cnt)) s1
, SUM(DECODE(gift_cd, '110650627', evt_win_cnt)) s2
, SUM(DECODE(gift_cd, '111650343', evt_win_cnt)) s3
, SUM(DECODE(gift_cd, '111971655', evt_win_cnt)) s4
, SUM(DECODE(gift_cd, '111130402', evt_win_cnt)) s5
, SUM(DECODE(gift_cd, '111072037', evt_win_cnt)) s6
, SUM(DECODE(gift_cd, '111170102', evt_win_cnt)) s7
, SUM(evt_win_cnt) totalSum
, ROUND(RATIO_TO_REPORT(SUM(evt_win_cnt)) OVER() * 100, 2) rat
FROM (SELECT gift_cd
, evt_win_cnt
, 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
FROM summary_aprilday
-- WHERE evt_dt = '20180228' -- 조건은 따로 없나요?
)
GROUP BY ageInfo
ORDER BY ageInfo
;
15세 미만 1119 1177 1200 1168 1333 1200 1150 8347 66.53
15세~19세 40 40 0.32
20세~24세 300 300 300 300 300 260 190 1950 15.54
30세~34세 110 110 0.88
40세~49세 300 300 300 300 300 300 250 2050 16.34
50세~59세 50 50 0.4
결과갑입니다 ㅜㅜ
맨위에 총합계를 구하고싶어요
롤업해야하나요 ㅠ???
연령별 상품 총 당첨자 합계도 쿼리로 뽑고싶어요 한번에요 맨위에요
SELECT NVL(ageInfo, '합계') ageInfo
, SUM(DECODE(gift_cd, '170013935', evt_win_cnt)) s1
, SUM(DECODE(gift_cd, '110650627', evt_win_cnt)) s2
, SUM(DECODE(gift_cd, '111650343', evt_win_cnt)) s3
, SUM(DECODE(gift_cd, '111971655', evt_win_cnt)) s4
, SUM(DECODE(gift_cd, '111130402', evt_win_cnt)) s5
, SUM(DECODE(gift_cd, '111072037', evt_win_cnt)) s6
, SUM(DECODE(gift_cd, '111170102', evt_win_cnt)) s7
, SUM(evt_win_cnt) totalSum
, ROUND(
RATIO_TO_REPORT(SUM(evt_win_cnt)) OVER(PARTITION BY GROUPING(ageInfo))
* 100, 2) rat
FROM (SELECT gift_cd
, evt_win_cnt
, 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
FROM summary_aprilday
-- WHERE evt_dt = '20180228' -- 조건은 따로 없나요?
) a
GROUP BY ROLLUP(ageInfo)
ORDER BY a.ageInfo NULLS FIRST
;
대박감사합니다 지역별로도 뽑아야하는데 퇴근오늘할수있겟죠 ㅠㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.