🗃️
Google BigQuery 가이드북: 데이터로 풀어보는 소비자 행동분석/
📝
7. 집합
📝

7. 집합

7.1. 집합 연산자7.2. UNION (A∪B)7.2.1. UNION ALL7.2.2. UNION DISTINCT7.3 INTERSECT (A∩B)7.3.1. INTERSECT DISTINCT7.4 EXCEPT (A-B)7.4.1. EXCEPT DISTINCT
 

7.1. 집합 연산자

집합 연산자는 SQL 쿼리에서 둘 이상의 쿼리 결과를 결합하거나 조작하는 데 사용됩니다. 주요 집합 연산자로는 UNION, INTERSECT, EXCEPT가 있습니다.
  1. UNION 연산자: UNION 연산자는 두 개 이상의 쿼리 결과를 합쳐 하나의 결과 집합으로 만듭니다. 해당 연산자를 사용하면 여러 데이터 소스에서 데이터를 가져와서 하나의 테이블처럼 사용할 수 있습니다. 예를 들어, 두 개의 테이블에서 고객 목록을 가져와 하나의 목록으로 합칠 수 있습니다.
  1. INTERSECT 연산자: INTERSECT 연산자는 쿼리 결과 간에 공통된 행 즉, 교집합을 반환합니다. 해당 연산자는 공통 요소를 식별하거나 비교하는 데 사용됩니다. 예를 들어, 두 개의 테이블에서 공통된 고객 목록을 찾을 수 있습니다.
  1. EXCEPT 연산자: EXCEPT 연산자는 첫 번째 쿼리 결과에서 두 번째 쿼리 결과를 제외한 결과를 반환합니다. 즉, 두 결과 집합의 차집합을 반환합니다. 이 연산자는 두 집합 간의 차이를 확인하거나 필터링하는 데 사용됩니다. 예를 들어, 첫 번째 테이블의 데이터에서 두 번째 테이블의 데이터를 제외하고 싶을 때 유용합니다.
출처: Weniv출처: Weniv
출처: Weniv
 

7.2. UNION (A∪B)

소개
두 집합의 모든 원소들을 가져오는 합집합의 개념입니다. BigQuery에서는 합집합 연산자로 UNION ALL 그리고 UNION DISTINCT 연산자가 있습니다.
UNION ALL은 두 개 이상의 쿼리 결과를 결합할 때 사용되며, 중복된 행을 제거하지 않습니다. 즉, 모든 결과 집합의 행을 포함하는 데 중복이 발생하더라도 중복된 행을 그대로 유지합니다. 이 연산자를 사용하면 데이터가 중복될 수 있지만, 처리 속도가 빠르며 결과 집합의 크기가 더 크게 될 수 있습니다.
반면 UNION DISTINCT 의 경우 두 개 이상의 쿼리 결과를 결합할 때 사용되며, 중복된 행을 제거합니다. 따라서 결과 집합에는 중복된 행이 포함되지 않습니다. 이 연산자를 사용하면 중복된 데이터가 필요 없을 때 유용합니다. 중복된 데이터를 제거하므로 결과 집합의 크기가 더 작게 유지됩니다.
사용시 주의해야 할 점으로는 합칠 쿼리들의 SELECT 절에서 명시하는 컬럼의 개수와 데이터 타입은 동일해야 합니다. 또한 조회된 테이블의 컬럼명은 첫 번째 쿼리의 SELECT 절 컬럼명으로 표시됩니다.
  • UNION ALL
SELECT
			column1
FROM Table1
UNION ALL
SELECT
			column1
FROM Table2
ORDER BY 1;
  • UNION DISTINCT
SELECT
			column1
FROM Table1
UNION DISTINCT
SELECT
			column1
FROM Table2
ORDER BY 1;
 

7.2.1. UNION ALL

HR 데이터를 활용해 UNION ALL 연산자를 실습해 보겠습니다. 먼저 “salary”가 2000에서 5000사이인 직원들의 고유한 job ID를 조회해 보겠습니다.
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000;
notion imagenotion image
 
그리고 “salary”가 5001에서 6000사이인 직원들의 고유한 job ID를 조회해 보겠습니다.
SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 
“IT_PROG”라는 직무는 두 집합에 동일하게 존재하는 것을 확인할 수 있습니다. 이제 UNION ALL 연산자를 사용하여 두 쿼리 결과를 합쳐보도록 하겠습니다. 집합 연산자 앞뒤의 쿼리들을 괄호로 묶는 것은 생략 가능합니다.
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000

UNION ALL

SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
괄호 부재
(SELECT
			 DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000)

UNION ALL

(SELECT
			 job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000);
