[PostgreSQL] 정렬, 그룹화 그리고 집합 연산 (Oracle 비교)

 

안녕하세요, 성조입니다.

오늘은 포스트그레 디비에서 ORDER BY, GROUP BY, HAVING과 집합 연산에 대한 간략한 정리를 진행해 봅니다.

포스팅은 PostgreSQL을 기준으로 설명하며, 마이그레이션을 오라클로 하거나 다른 데이터베이스 환경으로 넘길 때 차이점 등을 아주 간략하게 반영하여 공유드려봅니다.

---

 

ORDER BY란?

ORDER BY절은 조회된 데이터를 특정 컬럼을 기준으로 정렬할 때 사용한다.

기본적으로 다른 이기종 DB들도 동일하게 작동한다.

 

 

기본 정렬 및 다중 정렬 방법

-- PostgreSQL & Oracle 동일
SELECT emp_id, emp_name, salary, department_id
FROM employees
ORDER BY department_id ASC, salary DESC;

- 케이스 2

-- 1차 기준: 부서 ID 오름차순(ASC)
-- 2차 기준: 같은 부서 내에서는 급여 내림차순(DESC)
SELECT emp_id, emp_name, salary, department_id
FROM employees
ORDER BY department_id ASC, salary DESC;

-  오름차순 ASC -> 작은 값부터 큰 값 순서로 정렬하며, 가장 큰 값인 NULL은 맨 마지막으로 밀려나는 구조이며, 내부적으로는 NULLS LAST가 기본 적용.

- 내림차순 DESC -> 큰 값부터 작은 값 순서로 정렬하며, 가장 큰 값인 NULL은 맨 처음에 노출되는 구조이다. 동일하게 내부적으로는 NULLS FIRST가 기본 적용.

 

 

NULLS FIRST OR NULLS LAST 예약어

-- 요구사항: 급여가 낮은 순(ASC)으로 정렬하되, 
-- 급여가 아직 책정되지 않은(NULL) 신규 입사자를 리스트 맨 위에 보여주고 싶을 때
SELECT emp_name, salary
FROM employees
ORDER BY salary ASC NULLS FIRST;

NULL을 처리하는 기본 동작은 두 데이터베이스가 동일하지만, 문자열 정렬에서는 다음과 같은 이유로 결과가 미세하게 달라질 수 있다.

PostgreSQL은 데이터베이스가 생성될 때 설정된 Collation (언어 및 정렬 규칙, LC_COLLATE)의 영향을 매우 강하게 받기 때문인데 오라클 환경에서 조회하던 한글 데이터의 정렬 순서와 100% 일치하지 않을 수 있는 규칙이 존재하기 때문이다. 두 DB는 마이그레이션 하는 경우가 많기에 주의를 갖고 체크해야 하는 포인트이다. 또한, 아래 이어지는 설명처럼 NULL 값의 처리가 다른 점도 인지해야 한다.

 

 

포스트그레와 오라클의 NULL 값의 정렬 처리 포인트

1) 오라클 -> 빈 문자열 ('') = NULL이다.

오라클은 값이 비어있는 문자열('')을 입력하면 이를 자동으로 NULL로 변환하여 저장한다. 이런 로직으로 인해 ORDER BY로 정렬할 때 빈 문자열 데이터들도 모두 NULL 취급되어서 NULLS LAST 규칙에 따라 맨 끝으로 밀려난다.

 

2) 포스트그레스큐엘 -> 빈 문자열 ('') != NULL이다.

오라클도 깐깐하지만, PostgreSQL은 더 깐깐하다. ''은 그저 '길이가 0인 텍스트'일뿐이고, NULL 은 '데이터 자체가 없음'으로 완전히 구분지어서 저장하기 때문에 Oracle의 NULL 저장 방식과는 다르게 데이터를 처리한다. 따라서 ORDER BY 시 빈 문자열은 일반 문자열로 취급되어 알파벳순 정렬 규칙을 따르고, NULL 데이터가 위 NULLS LAST 규칙을 따르는 것처럼 이동하지 않는다.

 

 

GROUP BY이란?

GROUP BY 절은 특정 컬럼의 값이 같은 행(ROW)들을 하나의 그룹으로 묶어주는 역할을 한다. 주로 SUM, AVG, COUNT 같은 집계 함수와 짝을 이루어 통계 데이터를 뽑아낼 때 사용하는 절이다.

 

예시 SQL

-- 부서별(department_id) 직원 수와 총 급여액 구하기
SELECT department_id, COUNT(*) as emp_count, SUM(salary) as total_salary
FROM employees
GROUP BY department_id;

 

 

