데이터베이스 질문과 답변
SELECT A.상품
,NVL(A."20대미만",0) "20대미만"
,NVL(A."20대",0) "20대"
,NVL(A."30대",0) "30대"
,NVL(A."40대",0) "40대"
,NVL(A."50대",0) "50대"
,NVL(A."60대",0) "60대"
,NVL(A."70대이상",0) "70대이상"
,NVL(A.합계,0) 합계
FROM (
SELECT *
FROM (
SELECT CASE WHEN A.AGE<20 THEN '20대미만'
WHEN A.AGE>=20 AND A.AGE<30 THEN '20대'
WHEN A.AGE>=30 AND A.AGE<40 THEN '30대'
WHEN A.AGE>=40 AND A.AGE<50 THEN '40대'
WHEN A.AGE>=50 AND A.AGE<60 THEN '50대'
WHEN A.AGE>=60 AND A.AGE<70 THEN '60대'
WHEN A.AGE>=70 THEN '70대이상'
WHEN A.AGE IS NULL THEN '총합'
END AS 연령대
,DECODE ( (SELECT Z.PROD_NM FROM REDCRMEDU.TBCR_7000 Z WHERE Z.PROD_CD = A.PROD_CD) , NULL, '합계',(SELECT Z.PROD_NM FROM REDCRMEDU.TBCR_7000 Z WHERE Z.PROD_CD = A.PROD_CD)) AS 상품
, COUNT(A.PROD_CD) CNT
FROM CUT_NEEDS_MST A
WHERE A.NEEDS_TYPE IS NOT NULL
AND A.LCLS IS NOT NULL
AND A.MCLS IS NOT NULL
AND A.AGE IS NOT NULL
AND A.GENCAUS IS NOT NULL
AND A.PROD_CD IS NOT NULL
AND A.REG_CHNL IS NOT NULL
GROUP BY CUBE (
A.AGE
,A.PROD_CD
)
) PIVOT ( sum(CNT) FOR 연령대 IN ('20대미만' AS "20대미만",'20대' AS "20대",'30대' AS "30대",'40대' AS "40대",'50대' AS "50대",'60대' AS "60대",'70대이상' AS "70대이상",'총합' AS 합계))
) A ORDER BY A.합계
SQL은 이건데 PIVOT안에 데이터는 약 2000개로 실행시 0초대인데
PIVOT으로 감쌀시에 약 2초 정도 걸립니다.
테이블 인덱스와 연관이 있을까요????
SELECT NVL(z.prod_nm, '합계') 상품
, COUNT(CASE WHEN a.age < 20 THEN 1 END)) "20대미만"
, COUNT(CASE WHEN a.age >= 20 AND a.age < 30 THEN 1 END)) "20대"
, COUNT(CASE WHEN a.age >= 30 AND a.age < 40 THEN 1 END)) "30대"
, COUNT(CASE WHEN a.age >= 40 AND a.age < 50 THEN 1 END)) "40대"
, COUNT(CASE WHEN a.age >= 50 AND a.age < 60 THEN 1 END)) "50대"
, COUNT(CASE WHEN a.age >= 60 AND a.age < 70 THEN 1 END)) "60대"
, COUNT(CASE WHEN a.age >= 70 THEN 1 END)) "70대이상"
, COUNT(*) 합계
FROM cut_needs_mst a
, redcrmedu.tbcr_7000 z
WHERE a.prod_cd = z.prod_cd
AND a.needs_type IS NOT NULL
AND a.lcls IS NOT NULL
AND a.mcls IS NOT NULL
AND a.age IS NOT NULL
AND a.gencaus IS NOT NULL
AND a.prod_cd IS NOT NULL
AND a.reg_chnl IS NOT NULL
GROUP BY ROLLUP(z.prod_nm)
;
감사합니다!! PIVOT으로 구현했는데 안해도 되군요
) 버튼을 클릭하여 작성 하시면 됩니다.