문제)
아래 예와 같이 datetime으로 sorting 하여 item 기준으로 부분합을
구할려고 합니다.
동일 item이 2회 이상 발생될 경우 각각 합계를 구해야 합니다.
*table 예
datetime item qty
2006-09-05 10:00:10 aaa 1000
2006-09-05 10:10:50 aaa 2000
2006-09-05 10:12:00 aaa 1000
2006-09-05 10:12:31 bbb 1500
2006-09-05 10:12:50 bbb 1500
2006-09-05 10:13:00 aaa 1000
2006-09-05 10:13:30 aaa 2000
2006-09-05 10:14:50 ccc 2500
☞ 결과
item qty
aaa 4000
bbb 3000
aaa 3000
ccc 2500
답변)
이렇게 어떤 컬럼(datetime)의 순서에 따라서 정렬했을 경우
특정 컬럼(item)이 연속된 것끼리 그룹을 지어야 문제가 해결되는 쿼리는
실제 업무에서 상당히 빈번하게 접하게 된다.
이 글에서 이런 쿼리들에 대해서 간단히 정리하고 예를 모으도록 하겠다.
이런 쿼리를 앞으로 순차적그룹 쿼리(Sequential Group Query)라고 하고,
위에서 설명했듯이 순서를 결정하는 컬럼을 sequence 컬럼, 연속된 것끼리 그룹을
지어야 하는 컬럼을 group 컬럼이라고 하겠다.
어떤 문제에서는 group 컬럼이 없이, sequence 컬럼이 일정한 규칙
(예를 들어, 연속적으로 1씩 증가하는 것만 같은 그룹이 된다, 등등)
을 갖는 이유로 group 컬럼의 역할까지를 같이 할 수도 있다.
Sequential Group Query는 대개 아래와 같이,
lag 함수를 써서 가공한 컬럼을 sum 함수로 다시 가공한 값으로 고유값을 만들고,
이 고유값을 가지고 그룹단위로 계산을 해주면 해결된다.
(자세한 중간과정 풀이는 인라인뷰를 하나씩 제거하면서 할 수 있도록 각자에게 맡기겠다.)
SELECT item
, SUM (qty)
FROM (SELECT date_time
, item
, qty
, SUM (flag) OVER (ORDER BY date_time) grp
FROM (SELECT date_time
, item
, qty
, DECODE (LAG (item) OVER (ORDER BY date_time)
, item, 0
, 1
) flag
FROM t_table))
GROUP BY grp
, item
그런데, 이런 해법도 존재한다.
위에서 sequence 컬럼이 일정한 규칙을 갖는 경우, sequence 컬럼에서 rownum 이나 row_number() 함수로 빼주변 인라인뷰 사용을 한 번 줄이면서 그룹을 만들 수 있다. 위의 문제도 마찬가지이다.
쿼리를 분석해보자.
(튜닝의 관점에서는 이 방법이 경우에 따라 더 좋지 않을 수도 있다. 경우에 따라 적용해야 하겠다.)
SELECT item
, SUM (qty)
FROM (SELECT date_time
, item
, qty
, ROW_NUMBER () OVER (PARTITION BY item ORDER BY date_time)
- ROW_NUMBER () OVER (ORDER BY date_time) grp
FROM t_table)
GROUP BY item
, grp
ORDER BY MIN (date_time)
이 경우는 ROW_NUMBER () OVER (PARTITION BY item ORDER BY date_time) 값으로
일정한 규칙을 의도적으로 만들었다는 표현이 맞는 말이 되겠다.