[SQL] row_number() vs. count(1)

SQL에서 "partition by"를 사용하여, 동일한 파티션 내의 번호를 부여하는 싶은 경우가 있습니다. 이러한 경우에 다음의 두가지 문법 중에서 어떤 것을 선택해야 할까요?

row_number() over (partition by ...)

count(1) over (partition by ...)

아래의 쿼리를 발생시켰을때, 데이터에 따라서 동일한 결과를 보여주는 경우도 있습니다.

select element1, employee
     , row_number() over (partition by element1 order by employee) as 'row_number'
     , count(1) over (partition by element1 order by employee) as 'count'
from db.employee

위의 쿼리에 대한 결과가 다음과 같을 수도 있습니다. 이 경우에는 element1 컬럽에서 동일한 값을 하나의 그룹으로 보았을때, employee 값이 겹치는 경우가 없기 때문입니다.

element1     employee      row_number()     count(1)
0000001      00000003       1                1
0000001      00000004       2                2
0000001      00000005       3                3
0000003      00000045       1                1
0000003      00000046       2                2

하지만, 아래의 예시와 같이 partition 내에 동일한 값을 갖는 경우가 있을때는 row_number()와 count(1)은 다른 결과를 보여주게 됩니다.

create table example_table (
    number int,
    groupNumber int)

insert into example_table (
    number,
    groupNumber)
values
    (NULL, 1),
    (100, 1),
    (101, 1),
    (102, 1),
    (103, 1),

    (NULL, 2),
    (NULL, 2),
    (NULL, 2),
    (200, 2),
    (201, 2),
    (202, 2),

    (300, 3),
    (301, 3),
    (301, 3),
    (301, 3),
    (302, 3)


select
    E.*,
    row_number() over (partition by E.groupNumber order by E.number asc) as 'row_number()'
    count(1) over (partition by E.groupNumber order by E.number asc) as 'count(1)'
from
    example_table as E

아래와 같이 row_number()와 count(1)은 그룹내에 동일한 값을 갖는 가진 elements가 있는 경우에 아래와 같이 다르게 출력 됩니다. 동일한 순위가 없이, sequential number를 원한다면, row_number()를 사용해야 할 것이고요... 동일한 순위를 고려한 순위 값을 얻고 싶다면 count(1)을 사용하면 좋을꺼 같습니다. 이처럼, 어떤 것을 사용하는 것이 맞느냐? 라기 보다도 상황에 따라서 적절하게 사용하면 됩니다.

Number      groupNumber row_number()         count(1)
----------- ----------- -------------------- -----------
NULL        1           1                    1
100         1           2                    2
101         1           3                    3
102         1           4                    4
103         1           5                    5

NULL        2           1                    3 Here
NULL        2           2                    3
NULL        2           3                    3
200         2           4                    4
201         2           5                    5
202         2           6                    6

300         3           1                    1
301         3           2                    4 Here
301         3           3                    4
301         3           4                    4
302         3           5                    5