사전캠프 달리기반 퀘스트 03-04

문제 3 이용자의 포인트 조회하기

이용자들 별로 획득한 포인트를 학생들에게 이메일로 보내려고 합니다. 이를 위한 자료를 가공해봅시다. 특히 users 테이블에는 있으나 point_users 에는 없는 유저가 있어요. 이 유저들의 경우 point를 0으로 처리합시다.

[풀이]

우선 users 테이블과 point_users 테이블을 join해야하는데, 0포인트인 사람도 조회하려면 한 쪽에 없는 데이터도 합쳐주는 left join을 해야할 듯. 양쪽에 공통으로 user_id 컬럼이 있으니 얘를 기준으로 하고. 한 유저의 포인트 획득 정보가 여러 번 있을 수 있으니 sum()을 해야 하나? group by는 user_id로 하고.

그리고 0 point인 사람들은 point 컬럼에 null값이 있을 테니, 조회하면서 if()활용해서 값이 null이면 0으로 해줘 하면 될 듯 하다! 그리고 포인트가 높은 순으로 정렬이 돼있는 듯.

[첫 시도]

SELECT u.user_id "user_id"
	, u.email "email"
	, if(pu.point=null,0,pu.point) "point"
FROM users u left join point_users pu on u.user_id = pu.user_id 
GROUP BY 1
ORDER BY 3 DESC

나머지는 잘 됐는데, 생각과는 달리 null값이 0으로 변해주지 않았다... 뭘 잘못 쳤나하고 다시 구문을 쳐보는데 자동완성으로 ifnull이란 구문을 발견, 뭔가 이 녀석을 활용해야 할 것 같은 직감에 검색해보았다.

[null을 처리하는 방법들]

ifnull() : 컬럼의 값이 null을 반환할 때, 대체해 반환할 값을 입력해주는 함수

/* 기본 구조 */
SELECT IFNULL(컬럼명, 대체할 값)
/* 문제에 대입한다면 */
SELECT IFNULL(pu.point, 0)

if()로도 가능했다. is를 사용하면 됐던 것. 마찬가지로 CASE문으로도 가능!

/* 기본 구조 */
SELECT IF(컬럼명 is NULL, 참일 때 출력값, 거짓일 때 출력값)
/* 문제에 대입한다면 */
SELECT IF(pu.point is NULL, 0, pu.point)

※ SQL에서 NULL을 다룰 때 is는 되고 =는 안되는 이유

SQL에는 TRUE, FALSE 이외에도 데이터 값이 없음을 의미하는 UNKNOWN까지 3가 논리(3-valued logic)을 사용함. 이를 바탕으로 특정값과 NULL을 비교하면 그 결과는 참이나 거짓이 아니라, 항상 UNKNOWN이 나옴.

즉 TRUE와 FALSE를 기반으로 하는 논리 연산에서 NULL을 활용하기 위해선, is null이나 is not null을 거쳐 참과 거짓을 판별해야 함.

/* UNKNWON이 나와버리기 때문에 조건문의 판별이 불가능 */
SELECT if(point = NULL, 0, point)

COALESECE() : 지정한 표현식들 중 NULL이 아닌 첫 번째 값을 반환

/* 기본 구조 */
SELECT COALESCE(컬럼명, 대체값)
/* 문제에 적용 */
SELECT COALESCE(pu.point, 0)

컬럼명과 대체값이란 표현식이 둘 있음. 컬럼을 조회하다가 값이 있는 행에서는 그 값이 NULL이 아닌 첫 값이니까 그대로 반환하고, 값이 NULL인 행을 만나면 다음 표현식인 대체값이 NULL이 아닌 첫 값이니 대체값을 반환하게 됨!

/* 기본 구조 */
SELECT COALESCE(컬럼명1, 컬럼명2, 컬럼명3)
/* 이 꼴은 이번 문제에 적용은 못 할 듯 */

여러 컬럼을 조회하는 구조. 첫 컬럼에서 NULL값을 가진 행을 만나면 둘째 컬럼을 조회하는 방식. 같은 행에 첫 컬럼과 둘째 컬럼의 값이 모두 NULL이라면 셋째 컬럼의 값을 반환하겄지. 셋 다 NULL인 행에선 어떻게 되는 거지?

