유용한 함수 살펴보기

SQL로 비즈니스 데이터 분석하기

Michel Semaan

Data Scientist

날짜 다루기

DATE_TRUNC('quarter', '2018-08-13') → '2018-07-01 00:00:00+00:00'
'2018-07-01 00:00:00+00:00' :: DATE → '2018-07-01'

보고서의 날짜

읽기 쉬운 날짜는 보고서에서 중요합니다
기본 형식 '2018-08-13' 은 가독성이 낮습니다
'2018-08-13' 을 'Friday 13, August 2018' 로 바려면?

해결

TO_CHAR('2018-08-13', 'FMDay DD, FMMonth YYYY') → 'Friday 13, August 2018'

TO_CHAR()

TO_CHAR(DATE, TEXT) → TEXT (형식화된 날짜 문자열)
예시: Dy → 요일 약어(Mon, Tues 등)
- TO_CHAR('2018-06-01', 'Dy') → 'Fri'
- TO_CHAR('2018-06-02', 'Dy') → 'Sat'
형식 문자열의 패턴은 날짜 요소로 치환되고, 다른 문자는 그대로 유지됩니다
예시: DD → 일(01 - 31)
- TO_CHAR('2018-06-01', 'Dy - DD') → 'Fri - 01'
- TO_CHAR('2018-06-02', 'Dy - DD') → 'Sat - 02'

Pattern	설명
`FMDay`	요일 전체 이름(`Monday`, `Tuesday` 등)
`MM`	월(`01` - `12`)
`Mon`	월 약어(`Jan`, `Feb` 등)
`FMMonth`	월 전체 이름(`January`, `February` 등)
`YY`	연도 끝 두 자리(`18`, `19` 등)
`YYYY`	4자리 연도(`2018`, `2019` 등)

문서: https://www.postgresql.org/docs/9.6/functions-formatting.html

쿼리

SELECT DISTINCT
  order_date,
  TO_CHAR(order_date,
          'FMDay DD, FMMonth YYYY') AS format_1,
  TO_CHAR(order_date,
          'Dy DD Mon/YYYY') AS format_2
FROM orders
ORDER BY order_date ASC
LIMIT 3;

결과

order_date  format_1                format_2       
----------  ----------------------  ---------------
2018-06-01  Friday 01, June 2018    Fri 01/Jun 2018
2018-06-02  Saturday 02, June 2018  Sat 02/Jun 2018
2018-06-02  Sunday 03, June 2018    Sun 03/Jun 2018

윈도 함수 다시 보기

SUM(...) OVER (...): 열의 누적 합계 계산
- 예시: SUM(registrations) OVER (ORDER BY registration_month) 는 등록의 누적 합계를 계산합니다
LAG(...) OVER (...): 이전 행의 값을 가져옴
- 예시: LAG(mau) OVER (ORDER BY active_month) 는 이전 달의 활성 사용자(MAU)를 반환합니다
RANK() OVER (...): 정렬 순서에 따라 순위를 부여
- 예시: RANK() OVER (ORDER BY revenue DESC) 는 매출 기준으로 사용자, 매장, 또는 월에 순위를 매깁니다

쿼리

SELECT
  user_id,
  SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 3;

결과

user_id  revenue
-------  -------
18       626
76       553.25
73       537

쿼리

WITH user_revenues AS (
  SELECT
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY user_id)

SELECT
  user_id,
  RANK() OVER (ORDER BY revenue DESC)
    AS revenue_rank
FROM user_revenues
ORDER BY revenue_rank DESC
LIMIT 3;

결과

user_id  revenue_rank
-------  ------------
18       1
76       2
73       3

유용한 함수 살펴보기

SQL로 비즈니스 데이터 분석하기