[PostgreSQL] 데이터의 집계 및 결합

다양한 데이터의 결합 방법

데이터베이스로부터 원하는 형태의 데이터를 애플리케이션에 불러오다 보면 가끔 원하는 데이터가 분산되어 있다는 것을 알게 됩니다. 원하는 정보가 분산되어 있는 상황은 여러 가지가 있습니다.

1. 한 테이블에 여러 줄로 원하는 데이터가 분산되어 있는 경우: GROUP BY절을 이용해 “그룹화” 과정을 거쳐야 합니다.
2. 여러 테이블에 같은 유형의 데이터가 분산되어 있는 경우: UNION 혹은 UNION ALL 연산자를 사용해야 합니다.
3. 여러 테이블에 서로 다른 유형의 데이터가 분산되어 있는 경우: FROM절에 두 테이블을 부르고 WHERE절에 조건을 걸어 합치거나 JSON 연산자를 사용해야 합니다.

데이터 그룹화

DISTINCT 알아보기

테이블에서 중복되는 데이터를 없애는 DISTINCT절은 다음과 같이 SELECT절과 FROM절 사이에 사용됩니다.

SELECT
  DISTINCT <컬럼 지정>
FROM [테이블];

다음과 같이 SELECT 문에 컬럼을 한 개가 아닌 여러 개의 컬럼 지정도 가능합니다.

SELECT
  DISTINCT <컬럽1>, <컬럼2>
FROM [테이블]
ORDER BY <컬럼1>;

2개 이상 중복을 제거할 컬럼을 지정하고, 컬럼1을 기준으로 오름차순 정렬을 합니다.

GROUP BY 알아보기

원하는 자료를 그룹화 하는 방법입니다.

SELECT <그룹화 할 컬럼>
  FROM [테이블명]
  GROUP BY <그룹화 할 컬럼>;

그룹화 할 컬럼을 하나의 그룹으로 지정했기 때문에 중복된 자료가 존재할 수 없습니다. 즉, 그룹을 새로 생성할 때 동일한 내용을 갖고 있는 그룹을 만들 수 없습니다. 다음 코드에서는 여러 개의 컬럼을 지정합니다. SELECT절과 GROUP BY절에는 항상 같은 컬럼을 적거나 GROUP BY 관련 함수를 사용해야 합니다.

SELECT <컬럼1>, <컬럼2>
  FROM [테이블명]
  GROUP BY <컬럼1>, <컬럼2>;

두 개 이상의 컬럼을 지정하면 그룹화의 기준이 되는 컬럼을 SELECT문에 넣으면 그 컬럼의 유일한 값이 출력됩니다. 하지만 GROUP BY절은 DISTINCT절과는 다르게 집계 함수를 사용할 수 있으며, 집계 연산 이후에 HAVING절로 조건에 맞는 정보를 걸러낼 수 있습니다.

SELECT <컬럼1>,<컬럼2>, count(*)
  FROM [테이블명]
  GROUP BY <컬럼1>, <컬럼2>;

count() 함수는 그룹의 로우가 몇 개인지 반환합니다.

GROUP BY절을 사용할 때, 다음과 같이 SELECT절에 적은 컬럼 순서대로 숫자를 적어도 쿼리문은 동일하게 작동합니다.

SELECT <컬럼1>,<컬럼2>, count(*)
  FROM [테이블명]
  GROUP BY 1, 2;

1은 컬럼1을 가리키고, 2는 컬럼2를 가리킵니다. 1과 2를 바꾸면 컬럼2를 먼저 기분으로 잡고 그룹화된 결과가 출력됩니다.

HAVING 알아보기

집계를 한 데이터에서 원하는 조건만 검색하는 명령어는 WHERE절로 할 수 있습니다. 하지만 GROUP BY절에 조건을 걸고 검색할 때에는 WHERE절 못지 않게 HAVING 절을 자주 사용합니다. 두 명령문 모두 조건을 통해 검색을 지원합니다. 하지만 WHERE절은 집계 전 데이터를 조회할 때 사용하고, HAVING절은 집계된 데이터를 조회할 때 사용합니다. 즉, WHERE절에서는 집계함수를 사용하지 않습니다. WHERE절은 집계를 하지 않고 조건을 검색하기 때문에 HAVING절보다 성능이 더 빠릅니다.

SELECT <컬럼1>, count(*)
  FROM [테이블명]
  GROUP BY <컬럼1>
  HAVING count(*) > 3;

GROUP BY절로 컬럼1을 지정했기 때문에 SELECT문으로 컬럼1을 출력할 수 있습니다. 또한, HAVING절은 SELECT절의 집계함수를 기준으로 잡고 조건 연산을 합니다.

