유사 테이블 만들기

임의의 레코드를 가진 유사 테이블 만들기

WITH mst_devices AS(
          SELECT 1 AS device_id, 'PC' AS device_name
UNION ALL SELECT 2 AS device_id, 'SP' AS device_name
UNION ALL SELECT 3 AS device_id, '애플리케이션' AS device_name
)

SELECT *
  FROM mst_devices

WITH mst_devices AS(
          SELECT 1 AS device_id, 'PC' AS device_name
UNION ALL SELECT 2 AS device_id, 'SP' AS device_name
UNION ALL SELECT 3 AS device_id, '애플리케이션' AS device_name
)

SELECT u.user_id
     , d.device_name
  FROM mst_users AS u
  LEFT JOIN mst_devices AS d
  ON u.register_device = d.device_id

위 두 쿼리의 결과는 똑같다. UNION ALL은 처리가 비교적 무거우므로 레코드 수가 많아지면 성능 문제가 발생할 수 있다.

매출을 파악하기 위한 데이터 추출

SELECT dt
     , COUNT(*) AS purchase_count
     , SUM(purchase_amount) AS total_amount
     , AVG(purchase_amount) AS avg_amount
  FROM purchase_log
  GROUP BY dt
  ORDER BY dt

이동평균을 사용한 날짜별 추이 보기

SELECT dt
     , SUM(purchase_amount) AS total_amount
     , AVG(SUM(purchase_amount))
       OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 
       AS seven_day_avg
     , CASE WHEN 7 = COUNT(*)
                     OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            THEN AVG(SUM(purchase_amount))
                 OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            END AS seven_day_avg_strict
  FROM purchase_log
  GROUP BY dt
  ORDER BY dt

당월 매출 누계 구하기

SELECT dt
     , SUBSTRING(dt, 1, 7) AS year_month
     , SUM(purchase_amount) AS total_amount
     , SUM(SUM(purchase_amount))
           OVER(PARTITION BY SUBSTRING(dt, 1, 7)
                ORDER BY dt ROWS UNBOUNDED PRECEDING)
            AS agg_amount
  FROM purchase_log
  GROUP BY dt
  ORDER BY dt

위 쿼리의 ROWS UNBOUNDED PRECEDING은 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 바꾸어도 된다.
위 쿼리는 가독성 측면에서 수정할 수 있는 부분이 있다.

WITH daily_purchase AS (
  SELECT dt
       , SUBSTRING(dt, 1, 4) AS year
       , SUBSTRING(dt, 6, 2) AS month
       , SUBSTRING(dt, 9, 2) AS date
       , SUM(purchase_amount) AS purchase_amount
       , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt)

SELECT *
  FROM daily_purchase
  ORDER BY dt

위 쿼리를 이용해서 당월 누계 매출을 구할 수 있다.

WITH daily_purchase AS (
  SELECT dt
       , SUBSTRING(dt, 1, 4) AS year
       , SUBSTRING(dt, 6, 2) AS month
       , SUBSTRING(dt, 9, 2) AS date
       , SUM(purchase_amount) AS purchase_amount
       , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt)

SELECT dt
     , year || '-' || month AS year_month
     , purchase_amount
     , SUM(purchase_amount)
           OVER(PARTITION BY year_month
                ORDER BY dt ROWS UNBOUNDED PRECEDING)
                AS agg_amount
  FROM daily_purchase
  ORDER BY dt