Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Recursive CTE
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
DROP TABLE IF EXISTS t ; CREATE TABLE t ("date" timestamp without time zone, id_type integer) ; INSERT INTO t ("date", id_type) VALUES ('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), ('2017-01-10 07:19:27.1', 3), ('2017-01-10 07:19:28.0', 5), ('2017-01-10 07:19:29.0', 5), ('2017-01-10 07:19:30.1', 3), ('2017-01-10 07:19:31.0', 5), ('2017-01-10 07:19:32.0', 3), ('2017-01-10 07:19:33.1', 5), ('2017-01-10 07:19:35.0', 5), ('2017-01-10 07:19:36.1', 5), ('2017-01-10 07:19:37.1', 5) ; 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") ) , 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 ;
absolute service time: 0,49 sec
edit mode
|
history
begin
end
id_type
1
10.01.2017 07:19:21
10.01.2017 07:19:25
3
2
10.01.2017 07:19:26
10.01.2017 07:19:26
5
3
10.01.2017 07:19:27
10.01.2017 07:19:27
3
4
10.01.2017 07:19:28
10.01.2017 07:19:29
5
5
10.01.2017 07:19:30
10.01.2017 07:19:30
3
6
10.01.2017 07:19:31
10.01.2017 07:19:31
5
7
10.01.2017 07:19:32
10.01.2017 07:19:32
3
8
10.01.2017 07:19:33
10.01.2017 07:19:37
5