BigQuery 날짜형식 변환

BigQuery에서는 날짜와 시간을 다룰 때 다양한 함수들을 제공합니다. 날짜 형식을 변환하면서 해당 단위에 맞는 활성 사용자 수를 계산하는 SQL 쿼리 작성 방법을 단계별로 설명하겠습니다. 이 포스팅에서는 event_date 필드가 날짜 형식(DATE)이라고 가정하겠습니다.

Table of Contents

1. 날짜 형식: 년월일 (YYYY-MM-DD)

활성 사용자 수를 날짜 단위로 집계하려면 날짜 필드를 그대로 사용하면 됩니다.

SELECT 
  event_date AS day, 
  COUNT(DISTINCT user_id) AS active_users
FROM 
  your_table
GROUP BY 
  day
ORDER BY 
  day;

설명

event_date를 그대로 사용하여 날짜 단위로 그룹화했습니다.
COUNT(DISTINCT user_id)로 활성 사용자 수를 집계합니다.

2. 날짜 형식: 년월 (YYYY-MM)

날짜를 “년월” 단위로 변환하려면 FORMAT_DATE 또는 EXTRACT를 사용합니다.

SELECT 
  FORMAT_DATE('%Y-%m', event_date) AS year_month, 
  COUNT(DISTINCT user_id) AS active_users
FROM 
  your_table
GROUP BY 
  year_month
ORDER BY 
  year_month;

설명

FORMAT_DATE('%Y-%m', event_date)는 날짜를 “2025-01” 형식으로 변환합니다.
GROUP BY를 통해 “년월” 단위로 집계합니다.

3. 날짜 형식: 년 (YYYY)

“년” 단위로 집계하려면 EXTRACT(YEAR FROM ...)를 사용합니다.

SELECT 
  EXTRACT(YEAR FROM event_date) AS year, 
  COUNT(DISTINCT user_id) AS active_users
FROM 
  your_table
GROUP BY 
  year
ORDER BY 
  year;

설명

EXTRACT(YEAR FROM event_date)를 사용하여 “년” 값을 추출합니다.
GROUP BY year를 통해 연도별 활성 사용자 수를 집계합니다.

4. 주 단위 (주번호)

날짜를 “주번호” 단위로 변환하려면 EXTRACT로 주 번호를 추출합니다.

SELECT 
  EXTRACT(YEAR FROM event_date) AS year,
  EXTRACT(WEEK FROM event_date) AS week_number,
  COUNT(DISTINCT user_id) AS active_users
FROM 
  your_table
GROUP BY 
  year, week_number
ORDER BY 
  year, week_number;

설명

EXTRACT(WEEK FROM event_date)는 해당 날짜의 주 번호(1~53)를 반환합니다.
연도와 주 번호를 함께 GROUP BY하여 한 해 동안의 주별 데이터를 얻습니다.

5. 요일별 활성 사용자 (월~일)

요일을 추출하려면 FORMAT_DATE 또는 EXTRACT(DAYOFWEEK)를 사용합니다.

SELECT 
  FORMAT_DATE('%A', event_date) AS day_of_week, 
  COUNT(DISTINCT user_id) AS active_users
FROM 
  your_table
GROUP BY 
  day_of_week
ORDER BY 
  FIELD(day_of_week, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');

설명

FORMAT_DATE('%A', event_date)는 요일 이름(“Monday”, “Tuesday” 등)을 반환합니다.
ORDER BY FIELD(...)를 사용해 요일 순서로 정렬합니다.

6. 기타 변환 및 집계 예제

월별 누적 활성 사용자

SELECT 
  FORMAT_DATE('%Y-%m', event_date) AS year_month, 
  COUNT(DISTINCT user_id) AS active_users
FROM 
  your_table
WHERE 
  event_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY 
  year_month
ORDER BY 
  year_month;

블로그 마무리

이처럼 BigQuery에서는 날짜 변환 함수(FORMAT_DATE, EXTRACT)를 활용하여 다양한 단위로 데이터를 변환할 수 있습니다. 위의 쿼리 예제를 활용하면 날짜 단위에 따른 활성 사용자 수를 효과적으로 분석할 수 있습니다.

날짜 단위를 자유롭게 조합하여 여러분의 데이터 분석에 적용해 보세요! 😊