강의에서 지나가며 봤던 함수인디 이번에 제대로 이해할 수 있어서 좋았다.

[정답]

/* ifnull() 활용 */
SELECT u.user_id "user_id"
	, u.email "email"
	, IFNULL(pu.point, 0) "point"
FROM users u left join point_users pu on u.user_id = pu.user_id 
GROUP BY 1
ORDER BY 3 DESC

/* if() 활용 */
SELECT u.user_id "user_id"
	, u.email "email"
	, if(pu.point is null, 0, pu.point) "point"
FROM users u left join point_users pu on u.user_id = pu.user_id 
GROUP BY 1
ORDER BY 3 DESC

/* coalesce() 활용 */
SELECT u.user_id "user_id"
	, u.email "email"
	, COALESCE(pu.point,0) "point"
FROM users u left join point_users pu on u.user_id = pu.user_id 
GROUP BY 1
ORDER BY 3 DESC

[힌트 본 후]

다행히 아이디에 중복은 없었던 모양이라 GROUP BY는 굳이 필요 없었고, SUM()은 쓸 일도 없었지만, 이런 부분은 문제에서 좀 더 명확히 짚어줬으면 좋겠다는 생각이... 내가 너무 불편하게 읽는 건가 싶기도 하고... 무튼...

SELECT u.user_id
	, u.email
   	, COALESCE(p.point,0) "point"
FROM users u left JOIN point_users p ON u.user_id = p.user_id
order by p.point desc;

JOIN한 상태에선 u.email하면 컬럼명이 그렇게 나올 거라 생각했는데, user_id로만 나오는구먼! 기억해두자

문제 4 단골 고객님 찾기

갑자기 형식이 달라졌다. 오히려 좋아. 데이터베이스 연결해주는 걸 따라만 해봐서 잘 이해가 안 됐는데, 이번 기회에 연습해보자구. 문제 풀기 전에 HAVING이 뭔지 공부하고 오자. 기억이 안 난다 ㅠ

[HAVING 절]

GROUP BY한 결과에 조건을 붙이고 싶을 때 활용. 작성 방식은 WHERE절과 동일
WHERE는 조건을 먼저 적용한 후에 그룹화를 진행하는 반면, HAVING은 그룹화를 먼저 진행하고 조건을 적용

/* 기본 구조 */
SELECT 컬럼명
	, 컬럼명2
FROM 테이블명
GROUP BY 1 
HAVING 조건

[그룹화와 중복 제거]

GROUP BY와 DISTINCT의 공통점은 중복 데이터를 제거해주는 것. 다만 차이는?

집계함수(SUM,AVG 등등)을 사용해야하는 경우, 특정 그룹으로 분류하여 정렬이 필요함 → GROUP BY
그룹 분류 필요 없고 단순 중복 제거만을 위할 때 → DISTINCT (이 쪽이 속도 면에서 이득임!)

문제 4-1

고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요. 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.

[4-1 풀이]

우선 두 테이블을 join해야하고, 고객별이니까 CustomerName 기준으로 그룹화한 후 count(1)하고 TotalSpent 컬럼에 SUM()을 적용해 조회. 주문을 한 적 없는 고객도 포함돼야 하니까 LEFT JOIN해야겠고... 여기서 having절을 어따 쓰는 거지....?

[4-1 첫 시도]

select c.CustomerName 
	, count(1) "OrderCount"
	, sum(o.TotalAmount) "TotalSpent"
from orders o left join customer c on o.CustomerID = c.CustomerID 
group by 1

성공했다....?! having이나 서브쿼리를 전혀 활용하지 않았는디 이거 괜찮은 건가...

문제 4-2

나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

[풀이]

줄글로 쓰니 난잡해서 단계 순으로 써보기로...

1. 서브 쿼리 만들자. 두 테이블 조인하고, 고객명 기준으로 그룹화 한 담에 고객별로 나라, 고객명, 총 주문금액을 조회

2. 서브쿼리에서 다시 나라 기준으로 그룹화 한 담에 나라, 고객명, 총 주문 금액을 조회

3. 이때 조회할 고객명은 한 나라에서 총 주문 금액이 제일 높은 사람이란 조건을 달 것. HAVING절 활용

4. WHERE절로 하면 결과가 달라지는지 궁금하니 확인해보자

[첫 시도]

