[Mysql, Oracle, teradata] row_number() over (partition by) 활용

들어가기 앞서 . .

- 로그 데이터를 정제 및 분석 중인데, 쿼리로 다음 로그시간을 가져 온다거나, grouping, 조건을 포함해서 다음 로그시간을 가져온다거나 하는 작업을 할때 row_number() over (partition by) (teradata) 를 활용했다. 활용법을 정리하고자 하며, Mysql에서는 row_number()가 없기 때문에 따로 Oracle 및 teradata에 해당하는 구문을 나름대로 구현했다.

---

Sample Data (Mysql 기준)

CREATE TABLE `log_sample` (
  `id` varchar(15) NOT NULL, /*로그의 단순한 id, 'none'이 존재*/
  `logtime1` int(11) NOT NULL, /*로그의 시작 시간*/
  `logtime2` int(11) NOT NULL, /*다음 로그의 시작 시간*/
  `task` varchar(10) NOT NULL, /*해당 로그의 task명*/
  `cusno` varchar(20) NOT NULL,/*고객 번호*/
  `date` varchar(15) NOT NULL /* 로그의 날짜 */
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;


insert into `log_sample` values ('1234568', '153538', '153941', 'C', '1234', '20210223');
insert into `log_sample` values ('1234569', '153941', '155533', 'B', '1569', '20210223');
insert into `log_sample` values ('1234570', '155533', '160132', 'C', '2346', '20210223');
insert into `log_sample` values ('none', '160132', '160635', '', '1235', '20210223');
insert into `log_sample` values ('1234571', '160635', '162011', 'B', '6345', '20210223');
insert into `log_sample` values ('1234572', '122235', '123049', 'A', '1235', '20210224');
insert into `log_sample` values ('1234573', '123049', '123940', 'B', '6265', '20210224');
insert into `log_sample` values ('1234574', '123940', '125002', 'B', '7395', '20210224');
insert into `log_sample` values ('none', '125002', '130101', '', '2361', '20210224');
insert into `log_sample` values ('1234575', '130101', '140020', 'C', '7485', '20210224');
insert into `log_sample` values ('none', '140020', '142020', '', '2379', '20210224');
insert into `log_sample` values ('1234576', '142020', '143330', 'A', '6324', '20210224');

log_sample 테이블

 

row_number() : DB내 row의 수를 세기 위한 함수 

- Oracle과 teradata의 경우 row_number 함수를 활용해 데이터의 row_number를 계산할 수 있다. over를 활용해 내부에 Sorting 기준을 둔 상태에서 row의 수를 계산한다.

- Mysql의 경우는 row_number 함수를 제공하고 있지 않아 따로 row_num 변수를 활용해서 row_number를 계산하도록 하였다.

## Oracle , teradata
select a.*, row_number() over(order by `date`, `logtime1`) as rn
from log_sample as a
order by `date`, `logtime1`;

##Mysql
select a.*, @row_num := @row_num + 1 as rn
from log_sample as a, (select @row_num := 0) as b
order by `date`, `logtime1` ;

그림1. row_number인 rn이 추가된 table

 

row_number() partition by

- partition by 구문의 경우 grouping을 통해 row_number를 계산하는데 활용한다. 이는 group by를 생각해보면 이해하기 쉬울 것이다.

- sample_log 테이블에서는 date기준으로 2월 23일(group1)과 2월 24일(group2)에 대해 따로 row_number를 계산하기 위해 활용했다.

## Oracle , teradata
select
 a.*
,row_number() over(partition by `date` order by `date`, `logtime1`) as rn
from log_sample as a
order by `date`, `logtime1`

##Mysql
select
a.*
 ,case when @grp = `date` then @row_num := @row_num + 1 else @row_num := 1 end as rn
 ,(@grp := `date`) as tmp 
from log_sample as a, (select @row_num := 0, @grp := '') as r
order by `date`, `logtime1` ;

그림2. date group별로 row_number가 계산된 rn이 추가된 테이블

 

조건 추가된 row_number partition by

- 날짜별로 row_number가 계산된 상황에서 id가 'none'인 경우는 rn에 포함시키지 않지만 데이터로는 남겨두고 싶었다. 

- id가 none이 아닌 애들에 대해서 date로 grouping을 함과 동시에 id가 none이 아닌 애들을 먼저 위로 sorting을 하도록 해주었다.

## Oracle , teradata
select 
a.*
,case when not id = 'none' then row_number() over(partition by date order by case when id =`none` then 1 else 0 end, logtime1) else null as rn
from log_sample
order by `date`, `logtime1`;

## Mysql (위의 결과와 동일하게 해주기 위해 서브쿼리를 사용해서 ordering 및 id가 none인 rn을 null 처리함)
select
a.`id`, a.`logtime1`, a.`logtime2`, a.`task`, a.`cusno`, a.`date`,
case when id = 'none' then null else a.`rn` end as rn
from
(
select a.* , case when @grp = `date` and id <> 'none' then @row_num := @row_num + 1 else @row_num := 1 end as rn , (@grp := `date`) as tmp from log_sample as a, (select @row_num := 0, @grp := '') r order by case when id <> 'none' then 1 else 0 end, `date`, `logtime1`
) a
order by `date`, `logtime1` ;

(partition by 안에 case when을 통해 none이 아닌 애들을 우선적으로 sorting 해주지 않는다면 '그림3'의 5번째 row의 rn값이 4가 아니라 5가 됨)

그림3. id가 none이 아닌 데이터에 대해 row_number 추가된 table

 

마무리

- 이제 그림3과 같은 tmp 테이블을 생성했다고 가정하자

select a.*, b.logtime1 as logtime3 
from tmp a
left outer join tmp b
on a.`date` = b.`date` and a.`rn` = b.`rn`- 1
order by `date`, `logtime1`
;

그림4. 새로운 logtime3 추가된 table

logtime3의 경우 해당 로그의 다음 로그의 시작시간이다. logtime2와 다른 점은 id가 none인 애들을 무시했다는 점에 있다. 

 

(sql 쿼리야 물론 다양하고 훨씬 최적화된 방법이 많이 있겠지만.. )