[오라클] 불규칙한 데이터 항목의 그룹핑 팁

MONTH	GBN
2007-01	A
2007-02	A
2007-03	B
2007-04	B
2007-05	A
2007-06	A
2007-07	B
2007-08	C
2007-09	A
2007-10	C
2007-11	C

위와 같은 데이터로

시작일	종료일	GBN
2007-01	2007-02	A
2007-03	2007-04	B
2007-05	2007-06	A
2007-07	2007-07	B
2007-08	2007-08	C
2007-09	2007-09	A
2007-10	2007-11	C

 와 같은 결과를 얻고 싶을 때

다시 말해 소트의 기준(월)은 있으나 그룹핑의 기준이 애매할 경우 쓸 수 있는 방법에 대해 한번 생각해 보자.

보통의 경우 위와 같은 결과를 얻기 위해서는 프로그램단으로 데이터를 가져와 처리 하거나, 프로시저를 작성해야 할 것이다.

하지만, rank() 함수를 교묘히 사용하면 그룹핑을 할 수 있는 방법이 생긴다.

  select month, gbn,
            rank() over (partition by gbn order by month) rank1,
            rank() over (order by month) rank2
    from test
   order by month

위에서 rank1은 구분별로 윈도우를 주고 rank를 구한 것이고,
rank2는 구분 없이 rank를 구한것이다.

 결과는

MONTH	GBN	RANK1	RANK2
2007-01	A	1	1
2007-02	A	2	2
2007-03	B	1	3
2007-04	B	2	4
2007-05	A	3	5
2007-06	A	4	6
2007-07	B	3	7
2007-08	C	1	8
2007-09	A	5	9
2007-10	C	2	10
2007-11	C	3	11

이렇게 나오게 된다.

이때 rank2에서 rank1을 빼게 되면

MONTH	GBN	RANK
2007-01	A	0
2007-02	A	0
2007-03	B	2
2007-04	B	2
2007-05	A	2
2007-06	A	2
2007-07	B	4
2007-08	C	7
2007-09	A	4
2007-10	C	8
2007-11	C	8

이런 결과를 얻게 된다.

이제 이 결과를 한번더  gbn과 rank로 그룹핑을 하면 원하는 결과를 얻게 된다

select min(month) 시작일,
          max(month) 종료일,
          gbn
  from (select month, gbn,
                    rank() over (order by month) -
                    rank() over (partition by gbn order by month) rank
            from test
          order by month)
group by gbn, rank
order by 시작일

결과는

시작일	종료일	GBN
2007-01	2007-02	A
2007-03	2007-04	B
2007-05	2007-06	A
2007-07	2007-07	B
2007-08	2007-08	C
2007-09	2007-09	A
2007-10	2007-11	C

요렇게 나오게 된다~~ ^^