[PostgreSQL] PostgreSQL에서 주 단위 데이터 분석, DATE_TRUNC로 해결하는 방법 - 컴도리돌이

---

데이터를 다룰 때 특정 기간(예: 한 주, 한 달) 동안의 변화나 패턴을 분석해야 하는 경우가 많습니다. PostgreSQL의 DATE_TRUNC 함수를 사용하면 날짜를 원하는 단위로 손쉽게 자르고 그룹화할 수 있습니다.  예를 들어, 한 주 동안 같은 사용자가 여러 번 가입했거나 특정 이벤트에 여러 차례 참여한 경우를 찾아야 한다고 가정해 봅시다. 일반적인 GROUP BY로는 특정 날짜별 집계는 가능하지만, 주 단위로 그룹화하려면 어떻게 해야 할까요?

 

DATE_TRUNC 함수는 날짜 또는 시간을 지정한 단위로 잘라서 반환합니다. 이 함수를 사용하면 모든 날짜가 해당 주(week)의 시작일(월요일 00:00:00)로 변환됩니다. 이렇게 하면 같은 주에 속하는 데이터가 동일한 값으로 그룹화됩니다.

SELECT reg_dt, DATE_TRUNC('week', reg_dt) AS week_start FROM users;

 

reg_dt를 기준으로 같은 주(week)에 속하는 모든 데이터가 동일한 week_start 값을 갖게 됩니다. 즉, 특정 주에 발생한 모든 데이터를 한 그룹으로 묶을 수 있죠!

 

또한 같은 주에 2회 이상 등록한 사용자를 찾기 위해 DATE_TRUNC와 GROUP BY를 조합하여 쿼리를 작성할 수 있습니다. 단순한 COUNT(*)가 아니라, DATE_TRUNC와 GROUP BY를 조합하면 깔끔하게 해결할 수 있습니다.

SELECT user_id, DATE_TRUNC('week', reg_dt) AS week_start, COUNT(*) AS reg_count
FROM users
GROUP BY user_id, week_start
HAVING COUNT(*) > 1;

• DATE_TRUNC('week', reg_dt)를 사용하여 주 단위 그룹화
• GROUP BY user_id, week_start로 사용자별 주간 데이터 집계
• HAVING COUNT(*) > 1을 통해 같은 주에 두 번 이상 등록된 사용자 필터링

"한 주 동안 몇 명의 사용자가 가입했을까?"는 어떻게 해결할 수 있을까요? 한 주 동안 몇 명의 사용자가 가입했는지 확인하려면 여기서 COUNT(DISTINCT user_id)를 활용하면 유니크한 가입자 수를 쉽게 구할 수 있습니다. 

SELECT DATE_TRUNC('week', reg_dt) AS week_start, COUNT(DISTINCT user_id) AS unique_users
FROM users
GROUP BY week_start
ORDER BY week_start;

• DATE_TRUNC('week', reg_dt)를 사용하여 주 단위로 그룹화
• COUNT(DISTINCT user_id)로 중복을 제거한 사용자 수 계산
• ORDER BY week_start를 통해 주 단위로 정렬하여 가독성 향상

---