Lv_4 문제 풀이 과정입니다. 틀린 부분이나 보완할 점은 댓글로 알려주시면 감사드리겠습니다!
Lv_4 단골 고객님 찾기
테이블 생성
create table if not exists lv4_customers
(
customer_id int not null primary key,
customer_name varchar(45) not null,
country varchar(45) not null
);
create table if not exists lv4_orders
(
order_id int not null primary key,
customer_id int not null,
order_date date not null,
total_amount int not null,
foreign key (customer_id) references lv4_customers (customer_id)
);
insert into lv4_customers (customer_id, customer_name, country)
values (1, 'Alice', 'USA'),
(2, 'Bob', 'UK'),
(3, 'Charlie', 'USA'),
(4, 'David', 'Canada');
insert into lv4_orders (order_id, customer_id, order_date, total_amount)
values (101, 1, '2024-01-01', 150),
(102, 2, '2024-01-03', 200),
(103, 1, '2024-01-04', 300),
(104, 3, '2024-01-04', 50),
(105, 2, '2024-01-05', 80),
(106, 4, '2024-01-06', 400);문제 풀이
Lv_4-1. 고객별로 주문 건수와 총 주문 금액을 조회
먼저 문제를 보면 고객별로 주문 건수와 총 주문 금액을 구하려고 한다. 따라서 아래와 같이 생각할 수 있다.
- 고객별 -> 고객을 그룹핑
- 주문 건수 -> 고객별로 그룹핑된 상태에서 주문된 횟수만큼 더함
- 총 주문 금액 -> 고객별로 그룹핑된 상태에서 주문 금액을 더함
이후 하나씩 조회하면서 구하면 되는데 먼저 고객에 대한 정보도 필요하고, 주문에 대한 정보도 필요하기 때문에 JOIN을 먼저 해준다. 그리고 고객별로 그룹핑을 한 뒤에 나머지 데이터를 집계 함수를 사용해서 구하면 된다.
고객별 주문 건수 구하기
select c.customer_name as 'CustomerName', count(c.customer_name) as 'OrderCount', sum(total_amount) as 'TotalSpent'
from lv4_customers c inner join lv4_orders o on c.customer_id = o.customer_id
group by c.customer_name;Lv_4-2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회
문제를 보면 구해야 할 것이 많다는 것을 알 수 있다. 이럴 땐 문제를 나누는 것이 도움이 된다고 생각한다. 나는 먼저 아래와 같이 나눴다.
- 나라별로 총 주문 금액 찾기
- 1번에서 찾은 나라별 총 주문 금액에서 가장 높은 주문 금액을 찾기
아래와 같이 나눈 이유는 나라별로 총 주문 금액을 구하고 그 금액이 가장 높은을 해결하려면 SUM()을 사용하고 나온 값에 MAX()를 해줘야 하는데 이중 집계 함수는 사용할 수 없기 때문에 복잡할 수 있는 부분을 먼저 구하려고 했기 때문이다.
# 1. 먼저 각 고객별, 나라별로 총 주문 금액 구하기
select c.customer_id, c.country, sum(o.total_amount) as 'total_spent'
from lv4_customers c
inner join lv4_orders o on c.customer_id = o.customer_id
group by c.customer_id, c.country;
# 2. 나라별로 총 주문 금액이 가장 높은 금액 구하기
select ts.country, max(ts.total_spent) as 'Top_Spent'
from (select c.customer_id, c.country, sum(o.total_amount) as 'total_spent'
from lv4_customers c
inner join lv4_orders o on c.customer_id = o.customer_id
group by c.customer_id, c.country) as ts
group by ts.country;이러면 이제 절반 정도 해결한 것이다. 이제 남은 것은 나온 결과에다가 고객 이름을 붙여주면 된다. 여기서 ON절에 대한 이해가 필요하다.
간단히 말하자면 ON절은 JOIN 수행 시 두 테이블을 단일 테이블로 합치는 과정에서 두 테이블의 각 행에 어떤 열을 기준으로 값을 붙여서 단일 테이블로 만들 것인지를 정하는 역할이라고 할 수 있다.
그럼 이제 어떤 값을 사용해서 붙일 지 생각을 해보자. 나온 결과 값이 country와 Top_spent이기 때문에 동일한 값을 붙이되 같은 값을 가지는 고객의 이름을 추가로 붙여주면 문제를 해결할 수 있다.
# 고객 이름을 붙이기 위한 데이터 조회
select c.country, c.customer_name, sum(o.total_amount) as 'total_spent'
from lv4_customers c
inner join lv4_orders o on c.customer_id = o.customer_id
group by c.country, c.customer_name;이제 필요한 모든 데이터를 구했다. 구한 데이터를 합쳐서 원하는 값을 조회해보자.
# t1과 t2의 country, t1의 Top_Spent와 t2의 total_spent를 모두 만족하는 값에 데이터를 합침
select t1.country, t2.customer_name, t1.Top_Spent
from (select ts.country, max(ts.total_spent) as 'Top_Spent'
from (select c.customer_id, c.country, sum(o.total_amount) as 'total_spent'
from lv4_customers c
inner join lv4_orders o on c.customer_id = o.customer_id
group by c.customer_id, c.country) as ts
group by ts.country) as t1
inner join (select c.country, c.customer_name, sum(o.total_amount) as 'total_spent'
from lv4_customers c
inner join lv4_orders o on c.customer_id = o.customer_id
group by c.country, c.customer_name) as t2
on t1.country = t2.country and t1.Top_Spent = t2.total_spent;이렇게 하면 t2의 결과 행은 4개지만 total_spent 값과 Top_Spent 값이 같은 행만을 찾아서 합치기 때문에 원하는 결과를 찾을 수 있게 된다.
