TIL 4 | SQL 문제 풀이

dereck·2024년 11월 22일

TIL

목록 보기

4/21

잘못된 내용이나 더 나은 방법이 있다면 댓글로 남겨주시면 감사드리겠습니다!

Lv_5 가장 많이 팔린 품목은?

테이블 생성 및 데이터 삽입

create table if not exists lv5_products
(
    product_id   int         not null primary key,
    product_name varchar(45) not null,
    category     varchar(45) not null,
    price        int         not null
);
create table if not exists lv5_customers
(
    customer_id   int         not null primary key,
    customer_name varchar(45) not null,
    country       varchar(45) not null
);
create table if not exists lv5_orders
(
    order_id    int  not null primary key,
    product_id  int  not null,
    order_date  date not null,
    quantity    int  not null,
    customer_id int  not null,
    foreign key (product_id) references lv5_products (product_id),
    foreign key (customer_id) references lv5_customers (customer_id)
);
insert into lv5_products (product_id, product_name, category, price)
values (1, 'Laptop', 'Electronics', 1000),
       (2, 'Smartphone', 'Electronics', 800),
       (3, 'Headphones', 'Electronics', 150),
       (4, 'Coffee Maker', 'Home', 200),
       (5, 'Blender', 'Home', 100);
insert into lv5_customers (customer_id, customer_name, country)
values (1, 'Alice', 'USA'),
       (2, 'Bob', 'UK'),
       (3, 'Charlie', 'USA');
insert into lv5_orders (order_id, product_id, order_date, quantity, customer_id)
values (101, 1, '2024-02-01', 2, 1),
       (102, 3, '2024-02-02', 1, 2),
       (103, 2, '2024-02-03', 1, 1),
       (104, 4, '2024-02-04', 3, 3),
       (105, 1, '2024-02-05', 1, 2),
       (106, 5, '2024-02-06', 2, 3);

문제 풀이

Lv_5-1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 횟수를 출력

이전에 풀었던 문제들로 인해 간단히 풀 수 있는 내용도 서브쿼리를 사용하려고 해서 최대한 간단한 방법으로 풀기 위해 노력했다.

문제를 보면 각 고객(=고객별), 구매한 모든 제품의 총 금액(price * quantity), 주문 횟수(count)로 나눌 수 있을 것 같다. 다음으로 테이블을 보면 고객 정보는 lv5_customers에 주문한 제품의 총 금액은 lv5_products와 lv5_orders에 있기 때문에 모든 테이블을 조인해야할 것 같다.

select c.customer_name,
       sum(p.price * o.quantity) as 'TotalAmount',
       count(o.order_id)         as 'OrderCount'
from lv5_customers c
         inner join lv5_orders o on c.customer_id = o.customer_id
         inner join lv5_products p on o.product_id = p.product_id
group by c.customer_name;

처음엔 서브쿼리를 자동적으로 사용하려고 했지만 이중 집계가 필요한 상황이 아니고, 단순하게 테이블을 합치기만 해도 풀 수 있어서 위와 같이 풀었다.

Lv_5-2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회

이번 문제는 Lv_4와 비슷한 점이 많아서 같은 방식으로 풀었는데, 더 나은 방법을 생각하려고 하지 않았던 것 같다.

문제를 푸는 큰 틀은 이전과 똑같이 문제를 나눠서 생각했다.

각 제품 카테고리별
가장 많이 팔린 제품의 이름
총 판매량

# 과정 1
select p.category, p.product_name, sum(o.quantity) as 'total_quantity'
from lv5_products p
         inner join lv5_orders o on p.product_id = o.product_id
group by p.category, p.product_name;

# 과정 2
select st1.category, max(st1.total_quantity) as 'TotalSold'
from (select p.category, p.product_name, sum(o.quantity) as 'total_quantity'
      from lv5_products p
               inner join lv5_orders o on p.product_id = o.product_id
      group by p.category, p.product_name) as st1
group by st1.category;

# 과정 3
select p.category, p.product_name, sum(o.quantity) as 'total_quantity'
from lv5_products p
         inner join lv5_orders o on p.product_id = o.product_id
group by p.category, p.product_name;

# 과정 4
select t1.category, t2.product_name, t1.TotalSold
from (select st1.category, max(st1.total_quantity) as 'TotalSold'
      from (select p.category, p.product_name, sum(o.quantity) as 'total_quantity'
            from lv5_products p
                     inner join lv5_orders o on p.product_id = o.product_id
            group by p.category, p.product_name) as st1
      group by st1.category) as t1
         inner join (select p.category, p.product_name, sum(o.quantity) as 'total_quantity'
                     from lv5_products p
                              inner join lv5_orders o on p.product_id = o.product_id
                     group by p.category, p.product_name) as t2
                    on t1.category = t2.category and t1.TotalSold = t2.total_quantity;

지금 다시 봐도 엄청 복잡한 것 같다. 이 쿼리문을 최적화하는 방법을 찾아봤다.

최적화

1. CTE 사용

먼저 CTE(Common Table Expression)이란 하나의 쿼리문 범위 내에서만 존재하며 여러 번 참조될 수 있는 이름이 지정된 일회성 테이블(결과 데이터)이다. CTE에는 비재귀적 CTE와 재귀적 CTE 두 가지가 있다. (자세한 내용은 따로 찾아보는 것을 권장)

# CTE 문
WITH RankedProducts AS (
    SELECT 
        p.category,
        p.product_name,
        SUM(o.quantity) AS total_quantity,
        ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(o.quantity) DESC) AS rank
    FROM lv5_products p
    INNER JOIN lv5_orders o ON p.product_id = o.product_id
    GROUP BY p.category, p.product_name
)

SELECT category, product_name, total_quantity
FROM RankedProducts
WHERE rank = 1;

위 문제는 내가 풀었던 코드를 기반으로 최적화 방안에 대한 질문을 했을 때 결과값으로 준 내용이다. ~~솔직히 보고 공부할 맛이 뚝 떨어졌다.~~

중첩 서브쿼리 대신 CTE를 사용하여 가독성을 늘리고, 다중 서브쿼리에서 한 번의 계산으로 성능을 향상시킨 것 같다. 추가적으로 위 코드는 윈도우 함수로 ROW_NUMBER() OVER()를 사용했지만 동일 판매량 처리 시에는 RANK() 또는 DENSE_RANK()를 사용할 수 있다.

2. HAVING 절 사용

똑같이 서브쿼리와 집계 함수를 활용해서 문제를 풀어냈지만 HAVING 에 대한 생각을 하지 못한 것을 보면 아직 SQL에 대한 개념이 많이 부족한 것 같다. HAVING에 대한 내용을 더 찾아봐야 겠다는 생각을 하게 되었다.

SELECT p.category, p.product_name, SUM(o.quantity) AS total_quantity
FROM lv5_products p
         INNER JOIN lv5_orders o ON p.product_id = o.product_id
GROUP BY p.category, p.product_name
HAVING SUM(o.quantity) = (SELECT MAX(total_quantity)
                          FROM (SELECT p2.category, SUM(o2.quantity) AS total_quantity
                                FROM lv5_products p2
                                         INNER JOIN lv5_orders o2 ON p2.product_id = o2.product_id
                                GROUP BY p2.category) AS subquery
                          WHERE subquery.category = p.category);