제품 주문 데이터를 활용한 커버리지 비율 분석

이번 글에서는 SQL을 사용하여 제품 주문 데이터를 활용하여 커버리지 비율을 분석하는 방법에 대해 알아보겠습니다.

 

샘플 데이터를 아래와 같이 생성했습니다. 예시 데이터입니다. 이 쿼리는 orders 테이블을 생성하고, 해당 테이블에 샘플 데이터를 삽입하는 쿼리입니다. orders 테이블은 제품 주문 데이터를 저장하는 테이블입니다. 제품 ID(product_id), 제품 이름(product_name), 제품 주문량(product_orders)을 컬럼으로 가지며, 제품 ID를 기본 키로 설정합니다. Dbeaver를 통해 table 추가했습니다.

-- orders 테이블 생성
CREATE TABLE orders (
	product_id INT PRIMARY KEY,
	product_name VARCHAR(50),
	product_orders INT
);

-- sample 데이터 삽입
INSERT INTO orders (product_id, product_name, product_orders)
VALUES
(1, '상품A', 100),
(2, '상품B', 80),
(3, '상품C', 60),
(4, '상품D', 120),
(5, '상품E', 90),
(6, '상품F', 70),
(7, '상품G', 110),
(8, '상품H', 50),
(9, '상품I', 40),
(10, '상품J', 130);

커버리지 비율 기반으로 매출이 높은 제품 추출하는 쿼리

위에서 구한 커버리지 비율을 활용하여, 매출이 높은 제품들을 추출하는 쿼리를 작성할 수 있습니다. 예를 들어, 커버리지 비율이 0.8 이하인 제품들을 추출하면, 전체 제품 중 상위 80%에 해당하는 매출을 차지하는 제품들을 식별할 수 있습니다. 이를 기반으로, 해당 제품들에 대한 추가적인 분석 및 마케팅 전략 수립이 가능합니다.

with orders as (
    select
        product_id,
        product_name,
        product_orders,
        sum(product_orders) over(order by product_orders desc rows between unbounded preceding and current row) as cumulative_product_orders,
        sum(product_orders) over() as total_product_orders,
        sum(product_orders) over(order by product_orders desc rows between unbounded preceding and current row)
        / cast(sum(product_orders) over() as numeric) as coverage_rate
    from
        orders
    order by
        3 desc
)
select
    product_id,
    product_name,
    product_orders,
    cumulative_product_orders,
    total_product_orders,
    coverage_rate
from
    orders
where
    coverage_rate <= 0.8

 

쿼리를 간단히 살펴보면, orders 테이블에서 제품 주문 데이터를 가져와서 각 제품별로 주문량(product_orders)을 내림차순으로 정렬한 뒤에, 누적 주문량(cumulative_product_orders)과 전체 제품 주문량(total_product_orders)을 구합니다. 이때, 누적 주문량은 현재 행까지의 주문량을 모두 더한 값이며, 전체 제품 주문량은 모든 제품의 주문량을 합한 값입니다. 그리고 마지막으로, 누적 주문량을 전체 제품 주문량으로 나눈 값을 커버리지 비율(coverage_rate)로 구합니다.

 

윈도우 함수에서 rows between unbounded preceding and current row를 사용했습니다. 이유는 사용하지 않을 경우 동일한 주문 건이 있을 때 동일한 coverage_rate로 표시 되게 됩니다. 예제에는 나타나 있지 않지만 그런 경우가 생긴다면 실무에서는 위 문구를 사용하여 동일하지 않게 표시되게 주로 작업하는 거 같습니다.

 

커버리지 비율은 해당 제품의 주문량이 전체 주문량 중 얼마나 차지하는지를 나타내는 지표입니다. 위 쿼리를 사용하여 전체 데이터와 Top 80% 데이터를 추출했을 때 결과입니다.

전체 데이터

Top 80% 데이터 (where절에서 coverage_rate <= 0.8 추가)

이처럼 위에서 구한 커버리지 비율을 활용하여, 매출이 높은 제품들을 추출하는 쿼리를 작성할 수 있습니다. 예를 들어, 위와 같이 커버리지 비율이 0.8 이하인 제품들을 추출하면, 전체 제품 중 상위 80%에 해당하는 매출을 차지하는 제품들을 식별할 수 있습니다. 이를 기반으로, 해당 제품들에 대한 추가적인 분석 및 마케팅 전략 수립이 가능합니다. 또한, 제품 포트폴리오의 다양성을 높이는 등의 전략적인 의사결정에도 활용할 수 있습니다.