(SQL) COALESCE 함수 개념 및 연습문제

1. COALESCE 함수

• 목록에서 null이 아닌 첫 번째 값을 반환합니다. 
• 구문은 아래와 같이 COALESCE를 쓰고 이후에 원하는 인수를 얼마든지 추가할 수 있습니다.

출처 ❘ Udacity

• 대표적으로 사용되는 경우는 단 하나의 열에만 null이 아닌 값이 존재하는 여러 개의 열을 비교할 때 입니다.
• 아래의 예시 처럼 세 가지의 급여 지급 조건을 사용해서 전체 급여 필드를 계산해야 한다고 가정해봅시다.

• 위의 표에서 세가지 방법으로 급여가 지급되고 있습니다.
• 첫 번째는 시급, 두 번째는 월급, 세 번째는 매출입니다.
• 직원별로 단 하나의 열에만 null이 아닌 값이 존재하기 때문에 COALESCE 함수를 사용하기 좋습니다.
• COALESCE 함수 안에는 세 개의 서로 다른 인수가 존재합니다. 
• hourly_wage*40*52, salary*12, commission에 sales를 곱한 값입니다.
• 이를 통해 null이 아닌 첫번째 값을 반환하여 annual_income(연봉)이라는 열을 만듭니다.
• 또한 각각의 값에 스칼라를 곱하여 데이터를 표준화 함으로써 서로 다른 기준들을 바탕으로 연봉을 계산할 수 있도록 만들었다는 것을 알 수 있습니다.

 

2. 연습문제

1단계) 아래의 쿼리를 실행하여 누락된 데이터가 있는 행을 확인하십시오.

SELECT *
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

=> 위의 쿼리는 'a.id = o.account_id' JOIN 조건에 따라 테이블(계정 및 주문)의 "왼쪽 JOIN"을 만듭니다. JOIN 결과는 주문 테이블에 일치하는 행이 없더라도 반드시 계정 테이블의 모든 행을 포함합니다. 'id = 1731' 및 'name = 'Goldman Sachs Group''인 계정 테이블에 주문 테이블에 일치하는 행이 없는 행이 있습니다. 따라서 위의 쿼리는 주문 테이블의 각 열에 NULL이 있는 행을 반환합니다. 'id' 열도 비어 있음을 알 수 있습니다.

 

2단계) COALESCE를 사용하여 accounts.id 열을 null 값에 대한 account.id로 채우십시오.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, o.*
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

=> 위 쿼리는 'COALESCE(a.id, a.id)' 함수를 사용하여 'accounts.id' 값을 반환합니다. 다른 경우에는 정규화된 열 이름을 사용합니다. 구문을 이해하는 데 도움이 되도록 위의 coalesce 함수에 의도적으로 두 개의 (동일한) 인수를 유지했지만 COALESCE(a.id)에서도 올바르게 실행됩니다.

 

3단계) COALESCE를 사용하여 o.account_id 열을 null 값에 대한 account.id로 채우십시오.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.primary_poc, a.sales_rep_id,
	COALESCE(o.account_id, a.id) account_id, o.occurred_at, o.standard_qty, o.gloss_qty, o.poster_qty, o.total, o.standard_amt_usd, o.gloss_amt_usd, o.poster_amt_usd, o.total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;

 

4단계) COALESCE를 사용하여 qty 및 usd열 각각을 0으로 채우십시오.

SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, COALESCE(o.standard_qty, 0) standard_qty, COALESCE(o.gloss_qty,0) gloss_qty, COALESCE(o.poster_qty,0) poster_qty, COALESCE(o.total,0) total, COALESCE(o.standard_amt_usd,0) standard_amt_usd, COALESCE(o.gloss_amt_usd,0) gloss_amt_usd, COALESCE(o.poster_amt_usd,0) poster_amt_usd, COALESCE(o.total_amt_usd,0) total_amt_usd
FROM accounts a
LEFT JOIN orders o
ON a.id = o.account_id
WHERE o.total IS NULL;