代码之家 › 专栏 › 技术社区 › Adam Lassek

PostgreSQL SELECT必须跨多行匹配

postgresql-9.1 select postgresql sql

Adam Lassek · 技术社区 · 11 年前

我有一个表,其中包含一对表示时间跨度的时间戳。这些行的范围由用户id确定,每个用户可以有一个或多个与它们相关联的行。

这些数据是从一个抽象的“可用性”表单生成的,该表单表示用户在一周内何时可用。我需要输入一系列时间范围作为查询,并返回所有用户id 所有行 在桌上比赛。

给定此表:

CREATE TABLE "public"."availability" (
  "id" int4 NOT NULL,
  "user_id" int4,
  "starts_at" timestamp(6),
  "ends_at" timestamp(6),
  PRIMARY KEY ("id")
) WITH (OIDS=FALSE)

并且该数据:

User #1 is available Mon-Tue between 08:00 and 17:00

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 1  | 1       | 2013-03-18 08:00:00 | 2013-03-18 17:00:00 |
+----+---------+---------------------+---------------------+
| 2  | 1       | 2013-03-19 08:00:00 | 2013-03-19 17:00:00 |
+----+---------+---------------------+---------------------+

User #2 is available Sun-Sat all day

+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+

User #3 is available Wed between 06:00 and 18:00

+----+---------+---------------------+---------------------+
| 4  | 3       | 2013-03-20 06:00:00 | 2013-03-20 18:00:00 |
+----+---------+---------------------+---------------------+

我可以很容易地选择可用于任何在给定的时间戳中:

SELECT * FROM "public"."availability"
  WHERE ('2013-03-19 08:35:00' BETWEEN starts_at AND ends_at 
     AND '2013-03-19 18:25:00' BETWEEN starts_at AND ends_at)
    OR  ('2013-03-20 12:00:00' BETWEEN starts_at AND ends_at
     AND '2013-03-20 18:00:00' BETWEEN starts_at AND ends_at);

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+
| 4  | 3       | 2013-03-20 06:00:00 | 2013-03-20 18:00:00 |
+----+---------+---------------------+---------------------+

但我真正需要的是能够查询多个时间跨度,并且只返回 user_id 那匹配吗 全部的 条件。

查询: 2013-03-17 10:00:00 - 2013-03-17 16:00:00 , 2013-03-23 10:00:00 - 2013-03-23 16:00:00 应返回:

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+

查询: 2013-03-18 09:00:00 - 2013-03-18 16:00:00 , 2013-03-19 08:00:00 - 2013-03-19 15:45:00 应返回:

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 1  | 1       | 2013-03-18 08:00:00 | 2013-03-18 17:00:00 |
+----+---------+---------------------+---------------------+
| 2  | 1       | 2013-03-19 08:00:00 | 2013-03-19 17:00:00 |
+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+

查询: 2013-03-18 07:00:00 - 2013-03-18 18:00:00 不应返回任何内容。

SQLFiddle example

2 回复 | 直到 11 年前

kgrittn 11 年前

对于这样的应用程序,如果您使用的是PostgreSQL 9.2或更高版本,您可能需要尝试 range type 。以下是创建、加载和显示数据的示例:

CREATE TABLE availability (
  id      int4 NOT NULL,
  user_id int4,
  avail   tstzrange,
  PRIMARY KEY (id)
);
INSERT INTO availability VALUES
  (1, 1, '[2013-03-18 08:00:00, 2013-03-18 17:00:00)'),
  (2, 1, '[2013-03-19 08:00:00, 2013-03-19 17:00:00)'),
  (3, 2, '[2013-03-17 00:00:00, 2013-03-23 24:00:00)'),
  (4, 3, '[2013-03-20 06:00:00, 2013-03-20 18:00:00)');
SELECT * FROM availability ;

 id | user_id |                        avail                        
----+---------+-----------------------------------------------------
  1 |       1 | ["2013-03-18 08:00:00-05","2013-03-18 17:00:00-05")
  2 |       1 | ["2013-03-19 08:00:00-05","2013-03-19 17:00:00-05")
  3 |       2 | ["2013-03-17 00:00:00-05","2013-03-24 00:00:00-05")
  4 |       3 | ["2013-03-20 06:00:00-05","2013-03-20 18:00:00-05")
(4 rows)

然后您可以使用各种运算符进行查询。如果您想要包含的所有可用性范围任何指定的查询范围中的一个:

SELECT * FROM availability
  WHERE avail @> '[2013-03-19 08:35:00, 2013-03-19 18:25:00)'
     OR avail @> '[2013-03-20 12:00:00, 2013-03-20 18:00:00)';

