[RUBY-ON-RAILS] 가장 좋은 방법은 레일 + 포스트 그레스에 임의의 시간 간격으로 기록을 계산하려면

가장 좋은 방법은 레일 + 포스트 그레스에 임의의 시간 간격으로 기록을 계산하려면

내 애플 타임 스탬프 이벤트와 이벤트 테이블이 있습니다.

나는 가장 최근의 N 시간 간격의 각 동안 이벤트의 수를보고해야합니다. 다른 보고서의 간격은 "매주"또는 "매일"또는 "매 시간"또는 "각 15 분 간격"이 될 수 있습니다.

예를 들어, 사용자들은 각 주, 일, 또는 시간 또는 분 시간을받은 얼마나 많은 주문을 표시 할 수 있습니다.

1) 내 환경 설정은 동적으로 임의의 시간 간격으로 단일 SQL 쿼리 (내가 포스트 그레스를 사용하고 있습니다) 그룹 그렇게 할 것입니다. 그렇게 할 수있는 방법이 있습니까?

2) 쉬운하지만 추한 브 루트 포스 방법은 시작 / 끝 기간이 타임 스탬프으로 분류 내의 모든 레코드에 대한 단일 쿼리를하는 것입니다, 수동으로 어떤 간격으로 집계를 구축하는 방법이있다.

3) 또 다른 방법은 대신 때마다 I의) 간격마다 이벤트 테이블에 별도의 필드를 추가하고 난 레코드가 한 번 (작성시에있는 '히트'를 취할 수 있도록 정적 the_week the_day, the_hour를 저장하고, the_quarter_hour 필드된다 해당 필드에보고한다.

가장 좋은 방법은 내가 모델을 수정하고 (테이블 폭을 두 배로 겸손한 희생하더라도) 필요한 경우 간격 데이터 저장소를 미리 할 수 ​​주어진, 여기에 무엇입니까?

해결법

1. ==============================

   1.다행히, 당신은 PostgreSQL을 사용하고 있습니다. 윈도우 기능 generate_series ()는 당신의 친구입니다.

   다행히, 당신은 PostgreSQL을 사용하고 있습니다. 윈도우 기능 generate_series ()는 당신의 친구입니다.

   다음 테스트 테이블을 (사용자가 제공해야하는)을 감안할 때 :

   CREATE TABLE event(event_id serial, ts timestamp);
   INSERT INTO event (ts)
   SELECT generate_series(timestamp '2018-05-01'
                        , timestamp '2018-05-08'
                        , interval '7 min') + random() * interval '7 min';
   

   매 7 분에 대한 하나의 이벤트 (더하기 0 ~ 7 분, 무작위로).

   이 쿼리는 임의의 시간 간격에 대한 이벤트를 계산합니다. 실시 예 17 MINUTES :

   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;
   

   상기 질의는 CTE와 리드 () 구문과 상세를 이용한다. 우아하고 이해하기 어쩌면 더 쉽게,하지만 조금 더 비싼. 여기에 짧은, 빠른, 최소한의 버전은 다음과 같습니다

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

   그리고 TO_CHAR와 포맷 ().

   SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), count(e.ts) AS events
   FROM   generate_series(date_trunc('day', localtimestamp - interval '7 days')
                        , localtimestamp
                        , interval '15 min') g(start_time)
   LEFT   JOIN event e ON e.ts >= g.start_time
                      AND e.ts <  g.start_time + interval '15 min'
   GROUP  BY start_time
   ORDER  BY start_time;

   하지 포맷 된 문자열에 대한 기본 타임 스탬프 값에 여전히 ORDER BY와 GROUP BY. 즉보다 빠르고 안정적입니다.

   DB <> 바이올린 여기

   시간 프레임에 걸쳐 실행 수를 생산 관련 대답 :

from https://stackoverflow.com/questions/15576794/best-way-to-count-records-by-arbitrary-time-intervals-in-railspostgres by cc-by-sa and MIT license