SQL 명령어 우선순위

명령어설명우선순위

FROM	불러올 테이블을 지정합니다.	1
WHERE	지정 테이블에서 조건을 통해 데이터를 가져옵니다.	2
GROUP BY	지정한 컬럼을 하나의 그룹으로 묶습니다.	3
HAVING	집계된 컬럼 데이터를 필터링합니다.	4
SELECT	조회할 컬럼을 선택합니다.	5
DISTINCT	컬럼에서 중복된 데이터를 제외시킵니다.	6
ORDER BY	지정된 컬럼을 오름차순/내림차순으로 정렬합니다.	7
LIMIT	지정한 수만큼 조회할 컬럼을 제한합니다.	8

우선순위가 1번인 FROM절은 모든 데이터를 불러오기 위해서 가장 먼저 실행됩니다. 그리고 2번인 WHERE절을 통해 불러온 테이블에서 필터링을 합니다. 어느 조건에 의해 필터링된 데이터들을 3번인 GROUP BY절을 통해 그룹화를 합니다. 집계함수를 이용해 조건을 연산하는 4번 HAVING절로 그룹화된 데이터를 또 다시 필터링합니다. 그리고 5번 조회할 컬럼을 SELECT절로 선택합니다. 6번 DISTINCT절이 선택한 컬럼에서 중복된 데이터를 제외시킵니다. 물론 GROUP BY절이 이미 중복된 데이터를 제거했을 수도 있습니다. 그리고 7번 ORDER BY절로 정렬을 하고, 8번 보고 싶은 로우 개수만큼 LIMIT절로 제한합니다.

집계합수

기본적인 집계함수

함수출력내용예시

avg()	null 값이 아닌 모든 입력 값의 평균	avg(rating)
count(*)	입력한 로우의 총 개수	count(*)
count()	null 값이 아닌 모든 입력 로우 값의 개수	count(rating)
max()	null 값이 아닌 모든 입력 값의 최댓값	max(rating)
min()	null 값이 아닌 모든 입력 값의 최솟값	min(rating)
sum()	null 값이 아닌 모든 입력 값의 합산값	sum(rating)

불리언 연산 집계함수

불리언 연산을 하는 집계함수는 데이터 타입이 불리언일 때 사용 가능합니다.

함수출력내용예시

bool_and()	입력된 데이터가 모두 참이면 참을 출력	bool_and(is_spicy)
bool_or()	입력한 데이터 중 하나라도 참이면 참을 출력	bool_or(is_spicy)
every()	bool_and 함수와 똑같다	every(is_spicy)

배열을 담는 집계함수

함수출력내용

array_agg()	배열로 연결된 null 값을 포함한 입력 값
	더 높은 차원의 배열로 연결된 입력 배열

JSON 집계함수

다음 모든 함수는 어떤 타입의 값이든 전부 입력받을 수 있습니다.

함수출력내용

json_agg()	null을 포함해 json배열로 집계한 값
jsonb_agg()	null을 포함해 jsonb배열로 집계한 값
json_object_agg(name, value)	name-value 쌍을 json개체로 집계한 값 value은 null을 포함, name은 포함하지 않습니다.
jsonb_object_agg(name, value)	name-value 쌍을 jsonb개체로 집계한 값 value은 null을 포함, name은 포함하지 않습니다.

여러 개의 테이블을 로우로 연결하기

명령어의 전제 조건

서로 다른 테이블을 세로 방향으로 합치기 위해서는 기본적으로 2개의 쿼리문 사이에 서로를 연결하는 명령어를 추가해야 하며, 이를 위해 몇 가지 전제 조건이 성립해야 합니다.

1. 두 SQL은 서로 컬럼의 개수가 동일해야 한다.
2. 같은 위치에 동일한 형식과 의미의 정보가 담겨야 한다.

두 테이블 사이의 컬럼 개수가 다르면 남는 컬럼이 생기기 때문에 두 테이블을 조합할 수 없으며, 두 테이블 조합 시 컬럼의 위치를 일치시켜서 조합하므로 같은 위치에 같은 형식과 같은 의미의 정보를 가지고 있어야 합니다.

UNION, UNION ALL절

두 명령어의 차이점

UNION 명령어는 중복되는 로우는 하나의 로우로만 표현하는 반면, UNION ALL 명령어는 중복 여부를 확인하지 않고 두 테이블을 결합해서 출력합니다.

UNION ALL 명령어는 중복 여부를 체크하지 않기 때문에 결합하는 데이터의 양이 많아질수록 UNION 명령어보다 더 나은 성능을 보여줍니다. 이런 이유 때문에 중복되는 데이터를 제거할 필요가 없는 상황에서는 일반적으로 UNION ALL 명령어를 사용합니다.