/* 실패 코드 */
select Country
	, CustomerName "Top_Customer"
	, `Top_Spent`
from
	(
	select c.CustomerName 
		, c.Country 
		, sum(o.TotalAmount) "Top_Spent"
	from orders o left join customer c on o.CustomerID = c.CustomerID 
	group by 1, 2
	) SubQ01
group by 1, 2
having `Top_Spent` = max(`Top_Spent`)

실패했다.... group by에서 자꾸 오류가 발생함. 서브쿼리에선 고객명만 기준 삼고 싶고, 최종 select에선 나라만 기준 삼고 싶은데, 그게 안 된다 ㅠㅠㅠㅠㅜㅠㅜ 그리고 자꾸 컬럼명 alias해주는데 오류 남. 이건 백틱으로 감싸주면 해결...
only_full_group_by란 기능에 관련한 오류인데, 그룹화 기준이 되는 컬럼과 집계함수 컬럼 이외의 컬럼을 조회하려할 때 이 기능과 충돌한단다. 나라 기준일 땐 고객명이 문제인 거고, 고객명 기준일 땐 나라가 문제인 것...
이 문제는 any_value()를 덮거나 저 기능을 꺼버리면 넘어갈 순 있다는데, 전자는 컬럼의 아무 값이나 가져오는 거라 결과가 부정확하고... 기능을 끄는 건 잘 알지도 못 하는데 불안하다...

[두번째 시도]

/* 성공 코드 */
select `Country`
	, CustomerName "Top_Customer"
	, `TotalSpent` "Top_Spent"
from 
	(
	select  CustomerName
		, `Country`
		, `TotalSpent`
		, rank() over(partition by `Country` order by `TotalSpent` desc) "SpentRank"
	from
		(
		select c.CustomerName 
			, any_value(c.Country) "Country"
			, sum(o.TotalAmount) "TotalSpent"
		from orders o left join customer c on o.CustomerID = c.CustomerID 
		group by 1
		) SubQ1
	) SubQ2
where `SpentRank` = 1

정신이 반쯤 나간 상태로 일단 where절로 해결해보았.... 엄청난 고봉밥이다. 훨씬 간단히 표현할 수 있을 거 같은데 사고회로가 힘을 내주지 않음...

[세번째 시도]

/* 성공 코드 */
select Country
	, CustomerName "Top_Customer"
	, `TotalSpent` "Top_Spent"
from
	(
	select c.Country 
		, c.CustomerName
		, sum(o.TotalAmount) "TotalSpent"
		, rank() over(partition by c.Country order by sum(o.TotalAmount) desc) "SpentRank"
	from orders o left join customer c on o.CustomerID = c.CustomerID 
	group by 1,2
	) SubQ1
where `SpentRank` = 1

두번째 시도를 간략히 하는 데 성공.... 근데 having을 활용하는 방법을 당최 모르겠다...

/* 실패 코드 */
(
select c.Country 
	, c.CustomerName
	, sum(o.TotalAmount) "TotalSpent"
	, rank() over(partition by c.Country order by sum(o.TotalAmount) desc) "SpentRank"
from orders o left join customer c on o.CustomerID = c.CustomerID 
group by 1,2
/* having 시도 */
having `SpentRank` = 1
/* having 시도 */
) SubQ1

서브 쿼리 안에서 이렇게 1등인 녀석만 조회하도록 시도해 봤는데, "You cannot use the alias 'SpentRank' of an expression containing a window function in this context." 이러면서 안 된단다... 일단 집에 가자....
최종 조회에서 나라와 고객명을 열람하려면 서브 쿼리에서 계속 둘을 가져와줘야 하는데, 그러면 그룹화에서 자꾸 충돌이 발생한다... 그렇다고 둘다 그룹화해버리면 나라별 고객별 총 주문금액을 비교해버리니 Max()가 의미가 없어지고...

[네번째 시도]

/* 성공 코드 */
select Country
	, any_value(CustomerName) as Top_Customer
	, max(TotalSpent) as Top_spent
from 
	(
	select c.CustomerName
		, any_value(c.Country) as Country
		, sum(o.TotalAmount) as TotalSpent
	from orders o left join customer c on o.CustomerID = c.CustomerID 
	group by 1
	) SubQ1
group by 1

