답변

시간 시계열 데이터 처리 문제는 SQL을 통한 분석과 전통적인 프로그래밍 사이에서 발생했습니다. SQL-92에는 특별한 윈도우 함수가 없기 때문에 동적 메트릭(이동 합계, 평균 등) 및 시간 조건을 계산하기 위해 서브쿼리를 사용하는 방법을 고민해야 합니다.

문제 — 이동 평균으로 집계하거나 이전/다음 시간 값을 찾거나 임의의 일정 간격(예: 주간/월간 지표 계산)에 대한 효율적인 그룹화를 위한 표준 도구가 부족합니다.

해결책:

표준 기능만을 사용하여 각 행에 대한 상관 서브쿼리 또는 계산 기준(예: 월, 주)을 기준으로 그룹화를 사용합니다:

코드 예:

-- 윈도우 함수 없이 주별 그룹화 예
SELECT 
   YEAR(event_date) AS year,
   WEEK(event_date) AS week,
   SUM(value) AS total
FROM timeseries
GROUP BY YEAR(event_date), WEEK(event_date)
ORDER BY year, week;

-- 이전 기록을 찾기 위한 상관 서브쿼리
SELECT t1.id, t1.event_date, t1.value,
       (
         SELECT t2.value 
           FROM timeseries t2 
          WHERE t2.event_date < t1.event_date
          ORDER BY t2.event_date DESC
          LIMIT 1
       ) as prev_value
FROM timeseries t1;

주요 특징:

시간 함수(YEAR, MONTH, WEEK 등)를 명시적으로 기준으로 그룹화 및 집계를 해야 합니다.
이동 계산을 얻기 위해서는 상관 서브쿼리나 임시 테이블을 사용해야 합니다.
데이터 양이 많을 경우 성능이 저하됩니다. 각 행에 대한 서브쿼리는 실행 속도를 빠르게 저하시킵니다.

트릭 질문.

WEEK(event_date)가 모든 날짜에 대해 항상 명확하게 달력 주를 정의합니까?

아니요 — 서로 다른 DBMS(심지어 동일한 DBMS의 매개변수조차도)는 해의 첫 주를 다르게 정의합니다(예: ISO 8601 대 미국 시스템). 이는 집계 시 다른 결과를 초래할 수 있습니다. 함수의 작동 모드를 명시적으로 지정하거나 YEARWEEK를 사용해야 합니다.

SELECT YEARWEEK(event_date, 1)  -- 1: ISO 주는 월요일부터 시작
FROM timeseries;

상관 서브쿼리가 이전 값을 찾을 때 자동으로 중복을 제거합니까?

아니오, 상관 서브쿼리는 기본적으로 중복을 필터링하지 않습니다. 테이블에 같은 날짜에 여러 이벤트가 있는 경우, 서브쿼리는 정렬 기준에 따라 첫 번째 값을 반환하지만 다른 값은 무시합니다.

GROUP BY를 통해 시간을 고려하지 않고 날짜별로 집계할 수 있습니까?

네, 하지만 시간의 일부를 명시적으로 무시해야 하며, 예를 들어 DATE(event_date) 또는 TRUNC(event_date)를 통해 수행할 수 있습니다:

SELECT DATE(event_datetime), COUNT(*)
FROM events
GROUP BY DATE(event_datetime)

일반적인 오류 및 안티패턴

보고 기간에 대한 로컬 및 달력 표준을 고려하지 않고 YEAR/MONTH/WEEK 사용
대량 데이터에서 실행 시간이 급증하는 지나치게 복잡한 상관 서브쿼리 생성
시간 함수 작업 시 시간대 무시

실제 사례

부정적인 사례

팀은 매주 WEEK(date) 기능을 사용하여 주간 분석을 수행하였으며, ISO-8601 매개변정 설정을 하지 않았습니다. 따라서 1월의 첫 주 보고서가 "사라지는" 현상이 발생했습니다. 일부 이벤트는 미국 기준으로 작년 12월에 해당했습니다. 분석이 일치하지 않았습니다.

장점:

기간 분해를 신속하게 구현

단점:

데이터가 부정확하고, 보고서가 비즈니스 논리와 일치하지 않음

긍정적인 사례

전문가는 YEARWEEK(date, 1) 및 캘린더 테이블을 도입하여 국가 및 비즈니스 부서 간의 보고서 일관성을 크게 높였습니다.