SQL coalesce, CTE 설명과 예시 문제

개요

---

SQL coalesce 함수, CTE(Common Table Expressions)에 대해 알아보아요

 

참고

---

DataExpert.io SQL 문제

https://www.dataexpert.io/questions

Data Engineer SQL Questions

 

 

1. SQL coalesce 함수

---

SQL의 COALESCE 함수는 데이터 처리에서 결측치(NULL)를 처리할 때 유용하게 사용되는 함수입니다.

 

사용방법

주어진 인자들 중 첫 번째로 NULL이 아닌 값을 반환합니다.

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

 

여러 개의 인자를 받을 수 있고, 그것들 중 첫 번째로 NULL이 아닌 값을 반환하며 모두 NULL인 경우 NULL을 반환합니다.

최소 2개의 인자를 필요로하며, 타입은 모두 같아야 합니다.

 

case 1) 기본 값 채워넣기

자주 활용하는 방법 중 하나로, 사용자가 입력하지 않은 정보에 대해 기본 값을 채워 넣을 때 유용합니다.

SELECT id, COALESCE(phone, '010-0000-0000') AS contact
FROM customers;

 

case 2) 여러 값들 중 우선 순위로 처리

여러 이메일을 폼에 입력 받은 경우, 우선순위를 두어 특정 컬럼에 값을 채울 수도 있습니다.

SELECT COALESCE(email, secondary_email, '이메일 없음') AS preferred_email
FROM contacts;

 

예시 문제

Question: Salary Range Difference

---

https://www.dataexpert.io/question/salary-range-difference

문제

아래 형태의 playground.employees_salary 테이블이 주어진다.

Column	설명
id	ID
name	이름
salary	급여

 

아래 조건을 만족하도록 SQL을 작성하시오

• employee 중 최대, 최소 급여의 차이
• 단, employee가 아무도 없다면 0을 결과로

 

풀이 

문제에서 요구하는 조건 중 까다로울 법한 조건은 employee가 아무도 없다면 0을 결과로 출력하는 것입니다.

이 조건은 SQL coalesce 함수를 사용하면 간단하게 해결됩니다.

• coalesce란?
  
  • 두 인자를 받아, 둘 중 하나가 NULL 이라면 다른 나머지를 결과로 리턴
  • 만약 둘 다 NULL이면 NULL을, 둘 다 Not NULL이라면 첫 번째 인자를 리턴
  • ex) coalesce(NULL, B) = B, coalesce(A, NULL) = A
• 이 문제에서는 coalesce를 사용해 아래와 같은 수식으로 활용 가능합니다.
  
  • coalesce(최대 최소 급여의 차이, 0)

최대, 최소 급여는 서브 쿼리를 이용해 계산합니다.

 

해답

서브 쿼리 풀이

SELECT 
    COALESCE(
        (SELECT SUM(salary)
        FROM playground.employees_salary
        WHERE salary = (SELECT MAX(salary) FROM playground.employees_salary)) 
        - 
        (SELECT SUM(salary)
        FROM playground.employees_salary
        WHERE salary = (SELECT MIN(salary) FROM playground.employees_salary)),
    0
) AS difference

 

참고

MySQL 공식 문서 - COALESCE 함수

https://dev.mysql.com/doc/refman/8.4/en/comparison-operators.html#function_coalesce

MySQL :: MySQL 8.4 Reference Manual :: 14.4.2 Comparison Functions and Operators

https://velog.io/@gooook/SQL-COALESCE

 

 

2. CTE(Common Table Expressions)

---

CTE(Common Table Expression)는 SQL에서 복잡한 쿼리를 가독성 높게 작성할 수 있게 하는 도구입니다.

 

단일 쿼리 내에서 이름 붙인 임시 결과 집합으로, 해당 쿼리 블록이 끝나면 사라집니다.

 

문법

MySQL에서는 WITH 키워드를 이용해 CTE를 정의합니다.

아래처럼 먼저 CTE 정의 후, 바로 메인 쿼리에서 참조할 수 있습니다.

WITH cte_name (column1, column2, ...) AS (
    -- CTE에 포함될 쿼리
    SELECT ...
)
SELECT ...
FROM cte_name;

 

 

case 1) 복잡한 쿼리는 미리미리

아래와 같이, 집계 함수를 위에서 미리 빼두고 아래에서는 쿼리하고자 하는 대상에 대해 작성합니다.

WITH Sales_CTE AS (
    SELECT product_id, SUM(sales) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT product_id, total_sales
FROM Sales_CTE
WHERE total_sales > 1000;

 

case 2) 연속해서도 가능해요