요구하는 기대결과를 정확히 출력해주긴 하는 코드... 하지만 여전히 문제가 있다

1. having절을 전혀 활용하지 않았음

2. 서브 쿼리 안에서 country를 any_value()로 묶은 것은 고객 당 나라가 하나라서 괜찮은데, 밖에서 나라를 기준으로 했을 때는 고객명이 둘인 경우가 있어서, 지금의 출력 결과가 정확하다고 보긴 어려움... 여러 번 실행해도 결과는 같은디, 아마 max() 값을 보여주는 거라서 우연히 고정값이 나오고 있는 듯 하다. 이 문제에 한해서만 해결된 거란 소리...

그래도 발견한 점 하나! alias할 때 큰따옴표로 안 하고, as를 활용하니 백틱 사용하지 않아도 컬럼 호출에 문제가 없다 왜지
좀 탐색해보니 having절에 서브 쿼리를 넣을 수 있네?! 뭔가 실마리를 잡은 거 같기도

[다섯번째 시도]

/* 성공 코드 */
select c.Country
	, c.CustomerName as Top_Customer
	, sum(o.TotalAmount) as Top_Spent
from orders o left join customer c on o.CustomerID = c.CustomerID 
group by 1,2
having Top_Spent in (450, 400, 280)

ㅋㅋㅋㅋㅋㅋㅋ 이젠 이러고 있다... 저 450, 400, 280을 이제 각 국가별 max값으로 바꿔주면 되려나?

이 단계에서 얻은 정보! having절이기 때문에 셀렉트에서 지정한 alias명을 바로 활용할 수 있다! where절이었으면 Top_Spent를 못 쓰고 sum(o.TotalAmount)를 다 썼어야 했을 것.
Operand should contain 1 column(s) : 보통 조건절에서 발생하는 오류. 비교하는 양쪽의 컬럼 수가 안 맞는다는 의미.

[여섯번째 시도]

/* 성!!! 공!!! 코!!! 드!!! */
select c.Country as Country 
	, c.CustomerName as Top_Customer
	, sum(o.TotalAmount) as Top_spent
from orders o left join customer c on o.CustomerID = c.CustomerID 
group by 1,2
having Top_Spent in (
select 국가별최대주문금액
from 
	(
	select 국가명
		, max(고객별총주문금액) as "국가별최대주문금액"
	from 
		(
		select c.Country as "국가명" 
			, c.CustomerName as "고객명"
			, sum(o.TotalAmount) as "고객별총주문금액"
		from orders o left join customer c on o.CustomerID = c.CustomerID 
		group by 1,2
		) SubQ1
	group by 1
	) SubQ2
)

와!!!!!! 성공했다!!!!!! 근데 정말 가독성이 별로고 코드가 더러운 거 같다... 억지로 해낸 느낌.... 다섯번째 시도와 동일한데 (450, 400, 280) 부분을 서브쿼리화 했다.... 중간에 저 operand 오류를 만났던 건 서브쿼리를 한 번만 하는 바람에 국가명, 국가별최대주문금액 두 컬럼을 Top_Spent 하나와 비교하는 바람에 경험했다... 무튼 어찌어찌 해내긴 했드ㅏ..... 더 많이 배워서 더 이쁘고 좋은 방법으로 뚝딱 해내고 싶은 마음 뿐... 무튼 자자...

'SQL 공부' 카테고리의 다른 글

사전캠프 달리기반 퀘스트 06 (1)	2024.10.23
사전캠프 달리기반 퀘스트 05 (3)	2024.10.22
사전캠프 달리기반 퀘스트 01-02 (0)	2024.10.19
사전캠프 걷기반 퀘스트 11 (마지막 연습 문제) (1)	2024.10.14
사전캠프 걷기반 퀘스트 10 (0)	2024.10.12

일기장

사전캠프 달리기반 퀘스트 03-04

문제 3 이용자의 포인트 조회하기

문제 4 단골 고객님 찾기

문제 4-1

문제 4-2

'SQL 공부' 카테고리의 다른 글

티스토리툴바

사전캠프 달리기반 퀘스트 03-04

문제 3 이용자의 포인트 조회하기

문제 4 단골 고객님 찾기

문제 4-1

문제 4-2

'SQL 공부' 카테고리의 다른 글

관련글

티스토리툴바