[RUBY-ON-RAILS] PostgreSQL의에서 작동 쿼리의 시간을 수행

PostgreSQL의에서 작동 쿼리의 시간을 수행

나는 RoR에 스택 그리고 난 현재 시간이 작업의 지정된 시간 내에 있다는 것을 의미한다 "열기"모든 기록을 위해이 쿼리를 완료하는 데 몇 가지 실제 SQL을 작성했다. hours_of_operations에서 테이블에 두 개의 정수 열 평일 opens_on 및 closes_on 저장소, 두 시간 필드 하루의 각 시간 opens_at 및 closes_at 가게.

나는 저장된 값에 현재 날짜와 시간을 비교하는 쿼리를했지만 방법이 날짜 형식의 일종의 주조에이 있는지 궁금 하군요과 PostgreSQL 나머지를했다?

쿼리의 고기입니다 :

WHERE (
 (

 /* Opens in Future */
 (opens_on > 5 OR (opens_on = 5 AND opens_at::time > '2014-03-01 00:27:25.851655'))
 AND (
 (closes_on < opens_on AND closes_on > 5)
 OR ((closes_on = opens_on)
 AND (closes_at::time < opens_at::time AND closes_at::time > '2014-03-01 00:27:25.851655'))
 OR ((closes_on = 5)
 AND (closes_at::time > '2014-03-01 00:27:25.851655' AND closes_at::time < opens_at::time)))
 OR

 /* Opens in Past */
 (opens_on < 5 OR (opens_on = 5 AND opens_at::time < '2014-03-01 00:27:25.851655'))
 AND
 (closes_on > 5)
 OR
 ((closes_on = 5)
 AND (closes_at::time > '2014-03-01 00:27:25.851655'))
 OR (closes_on < opens_on)
 OR ((closes_on = opens_on)
 AND (closes_at::time < opens_at::time))
 )

 )

같은 밀도의 복잡성에 대한 목 이유는 운영 시간은 일요일 정오부터 오전 6 월요일을 통과, 예를 들어, 주말 주위에 래핑 수 있다는 것이다. 내가 UTC에 값을 저장하기 때문에, 사용자의 로컬 시간이 아주 이상한 방법으로 포장 할 수있는 많은 경우가 있습니다. 보장하지만 위의 쿼리는 당신이 일주일의 두 배를 입력 할 수 있고 우리는 포장에 대한 보상.