UNION 명령어의 중복 제거

UNION 명령어는 두 테이블을 결합할 때 두 테이블에 중복되는 로우만 제거해서 출력하는 것이 아니라, 두 테이블을 먼저 합치고 합친 로우들을 모두 검사하여 중복이 있으면 제거합니다.

INTERSECT, INTERSECT ALL절

INTERSECT 명령어는 두 테이블에 공통되는 로우만을 남기는 명령어입니다. INTERSECT 명령어는 중복되는 결과를 한 번만 출력하고, INTERSECT ALL 명령어는 중복 제거를 하지 않습니다.

EXCEPT, EXCEPT ALL절

EXCEPT 명령어는 두 테이블의 로우 정보 중 중복되지 않은 부분만을 출력하는 명령어입니다. 앞에 적은 테이블에서 뒤에 적은 테이블과 중복되는 로우를 제거한 결과가 나옵니다.

EXCEPT 명령어도 UNION과 마찬가지로 EXCEPT 명령어와 EXCEPT ALL 명령어가 있는데, EXCEPT 명령어는 중복을 제거한 명령어이고, EXCEPT ALL 명령어는 중복을 제거하지 않은 명령어입니다.

여러 개의 테이블을 컬럼으로 연결하기

FROM과 WHERE절을 이용한 데이터 결합

두 테이블을 모두 FROM절에 불러오는 의미

SELECT *
FROM rating, ramen;

두 테이블을 FROM절로 동시에 불러오면 좌측과 우측에 각각의 테이블이 출력됩니다. 이 결과는 두 테이블의 각각의 로우에 대한 모든 조합입니다.

두 테이블을 FROM절에 놓은 것은 사실 두 테이블을 교차 조인(CROSS JOIN)한다는 의미입니다. 두 테이블의 각각의 로우가 서로 한번씩 결합시키는 것을 교차 조인이라고 합니다.

두 테이블을 동시에 FROM절에 넣을 때 사실 두 테이블 사이의 CROSS JOIN 명령을 생략한 것입니다.

SELECT *
FROM rating CROSS JOIN ramen;

WHERE절로 결합된 데이터 고르기

두 테이블을 불러와서 원하는 정보를 한 번에 표시하려면 FROM절에 2개의 테이블을 올린 후 원하는 정보만 걸러내는 것입니다.

SELECT rating.user_id, rating.rating,
  ramen.name, ramen.quantity, ramen.is_spicy
FROM rating, ramen
WHERE ramen.id = rating.item_id AND rating.item_type = 'ramen';

정리하기

FROM절과 WHERE절을 이용하여 여러 테이블의 컬럼 정보를 결합하기 위해서는 우선 FROM절에 두 테이블을 모두 적어서, 두 테이블 로우의 모든 조합을 먼저 구합니다. 그 이후 각 테이블의 로우의 모든 조합에서 우리가 생각하기에 컬럼 사이의 관계가 의미 있는 정보만 남도록 WHERE절에 조건을 걸면 두 테이블의 관계가 연결된 조합만 남게됩니다.

데이터베이스 테이블 사이의 관계가 1:N 또는 M:N으로 모델링되어 운영되는 사례가 많기 때문에 원하는 정보가 여러 테이블의 컬럼에 걸쳐 나눠지는 상황이 자주 발생하게 됩니다. 그래서 표준 SQL에서는 이 패턴을 보다 읽기 좋게 하고, 다른 방식의 결합방법을 사용할 수 있도록 JOIN이라는 명령어를 만들었습니다.

JOIN을 이용한 데이터 결합

SELECT rating.user_id, rating.rating,
  ramen.name, ramen.quantity, ramen.is_spicy
FROM (
  rating JOIN ramen
  ON ramen.id = rating.item_id AND rating.item_type = 'ramen'
)

두 테이블을 JOIN문을 활용해 서로 연결할 것임을 명시적으로 표현했습니다. 또한 그와 관련된 조건문은 ON절을 이용해 표현하고 있습니다.

이전에 FROM을 사용한 쿼리문과의 성능상 차이는 전혀 없습니다. 왜냐하면 작동하는 방식이 완전히 동일하기 때문입니다.

다양한 JOIN의 종류

 

JOIN문은 여러 테이블의 컬럼정보를 결합하는 결합 구문입니다. 이 결합 구문에는 여러 가지 종류가 있는데 외부 조인과 내부 조인 구문으로 구분할 수 있습니다.

