sql - 在 PostgreSQL 中执行这个小时的操作查询

Question

我在 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 格式存储值，因此在很多情况下，用户的本地时间可能会以一种非常奇怪的方式换行。上面的查询确保您可以在一周中输入任意两次，我们会补偿包装。

Answer 1

表格布局
重新设计表格以将开放时间(营业时间)存储为一组 tsrange (range of timestamp without time zone )值。需要 Postgres 9.2 或更高版本 .
随机选择一周来安排您的营业时间。我喜欢一周:
1996-01-01(星期一)至 1996-01-07(星期日)
这是最近的闰年，1 月 1 日恰好是星期一。但对于这种情况，它可以是任何随机的一周。只要保持一致。
安装附加模块 btree_gist 第一的:

CREATE EXTENSION btree_gist;

看:

Equivalent to exclusion constraint composed of integer and range

然后像这样创建表:

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 为您的范围强制执行包含边界，有两个值得注意的后果:

始终包括恰好落在下边界或上边界的时间点。

同一商店的相邻条目实际上是不允许的。对于包含边界，这些边界会“重叠”，排除约束会引发异常。相邻的条目必须合并为一行。除非他们在周日午夜结束，在这种情况下，他们必须分成两行。函数f_hoo_hours()下面处理这个。

检查约束 hoo_standard_week 使用 "range is contained by" operator <@ 强制执行暂存周的外部边界.
与 含边界，你必须观察 角箱时间在周日午夜结束:

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

您必须一次搜索两个时间戳。这是一个与 相关的案例独家不会出现此缺点的上限:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

功能 f_hoo_time(timestamptz)“规范化”任何给定的 timestamp with time zone :

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

PARALLEL SAFE仅适用于 Postgres 9.6 或更高版本。
该函数需要 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)
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS
$func$
DECLARE
   ts_from timestamp := f_hoo_time(_from);
   ts_to   timestamp := f_hoo_time(_to);
BEGIN
   -- sanity checks (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', ts_to  , '[]'))
           , (tsrange(ts_from, '1996-01-08', '[]'));
   ELSE                     -- simple case: range in standard week
      hoo_hours := tsrange(ts_from, ts_to, '[]');
      RETURN NEXT;
   END IF;

   RETURN;
END
$func$;

致 INSERT单个输入行:

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

对于任意数量的输入行:

INSERT INTO hoo(shop_id, hours)
SELECT id, f_hoo_hours(f, t)
FROM  (
   VALUES (7, timestamptz '2016-01-11 00:00+0', timestamptz '2016-01-11 08:00+0')
        , (8, '2016-01-11 00:00+1', '2016-01-11 08:00+1')
   ) t(id, f, t);

如果范围需要在 UTC 星期一 00:00 拆分，则每行都可以插入两行。
询问
通过调整后的设计，您可以将整个庞大、复杂、昂贵的查询替换为...:

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

为了一点悬念，我在解决方案上放了一个扰流板。移动 鼠标悬停 它。
该查询由上述 GiST 索引支持并且速度很快，即使对于大表也是如此。
分贝<> fiddle here (更多例子)
旧 sqlfiddle
如果你想计算总营业时间(每家商店)，这里有一个食谱:

Calculate working hours between 2 dates in PostgreSQL

指标与表现
containment operator for range types可以用 GiST 支持或 SP-GiST指数。两者都可以用来实现排除约束，但只有 GiST 支持 multicolumn indexes :

Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.

和 order of index columns matters :

A multicolumn GiST index can be used with query conditions thatinvolve any subset of the index's columns. Conditions on additionalcolumns restrict the entries returned by the index, but the conditionon the first column is the most important one for determining how muchof the index needs to be scanned. A GiST index will be relativelyineffective if its first column has only a few distinct values, evenif there are many distinct values in additional columns.

所以我们有 利益冲突 这里。对于大表， shop_id 会有更多不同的值。比 hours .

具有领先 shop_id 的 GiST 索引写入和强制执行排除约束更快。

但是我们正在搜索hours在我们的查询中。首先拥有该列会更好。

如果需要查找shop_id在其他查询中，一个普通的 btree 索引要快得多。

最重要的是，我找到了一个 SP-GiST 索引就在 hours成为 最快 为查询。

基准
在旧笔记本电脑上使用 Postgres 12 进行新测试。
我的脚本生成虚拟数据:

INSERT INTO hoo(shop_id, hours)
SELECT id
     , 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')
FROM   generate_series(1, 30000) id
JOIN   generate_series(0, 6) d ON random() > .33;

结果在 ~ 141k 随机生成的行， ~ 30k 不同 shop_id , ~ 12k 不同 hours .表大小 8 MB。
我删除并重新创建了排除约束:

ALTER TABLE hoo
  DROP CONSTRAINT hoo_no_overlap
, ADD CONSTRAINT hoo_no_overlap  EXCLUDE USING gist (shop_id WITH =, hours WITH &&);  -- 3.5 sec; index 8 MB
    
ALTER TABLE hoo
  DROP CONSTRAINT hoo_no_overlap
, ADD CONSTRAINT hoo_no_overlap  EXCLUDE USING gist (hours WITH &&, shop_id WITH =);  -- 13.6 sec; index 12 MB

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; ，我运行了两个查询:

第一季度 : 深夜，只找到 35行

Q2 : 下午，发现 4547 行 .

结果
对每个都进行了仅索引扫描(当然，“无索引”除外):

index                 idx size  Q1        Q2
------------------------------------------------
no index                        38.5 ms   38.5 ms 
gist (shop_id, hours)    8MB    17.5 ms   18.4 ms
gist (hours, shop_id)   12MB     0.6 ms    3.4 ms
gist (hours)            11MB     0.3 ms    3.1 ms
spgist (hours)           9MB     0.7 ms    1.8 ms  -- !

SP-GiST 和 GiST 对于发现很少结果的查询是相当的(GiST 对于很少的结果甚至更快)。

随着结果数量的增加，SP-GiST 的扩展性更好，而且规模也更小。

如果您阅读的内容多于您编写的内容(典型用例)，请按照一开始的建议保留排除约束并创建额外的 SP-GiST 索引以优化读取性能。