选择一个具有多个时间范围的行

Question

我有一张桌子：

CREATE TABLE logins(
    id SERIAL NOT NULL PRIMARY KEY,
    login_time TSRANGE NOT NULL,
    user_id INTEGER NOT NULL REFERENCES users(id),
    CONSTRAINT overlapping_timeslots EXCLUDE USING GIST (
        user_id WITH =,
        timeslot WITH &&
    )
);

当用户登录到用login_time保存的tsrange(login_time,logout_time)中时。

现在，我尝试搜索一个登录的用户：

-- ('2013-12-31 16:40:05','2013-12-31 17:40:05')
-- ('2014-01-04 14:27:45','2014-01-04 17:30:56')
-- ('2014-01-05 14:59:55','2014-01-05 16:03:39')
-- ('2014-01-01 17:20:54','2014-01-01 22:50:57')
-- Not logged in at ('2013-12-31 18:40:05','2014-01-01 01:20:05')

我有这个查询，但是没有有用的结果。

SELECT user_id FROM (


select * from logins 
    where user_id in(select user_id from timed_requests where timeslot && tsrange('2013-12-31 16:20:05','2013-12-31 17:40:05'))
    and user_id in(select user_id from timed_requests where timeslot && tsrange('2014-01-04 14:30:45','2014-01-04 17:20:56'))
    and user_id in(select user_id from timed_requests where timeslot && tsrange('2014-01-05 15:09:55','2014-01-05 16:00:39'))
    and user_id in(select user_id from timed_requests where timeslot && tsrange('2014-01-01 17:20:54','2014-01-01 22:50:57')
    and user_id not in(select user_id from timed_requests where timeslot && tsrange('2013-12-31 18:40:05','2014-01-01 01:20:05'))
    ) ss
GROUP BY user_id
order by user_id;

有谁知道我如何写一个查询，谁能搜索一个用户谁登录在3-4给定的时间点。

Answer 1

这是典型的relational division案例。解决这个问题有很多种方法。这应该是最快和最简单的：

SELECT DISTINCT user_id
FROM   logins l1
JOIN   logins l2 USING (user_id)
JOIN   logins l3 USING (user_id)
JOIN   logins l4 USING (user_id)
LEFT   JOIN logins l5 ON t5.user_id = t1.user_id AND 
  NOT (l4.timeslot && tsrange('2013-12-31 18:40:05','2014-01-01 01:20:05'))
WHERE  l1.timeslot && tsrange('2013-12-31 16:20:05','2013-12-31 17:40:05')
AND    l2.timeslot && tsrange('2014-01-04 14:30:45','2014-01-04 17:20:56')
AND    l3.timeslot && tsrange('2014-01-05 15:09:55','2014-01-05 16:00:39')
AND    l4.timeslot && tsrange('2014-01-01 17:20:54','2014-01-01 22:50:57')
AND    l5.user_id IS NULL
ORDER  BY 1;

您有一个排除约束，但是在单个测试范围内可以多次登录相同的限制，因此我们需要GROUP BY或DISTINCT。

在这个相关的答案中，我们汇集了一整套技术：

How to filter SQL results in a has-many-through relation

为了避免重复开始，同时从users表中检索整行(这不是您的问题，但可能存在)，此表单可能会更快：

SELECT *
FROM   users u
WHERE  EXISTS (SELECT 1 FROM logins WHERE user_id = u.user_id
       AND timeslot && tsrange('2013-12-31 16:20:05','2013-12-31 17:40:05'))
AND    EXISTS (SELECT 1 FROM logins WHERE user_id = u.user_id
       AND timeslot && tsrange('2014-01-04 14:30:45','2014-01-04 17:20:56'))
AND    EXISTS (SELECT 1 FROM logins WHERE user_id = u.user_id
       AND timeslot && tsrange('2014-01-05 15:09:55','2014-01-05 16:00:39'))
AND    EXISTS (SELECT 1 FROM logins WHERE user_id = u.user_id
       AND timeslot && tsrange('2014-01-01 17:20:54','2014-01-01 22:50:57'))
AND    NOT EXISTS (SELECT 1 FROM logins WHERE user_id = u.user_id
       AND timeslot && tsrange('2013-12-31 18:40:05','2014-01-01 01:20:05'))
ORDER  BY u.user_id;

登录的排除约束对于这些查询非常有用。它是由一个多GiST索引实现的，使得这些查找非常快。

Answer 2

处理这类查询的一种方法是使用聚合和having子句进行筛选。现在还不清楚您的查询到底在做什么(例如，timed_requests和logins是什么)。

下面封装了逻辑：

select user_id
from timed_requests 
group by user_id
having sum(case when timeslot && tsrange('2013-12-31 16:20:05','2013-12-31 17:40:05') then 1 else 0 end) > 0 and
       sum(case when timeslot && tsrange('2014-01-04 14:30:45','2014-01-04 17:20:56') then 1 else 0 end) > 0 and
       sum(case when timeslot && tsrange('2014-01-05 15:09:55','2014-01-05 16:00:39') then 1 else 0 end) > 0 and
       sum(case when timeslot && tsrange('2014-01-01 17:20:54','2014-01-01 22:50:57') then 1 else 0 end) > 0 and
       sum(case when timeslot && tsrange('2013-12-31 18:40:05','2014-01-01 01:20:05') then 1 else 0 end) > 0;

having子句中的每个条件都在计算满足特定条件的行数。