或:

SELECT * FROM availability
  WHERE avail @> ANY
          (ARRAY ['[2013-03-19 08:35:00, 2013-03-19 18:25:00)'::tstzrange,
                  '[2013-03-20 12:00:00, 2013-03-20 18:00:00)'::tstzrange]);

 id | user_id |                        avail                        
----+---------+-----------------------------------------------------
  3 |       2 | ["2013-03-17 00:00:00-05","2013-03-24 00:00:00-05")
  4 |       3 | ["2013-03-20 06:00:00-05","2013-03-20 18:00:00-05")
(2 rows)

如果您想要包含的所有可用性范围 全部的 指定查询范围的 在单个范围内 :

SELECT * FROM availability
  WHERE avail @> '[2013-03-17 10:00:00, 2013-03-17 16:00:00)'
    AND avail @> '[2013-03-23 10:00:00, 2013-03-23 16:00:00)';

或:

SELECT * FROM availability
  WHERE avail @> ALL
          (ARRAY ['[2013-03-17 10:00:00, 2013-03-17 16:00:00)'::tstzrange,
                  '[2013-03-23 10:00:00, 2013-03-23 16:00:00)'::tstzrange]);

 id | user_id |                        avail                        
----+---------+-----------------------------------------------------
  3 |       2 | ["2013-03-17 00:00:00-05","2013-03-24 00:00:00-05")
(1 row)

如果您想要包含的所有可用性范围任何在指定的查询范围中, 但仅适用于具有以下可用性范围的用户 全部的 指定查询范围的 :

WITH s(ts) AS
(
  VALUES
    ('[2013-03-18 09:00:00, 2013-03-18 16:00:00)'::tstzrange),
    ('[2013-03-19 08:00:00, 2013-03-19 15:45:00)'::tstzrange)
)
SELECT DISTINCT a1.*
  FROM s s1
  JOIN availability a1 ON a1.avail @> s1.ts
    AND NOT EXISTS
        (
          SELECT * FROM s s2
            WHERE NOT EXISTS
                  (
                    SELECT * FROM availability a2
                      WHERE a2.user_id = a1.user_id
                        AND a2.avail @> s2.ts
                  )
        );

或(调整Clodoaldo-Neto的查询以使用范围):

SELECT a.*
  FROM availability a
  JOIN (
         SELECT
             user_id,
             sum(('[2013-03-18 09:00:00, 2013-03-18 16:00:00)'::tstzrange
                   <@ avail)::integer
                 +
                 ('[2013-03-19 08:00:00, 2013-03-19 15:45:00)'::tstzrange
                   <@ avail)::integer
                ) period
           FROM availability
           GROUP BY user_id
       ) s ON a.user_id = s.user_id
  WHERE period >= 2;

您可以创建一个索引,以便在这样的大表上快速进行搜索:

CREATE INDEX availability_avail ON availability USING gist (avail);

笔记:

为了便于阅读,我省略了模式和引号。
索引不太可能与四行一起使用,因为通过直接读取一个数据页,可以更快地获得所有数据。有了大桌子,可能会有很大的不同。
我使用了 TIMESTAMP WITH TIME ZONE 因为使用默认值(裸露) TIMESTAMP 时钟每年在夏令时结束时向后移动。要捕捉瞬间,请使用 带时区的时间戳 ( timestamptz 简称)。
当直接使用时,文字不需要显式强制转换;当使用 ANY 或 ALL 在查询的形式中,需要显式强制转换。
范围上的方括号表示范围包括相邻时间,而圆括号表示范围排除相邻时间。时间戳通常使用 [) 从而考虑以给定时间结束的范围和以相同时间开始的另一范围相邻而不是重叠 .
'24:00:00' 对于一个日期和 '00:00:00' 下一次约会是在同一时刻。
前面两点允许更容易地指定午夜结束的时间戳。不存在“失去第二秒”或其他奇怪情况的风险。

Clodoaldo Neto 11 年前

SQL Fiddle

这利用了将布尔型转换为0或1的整数。

select a.*
from
    availability a
    inner join
    (
        select
            user_id,
            sum (
                ('2013-03-18 09:00:00' between starts_at and ends_at
                 and
                 '2013-03-18 16:00:00' between starts_at and ends_at
                )::integer
                +
                ('2013-03-19 08:00:00' between starts_at and ends_at
                 and
                 '2013-03-19 15:45:00' between starts_at and ends_at
                )::integer
            ) period
        from availability
        group by user_id
    ) s on a.user_id = s.user_id
where period >= 2

更改 where 条件设置为要匹配的周期数。