해결법

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

   1.값 (시간대없이 소인의 범위) tsrange 세트로 테이블 저장 개방 시간 (작동 시간)을 재 - 디자인. 포스트 그레스 9.2 이상이 필요합니다.

   값 (시간대없이 소인의 범위) tsrange 세트로 테이블 저장 개방 시간 (작동 시간)을 재 - 디자인. 포스트 그레스 9.2 이상이 필요합니다.

   당신의 영업 시간을 무대에 임의의 주 선택하십시오. 나는 주를 좋아한다 : 1996-01-01 1996년 1월 7일에 (월) (일) 즉 월 1 일 편리 월요일 될 일이 가장 최근의 윤년이다. 그러나이 경우에 어떤 무작위 주이 될 수 있습니다. 그냥 일치.

   먼저 추가 모듈 btree_gist를 설치합니다. 왜?

   CREATE EXTENSION btree_gist;
   

   이 같은 테이블을 만듭니다

   CREATE TABLE hoo (
      hoo_id  serial PRIMARY KEY
    , shop_id int NOT NULL REFERENCES shop(shop_id)     -- reference to shop
    , hours   tsrange NOT NULL
    , CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id with =, hours WITH &&)
    , CONSTRAINT hoo_bounds_inclusive CHECK (lower_inc(hours) AND upper_inc(hours))
    , CONSTRAINT hoo_standard_week CHECK (hours <@ tsrange '[1996-01-01 0:0, 1996-01-08 0:0]')
   );
   

   한 열 시간은 당신의 모든 열을 대체합니다

   opens_on, closes_on, opens_at, closes_at

   예를 들어, 수요일, 목요일 18 : 30 ~ 05:00와 표준시의 운영 시간은 다음과 같이 입력 :

   '[1996-01-03 18:30, 1996-01-04 05:00]'
   

   배제 제약 hoo_no_overlap는 매장 당 항목을 중복 방지 할 수 있습니다. 또한 쿼리를 지원하기 위해 발생 요지 지수로 구현됩니다. 인덱싱 전략을 논의 아래의 장 "인덱스 및 성능"을 고려하십시오.

   점검 제한 조건 hoo_bounds_inclusive 강제 시행은 두 가지 주목할만한 결과로, 당신의 범위에 대한 경계를 포괄적으로 :

   점검 제한 조건 hoo_standard_week 운영자 "범위에 포함된다"로 준비 주간의 외부 경계를 시행 <@.

   포괄적 인 범위로, 당신은 시간이 일요일 자정 감싸는 특별한 / 코너 케이스를 관찰해야합니다 :

   '1996-01-01 00:00+0' = '1996-01-08 00:00+0'
    Mon 00:00 = Sun 24:00 (= next Mon 00:00)
   

   한 번에 두 타임 스탬프를 검색 할 수 있습니다. 다음은 이러한 단점을 나타내지 않을 것이다 독점적 인 상한과 관련된 경우는 다음과 같습니다

   "정상화"시간대에 주어진 타임 스탬프 :

   CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz)
     RETURNS timestamp AS
   $func$
   SELECT date '1996-01-01'
       + ($1 AT TIME ZONE 'UTC' - date_trunc('week', $1 AT TIME ZONE 'UTC'))
   $func$  LANGUAGE sql IMMUTABLE;
   

   이 기능은 timestamptz 반환 타임 스탬프를합니다. ! 그것은 각각의 주 ($ 1 경과 간격 추가 -. 우리의 준비 주일의 시작 지점으로 UTC 시간 ()에서 date_trunc ( '주', $ 1) (날짜 + 간격이 타임 스탬프를 생성).

   범위를 정상화하고 그 교차 월 00:00 분할합니다. 이 함수는 (두 timestamptz로) 상관 구간을 취하고 하나 개 또는 두 개의 정규화 tsrange 값을 생성한다. 그것은 어떤 법적 입력을 포함하고 나머지는 허용하지 :

   CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz)
     RETURNS TABLE (hoo_hours tsrange) AS
   $func$
   DECLARE
      ts_from timestamp := f_hoo_time(_from);
      ts_to   timestamp := f_hoo_time(_to);
   BEGIN
      -- test input for sanity (optional)
      IF _to <= _from THEN
         RAISE EXCEPTION '%', '_to must be later than _from!';
      ELSIF _to > _from + interval '1 week' THEN
         RAISE EXCEPTION '%', 'Interval cannot span more than a week!';
      END IF;
   
      IF ts_from > ts_to THEN  -- split range at Mon 00:00
         RETURN QUERY
         VALUES (tsrange('1996-01-01 0:0', ts_to  , '[]'))
              , (tsrange(ts_from, '1996-01-08 0:0', '[]'));
      ELSE                     -- simple case: range in standard week
         hoo_hours := tsrange(ts_from, ts_to, '[]');
         RETURN NEXT;
      END IF;
   
      RETURN;
   END
   $func$  LANGUAGE plpgsql IMMUTABLE COST 1000 ROWS 1;
   

   하나의 입력 행을 삽입하려면 :

   INSERT INTO hoo(shop_id, hours)
   SELECT 123, f_hoo_hours('2016-01-11 00:00+04', '2016-01-11 08:00+04');
   

   범위가 월 00:00 분할을 필요로하는 경우 두 개의 행이 결과.

   여러 입력 행을 삽입하려면 :

   INSERT INTO hoo(shop_id, hours)
   SELECT id, hours
   FROM  (
      VALUES (7, timestamp '2016-01-11 00:00', timestamp '2016-01-11 08:00')
           , (8, '2016-01-11 00:00', '2016-01-11 08:00')
      ) t(id, f, t), f_hoo_hours(f, t) hours;  -- LATERAL join
   

   암시 적 측면에 대해 조인

   조정 된 디자인으로, 전체 큰는, 복잡하고 비싼 쿼리와 함께 ...이 교체 할 수 있습니다 :

   약간의 긴장감을 위해 나는 솔루션을 통해 스포일러 판을 넣어. 위에 마우스를 이동합니다.

   쿼리에도 큰 테이블, 빠른 말했다 GIST 지수에 의해 백업됩니다.

   SQL 바이올린 (자세한 예제와 함께).

   당신이 총 영업 시간을 (상점 당)를 계산하려면, 여기 조리법이다 :

   다양한 종류의 수납 조작자는 요지 또는 SP-요지 인덱스가 지원 될 수있다. 어느 제외 제약 조건을 구현하는 데 사용되는,하지만 요점 지원은 인덱스를 여러 열로 할 수 있습니다 :

   그리고 인덱스 컬럼의 순서가 중요하다 :

   우리는 여기에서 충돌 관심을 가지고 그래서. 큰 테이블의 경우, 시간보다 shop_id에 대한 더 많은 고유 값이있을 것이다.

   내 스크립트는 더미 데이터를 생성합니다 :

   INSERT INTO hoo(shop_id, hours)
   SELECT id, hours
   FROM   generate_series(1, 30000) id, generate_series(0, 6) d
        , f_hoo_hours(((date '1996-01-01' + d) + interval  '4h' + interval '15 min' * trunc(32 * random()))            AT TIME ZONE 'UTC'
                    , ((date '1996-01-01' + d) + interval '12h' + interval '15 min' * trunc(64 * random() * random())) AT TIME ZONE 'UTC') AS hours
   WHERE  random() > .33;
   

   141K 무작위로 생성 된 행의 결과, 12K 별개의 시간을 별개의 shop_id을 30K. (일반적으로 큰 차이가 될 것이다.) 표 크기 8메가바이트

   나는 떨어 제외 제약 조건을 다시 :

   ALTER TABLE hoo ADD CONSTRAINT hoo_no_overlap
      EXCLUDE USING gist (shop_id WITH =, hours WITH &&);  --  4.4 sec !!
   
   ALTER TABLE hoo ADD CONSTRAINT hoo_no_overlap
      EXCLUDE USING gist (hours WITH &&, shop_id WITH =);  -- 16.4 sec
   

   빠른 ~ 4 배이다 첫번째 shop_id.

   또한, 나는 읽기 성능이 더 테스트 :

   CREATE INDEX hoo_hours_gist_idx   on hoo USING gist (hours);
   CREATE INDEX hoo_hours_spgist_idx on hoo USING spgist (hours);  -- !!
   

   VACUUM FULL는 ~이 분석 후 ;, 나는 두 개의 쿼리를 실행 :

   있어 인덱스 만 (물론 "아니요 인덱스"제외) 각각에 대한 검색 :

   index                 idx size  Q1         Q2
   ------------------------------------------------
   no index                        41.24 ms   41.2 ms 
   gist (shop_id, hours)    8MB    14.71 ms   33.3 ms
   gist (hours, shop_id)   12MB     0.37 ms    8.2 ms
   gist (hours)            11MB     0.34 ms    5.1 ms
   spgist (hours)           9MB     0.29 ms    2.0 ms  -- !!
   

   당신이 더 당신이 (일반적인 사용 사례를) 쓰기보다 많이 읽는다면 초기에 제안, 제외 제약 조건을 유지하고 최적화 성능을 읽을에 추가 SP-GIST 인덱스를 만들 수 있습니다.

from https://stackoverflow.com/questions/22108477/perform-this-hours-of-operation-query-in-postgresql by cc-by-sa and MIT license