꿈꾸는 개발자, DBA 커뮤니티 구루비

하위 포함 집계 구하기 도움 요청드립니다.

by SKLEE 통계 [2016.03.30 00:45:39]

통계 쿼리 작성하다 너무 안풀려서 문의 드립니다.

아래와 같은 3개의 테이블이 있고

지역테이블
지역코드	상위지역코드
서울	한국
부산	한국
대구	한국
구로구	서울
구로동	구로구
영등포구	서울
사하구	부산
서구	대전
관악구	서울
신림동	관악구
봉천동	관악구
과정제1동	사하구
과정제2동	사하구
둔산동	서구

제품테이블
제품코드(대)	제품코드(중)	제품코드(소)
대형가전	냉장고	삼성냉장고1
대형가전	냉장고	삼성냉장고2
대형가전	냉장고	엘지냉장고1
대형가전	세탁기	대우세탁기1
대형가전	세탁기	대우세탁기2
대형가전	세탁기	삼성세탁기1
대형가전	세탁기	삼성세탁기2
대형가전	에어컨	엘지에어컨1
소형가전	밥솥	쿠쿠1
소형가전	밥솥	쿠첸2
소형가전	전자레인지	삼성레인지
기타가전	면도기	필립스1
기타가전	면도기	필립스2
기타가전	면도기	카이저

판매테이블
신림동	삼성냉장고1
신림동	삼성냉장고1
봉천동	삼성냉장고2
과정제1동	대우세탁기2
과정제1동	삼성냉장고1
과정제1동	필립스2
봉천동	삼성레인지
과정제2동	삼성냉장고1
신림동	쿠첸2
봉천동	엘지냉장고1
과정제1동	필립스2

조회 페이지 에서는

셀렉트 박스 3개로 지역 : 시 구 동

셀렉트 박스 3개로 제품 : 대분류 중분류 소분류

로 되어 있습니다. 지역에서 시를 서울을 고르면 구는 서울에 속한 구만 나오는

제품도 같은 형식이고여

지역이나 제품은 3단계 까지 모두 선택 하지 않고 임의 로 선택 할수 있습니다.

예를 들면 지역은 선택하지 않고 대분류만 대형가전을 선택 하면

	삼성냉장고1	삼성냉장고2	엘지냉장고1	소계	대우세탁기1	대우세탁기2	삼성세탁기1	삼성세탁기2	소계	엘지에어컨1	소계	합계
서울	2	1	1	4
대전	0	0	0
부산	1	1	0
합계	3	2	0

대형가전 아래 모든 제품이 포함 되는 이런 통계가 나오고

지역에서 서울 선택 하고 제품은 대분류 대형가전 중분류 냉장고를 선택 하면

	삼성냉장고1	삼성냉장고2	엘지냉장고1	합계
구로구	x	x	x	x
관악구	x	x	x	x
영등포구	x	x	x	x
합계	x	x	x	x

지역을 서울,관악구 선택하고 제품을 기타를 선택하면 이런 식으로 나와야 합니다.

	필립스1	필립스2	카이저	합계
봉천동	x	x	x	x
신림동	x	x	x	x
합계	x	x	x	x

합계는 필요하지만 소계는 꼭 필요 한것은 아니라고 하네요.

제품,지역,판매 테이블은 계속 추가 가능 한 형태 입니다.

제품이 계속 추가되니 pivot 을 써야 할거 같은데 제 실력으론 너무 복잡해서 3일째 고생중입니다. ㅠ.ㅠ

도움 부탁드립니다.

by 마농 [2016.03.30 16:58:09]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