GROUP BY와 NULL (오라클과 포스트그레)

SQL의 대원칙 중 하나는 NULL은 알 수 없는 값이다. NULL = NULL은 거짓이 나오는 구조라는 게 기본 원칙이다.

다만, GROUP BY 절에서만큼은 예외적으로 여러 개의 NULL을 '동일한 하나의 그룹'으로 취급한다. (두 데이터베이스 모두 동일.)

하지만, 이전 정렬 조건처럼 빈 문자열('')을 이슈처럼 결합하여 다뤘다면 이미 엄격한 구분으로 한 포스트그레와 일부 유연한 오라클 데이터베이스의 명확한 차이점이 가시적으로 나타나게 된다.

 

PostgreSQL

- NULL 그룹 (1번)

-  '' 빈 문자열 그룹 (2번)

 

Oracle

- NULL 그룹 (단일)

이런 구조가 되면서 차이점이 발생된다.

 

 

GROUP BY와 공집합(Empty Set)

조건(WHERE)에 맞는 데이터가 아예 없는 '공집합' 상태에서 통계를 낼 때, GROUP BY를 썼느냐 안 썼느냐에 따라 서버가 받는 응답 형식이 다르게 변하는 것을 주의해야 한다. (이거 잘못하면 NullPointerException 에러가 발생되는 원리이다.

 

케이스 A: GROUP BY가 없는 단순 집계(Scalar Aggregation)

데이터가 정말 아예 없어도 DB는 억지로 통계 결과를 만들어 무조건 1건의 행(ROW)을 반환하는 집계

-- 조건(1=0)을 만족하는 직원이 아예 없는 경우
SELECT COUNT(*), SUM(salary) 
FROM employees 
WHERE 1 = 0;

결과는 COUNT(*): 0, SUM(salary): NULL 형식으로 나타나게 된다.

 

케이스 B: GROUP BY 가 있는 집계 (Group Aggregation)

그룹을 지을 원본 데이터 자체가 없으므로, 그룹 생성 자체를 포기하는 방법으로 아무런 행도 반환하지 않는 케이스.

SELECT department_id, COUNT(*)
FROM employees
WHERE 1 = 0
GROUP BY department_id;

* B 케이스처럼 데이터베이스에서 GROUP BY를 썼을 때 데이터가 아예 안 넘어오는 상황을 백엔드 코드에서 인지하지 못해서 데이터 연산 처리를 잘못하고, '결과배열[0].카운트' 이런 코드로 접근하려다가 서버 에러가 터지는 경우도 많아서 중요 학습 포인트로 인지하면 좋다.

 

GROUP BY를 사용할 때는 API의 조회 결과가 0건일 수 있다는 예외 처리를 기본적으로 설정해 놓는 것이 좋다.

 

 

 

그룹화된 결과의 필터링 HAVING

데이터를 필터링한다는 목적이 WHERE와 같지만, WHERE 절과 HAVING 절은 데이터베이스 내부에서 실행되는 순서와 대상이 완전히 다르다.

- WHERE: 데이터를 그룹으로 묶기 전에, 원본 테이블의 개별 행(Row)들을 걸러내는 역할을 하는 절이다.

- HAVING: 데이터를 GROUP BY로 묶고 집계(Sum, Avg 등)를 끝낸 후에, 그 결과 그룹들을 걸러내는 절이다.

 

예시 SQL

SELECT department_id, AVG(salary) as avg_salary
FROM employees
WHERE status = 'ACTIVE'       -- 1. [그룹화 전] 재직 중인 직원만 먼저 추려냄
GROUP BY department_id        -- 2. 부서별로 그룹화 및 평균 급여 계산
HAVING AVG(salary) >= 5000    -- 3. [그룹화 후] 평균 급여가 5000 이상인 '부서'만 남김
ORDER BY avg_salary DESC;     -- 4. 최종 결과 정렬

 

성능 튜닝 팁 정의

WHERE 절에 써야 할 일반 조건을 HAVNIG 절로 사용하는 경우가 있다. 그리고, 결과 데이터는 같지만, 이런 데이터들은 사실 데이터베이스에 필요 없는 데이터들까지 전부 그룹화 연산을 한 다음에 진행되는 방식이 되기에 조금만 큰 규모에 있는 서비스에서 이런 구조의 형식으로 코드를 작성하고 실행하는 경우 쿼리 성능이 크게 저하되는 문제가 있다. 그래서, 일반 조건은 반드시 WHERE 절에 배치하여 '*'를 때려 넣는 것이 아니라 정확하게 데이터를 먼저 연산할 수 있도록 사전에 데이터들을 줄여놓는 처리 후에 작업하는 것이 데이터베이스 성능에 관여하게 된다.

 

 

집합 연산 (Set Operation)

두 개 이상의 서로 다른 SELECT 쿼리 결과를 수학의 벤 다이어그렘처럼 합치거나 빼는 기능으로 사용하기 위한 연산 절인데 필수 전제 조건이 필요하다. -> 연산하려는 쿼리들끼리 반환하는 컬럼의 개수와 데이터 타입이 정확히 일치해야 한다.

 

합집합 UNION vs UNION ALL

두 쿼리의 결과를 위아래로 이어 붙이는 것인데 성능 차이가 심하기 때문에 둘을 명확하게 구분해서 사용해야 된다.

- UNION: 두 결과를 합친 뒤, 중복된 행을 찾아 제거한다. 중복을 찾기 위해 내부적으로 전체 데이터를 정렬(Sort)하는 무거운 작업이 발생한다.

- UNION ALL: 중복 확인 없이 그냥 두 결과를 가져다 붙인다. 정렬 작업이 없으므로 속도는 빠른데 중복 데이터를 매우 많이 가져갈 수 있는 문제가 있다.

-- 특별히 중복 제거가 필요한 상황이 아니라면, UNION ALL을 사용하는 것을 권장하지만 실제 중복 제거가 필요한 경우가 많기에 조심스러운 부분이다.
SELECT emp_id, emp_name FROM current_employees
UNION ALL 
SELECT emp_id, emp_name FROM retired_employees;

 

교집합 INTERSECT

두 쿼리 결과에 공통으로 존재하는 행만 남기고 나머지는 버린다. (기본적으로 중복된 행은 1개로 압축하여 반환한다.)

-- 우수 직원 명단과 보너스 수령자 명단에 둘 다 속하는(교집합) 직원 찾기
SELECT emp_id FROM top_performers
INTERSECT
SELECT emp_id FROM bonus_receivers;

 

 

차집합 PostrgreSQL(EXCEPT)와 Oracle(MINUS)

위 방법은 첫 번째 쿼리 결과에서 두 번째 쿼리에 있는 내용과 겹치는 부분을 빼버리는(제외하는) 연산으로 두 DB의 문법이 갈리는 포인트를 가지고 있다.

 

표준 SQL을 잘 지키는 PostgreSQL은 표준 예약어인 EXCEPT를 사용한다. (이러니 인기 많고 좋아할 수밖에..)

-- 전체 직원 중 특정 프로젝트(A) 참여자를 제외한 나머지 직원 찾기
SELECT emp_id FROM all_employees
EXCEPT
SELECT emp_id FROM project_a_members;

 

 

오라클은 독자적인 예약어로 MINUS를 사용해 왔다.

다만, 포스트그레 인기가 점점 커져서 그런지.. 21c 버전 이후는 표준인 EXCEPT를 지원하고 있다. 하지만 대부분의 실무 레거시는 21c 버전 이전 코드들이 돌아가고 있을 테니 이런 데이터베이스 전환 부채들을 놓치면 매우 곤란해질 수 있다.

SELECT emp_id FROM all_employees
MINUS
SELECT emp_id FROM project_a_members;

 

 

인지 정리하기

1) ORDER BY + NULL -> 포스트그레나 오라클 모두 NULL이 크다고 판단하지만, 실제 데이터를 저장 처리하는 것이 다른 차이점이 있다.

2) GROUP BY + NULL -> 오라클은 여러 NULL 값은 하나의 그룹으로 묶이지만 포스트그레는 각 다른 그룹으로 분리한다. (위 참조)

3) GROUP BY + 공집합 -> WHERE 조건에 의해 공집합이 발생하면, GROUP BY가 없는 집계는 1행(Null, 0 등)을 반환하고, GROUP BY가 있는 집계는 아예 결과가 없게 된다.(Empty Set).

4) HAVING -> 그룹화가 완료된 결과 집합에 대한 필터를 진행하는 것이라 데이터베이스 성능을 저하시키고 싶지 않다면 주의해야 한다.

5) 차집합 연산 -> SQL 표준을 따르는 포스트그레와 독자 예약어를 사용하지만 최신 버전들은 SQL 표준도 지원하는 오라클을 인지하기

 

 

---

감사합니다.

다음 포스팅 때 뵙겠습니다.