괄호로 둘러싸인 SELECT 문
notion imagenotion image
notion imagenotion image
 
만약 아래와 같이 각 쿼리 결과 테이블의 컬럼 개수가 같지 않은 경우, 에러가 발생하기 때문에 주의할 필요가 있습니다.
SELECT
			DISTINCT job_id # column1
    , department_id # column2
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000

UNION ALL

SELECT
			job_id # column1
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 
하지만 컬럼의 개수가 같더라도 합쳐지는 컬럼들의 데이터 타입이 같지 않은 경우도 에러를 반환합니다. 아래의 경우 “department_id”는 INTEGER 형이지만 “phone_number”는 STRING 형이기 때문에 연산을 수행할 수 없습니다.
만약 “phone_number” 컬럼이 INTEGER형이라면 쿼리 내에서 오류는 발생하지 않을 것으로 예상됩니다. 그러나 “department_id”와 “phone_number” 컬럼은 서로 다른 성격의 데이터이며, 이 두 열을 함께 합치면 데이터의 의미가 훼손될 수 있습니다. 따라서 데이터를 통합할 때는 데이터를 정확하게 이해하고, 데이터 유형과 의미가 변하지 않도록 주의해야 합니다.
SELECT
			DISTINCT job_id
    , department_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000

UNION ALL

SELECT
			job_id
    , phone_number
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
notion imagenotion image
 

7.2.2. UNION DISTINCT

앞서 UNION ALL 의 예시 결과를 보면, “IT_PROG” 가 첫 번째 행 그리고 여섯 번째 행에서도 나타납니다. 이는 salary가 2000에서 5000 사이인 쿼리 결과에서 하나 그리고 salary가 5001에서 6000 사이인 쿼리 결과에서 하나로 결괏값에 중복이 존재하지만, UNION ALL 연산자이기 때문에 모두 표시됩니다.
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000

UNION ALL

SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 
위와 같이 중복된 행을 원하지 않을 경우 UNION ALL 이 아닌 UNION DISTINCT를 사용하면 중복된 행 없이 테이블을 반환합니다.
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000

UNION DISTINCT

SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 

7.3 INTERSECT (A∩B)

두 쿼리의 결과에 공통으로 존재하는 행만 반환하는 개념입니다. Big Query에서는 교집합 연산자로 INTERSECT DISTINCT 연산자가 있습니다. 
SELECT
			column1
FROM Table1
INTERSECT DISTINCT
SELECT
			column1
FROM Table2;
 

7.3.1. INTERSECT DISTINCT

HR 데이터를 활용해 INTERSECT DISTINCT 연산자를 실습해 보겠습니다. 위에서 조회한 두 쿼리문을 다시 보도록 하겠습니다.
 
salary가 2000에서 5000사이인 직원들의 고유한 job ID와 salary가 5001에서 6000사이인 직원들의 고유한 job ID 두 쿼리 결괏값에서 “IT_PROG”라는 직무가 두 집합에 동일하게 존재하였습니다. 
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000;
notion imagenotion image
SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 
INTERSECT DISTINCT 연산자를 사용하여 두 쿼리 결괏값에 공통으로 존재하는 행만 반환하게 됩니다. 두 쿼리에서 공통적으로 반환된 결괏값은 “IT_PROG”가 있는 것을 확인할 수 있습니다.
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000
INTERSECT DISTINCT
SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 

7.4 EXCEPT (A-B)

첫 번째 결과 집합에는 나타나지만 두 번째 결과 집합에는 나타나지 않는 행만 반환합니다. Big Query에서는 차집합 연산자로 EXCEPT DISTINCT 연산자가 있습니다. 
SELECT
			column1
FROM Table1
EXCEPT DISTINCT
SELECT
			column1
FROM Table2;
 

7.4.1. EXCEPT DISTINCT

HR 데이터를 활용해 EXCEPT DISTINCT 연산자를 실습해 보겠습니다. 위에서 조회한 두 쿼리문을 다시 보도록 하겠습니다. 
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000;
notion imagenotion image
SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image
 
EXCEPT DISTINCT 연산자를 사용하여 “salary”가 2000에서 5000사이인 직원들의 고유한 job ID를 조회한 첫 번째 쿼리 결괏값에서 “salary”가 5001에서 6000사이인 직원들의 고유한 job ID를 조회한 두 번째 쿼리 결괏값에는 나타나지 않는 행을 조회해 보도록 하겠습니다.
SELECT
			DISTINCT job_id
FROM `HR.employees`
WHERE salary BETWEEN 2000 and 5000
EXCEPT DISTINCT
SELECT
			job_id
FROM `HR.employees`
WHERE salary BETWEEN 5001 and 6000;
notion imagenotion image