自内联接SQL查询中缺少行

Question

我使用的是PostgreSQL 8.3.8。

我在time_boundaries表中有一个时间边界列表(按日期划分)：

CREATE TABLE role_times_boundaries
(
  role_date DATE,
  time_boundary TIME
);

INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-24'::date, '09:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-24'::date, '10:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '07:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '08:50:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '09:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '12:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '13:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '16:00:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '17:30:00'::time);
INSERT INTO role_times_boundaries (role_date, time_boundary) VALUES ('2013-04-25'::date, '20:00:00'::time);

所以，我有这个表的内容：

 role_date  | time_boundary 
------------+---------------
 2013-04-24 | 09:00:00
 2013-04-24 | 10:00:00
 2013-04-25 | 07:00:00
 2013-04-25 | 08:50:00
 2013-04-25 | 09:00:00
 2013-04-25 | 12:00:00
 2013-04-25 | 13:00:00
 2013-04-25 | 16:00:00
 2013-04-25 | 17:30:00
 2013-04-25 | 20:00:00

目标

我想要构建一个“时间切片列表”表，方法是在"role_times_boundaries“上做一个自内连接，将每个time_boundary作为"start_time"，并在同一日期使用下一个time_boundary (按顺序排列)。目标是要取得这样的结果：

 role_date  | start_time | end_time 
------------+------------+----------
 2013-04-24 | 09:00:00   | 10:00:00
 2013-04-25 | 07:00:00   | 08:50:00
 2013-04-25 | 08:50:00   | 09:00:00
 2013-04-25 | 09:00:00   | 12:00:00
 2013-04-25 | 12:00:00   | 13:00:00
 2013-04-25 | 13:00:00   | 16:00:00
 2013-04-25 | 16:00:00   | 17:30:00
 2013-04-25 | 17:30:00   | 20:00:00

试验性

我试图通过这个SQL查询获得希望的结果。

SELECT role_times_boundaries.role_date,
       role_times_boundaries.time_boundary AS start_time,
       end_time_boundaries.time_boundary AS end_time
FROM role_times_boundaries
INNER JOIN (
             SELECT role_date,
                    time_boundary
             FROM role_times_boundaries
           ) AS end_time_boundaries ON (
                                       role_times_boundaries.role_date = end_time_boundaries.role_date
                                       AND end_time_boundaries.time_boundary = (
                                                                                  SELECT MIN(a_list_of_end_boundaries.time_boundary)
                                                                                  FROM role_times_boundaries AS a_list_of_end_boundaries
                                                                                  WHERE a_list_of_end_boundaries.time_boundary > role_times_boundaries.time_boundary
                                                                                )
                                     )

结果如下：

 role_date  | start_time | end_time 
------------+------------+----------
 2013-04-24 | 09:00:00   | 10:00:00
 2013-04-25 | 07:00:00   | 08:50:00
 2013-04-25 | 08:50:00   | 09:00:00
 2013-04-25 | 12:00:00   | 13:00:00
 2013-04-25 | 13:00:00   | 16:00:00
 2013-04-25 | 16:00:00   | 17:30:00
 2013-04-25 | 17:30:00   | 20:00:00

如果您看得清楚，09:00:00到12:00时间片丢失了！但我还是不明白为什么，仍然找不到我的错误。

Answer 1

解决方案

好的，首先让我们简化一下您的查询：

SELECT
  l.role_date,
  l.time_boundary AS start_time,
  r.time_boundary AS end_time
FROM role_times_boundaries l
INNER JOIN role_times_boundaries AS r ON ( -- You don't need that inner query, it's redundant
  l.role_date = r.role_date
  AND r.time_boundary = (
    SELECT MIN(r2.time_boundary)
    FROM role_times_boundaries AS r2
    WHERE r2.time_boundary > l.time_boundary))

现在的问题是，您正在比较在r2中的所有time_boundarie，而不是受角色日期限制的r2，所以修正查询应该是：

