PostgreSQL에서 동적 시간 간격 내에서 이벤트를 효율적으로 계산하는 방법은 무엇입니까?

시간 간격으로 이벤트를 효율적으로 계산하는 방법

소개

애플리케이션은 종종 특정 내의 이벤트 계산과 같이 이벤트 기반 데이터에서 통찰력을 추출해야 합니다. 시간 간격. 이 작업은 간격 크기가 동적으로 변할 때 문제를 제기합니다.

문제 설명

타임스탬프가 지정된 이벤트가 있는 데이터 테이블에는 이벤트를 주간, 일일 등 임의의 시간 간격으로 그룹화하는 동적 SQL 쿼리가 필요합니다. 매시간 또는 심지어 15분 간격.

솔루션

Postgres 14 또는 최신

Postgres 버전 14 및以降의 경우 date_bin() 함수가 간단한 솔루션을 제공합니다. 데이터가 포함된 시간대당 하나의 행을 검색하려면 다음 쿼리를 사용하세요.

SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time
     , count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1;

특정 날짜 이후의 모든 시간 슬롯을 가져오려면 다음과 같이 쿼리를 조정하세요.

SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1;

Postgres 13 이상

이전 Postgres 버전의 경우 다음 쿼리가 가능합니다. 사용됨:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

이 쿼리는 각 시간 슬롯에 대해 하나의 행을 제공하며 이벤트가 없는 슬롯의 개수는 0입니다.

최적화

간격 데이터 사전 저장 이벤트 테이블은 성능을 향상시킬 수 있습니다. 그러나 이 접근 방식은 테이블 크기를 두 배로 늘립니다. 절충안을 수용할 수 있으면 상당한 속도 향상을 제공할 수 있습니다.

추가 고려 사항

• 상한 및 하한 시간 경계를 올바르게 처리합니다.
• to_char를 사용합니다. () 함수를 사용하여 표시 목적으로 타임스탬프 형식을 지정합니다.
• generate_series() 함수를 사용하여 전체 타임스탬프를 생성합니다. 시간대 설정.

위 내용은 PostgreSQL에서 동적 시간 간격 내에서 이벤트를 효율적으로 계산하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!