연속해서 CTE를 만들고, 꼬리물기 식으로 CTE를 가져오면서 쿼리를 작성할 수도 있습니다.

WITH MonthlySales AS (
    -- 월별 매출 합계를 계산
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
        SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
TopMonth AS (
    -- 최고 매출 달 계산
    SELECT 
        sales_month,
        total_sales
    FROM MonthlySales
    WHERE total_sales = (SELECT MAX(total_sales) FROM MonthlySales)
)
SELECT 
    ms.sales_month,
    ms.total_sales,
    CASE 
        WHEN ms.sales_month = tm.sales_month THEN '최고 매출 달'
        ELSE '일반'
    END AS sales_category
FROM MonthlySales ms
LEFT JOIN TopMonth tm
ON ms.sales_month = tm.sales_month
ORDER BY ms.sales_month;

 

 

예시 문제

Question: Identify Department for Reduction

---

https://www.dataexpert.io/question/department-reduction-criteria

Question: Identify Department for Reduction

 

 

문제

아래 형태의 playground.dept, playground.emp 테이블이 각각 주어진다.

 

dept

Column	설명
id	부서 ID
name	부서명

 

 

emp

Column	설명
id	직원 ID
full_name	직원 이름
salary	직원 급여
department	직원이 속한 부서(ID)

 

 

아래 조건을 만족하도록 SQL을 작성하시오

• 5명 이하의 직원을 가진 부서
• 정렬 조건
  • 직원의 연봉 총합 (내림차순)
  • 직원 수 (내림차순)
  • 부서 ID (오름차순)
• 위 두 가지 결과 리스트에서, 짝수 행은 제거한 후 홀수 행만 남겨둔다.

 

풀이

문제를 쉽게 풀기 위해 두 가지 주요한 SQL 개념을 활용하였습니다.

 

1) CTE 사용

이러한 복잡한 조건이 주어진 경우, 조건에 따라 테이블을 분리 및 생성하는 것이 좋습니다.

(개인적으로 테이블을 잘게 나누는 것이 나중에 쿼리에 대한 히스토리를 파악하기 위한 측면에서도 좋다고 생각합니다!)

 

그 방식 중 자주 사용하는 것은 WITH 절을 이용한 CTE 사용입니다.

 

CTE란?

https://docs.snowflake.com/ko/user-guide/queries-cte

CTEs(일반 테이블 식) 관련 작업하기 | Snowflake Documentation

• 임시 뷰를 만들어 쿼리에 사용하는 방식

 

특히 이 문제는 정렬 이전에 5명 이하의 직원을 가진 부서 라는 대전제가 깔려 있기 때문에 CTE를 만들기 좋습니다.

따라서 미리 만들어둡니다.

 

5명 이하의 직원을 가진 부서 (부서명, 전체 직원 수, 총 급여, 부서 ID)

WITH under_five_emp AS (
	SELECT
        dep.name as dep_name,
        count(emp.id) as emp_number,
        sum(emp.salary) as total_salary,
        dep.id as dep_id
    FROM playground.dept dep
    JOIN playground.emp emp on dep.id = emp.department
    GROUP BY dep.name, dep.id
    HAVING COUNT(emp.id) <= 5
)

• 후일을 위해.. 미리 직원 수와 총 급여를 계산해 가져옵니다.

 

2) ROW_NUMBER() 사용

ROW_NUMBER()는 쿼리의 결과로 나온 행의 일련 번호를 부여하는 기능을 합니다.

ROW_NUMBER()란?

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16

ROW_NUMBER(Transact-SQL) - SQL Server

 

ROW_NUMBER 안에 order by절을 넣을 수 있는데, 이 정렬 조건에 따라 행의 번호가 붙게됩니다.

따라서 이 문제에서는 order by에 문제에서 조어진 정렬 조건을 삽입 후 홀수인 것만 반환하면 됩니다.

 

해답

WITH under_five_emp AS (
	SELECT
        dep.name as dep_name,
        count(emp.id) as emp_number,
        sum(emp.salary) as total_salary,
        dep.id as dep_id
    FROM playground.dept dep
    JOIN playground.emp emp on dep.id = emp.department
    GROUP BY dep.name, dep.id
    HAVING COUNT(emp.id) <= 5
)
SELECT 
  dep_name,
  emp_number,
  total_salary
FROM (
  SELECT 
    dep_name,
    emp_number,
    total_salary,
    ROW_NUMBER() OVER (
      ORDER BY total_salary DESC, emp_number DESC, dep_id ASC
    ) AS row_num
  FROM under_five_emp
) AS subquery
WHERE row_num % 2 = 1