SELECT
  l.role_date,
  l.time_boundary AS start_time,
  r.time_boundary AS end_time
FROM role_times_boundaries l
INNER JOIN role_times_boundaries AS r ON (
  l.role_date = r.role_date
  AND r.time_boundary = (
    SELECT MIN(r2.time_boundary)
    FROM role_times_boundaries AS r2
    -- Note the added restriction:
    WHERE r2.time_boundary > l.time_boundary and r2.role_date = l.role_date))

交替查询

下面是也适用于您的用例，并且可能更具可读性：

select
  l.role_date as role_date,
  l.time_boundary as start_time,
  min(r.time_boundary) as end_time
from role_times_boundaries l
join role_times_boundaries r on
  r.role_date = l.role_date
  and r.time_boundary > l.time_boundary
group by l.role_date, l.time_boundary
order by l.role_date, l.time_boundary

Answer 2

如果升级到PostgreSQL 8.4或更高版本，您可以使用窗口函数 (用甲骨文术语来说是“分析函数”)，例如rank()、row_number()、lead()和lag()

SELECT tb.role_date AS role_date
        , tb.time_boundary AS start_time
        , LEAD (time_boundary) OVER www AS end_time
FROM role_times_boundaries tb
WINDOW www AS (PARTITION BY tb.role_date ORDER BY tb.time_boundary)
        ;

或与前面的查询相同的另一个查询：

SELECT tb.role_date AS role_date
        , tb.time_boundary AS start_time
        , LEAD (time_boundary) OVER ( PARTITION BY tb.role_date ORDER BY tb.time_boundary) AS end_time
FROM role_times_boundaries tb;

这将为您提供以下结果集：

 role_date  | start_time | end_time 
------------+------------+----------
 2013-04-24 | 09:00:00   | 10:00:00
 2013-04-24 | 10:00:00   | 
 2013-04-25 | 07:00:00   | 08:50:00
 2013-04-25 | 08:50:00   | 09:00:00
 2013-04-25 | 09:00:00   | 12:00:00
 2013-04-25 | 12:00:00   | 13:00:00
 2013-04-25 | 13:00:00   | 16:00:00
 2013-04-25 | 16:00:00   | 17:30:00
 2013-04-25 | 17:30:00   | 20:00:00
 2013-04-25 | 20:00:00   | 
(10 rows)

要删除没有end_time的句点，可以将其封装到子查询中：

SELECT role_date , start_time , end_time
FROM (
        SELECT tb.role_date AS role_date
        , tb.time_boundary AS start_time
        , LEAD (time_boundary) OVER ( PARTITION BY tb.role_date ORDER BY tb.time_boundary) AS end_time
        FROM role_times_boundaries tb
        ) sq
WHERE sq.start_time <= sq.end_time;

这将给出以下结果：

 role_date  | start_time | end_time 
------------+------------+----------
 2013-04-24 | 09:00:00   | 10:00:00
 2013-04-25 | 07:00:00   | 08:50:00
 2013-04-25 | 08:50:00   | 09:00:00
 2013-04-25 | 09:00:00   | 12:00:00
 2013-04-25 | 12:00:00   | 13:00:00
 2013-04-25 | 13:00:00   | 16:00:00
 2013-04-25 | 16:00:00   | 17:30:00
 2013-04-25 | 17:30:00   | 20:00:00
(8 rows)

UPDATE:另一个避免使用窗口函数的查询，它通过使用NOT EXISTS关键字来解决问题：

SELECT lo.role_date
        , lo.time_boundary AS start_time
        , hi.time_boundary AS end_time
FROM role_times_boundaries lo
JOIN role_times_boundaries hi
    ON lo.role_date = hi.role_date
    AND lo.time_boundary < hi.time_boundary
    AND NOT EXISTS ( -- eliminate the men in the middle ...
        SELECT * FROM role_times_boundaries nx
        WHERE   nx.role_date = hi.role_date
        AND nx.time_boundary > lo.time_boundary
        AND nx.time_boundary < hi.time_boundary
        );