By Kesus Kim

이 글의 원본은 여기에서 보실 수 있습니다.

혹시 더 나은 번역을 제안해주신다면 감사하게 수용하겠습니다.

Postgres 를 쓰다보면 샘플 데이터를 만들어내거나 리포트를 만들기 위해 필요한 조인을 하기 위한 일련의 레코드가 필요한 경우가 있다. 간단하면서 잘 사용할 수 있는, 집합을 반환하는 generate_series 라는 Postgres 의 함수를 사용하자. generate_series 라는 이름이 의미하듯, 이 함수는 어느 한 지점으로부터 다른 지점까지의 증가하는 일련의 집합, 즉 수열을 생성하여 반환한다. generate_series 는 두 개의 데이터형에 대해 동작한다.

  • 정수형
  • 타임스탬프

가장 기본적인 예제부터 시작해보자.

SELECT * 
FROM generate_series(1, 5);
 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

위에서 볼 수 있듯, 꽤나 직관적으로 동작한다. 그런데 이런걸 가지고 어떻게 유용하게 써먹을수 있을까?

가짜 데이터 생성하기

generate_series 함수를 CTE (Common Table Expression) 안에 정의함으로써 손쉽게 수열을 생성한 후 각각의 값들에 대해 어떠한 연산을 수행할 수 있다. 가짜 데이터를 생성하고 싶다면 random() 함수를 이용할 수 있다.

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 5)
)

SELECT generate_series * random()
FROM numbers;

     ?column?
-------------------
  0.87764338683337
 0.345125129446387
  2.10317854676396
 0.937898803502321
  1.72822773223743
(5 rows)

JOIN 을 이용한, 예쁘게 정렬된 주간 리포트

시간별로 수집한 데이터를 통계화 하는 것은 꽤나 자주 있는 일이다. 한 좋은 예로 ‘1주일 동안 새로 들어온 사용자의 수’ 가 궁금할 수 있다. 이를 구하는 가장 간단한 방법은 Postgres 에서 지원하는 date_trunc 함수를 이용하는 것이다.

SELECT date_trunc('week', created_at)
       count(*)
FROM users
GROUP BY 1
ORDER BY 1;

위 쿼리에는 문제점이 있다. 시간에 따른 데이터의 집합을 이용하여 생성 날짜가 있는 경우를 계수하는데, 만약 아무도 회원 가입을 안 한 주간이 있다면 그래프 내에 0 이라는 원치 않는 값이 나온다. 이러한 경우를 완화하기 위하여 수열을 생성한 후에 주 단위로 OUTER JOIN 을 수행하도록 하자.

WITH range_values AS (
  SELECT date_trunc('week', min(created_at)) as minval,
         date_trunc('week', max(created_at)) as maxval
  FROM users),

week_range AS (
  SELECT generate_series(minval, maxval, '1 week'::interval) as week
  FROM range_values
),

weekly_counts AS (
  SELECT date_trunc('week', created_at) as week,
         count(*) as ct
  FROM users
  GROUP BY 1
)

SELECT week_range.week,
       weekly_counts.ct
FROM week_range
LEFT OUTER JOIN weekly_counts on week_range.week = weekly_counts.week;

generate_series 를 사용하는 또 다른 방법이 있나요?

Postgres 에는 숨은 보석들이 아주 많다. generate_series 는 간편하게 사용할 수 있는 Postgres 의 내장 기능 중 하나일 뿐이다. 만약 generate_series에 대한 창의적인 사용법을 알고 계시다면 @citusdata 에도 꼭 알려주시기 바란다.