Forme grupos de linhas consecutivas com o mesmo valor

1. Funções de janela mais subconsultas

Conte os passos para formar grupos, semelhante à ideia de Evan , com modificações e correções:

SELECT id_type
     , min(date) AS begin
     , max(date) AS end
     , count(*)  AS row_ct  -- optional addition
FROM  (
   SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp
   FROM  (
      SELECT date, id_type
           , lag(id_type, 1, id_type) OVER (ORDER BY date) <> id_type AS step
      FROM   tmp
      ) sub1
   ) sub2
GROUP  BY id_type, grp
ORDER  BY min(date);

Isso pressupõe que as colunas envolvidas sejam NOT NULL. Caso contrário, você precisa fazer mais.

Também supondo dateser definido UNIQUE, caso contrário você precisa adicionar um desempate às ORDER BYcláusulas para obter resultados determinísticos. Como: ORDER BY date, id.

Explicação detalhada (resposta a pergunta muito semelhante):

• Selecione a sequência contínua mais longa

Observe em particular:

• Em casos relacionados, lag()com 3 parâmetros pode ser essencial cobrir o canto da primeira (ou última) linha de forma elegante. (O 3º parâmetro é usado como padrão se não houver nenhuma linha anterior (próxima).

    lag(id_type, 1, id_type) OVER ()
  

Como estamos interessados ​​apenas em uma mudança real de id_type( TRUE), isso não importa neste caso em particular. NULLe FALSEambos não contam como step.

• count(step OR NULL) OVER (ORDER BY date)é a sintaxe mais curta que também funciona no Postgres 9.3 ou anterior. count()conta apenas valores não nulos ...

No Postgres moderno, a sintaxe mais limpa e equivalente seria:

    count(step) FILTER (WHERE step) OVER (ORDER BY date)

Detalhes:

• Para desempenho absoluto, SUM é mais rápido ou COUNT?

2. Subtraia duas funções de janela, uma subconsulta

Semelhante à ideia de Erik com modificações:

SELECT min(date) AS begin
     , max(date) AS end
     , id_type
FROM  (
   SELECT date, id_type
        , row_number() OVER (ORDER BY date)
        - row_number() OVER (PARTITION BY id_type ORDER BY date) AS grp
   FROM   tmp
   ) sub
GROUP  BY id_type, grp
ORDER  BY min(date);

Se datefor definido UNIQUE, como mencionei acima, dense_rank()seria inútil, pois o resultado é o mesmo que for row_number()e este último é substancialmente mais barato.

Se nãodate estiver definido (e não sabemos se as únicas duplicatas estão em ), todas essas consultas são inúteis, pois o resultado é arbitrário.UNIQUE(date, id_type)

Além disso, uma subconsulta geralmente é mais barata que um CTE no Postgres. Use CTEs apenas quando precisar deles.

Respostas relacionadas com mais explicações:

• GROUP BY e valores numéricos sequenciais agregados
• Agrupar por atributo de repetição
• GROUP BY sequência ininterrupta de logs para o mesmo local

Em casos relacionados em que já temos um número em execução na tabela, podemos nos contentar com uma única função de janela:

• Classificação com base na sequência de datas

3. Desempenho superior com a função plpgsql

Como essa pergunta se tornou inesperadamente popular, adicionarei outra solução para demonstrar o melhor desempenho.

SQL tem muitas ferramentas sofisticadas para criar soluções com sintaxe curta e elegante. Mas uma linguagem declarativa tem seus limites para requisitos mais complexos que envolvem elementos procedimentais.

Uma solução procedural com uma função do lado do servidor é mais rápida para isso do que qualquer coisa postada até agora, porque precisa apenas de uma única varredura sequencial sobre a tabela e uma única operação de classificação . Se um índice de ajuste estiver disponível, mesmo que seja apenas uma varredura de índice único.

CREATE OR REPLACE FUNCTION f_tmp_groups()
  RETURNS TABLE (id_type int, grp_begin timestamp, grp_end timestamp)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _row  tmp;                       -- use table type for row variable
BEGIN
   FOR _row IN
      TABLE tmp ORDER BY date       -- add more columns for deterministic order 
   LOOP
      CASE _row.id_type = id_type 
      WHEN TRUE THEN                -- same group continues
         grp_end := _row.date;      -- remember last date so far
      WHEN FALSE THEN               -- next group starts
         RETURN NEXT;               -- return result for last group
         id_type   := _row.id_type;
         grp_begin := _row.date;
         grp_end   := _row.date;
      ELSE                          -- NULL for 1st row
         id_type   := _row.id_type; -- remember row data for starters
         grp_begin := _row.date;
         grp_end   := _row.date;
      END CASE;
   END LOOP;

   RETURN NEXT;                     -- return last result row      
END
$func$;

Ligar:

SELECT * FROM f_tmp_groups();

Teste com:

EXPLAIN (ANALYZE, TIMING OFF)  -- to focus on total performance
SELECT * FROM  f_tmp_groups();

Você pode tornar a função genérica com tipos polimórficos e passar o tipo de tabela e os nomes das colunas. Detalhes:

• Refatorar uma função PL/pgSQL para retornar a saída de várias consultas SELECT

Se você não quiser ou não puder persistir uma função para isso, valeria a pena criar uma função temporária na hora. Custa alguns ms.

• How to create a temporary function in PostgreSQL?

db<>fiddle here - comparing performance for all three. (Building on Jack's test case, modified.)

dbfiddle for Postgres 8.4, where performance differences are even bigger. (Not operational any more.)

You can do this as a simple subtraction of ROW_NUMBER() operations (or if your dates are not unique, though still unique per id_type, then you can use DENSE_RANK() instead, though it will be a more expensive query):

WITH IdTypes AS (
   SELECT
      date,
      id_type,
      Row_Number() OVER (ORDER BY date)
         - Row_Number() OVER (PARTITION BY id_type ORDER BY date)
         AS Seq
   FROM
      tmp
)
SELECT
   Min(date) AS begin,
   Max(date) AS end,
   id_type
FROM IdTypes
GROUP BY id_type, Seq
ORDER BY begin
;

See this work at DB Fiddle (or see the DENSE_RANK version)

Result:

begin                  end                    id_type
---------------------  ---------------------  -------
2017-01-10 07:19:21    2017-01-10 07:19:25    3
2017-01-10 07:19:26    2017-01-10 07:19:26    5
2017-01-10 07:19:27.1  2017-01-10 07:19:27.1  3
2017-01-10 07:19:28    2017-01-10 07:19:29    5
2017-01-10 07:19:30.1  2017-01-10 07:19:30.1  3
2017-01-10 07:19:31    2017-01-10 07:19:31    5
2017-01-10 07:19:32    2017-01-10 07:19:32    3
2017-01-10 07:19:33.1  2017-01-10 07:19:37.1  5

Logically, you can think of this as a simple DENSE_RANK() with a PREORDER BY, that is, you want the DENSE_RANK of all the items that are ranked together, and you want them ordered by the dates, you just have to deal with the pesky problem of the fact that at each change in the date, DENSE_RANK will increment. You do that by using the expression as I showed you above. Imagine if you had this syntax: DENSE_RANK() OVER (PREORDER BY date, ORDER BY id_type) where the PREORDER is excluded from the ranking calculation and only the ORDER BY is counted.

Note that it's important to GROUP BY both the generated Seq column as well as the id_type column. Seq is NOT unique by itself, there can be overlaps--you must also group by id_type.

For further reading on this topic:

• Detect changes between row values—read the See It For Yourself section.
• Or this simpler explanation

That first link gives you some code you can use if you wanted the begin or end date to be the same as the previous or next period's end/begin date (so there are no gaps). Plus other versions that could assist you in your query. Though they have to be translated from SQL Server syntax...

No Postgres 8.4 você pode usar uma função RECURSIVA .

Como eles fazem isso

A função recursiva adiciona um nível a cada id_type diferente, selecionando as datas uma a uma em ordem decrescente.

       date           | id_type | lv
--------------------------------------
2017-01-10 07:19:21.0      3       8
2017-01-10 07:19:22.0      3       8
2017-01-10 07:19:23.1      3       8
2017-01-10 07:19:24.1      3       8
2017-01-10 07:19:25.0      3       8
2017-01-10 07:19:26.0      5       7
2017-01-10 07:19:27.1      3       6
2017-01-10 07:19:28.0      5       5
2017-01-10 07:19:29.0      5       5
2017-01-10 07:19:30.1      3       4
2017-01-10 07:19:31.0      5       3
2017-01-10 07:19:32.0      3       2
2017-01-10 07:19:33.1      5       1
2017-01-10 07:19:35.0      5       1
2017-01-10 07:19:36.1      5       1
2017-01-10 07:19:37.1      5       1

Em seguida, use MAX(date), MIN(date) agrupando por nível, id_type para obter o resultado desejado.

with RECURSIVE rdates as 
(
    (select   date, id_type, 1 lv 
     from     yourTable
     order by date desc
     limit 1
    )
    union
    (select    d.date, d.id_type,
               case when r.id_type = d.id_type 
                    then r.lv 
                    else r.lv + 1 
               end lv    
    from       yourTable d
    inner join rdates r
    on         d.date < r.date
    order by   date desc
    limit      1)
)
select   min(date) StartDate,
         max(date) EndDate,
         id_type
from     rdates
group by lv, id_type
;

+---------------------+---------------------+---------+
| startdate           |       enddate       | id_type |
+---------------------+---------------------+---------+
| 10.01.2017 07:19:21 | 10.01.2017 07:19:25 |    3    |
| 10.01.2017 07:19:26 | 10.01.2017 07:19:26 |    5    |
| 10.01.2017 07:19:27 | 10.01.2017 07:19:27 |    3    |
| 10.01.2017 07:19:28 | 10.01.2017 07:19:29 |    5    |
| 10.01.2017 07:19:30 | 10.01.2017 07:19:30 |    3    |
| 10.01.2017 07:19:31 | 10.01.2017 07:19:31 |    5    |
| 10.01.2017 07:19:32 | 10.01.2017 07:19:32 |    3    |
| 10.01.2017 07:19:33 | 10.01.2017 07:19:37 |    5    |
+---------------------+---------------------+---------+

Confira: http://rextester.com/WCOYFP6623

Por alguns pontos,

• Não chame uma tabela não temporária tmpque fica confusa.
• Não use texto para timestamps (você está fazendo isso no seu exemplo, podemos dizer porque o timestamp não foi truncado e tem .0)
• Não chame um campo que tenha tempo nele date. Se tiver data e hora, é um carimbo de data/hora (e armazene-o como um)

Melhor usar uma função de janela ..

SELECT id_type, grp, min(date), max(date)
FROM (
  SELECT date, id_type, count(is_reset) OVER (ORDER BY date) AS grp
  FROM (
    SELECT date, id_type, CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS is_reset
    FROM tmp
  ) AS t
) AS g
GROUP BY id_type, grp
ORDER BY min(date);

Saídas

 id_type | grp |          min          |          max          
---------+-----+-----------------------+-----------------------
       3 |   0 | 2017-01-10 07:19:21.0 | 2017-01-10 07:19:25.0
       5 |   1 | 2017-01-10 07:19:26.0 | 2017-01-10 07:19:26.0
       3 |   2 | 2017-01-10 07:19:27.1 | 2017-01-10 07:19:27.1
       5 |   3 | 2017-01-10 07:19:28.0 | 2017-01-10 07:19:29.0
       3 |   4 | 2017-01-10 07:19:30.1 | 2017-01-10 07:19:30.1
       5 |   5 | 2017-01-10 07:19:31.0 | 2017-01-10 07:19:31.0
       3 |   6 | 2017-01-10 07:19:32.0 | 2017-01-10 07:19:32.0
       5 |   7 | 2017-01-10 07:19:33.1 | 2017-01-10 07:19:37.1
(8 rows)

Explicação

Primeiro precisamos de resets.. Nós os geramos comlag()

SELECT date, id_type, CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS is_reset
FROM tmp
ORDER BY date;

         date          | id_type | is_reset 
-----------------------+---------+----------
 2017-01-10 07:19:21.0 |       3 |         
 2017-01-10 07:19:22.0 |       3 |         
 2017-01-10 07:19:23.1 |       3 |         
 2017-01-10 07:19:24.1 |       3 |         
 2017-01-10 07:19:25.0 |       3 |         
 2017-01-10 07:19:26.0 |       5 |        1
 2017-01-10 07:19:27.1 |       3 |        1
 2017-01-10 07:19:28.0 |       5 |        1
 2017-01-10 07:19:29.0 |       5 |         
 2017-01-10 07:19:30.1 |       3 |        1
 2017-01-10 07:19:31.0 |       5 |        1
 2017-01-10 07:19:32.0 |       3 |        1
 2017-01-10 07:19:33.1 |       5 |        1
 2017-01-10 07:19:35.0 |       5 |         
 2017-01-10 07:19:36.1 |       5 |         
 2017-01-10 07:19:37.1 |       5 |         
(16 rows)

Então contamos para obter grupos.

SELECT date, id_type, count(is_reset) OVER (ORDER BY date) AS grp
FROM (
  SELECT date, id_type, CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS is_reset
  FROM tmp
  ORDER BY date
) AS t
ORDER BY date

         date          | id_type | grp 
-----------------------+---------+-----
 2017-01-10 07:19:21.0 |       3 |   0
 2017-01-10 07:19:22.0 |       3 |   0
 2017-01-10 07:19:23.1 |       3 |   0
 2017-01-10 07:19:24.1 |       3 |   0
 2017-01-10 07:19:25.0 |       3 |   0
 2017-01-10 07:19:26.0 |       5 |   1
 2017-01-10 07:19:27.1 |       3 |   2
 2017-01-10 07:19:28.0 |       5 |   3
 2017-01-10 07:19:29.0 |       5 |   3
 2017-01-10 07:19:30.1 |       3 |   4
 2017-01-10 07:19:31.0 |       5 |   5
 2017-01-10 07:19:32.0 |       3 |   6
 2017-01-10 07:19:33.1 |       5 |   7
 2017-01-10 07:19:35.0 |       5 |   7
 2017-01-10 07:19:36.1 |       5 |   7
 2017-01-10 07:19:37.1 |       5 |   7
(16 rows)

Em seguida, envolvemos em uma subseleção GROUP BYe ORDERselecionamos o min max (range)

SELECT id_type, grp, min(date), max(date)
FROM (
  .. stuff
) AS g
GROUP BY id_type, grp
ORDER BY min(date);

Here is another method, which is similar to Evan's and Erwin's in that it uses LAG to determine islands. It differs from those solutions in that it uses only one level of nesting, no grouping, and considerably more window functions:

SELECT
  id_type,
  date AS begin,
  COALESCE(
    LEAD(prev_date) OVER (ORDER BY date ASC),
    last_date
  ) AS end
FROM
  (
    SELECT
      id_type,
      date,
      LAG(date) OVER (ORDER BY date ASC) AS prev_date,
      MAX(date) OVER () AS last_date,
      CASE id_type
        WHEN LAG(id_type) OVER (ORDER BY date ASC)
        THEN 0
        ELSE 1
      END AS is_start
    FROM
      tmp
  ) AS derived
WHERE
  is_start = 1
ORDER BY
  date ASC
;

The is_start computed column in the nested SELECT marks the beginning of each island. Additionally, the nested SELECT exposes each row's previous date and the dataset's last date.

For rows that are the beginnings of their respective islands, the previous date effectively is the previous island's ending date. That is what the main SELECT uses it as. It picks only the rows matching the is_start = 1 condition, and for each returned row it shows the row's own date as begin and the following row's prev_date as end. As the last row does not have a following row, LEAD(prev_date) returns a null for it, for which the COALESCE function substitutes the dataset's last date.

You can play with this solution at dbfiddle.

When introducing additional columns identifying the islands, you will probably want to introduce a PARTITION BY subclause to each window function's OVER clause. For instance, if you want to detect the islands within groups defined by a parent_id, the above query will probably need to look like this:

SELECT
  parent_id,
  id_type,
  date AS begin,
  COALESCE(
    LEAD(prev_date) OVER (PARTITION BY parent_id ORDER BY date ASC),
    last_date
  ) AS end
FROM
  (
    SELECT
      parent_id,
      id_type,
      date,
      LAG(date) OVER (PARTITION BY parent_id ORDER BY date ASC) AS prev_date,
      MAX(date) OVER (PARTITION BY parent_id) AS last_date,
      CASE id_type
        WHEN LAG(id_type) OVER (PARTITION BY parent_id ORDER BY date ASC)
        THEN 0
        ELSE 1
      END AS is_start
    FROM
      tmp
  ) AS derived
WHERE
  is_start = 1
ORDER BY
  date ASC
;

And if you decide to go with either Erwin's or Evan's solution, I believe a similar change will need to be added to it as well.

More out of academic interest than as a practical solution, you can also achieve this with a user-defined aggregate. Like the other solutions, this will work even on Postgres 8.4, but as others have commented, please upgrade if you can.

The aggregate handles null as if it is a different foo_type, so runs of nulls would be given the same grp — that may or may not be what you want.

create function grp_sfunc(integer[],integer) returns integer[] language sql as $$
  select array[$1[1]+($1[2] is distinct from $2 or $1[3]=0)::integer,$2,1];
$$;

create function grp_finalfunc(integer[]) returns integer language sql as $$
  select $1[1];
$$;

create aggregate grp(integer)(
  sfunc = grp_sfunc
, stype = integer[]
, finalfunc = grp_finalfunc
, initcond = '{0,0,0}'
);

select min(foo_at) begin_at, max(foo_at) end_at, foo_type
from (select *, grp(foo_type) over (order by foo_at) from foo) z
group by grp, foo_type
order by 1;

begin_at              | end_at                | foo_type
:-------------------- | :-------------------- | -------:
2017-01-10 07:19:21   | 2017-01-10 07:19:25   |        3
2017-01-10 07:19:26   | 2017-01-10 07:19:26   |        5
2017-01-10 07:19:27.1 | 2017-01-10 07:19:27.1 |        3
2017-01-10 07:19:28   | 2017-01-10 07:19:29   |        5
2017-01-10 07:19:30.1 | 2017-01-10 07:19:30.1 |        3
2017-01-10 07:19:31   | 2017-01-10 07:19:31   |        5
2017-01-10 07:19:32   | 2017-01-10 07:19:32   |        3
2017-01-10 07:19:33.1 | 2017-01-10 07:19:37.1 |        5

dbfiddle here

Isso pode ser feito RECURSIVE CTEpara passar o "tempo de início" de uma linha para a próxima, e algumas preparações extras (de conveniência).

Esta consulta retorna o resultado que você deseja:

WITH RECURSIVE q AS
(
    SELECT
        id_type,
        "date",
        /* We compute next id_type for convenience, plus row_number */
        row_number()  OVER (w) AS rn,
        lead(id_type) OVER (w) AS next_id_type
    FROM
        t
    WINDOW
        w AS (ORDER BY "date") 
)

após a preparação... parte recursiva

, rec AS 
(
    /* Anchor */
    SELECT
        q.rn,
        q."date" AS "begin",
        /* When next_id_type is different from Look also at **next** row to find out whether we need to mark an end */
        case when q.id_type is distinct from q.next_id_type then q."date" END AS "end",
        q.id_type
    FROM
        q
    WHERE
        rn = 1

    UNION ALL

    /* Loop */
    SELECT
        q.rn,
        /* We keep copying 'begin' from one row to the next while type doesn't change */
        case when q.id_type = rec.id_type then rec.begin else q."date" end AS "begin",
        case when q.id_type is distinct from q.next_id_type then q."date" end AS "end",
        q.id_type
    FROM
        rec
        JOIN q ON q.rn = rec.rn+1
)
-- We filter the rows where "end" is not null, and project only needed columns
SELECT
    "begin", "end", id_type
FROM
    rec
WHERE
    "end" is not null ;

Você pode verificar isso em http://rextester.com/POYM83542

Este método não escala bem. Para uma tabela de 8_641 linhas, são necessários 7 segundos, para uma tabela com o dobro desse tamanho, são necessários 28 segundos. Mais algumas amostras mostram tempos de execução parecidos com O(n^2).

O método de Evan Carrol leva menos de 1s (ou seja: vá em frente!), e se parece com O(n). As consultas recursivas são absolutamente ineficientes e devem ser consideradas o último recurso.