내부 조인문은 두 테이블 값이 서로 연결되는 부분만 결과로 출력하고, 외부 조인문은 서로 연결되지 않고 한쪽에만 데이터가 있는 로우도 출력합니다.

명령어축약형내부/외부 구분

INNER JOIN	JOIN	내부
LEFT OUTER JOIN	LEFT JOIN	외부
RIGHT OUTER JOIN	RIGHT JOIN	외부
FULL OUTER JOIN	FULL JOIN	외부

각 JOIN문은 축약형이 존재합니다. 축약형 구문은 원래 구문과 같은 기능을 하기 때문에 쿼리문 작성에 익숙해지면 축약형을 더 빈번하게 사용합니다.

 

 

LEFT JOIN 명령어는 명령어 앞에 쓰인 테이블을 기준으로 뒤에 쓰인 테이블과 연결되는 정보만 불러오고, 만약 연결된 정보가 없다면 NULL 값으로 출력합니다. RIGHT JOIN 명령은 LEFT JOIN 명령어와는 반대로 뒤에 적힌 테이블을 기준으로 정보를 출력합니다.

FULL JOIN의 경우 연결된 로우는 서로 연결하여 출력하고, 서로 연결되지 않은 로우는 연결되지 않은 부분의 정보를 NULL 값으로 비워둔 채 출력합니다.

JOIN문이 사용되는 위치

JOIN문이 사용되는 위치는 FROM절 다음에 온다는 공통점이 있습니다.

SELECT users.name, ramen.name, rating.rating,
  ramen.quantity, ramen.is_spicy
FROM rating
JOIN raemn
ON ramen.id = rating.item_id AND rating.item_type = 'ramen'
JOIN users
ON users.id = rating.user_id
WHERE rating >= 3;

2개의 JOIN문이 사용되는 경우 쿼리의 구조가 헷갈리기 쉬운데, 이럴 때는 JOIN문 앞뒤로 각각의 테이블이 온다는 것을 기억하고 괄호를 적으면 더 이해하기 쉽습니다.

SELECT users.name, ramen.name, rating.rating,
  ramen.quantity, ramen.is_spicy
FROM (
  (
    rating JOIN raemn
    ON ramen.id = rating.item_id AND rating.item_type = 'ramen'
  ) JOIN users
  ON users.id = rating.user_id
)
WHERE rating >= 3;

FROM절 안에 JOIN문이 들어있고 WHERE절은 그 다음에 있다는 것을 알 수 있습니다.

JOIN문 뒤에 오는 명령어

테이블 JOIN 테이블 ON 연결할 조건
테이블 JOIN 테이블 USING ( 연결할 컬럼 목록 )

JOIN문 뒤에는 ON 명령어나 USING 명령어가 주로 붙습니다. ON 명령어 뒤에는 테이블을 연결하는 조건을 적고, USING 명령어 뒤에는 두 테이블을 연결할 공통된 컬럼 이름을 적습니다.

SELECT drink_rating.user_id, drink_rating.rating,
  drink.name, drink.quantity, drink.volume
FROM (
  (
    SELECT rating.item_id AS id, rating,rating, rating.user_id
    FROM rating
    WHERE rating.item_type = 'drink'
  ) dring_rating
  JOIN drink
  ON drink.id = dring_rating.id
)

여기서 ON 명령어 뒤에 id 값이 일치하면 서로 연결하도록 설정했습니다. USING절을 이용하면 좀 더 간단하게 작성할 수 있습니다.

SELECT drink_rating.user_id, drink_rating.rating,
  drink.name, drink.quantity, drink.volume
FROM (
  (
    SELECT rating.item_id AS id, rating,rating, rating.user_id
    FROM rating
    WHERE rating.item_type = 'drink'
  ) dring_rating
  JOIN drink
  USING (id)
)

id 컬럼 이름이 공통되기 때문에 USING을 대신 사용할 수 있었지만, 만약 컬럼의 이름이 서로 다르다면 USING을 사용할 수 없습니다.

JOIN과 함께 쓰이는 함수

JOIN문을 사용하다보면 자주 쓰게 되는 함수가 있습니다.

COALESCE

COALESCE(인자1, 인자2, ... 인자n)

COALESCE 함수는 첫 번째 인자부터 순서대로 NULL 값이 아니면 그 값을 반환하고, 만약 NULL 값이면 다음 인자를 확인합니다. 이런 식으로 순서대로 확인해서 NULL 값이 아닌 값이 나오면 그 값을 반환하는 함수가 COALESCE 함수입니다.

이 함수는 외부 조인문을 사용할 때 NULL 값이 나오는 자리에 다른 값을 출력하기 위해서 주로 사용됩니다.