WITH locations AS
(
SELECT '서울' loc, '한국' p_loc FROM dual
UNION ALL SELECT '부산'     , '한국'   FROM dual
UNION ALL SELECT '대구'     , '한국'   FROM dual
UNION ALL SELECT '구로구'   , '서울'   FROM dual
UNION ALL SELECT '구로동'   , '구로구' FROM dual
UNION ALL SELECT '영등포구' , '서울'   FROM dual
UNION ALL SELECT '사하구'   , '부산'   FROM dual
UNION ALL SELECT '서구'     , '대전'   FROM dual
UNION ALL SELECT '관악구'   , '서울'   FROM dual
UNION ALL SELECT '신림동'   , '관악구' FROM dual
UNION ALL SELECT '봉천동'   , '관악구' FROM dual
UNION ALL SELECT '과정제1동', '사하구' FROM dual
UNION ALL SELECT '과정제2동', '사하구' FROM dual
UNION ALL SELECT '둔산동'   , '서구'   FROM dual
)
, products AS
(
SELECT '대형가전' cd1, '냉장고' cd2, '삼성냉장고1' cd3 FROM dual
UNION ALL SELECT '대형가전', '냉장고'    , '삼성냉장고2' FROM dual
UNION ALL SELECT '대형가전', '냉장고'    , '엘지냉장고1' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '대우세탁기1' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '대우세탁기2' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '삼성세탁기1' FROM dual
UNION ALL SELECT '대형가전', '세탁기'    , '삼성세탁기2' FROM dual
UNION ALL SELECT '대형가전', '에어컨'    , '엘지에어컨1' FROM dual
UNION ALL SELECT '소형가전', '밥솥'      , '쿠쿠1'       FROM dual
UNION ALL SELECT '소형가전', '밥솥'      , '쿠첸2'       FROM dual
UNION ALL SELECT '소형가전', '전자레인지', '삼성레인지'  FROM dual
UNION ALL SELECT '기타가전', '면도기'    , '필립스1'     FROM dual
UNION ALL SELECT '기타가전', '면도기'    , '필립스2'     FROM dual
UNION ALL SELECT '기타가전', '면도기'    , '카이저'      FROM dual
)
, sales AS
(
SELECT '신림동' loc, '삼성냉장고1' cd3 FROM dual
UNION ALL SELECT '신림동'   , '삼성냉장고1' FROM dual
UNION ALL SELECT '봉천동'   , '삼성냉장고2' FROM dual
UNION ALL SELECT '과정제1동', '대우세탁기2' FROM dual
UNION ALL SELECT '과정제1동', '삼성냉장고1' FROM dual
UNION ALL SELECT '과정제1동', '필립스2'     FROM dual
UNION ALL SELECT '봉천동'   , '삼성레인지'  FROM dual
UNION ALL SELECT '과정제2동', '삼성냉장고1' FROM dual
UNION ALL SELECT '신림동'   , '쿠첸2'       FROM dual
UNION ALL SELECT '봉천동'   , '엘지냉장고1' FROM dual
UNION ALL SELECT '과정제1동', '필립스2'     FROM dual
)
, temp1 AS
(
-- 조회 대상 지역 --
SELECT CONNECT_BY_ROOT(loc) r_loc
     , loc
  FROM locations
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH NVL2(:v_loc3, loc, p_loc) = COALESCE(:v_loc3, :v_loc2, :v_loc1, '한국')
 CONNECT BY PRIOR loc = p_loc
)
, temp2 AS
(
-- 조회 대상 제품 --
SELECT DECODE('', :v_cd1, cd1, :v_cd2, cd2) r_cd
     , cd3
  FROM products
 WHERE cd1 = NVL(:v_cd1, cd1)
   AND cd2 = NVL(:v_cd2, cd2)
   AND cd3 = NVL(:v_cd3, cd3)
)
-- 1. Title 부분 --
SELECT '구분' gb
     , a.*
  FROM (SELECT NVL(cd3, '합계') gb
             , ROW_NUMBER() OVER(ORDER BY r_cd, cd3) rn
          FROM temp2
         GROUP BY ROLLUP((r_cd, cd3))
        )
 PIVOT (MIN(gb) FOR rn IN (  1,  2,  3,  4,  5,  6,  7,  8,  9, 10
                          , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
                          ) ) a
 UNION ALL
