sql - 连续日期postgresql

Question

我需要知道每个文档是否没有连续的日期。我有这张 table :

document | the_day  
     1   | 2015-01-01  
     1   | 2015-01-02  
     1   | 2015-01-03  
     1   | 2015-01-04  
     1   | 2015-01-05  
     1   | 2015-01-06  
     2   | 2015-01-01  
     2   | 2015-01-03  
     2   | 2015-01-04  
     2   | 2015-01-05  
     2   | 2015-01-06  
     3   | 2015-01-01  
     3   | 2015-01-02  
     3   | 2015-01-03  
     3   | 2015-01-04  
     3   | 2015-01-05  
     3   | 2015-01-06  

如您所见，只有一个差距:在文档 2 中缺少“2015-01-02”。我想知道这个差距。我有这个选择:

SELECT document, the_day, the_day - lag(the_day) OVER w AS gap
              FROM mytable
              where active=true and fault=false
               WINDOW w AS (ORDER BY document,the_day)

此选择为我提供每个日期的寄存器和间隔，在大多数情况下为 1，但是当结果中开始另一个文档时，它给我的间隔是错误的。我不知道这是正确的方法还是使功能...这里是构建表的代码:

--Table: public.test_consecutives

--DROP TABLE public.test_consecutives;

CREATE TABLE public.test_consecutives (
  document  integer,
  the_day   date
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public.test_consecutives
  OWNER TO postgres;
INSERT INTO test_consecutives (document, the_day) VALUES
    (1, '2015-01-01'),
    (1, '2015-01-02'),
    (1, '2015-01-03'),
    (1, '2015-01-04'),
    (1, '2015-01-05'),
    (1, '2015-01-06'),
    (2, '2015-01-01'),
    (2, '2015-01-03'),
    (2, '2015-01-04'),
    (2, '2015-01-05'),
    (2, '2015-01-06'),
    (3, '2015-01-01'),
    (3, '2015-01-02'),
    (3, '2015-01-03'),
    (3, '2015-01-04'),
    (3, '2015-01-05'),
    (3, '2015-01-06');

Answer 1

如果您不指定PARTITION，PostgreSQL 将假定它是整个表。您的查询应包含 PARTITION BY 子句:

SELECT document, the_day, the_day - lag(the_day) OVER w AS gap
    FROM mytable
    where active=true and fault=false
    WINDOW w AS (PARTITION BY document ORDER BY document,the_day)