Выполните запрос часов этой работы в PostgreSQL

Я нахожусь в стеке RoR, и мне пришлось написать какой-то реальный SQL для выполнения этого запроса для всех записей, которые "открыты", что означает, что текущее время находится в пределах указанных часов работы. в hours_of_operations таблица два integer столбцы opens_on а также closes_on хранить в будний день и два time поля 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 ответ

Решение

Макет таблицы

Перепроектировать стол и часы работы магазина (часы работы) как набор tsrange (диапазон меток времени без часового пояса) значения. Требуется Postgres 9.2 или более поздняя версия.

Выберите случайную неделю, чтобы настроить часы работы. Мне нравится неделя:
С 1996-01-01 (понедельник) по 1996-01-07 (воскресенье)
Это самый последний високосный год, когда 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]')
);

Один столбец hours заменяет все ваши столбцы:

opens_on, closes_on, opens_at, closes_at

Например, часы работы со среды, 18:30 до четверга, 05:00 UTC вводятся как:

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

Ограничение исключения hoo_no_overlap предотвращает наложение записей на магазин. Он реализован с помощью индекса GiST, который также поддерживает ваш запрос. Рассмотрите главу "Индекс и производительность" ниже, обсуждая стратегию индексирования.

Ограничение проверки hoo_bounds_inclusive устанавливает обязательные границы для ваших диапазонов с двумя заслуживающими внимания последствиями:

  • Момент времени, попадающий точно на нижнюю или верхнюю границу, всегда включается.
  • Смежные записи для того же магазина фактически запрещены. С инклюзивными границами они будут "перекрываться", и ограничение исключения вызовет исключение. Смежные записи должны быть объединены в один ряд. За исключением случаев, когда они переносятся в воскресенье около полуночи, в этом случае они должны быть разбиты на два ряда. Смотрите инструмент 2 ниже.

Ограничение проверки 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)

Вы должны искать обе отметки времени одновременно. Вот связанный случай с исключительной верхней границей, которая не будет демонстрировать этот недостаток:

функция f_hoo_time(timestamptz)

"Нормализовать" любой данный timestamp with time zone:

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 и возвращается timestamp, Добавляет прошедший интервал соответствующей недели ($1 - date_trunc('week', $1) в UTC время (!) до отправной точки нашей подготовительной недели. (date + interval производит timestamp.)

функция f_hoo_hours(timestamptz, timestamptz)

Чтобы нормализовать диапазоны и разбить те пересекающие пн 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 одна строка ввода:

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 несколько входных строк:

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

О неявном LATERAL присоединиться:

запрос

С измененным дизайном весь ваш большой, сложный, дорогой запрос может быть заменен на... это:

SELECT *
FROM hoo
WHERE hours @> f_hoo_time(now());

Для небольшого ожидания я поместил спойлер на раствор. Наведите курсор на него.

Запрос поддерживается указанным индексом GiST и быстро, даже для больших таблиц.

SQL Fiddle (с большим количеством примеров).

Если вы хотите рассчитать общее количество часов работы (на магазин), вот рецепт:

Индекс и производительность

Оператор содержания для типов диапазона может поддерживаться индексом GiST или SP-GiST. Любой может быть использован для реализации ограничения исключения, но только GiST поддерживает многоколоночные индексы:

В настоящее время только индексы типа B-tree, GiST, GIN и BRIN поддерживают многоколоночные индексы.

И порядок столбцов индекса имеет значение:

Многостолбцовый индекс GiST может использоваться с условиями запроса, которые включают любое подмножество столбцов индекса. Условия для дополнительных столбцов ограничивают записи, возвращаемые индексом, но условие для первого столбца является наиболее важным для определения того, какую часть индекса необходимо сканировать. Индекс GiST будет относительно неэффективным, если его первый столбец имеет только несколько отдельных значений, даже если в дополнительных столбцах много различных значений.

Таким образом, у нас есть конфликтующие интересы здесь. Для больших таблиц будет гораздо больше различных значений shop_id чем для hours,

  • Индекс GiST с ведущими shop_id быстрее писать и применять ограничение исключения.
  • Но мы ищем hours столбец в нашем запросе. Иметь эту колонку первым было бы лучше.
  • Если нам нужно посмотреть вверх shop_id в других запросах обычный btree-индекс намного быстрее для этого.
  • В довершение всего я нашел индекс SP-GiST на hours быть самым быстрым для запроса.

эталонный тест

Мой скрипт для генерации фиктивных данных:

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;

В результате получается 141 тыс. Случайно сгенерированных строк, 30 тыс. Различных shop_id 12к отличная hours, (Обычно разница будет больше.) Размер таблицы 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

shop_id во-первых, в 4 раза быстрее.

Кроме того, я протестировал еще два на производительность чтения:

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

После VACUUM FULL ANALYZE hoo; Я выполнил два запроса:

  • Q1: поздно ночью, найти только 53 строки
  • Q2: днем, найдя 2423 строки.

Результаты

Получил сканирование только по индексу для каждого (кроме "без индекса", конечно):

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 и GiST находятся на одном уровне с запросами, которые дают мало результатов (GiST даже быстрее для очень немногих).
  • SP-GiST лучше масштабируется с ростом числа результатов, а также меньше.

Если вы читаете намного больше, чем пишете (типичный вариант использования), сохраните ограничение исключения, как было предложено в начале, и создайте дополнительный индекс SP-GiST для оптимизации производительности чтения.

Источник
Другие вопросы по тегам