MySQL 단일 쿼리로 NULL과 0을 제외한 고유 값 개수 계산하기

데이터 분석 작업을 하다 보면 NULL과 0을 제외한 고유(distinct) 값의 개수를 한 번의 쿼리로 집계해야 하는 경우가 종종 있습니다. 이 글에서는 MySQL에서 이를 처리하는 방법을 예제와 함께 단계별로 살펴보겠습니다.

1. 테이블 생성

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable(
    Value int
);
Query OK, 0 rows affected (1.35 sec)

2. 샘플 데이터 삽입

INSERT 명령어를 사용하여 테이블에 여러 레코드를 삽입합니다.

mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values(NULL);
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.59 sec)
mysql> insert into DemoTable values(0);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.70 sec)
mysql> insert into DemoTable values(0);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(NULL);
Query OK, 1 row affected (0.16 sec)

3. 데이터 확인

SELECT 문을 사용하여 테이블에 저장된 전체 레코드를 조회합니다.

mysql> select *from DemoTable;

실행 결과는 다음과 같습니다.

+-------+
| Value |
+-------+
| 10    |
| NULL  |
| 10    |
| 0     |
| 20    |
| 10    |
| 0     |
| NULL  |
+-------+
8 rows in set (0.00 sec)

4. NULL과 0을 제외한 고유 값 집계

다음 쿼리는 NULL의 개수, 0의 개수, 그리고 NULL과 0을 제외한 고유 값의 개수를 하나의 쿼리로 동시에 계산합니다.

mysql> select sum(Value is null) as NumberOfNull,
   sum(Value=0) as NumberOfZero,
   count(distinct Value > 0) as ValueExceptZeroAndNull
from DemoTable;

실행 결과는 다음과 같습니다.

+--------------+--------------+------------------------+
| NumberOfNull | NumberOfZero | ValueExceptZeroAndNull |
+--------------+--------------+------------------------+
| 2            | 2            | 2                      |
+--------------+--------------+------------------------+
1 row in set (0.00 sec)

쿼리 동작 원리

• sum(Value is null): MySQL에서 비교 조건식은 참일 때 1, 거짓일 때 0을 반환하므로, 이를 합산하면 NULL인 행의 개수가 됩니다.
• sum(Value=0): 같은 원리로 값이 0인 행의 개수를 구합니다.
• count(distinct Value > 0): NULL과 0을 제외한 값들의 고유 개수를 집계합니다.

결과를 보면 NULL은 2개, 0은 2개, 그리고 NULL과 0을 제외한 고유 값(10, 20)은 2개로 정확하게 집계된 것을 확인할 수 있습니다.

실무 활용 팁

조건식의 고유 결과 개수가 아닌 실제 양수 값의 고유 개수를 세고 싶다면 CASE 표현식을 함께 사용하는 것이 더 안전합니다.

select count(distinct case when Value > 0 then Value end) as DistinctPositiveValues
from DemoTable;

COUNT 함수는 내부적으로 NULL을 제외하고 개수를 세기 때문에, 위 쿼리는 10과 20이라는 두 개의 고유한 양수 값을 정확히 반환합니다. 상황에 맞게 두 방식을 선택하여 사용하시기 바랍니다.