특정 값 기준으로 그룹핑하기

안녕하세요. ProDBA의 질문으로 SQL 연습을 해봤습니다.

아래는 샘플 데이터입니다. 요구사항은 먼저 1,2,5 번째 컬럼을 기준으로 채번을 하는 것입니다. (RANK)

그 후에 8번째 컬럼에서 30을 초과하는 값을 경계로 하여 순위를 매깁니다. (IDEN_NUM)

샘플 데이터가 그림이라 타이핑하는데 약간 애를 먹었습니다.

대충 일부 데이터만 가지고 테스트를 해봤습니다.

WITH t AS (
SELECT '20160519' ONE, 765 TWO, 127.294 THREE, 36.581 FOUR, 912 FIVE, 940 SIX, 50 SEVEN, 29 EIGHT, 36110250 EMD_CD, 'Jochiwon-eup' EMD_ENG_NM, 36 CTYPRVN_CD, 'Sejong-si' CTP_ENG_NM FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.299, 36.592,  945,  951, 50, 7  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.293, 36.59 ,  953,  955, 50, 3  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.294, 36.594,  957, 1533, 50, 337, 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.29 , 36.607, 1534, 1535, 50, 2  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.289, 36.607, 1537, 1541, 50, 5  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.298, 36.607, 1547, 1548, 50, 2  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.299, 36.607, 1551, 1552, 50, 2  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.294, 36.607, 1555, 2253, 50, 419, 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.29 , 36.607, 2302, 2304, 50, 3  , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 765  , 127.294, 36.607, 2306, 2344, 50, 39 , 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 12299, 127.268, 36.607,    2, 1422, 60, 861, 36110380, 'Jeondong-myeon', 36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 12299, 127.205, 36.607, 1448, 2035, 60, 348, 36110370, 'Jeonui-myeon',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 12299, 127.205, 36.699, 2112, 2113, 60, 2  , 36110370, 'Jeonui-myeon',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 12299, 127.268, 36.699, 2112, 2113, 60, 150, 36110380, 'Jeondong-myeon', 36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 15253, 127.292, 36.597,    5,  720, 50, 436, 36110250, 'Jochiwon-eup',   36, 'Sejong-si' FROM DUAL
UNION ALL SELECT '20160519', 15253, 127.389, 36.673,  749,  800, 50, 12 , 43113320, 'Oksan-myeon',    36, 'Sejong-si' FROM DUAL
)
SELECT ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, EMD_CD, EMD_ENG_NM, CTYPRVN_CD, CTP_ENG_NM,
       ROW_NUMBER() OVER (PARTITION BY TWO ORDER BY ONE, FIVE) rank,
       SUM(c1) OVER (PARTITION BY TWO ORDER BY ONE, FIVE) + 1 iden_num
  FROM (SELECT ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, EMD_CD, EMD_ENG_NM, CTYPRVN_CD, CTP_ENG_NM,
               CASE WHEN LAG(EIGHT,1,1) OVER (PARTITION BY TWO ORDER BY ONE, FIVE) < 30 THEN 0 ELSE 1 END c1
          FROM t) t1

좀 조잡하게 되었는데, 순서는 이렇습니다.

1. 8번째 컬럼의 이전 행 값이 30 미만일 경우이면 0, 그 외의 경우는 1로 지정

2. 1,2,5 번째 컬럼으로 채번

3. 1,2,5 번째 컬럼 기준으로 1번에서 지정한 값의 합계를 구함

 

잠시간의 뿌듯함을 뒤로하고..

이제 모범 답안을 보자면 아래와 같습니다.

WITH t AS
(
SELECT 1 c1, 2 c2, 1 c5, 29 c8 FROM dual
UNION ALL SELECT 1, 2, 2, 7 FROM dual
UNION ALL SELECT 1, 2, 3, 3 FROM dual
UNION ALL SELECT 1, 2, 4, 337 FROM dual
UNION ALL SELECT 1, 2, 5, 2 FROM dual
UNION ALL SELECT 1, 2, 6, 5 FROM dual
UNION ALL SELECT 1, 2, 7, 2 FROM dual
UNION ALL SELECT 1, 2, 8, 2 FROM dual
UNION ALL SELECT 1, 2, 9, 419 FROM dual
UNION ALL SELECT 1, 2, 10, 3 FROM dual
UNION ALL SELECT 1, 2, 11, 39 FROM dual
UNION ALL SELECT 1, 3, 1, 861 FROM dual
UNION ALL SELECT 1, 3, 2, 348 FROM dual
UNION ALL SELECT 1, 3, 3, 2 FROM dual
UNION ALL SELECT 1, 3, 4, 150 FROM dual
)
SELECT c1, c2, c5, c8
, COUNT(CASE WHEN c8 >= 30 THEN 1 END)
OVER(PARTITION BY c1, c2 ORDER BY c5
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1 iden_num
FROM t
;

마농님이 답변글로 남겨주신 쿼리입니다. 아직 ROWS ... PRECEDING 구문에 대한 이해가 부족해서 사용해보지 못했는데요. COUNT OVER 함수를 사용한 것도 차이입니다. 아무래도 심플한 방법이 눈에도 잘 들어오고 좋겠죠.

 

http://wiki.gurubee.net/pages/viewpage.action?pageId=26744104