-- 2. 통계 부분
SELECT r_loc
     , SUM(DECODE(rn,  1, cnt))||'' c01
     , SUM(DECODE(rn,  2, cnt))||'' c02
     , SUM(DECODE(rn,  3, cnt))||'' c03
     , SUM(DECODE(rn,  4, cnt))||'' c04
     , SUM(DECODE(rn,  5, cnt))||'' c05
     , SUM(DECODE(rn,  6, cnt))||'' c06
     , SUM(DECODE(rn,  7, cnt))||'' c07
     , SUM(DECODE(rn,  8, cnt))||'' c08
     , SUM(DECODE(rn,  9, cnt))||'' c09
     , SUM(DECODE(rn, 10, cnt))||'' c10
     , SUM(DECODE(rn, 11, cnt))||'' c11
     , SUM(DECODE(rn, 12, cnt))||'' c12
     , SUM(DECODE(rn, 13, cnt))||'' c13
     , SUM(DECODE(rn, 14, cnt))||'' c14
     , SUM(DECODE(rn, 15, cnt))||'' c15
     , SUM(DECODE(rn, 16, cnt))||'' c16
     , SUM(DECODE(rn, 17, cnt))||'' c17
     , SUM(DECODE(rn, 18, cnt))||'' c18
     , SUM(DECODE(rn, 19, cnt))||'' c19
     , SUM(DECODE(rn, 20, cnt))||'' c20
  FROM (SELECT a.r_loc
             , ROW_NUMBER() OVER(PARTITION BY a.r_loc ORDER BY b.r_cd, b.cd3) rn
             , COUNT(c.loc) cnt
          FROM temp1 a
         CROSS JOIN temp2 b 
          LEFT OUTER JOIN sales c
            ON a.loc = c.loc
           AND b.cd3 = c.cd3
         GROUP BY a.r_loc, b.r_cd, ROLLUP(b.cd3)
        )
 GROUP BY ROLLUP(r_loc)
;

by SKLEE [2016.03.30 22:30:58]

정말 정말 감사합니다 마농님.

작성해 주신 코드 분석 해서 해결해 보겠습니다.

한가지 더 여쭙자면 이 코드론 상품이 20개 넘어가면 쿼리를 수정해야 하고 상품이 20개 보다 적으면 빈칸이 쭉 나오게 되는데 이런걸 피하려면 프로시져를 이용해서 쿼리를 동적으로 만들어야 하는 건가요?

by 마농 [2016.03.31 10:12:57]

쿼리 한방으로 다양한 가변 조건을 만족하기란 쉽지 않죠.
- 어렵긴 하더라도 가능은 하죠.
그러나 쿼리만으로 가변열을 적용하는 것은 현재까지는 불가능합니다.
- 동적 쿼리를 이용해야 하구요.

저는 한방 쿼리로 비슷하게 결과를 유도해 본 것 뿐이구요.
- 그대로 적용하기엔 여러가지 문제를 해결해야 할 듯 하네요.
- 문제를 해결하기 위한 다양한 기법들을 소개하는 수준입니다.
- 참고만 하시면 될 듯 하구요.

프로그래밍과 유기적으로 연동해서 동적으로 구현해야 합니다.
가변열을 동적으로 구현하기 위해서는
- 위 쿼리에서 -- 조회 대상 제품 -- 이라고 표현된 부분이 선 수행 되어
- 이 결과를 이용해 루프 돌려가며 SQL 문장을 완성시켜야 겠지요.

그리고 꼭 동적 쿼리만이 답은 아닙니다.
피벗 형태의 결과를 쿼리로 뽑지 말고 프로그래밍으로 구현할 수도 있겠지요.
꼭 실제 구현하지 않더라도 자동으로 해주는 기능을 제공하기도 하구요.

by 손님 [2016.03.31 10:54:48]

마농님 조언 감사 드립니다. 참고해서 개발해 보도록 하겠습니다

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변