데.분.레 - 복습 7

조건 플래그를 0과 1로 표현하기

SELECT m.user_id
     , m.card_number
     , COUNT(p.user_id) AS purchase_count
     , CASE WHEN m.card_number IS NOT NULL THEN 1
            ELSE 0 END AS has_card
     , SIGN(COUNT(p.user_id)) AS has_purchased
  FROM mst_users_with_card_number AS m
  LEFT JOIN purchase_log AS p
       ON m.user_id = p.user_id
  GROUP BY m.user_id, m.card_number

SIGN : 괄호 안의 값의 음수/양수/0 여부를 -1/+1/0 으로 보여준다.

계산한 테이블에 이름 붙여 재사용하기

CTE : Common Table Expression 의 준말로, 공통 테이블 식을 뜻한다. 일시적인 테이블에 이름을 붙여 재사용할 수 있는 기능이다. 이를 활용하면 복잡한 쿼리의 가독성이 크게 높아진다.

WITH product_sales_ranking AS (
SELECT category_name
     , product_id
     , sales
     , ROW_NUMBER() OVER (PARTITION BY category_name
                          ORDER BY sales DESC) AS rank
  FROM product_sales
  )
  SELECT *
    FROM product_sales_ranking

WITH : 이름을 가진 서브쿼리를 정의한 후 사용하는 구문이다. 쿼리의 전체적인 가독성을 높이고, 재사용할 수 있는 장점이 있다.
WITH 별명 AS (SUB QUERY) 와 같은 형식으로 사용한다.

WITH product_sales_ranking AS (
SELECT category_name
     , product_id
     , sales
     , ROW_NUMBER() OVER (PARTITION BY category_name
                          ORDER BY sales DESC) AS rank
  FROM product_sales
  )
, mst_rank AS (
  SELECT DISTINCT rank
    FROM product_sales_ranking
  )

SELECT *
  FROM mst_rank

WITH절에 기입된 서브쿼리의 경우, 먼저 사용된 쿼리는 뒤의 서브쿼리에서 사용될 수 있다. WITH 구문을 사용해서 여러 테이블을 정의할 떄는 쉼표를 사용해 테이블을 나열한다.

WITH product_sales_ranking AS (
SELECT category_name
     , product_id
     , sales
     , ROW_NUMBER() OVER (PARTITION BY category_name
                          ORDER BY sales DESC) AS rank
  FROM product_sales
  )
, mst_rank AS (
  SELECT DISTINCT rank
    FROM product_sales_ranking
  )
SELECT m.rank
     , r1.product_id AS dvd
     , r1.sales AS dvd_sales
     , r2.product_id AS cd
     , r2.sales AS cd_sales
     , r3.product_id AS book
     , r3.sales AS book_sales
  FROM mst_rank AS m
  LEFT JOIN
    product_sales_ranking AS r1
    ON m.rank = r1.rank
    AND r1.category_name = 'dvd'
  LEFT JOIN
    product_sales_ranking AS r2
    ON m.rank = r2.rank
    AND r2.category_name = 'cd'
  LEFT JOIN
    product_sale_ranking AS r3
    ON m.rank = r3.rank
    AND r3.category_name = 'book'
  ORDER BY m.rank

유사 테이블 만들기

이건 다음 포스트에서!