매출 데이터 SQL 연습 1 - 유현영

OVER() 함수

•

이동 평균 

◦

구간의 평균이 아니라 이동에 따라서 기준을 다르게 스콥핑

◦

보통 매출 추이를 볼때 활용함

•

사용 

◦

OVER order by

◦

OVER partition by

◦

OVER between … and …

◦

ROW BETWEEN 1 PRECEDIND AND 1 FOLLEWING 

•

1. 시계열 일/주/월/분기별 매출액 및 주문 건수

◦

일별 매출

-- 1-1. 시계열 일별 매출액 및 주문 건수
SELECT date_trunc('day', order_date)::date as day
	, sum(amount) as sum_amount, count(distinct a.order_id) as daily_ord_cnt
from nw.orders a
	join nw.order_items b on a.order_id = b.order_id
group by date_trunc('day', order_date)::date 
order by 1;
JavaScript
복사

SQL 쿼리 작성

위 시각화 차트 구성

◦

주별 매출

-- 1-2. 시계열 주별 매출액 및 주문 건수
SELECT
  TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-MM') || '-' || ((EXTRACT(DAY FROM (DATE_TRUNC('week', order_date) - DATE_TRUNC('month', order_date))) / 7 + 1)::INT || '주차') AS month_week_label,
  SUM(amount) AS sum_amount,
  COUNT(DISTINCT a.order_id) AS order_count
FROM nw.orders a
JOIN nw.order_items b ON a.order_id = b.order_id
GROUP BY month_week_label
ORDER BY month_week_label;
SQL
복사

◦

월별 매출

◦

1-3. 시계열 월별 매출액 및 주문 건수

-- 1-3. 시계열 월별 매출액 및 주문 건수
SELECT
TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-MM') AS month,
SUM(amount) AS sum_amount,
COUNT(DISTINCT a.order_id) AS order_count
FROM nw.orders a
JOIN nw.order_items b ON a.order_id = b.order_id
GROUP BY month
ORDER BY month;
SQL
복사

SQL 쿼리 작성

위 시각화 차트 구성

분기별

◦

매출, 개수, 상품별

-- 1-4-1. 시계열 분기별+상품별 매출액 및 주문 건수
-- 상품별의 시각화가 의미가 없을텐데

SELECT
  TO_CHAR(DATE_TRUNC('quarter', order_date), 'YYYY-"Q"Q') AS quarter,
  c.product_name,
  SUM(amount) AS total_sales,
  COUNT(DISTINCT a.order_id) AS number_of_orders
FROM nw.orders a
    JOIN nw.order_items b ON a.order_id = b.order_id
    JOIN nw.products c ON b.product_id = c.product_id 
GROUP BY quarter, c.product_name
ORDER BY quarter, c.product_name;
SQL
복사

SQL 쿼리 작성

시각화 차트 구성 (자유롭게)

2. 월별 상품 카테고리별 매출액 및 주문 건수, 월 전체 매출액 대비 비율

step 1: 상품 카테고리 별 월별 매출액 추출

-- 2. 월별 상품카테고리별 매출액 및 주문 건수

SELECT
  TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-MM') AS month,
   d.category_name,
  SUM(amount) AS sum_amount,
  COUNT(DISTINCT a.order_id) AS order_count
FROM nw.orders a
    JOIN nw.order_items b ON a.order_id = b.order_id
    JOIN nw.products c ON b.product_id = c.product_id 
    JOIN nw.categories d ON c.category_id = d.category_id 
GROUP BY month, d.category_name
ORDER BY month, d.category_name;
SQL
복사

◦

메타베이스 링크

step 2: step 1의 집합에서 전체 매출액을 analytic으로 구한 뒤에 매출액 비율 계산.

-- 2-2. 월별 상품카테고리별 월 전체 매출액 대비 비율

SELECT
  month,
  category_name,
  sum_amount,
  order_count,
  -- 월별 총 매출액 대비 카테고리별 매출액의 비율을 계산합니다.
  (sum_amount / SUM(sum_amount) OVER(PARTITION BY month)) * 100 AS sales_ratio
FROM (
  SELECT
    TO_CHAR(DATE_TRUNC('month', a.order_date), 'YYYY-MM') AS month,
    d.category_name,
    SUM(b.amount) AS sum_amount,
    COUNT(DISTINCT a.order_id) AS order_count
  FROM nw.orders a
      JOIN nw.order_items b ON a.order_id = b.order_id
      JOIN nw.products c ON b.product_id = c.product_id
      JOIN nw.categories d ON c.category_id = d.category_id
  GROUP BY month, d.category_name
) AS monthly_sales
ORDER BY month, category_name;
SQL
복사

◦

메타베이스 링크

SQL 쿼리 작성

위 시각화 차트 구성

3. 상품별 전체 매출액 및 해당 상품 카테고리 전체 매출액 대비 비율, 해당 상품 카테고리에서 매출 순위

step 1: 상품별 전체 매출액을 구함

-- 3-1. 상품별 전체 매출액 

SELECT
  c.product_name,
  SUM(amount) AS sum_amount
FROM nw.orders a
    JOIN nw.order_items b ON a.order_id = b.order_id
    JOIN nw.products c ON b.product_id = c.product_id 
    JOIN nw.categories d ON c.category_id = d.category_id 
GROUP BY c.product_name
ORDER BY sum_amount;
SQL
복사

step 2: step 1의 집합에서 상품 카테고리별 전체 매출액을 구하고, 비율과 매출 순위를 계산.

아 카테고리가 상품보다 더 상위의 개념이구나

-- 3-2. 해당 상품 카테고리 전체 매출액 대비 비율
-- 카테고리 안에 여러 상품이 있는 형태

SELECT
  category_name,
  category_sales,
  sales_ratio,
  RANK() OVER(ORDER BY sales_ratio DESC) AS rank
FROM (
  SELECT
    d.category_name,
    SUM(b.amount) AS category_sales,
    SUM(b.amount) * 100.0 / SUM(SUM(b.amount)) OVER () AS sales_ratio
  FROM nw.orders a
  JOIN nw.order_items b ON a.order_id = b.order_id
  JOIN nw.products c ON b.product_id = c.product_id
  JOIN nw.categories d ON c.category_id = d.category_id
  GROUP BY d.category_name
) AS sales_data
ORDER BY sales_ratio DESC;
SQL
복사

SQL 쿼리 작성

시각화 차트 구성 (자유롭게)

4. 동년도 월별 누적 매출 및 동일 분기 월별 누적 매출

◦

step 1: 월별 매출액을 구한다 

◦

step 2: 월별 매출액 집합에 동일 년도의 월별 누적 매출과 동일 분기의 월별 누적 매출을 구함.

*왜 문제가 생기는지 검토 필요

SQL 쿼리 작성

시각화 차트 구성 (월별 라인차트)

5. 5일 이동 평균 매출액 구하기. (매출액의 경우 주로 1주일 이동 평균 매출을 구하나 데이터가 토,일 매출이 없음.)

-- 5. 5일 이동 평균 매출액 구하기.

SELECT
  order_date,
  sales,
  AVG(sales) OVER (ORDER BY order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM (
  SELECT
    a.order_date,
    SUM(b.amount) AS sales
  FROM nw.orders a
    JOIN nw.order_items b ON a.order_id = b.order_id
  GROUP BY order_date
) AS daily_sales
ORDER BY order_date;
SQL
복사

SQL 쿼리 작성

시각화 차트 구성 (날짜별 라인차트)