PostgreSQL과 Athena에서 sequence와 generate_series 사용법

0. 배경

데이터 분석을 하다 보면 특정 시간에 발생한 이벤트를 집계해야 하는 경우가 많습니다. 예를 들어, 특정 서비스의 라이브 스트리밍 접속 유저 수를 시간대별로 분석한다고 가정해 봅시다. 일반적으로 로그 데이터를 이용하여 각 시간에 접속한 유저 수를 집계하게 되는데, 여기서 흔히 발생하는 문제가 하나 있습니다.

문제점: 데이터가 없는 시간대는 누락된다

로그 기반의 데이터는 이벤트가 발생한 경우에만 저장되므로, 특정 시간대에 접속자가 없었다면 해당 시간은 쿼리 결과에서 아예 빠지게 됩니다. 이렇게 되면 시간 흐름에 따른 유저 수 변화를 직관적으로 파악하기 어려워지고, 시각화할 때도 그래프가 끊겨 보이는 등의 문제가 발생할 수 있습니다.

해결 방법: 전체 시간대를 생성하여 보강하기

이 문제를 해결하기 위해 시간대 데이터를 미리 생성한 후 로그 데이터와 결합하는 방식을 사용할 수 있습니다. SQL에서는 SEQUENCE 또는 GENERATE_SERIES 함수를 이용하여 원하는 시간 범위의 모든 시간 단위를 생성할 수 있습니다. 그런 다음, 생성된 시간 데이터와 실제 로그 데이터를 LEFT JOIN하여 데이터가 없는 시간대도 함께 포함하도록 만들 수 있습니다.

이러한 방식은 단순한 집계를 넘어, 데이터 시각화 및 분석의 완성도를 높이는 데에도 큰 도움이 됩니다. 예를 들어, 시간대별 트래픽 변화, 매출 분석, 사용자 행동 패턴 등을 보다 정확하게 파악할 수 있습니다.

PostgreSQL에서는 generate_series 함수를, AWS Athena에서는 sequence 함수를 이용하여 이를 쉽게 해결할 수 있습니다. 이번 글에서는 두 함수의 사용법과 차이점을 정리해보겠습니다.

1. PostgreSQL의 generate_series

generate_series 함수는 일정한 간격으로 증가하는 숫자 또는 날짜 데이터를 생성하는 데 유용합니다. 기본적인 구문은 다음과 같습니다.

1.1 숫자 시리즈 생성

SELECT generate_series(1, 10);

결과:

 generate_series
----------------
              1
              2
              3
              ...
             10

간격을 지정할 수도 있습니다.

SELECT generate_series(1, 10, 2);

결과:

 generate_series
----------------
              1
              3
              5
              7
              9

1.2 날짜 시리즈 생성

날짜를 생성할 수도 있습니다.

SELECT generate_series('2024-01-01'::date, '2024-01-07'::date, '1 day');

결과:

 generate_series
----------------
 2024-01-01
 2024-01-02
 2024-01-03
 ...
 2024-01-07

2. AWS Athena의 sequence

Athena에서는 PostgreSQL의 generate_series 대신 sequence 함수를 사용합니다. 기본적인 사용법은 다음과 같습니다.

2.1 숫자 시리즈 생성

SELECT sequence(1, 10, 2);

결과:

 [1, 3, 5, 7, 9]

이렇게 생성된 배열을 unnest 함수를 이용하여 개별 행으로 변환할 수 있습니다.

SELECT * FROM unnest(sequence(1, 10, 2)) AS t(num);

결과:

 num
----
  1
  3
  5
  7
  9

2.2 날짜 시리즈 생성

Athena에서는 sequence를 이용하여 날짜 시리즈도 만들 수 있습니다.

SELECT sequence(date('2024-01-01'), date('2024-01-07'), interval '1' day);

결과:

 [2024-01-01, 2024-01-02, 2024-01-03, ..., 2024-01-07]

마찬가지로 unnest을 이용해 개별 행으로 변환할 수 있습니다.

SELECT * FROM unnest(
  sequence(date('2024-01-01'), date('2024-01-07'), interval '1' day)
) AS t(date);

3. generate_series vs sequence 차이점

기능PostgreSQL (generate_series)AWS Athena (sequence)

숫자 생성	generate_series(start, stop, step)	sequence(start, stop, step)
날짜 생성	generate_series(start::date, stop::date, interval 'X')	sequence(date(start), date(stop), interval 'X')
결과 형식	개별 행 (row-based)	배열 (array-based)
변환 방법	그대로 사용 가능	unnest(sequence(...)) 사용

4. 실전 예제: 시간별 접속자 수 분석

Athena에서 특정 기간 동안의 시간별 접속자 수를 조회하는 예제입니다.

WITH time_series AS (
  SELECT * FROM unnest(
    sequence(date('{{Start_Date}}'), date('{{End_Date}}') + interval '1' day, interval '1' hour)
  ) AS t(hour)
)
SELECT
  date_format(t.hour, '%Y-%m-%d') AS "날짜",
  date_format(t.hour, '%H') || '시' AS "시간",
  COALESCE(COUNT(DISTINCT COALESCE(user_id, pcid)), 0) AS "접속 유저수"
FROM time_series t
LEFT JOIN data_mart.logs_kst lk ON date_trunc('hour', lk.event_timestamp_kst) = t.hour
AND event_name = 'live'
AND event_type = 'pageview'
AND event_location = 'live'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

이렇게 하면 특정 기간 동안의 모든 시간대에 대해 접속자 수를 조회할 수 있습니다.

5. 마무리

• PostgreSQL에서는 generate_series를 사용하고, Athena에서는 sequence를 사용합니다.
• PostgreSQL의 generate_series는 개별 행을 반환하지만, Athena의 sequence는 배열을 반환하여 unnest와 함께 사용해야 합니다.
• 날짜와 숫자 모두 생성할 수 있으며, ETL이나 데이터 분석 시 유용하게 활용할 수 있습니다.

이제 generate_series와 sequence를 활용하여 원하는 형태의 데이터를 효과적으로 생성해보세요!