CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
losthorse
losthorse

Reputation: 1570

Postgres 9.1 - Getting the next value

I have some data (sorted by "TimeStamp") that looks like this:

      Key     |        TimeStamp        |   Column3   |   ColumnN
--------------+-------------------------+-------------+--------------
       1      |   2012-01-01 08:00:23   |     ...     |     ...
       2      |   2012-01-01 08:01:07   |     ...     |     ...
       3      |   2012-01-01 08:02:56   |     ...     |     ...
       6      |   2012-01-01 08:02:56   |     ...     |     ...
       4      |   2012-01-01 08:03:39   |     ...     |     ...
       5      |   2012-01-01 08:04:32   |     ...     |     ...

I need to select the next value (not merely the next row) of "TimeStamp" for each record.

For example, the above data would look like this:

      Key     |          Begin          |           End           |   Column3   |   ColumnN
--------------+-------------------------+-------------------------+-------------+--------------
       1      |   2012-01-01 08:00:23   |   2012-01-01 08:01:07   |     ...     |     ...
       2      |   2012-01-01 08:01:07   |   2012-01-01 08:02:56   |     ...     |     ...
       3      |   2012-01-01 08:02:56   |   2012-01-01 08:03:39   |     ...     |     ...
       6      |   2012-01-01 08:02:56   |   2012-01-01 08:03:39   |     ...     |     ...
       4      |   2012-01-01 08:03:39   |   2012-01-01 08:04:32   |     ...     |     ...
       5      |   2012-01-01 08:04:32   |   NULL                  |     ...     |     ...

I have been trying to use a window function for this, but I have not been able to get this result. Any thoughts?

Upvotes: 2

Views: 1895

Answers (3)

Vincent Savard
Vincent Savard

Reputation: 35927

Even cooler than dense_rank, you can use the lead window function :

SELECT Key, ts, lead(ts) OVER(ORDER BY ts ASC)
FROM tbl;

Shamelessly stolen sqlfiddle link from Erwin's answer.

Edit: Hmm, actually it doesn't work exactly like you described, since it won't select the next higher value when two values are equal. I won't delete the answer because I think it can be useful in this context, but I'll mark it as community wiki.

Upvotes: 5

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

This should be much faster than correlated subqueries:

WITH x AS (
   SELECT *, dense_rank() OVER (ORDER BY ts) AS rnk
   FROM   tbl
   )
SELECT x.key, x.ts AS ts_begin, y.ts As ts_end
FROM   x
LEFT   JOIN (SELECT DISTINCT ts, rnk FROM x) y ON y.rnk = (x.rnk + 1)
ORDER  BY x.ts

->sqlfiddle

  • Use the window function dense_rank() to get a ranking without gaps in a CTE.
  • Then LEFT JOIN the result to a DISTINCT version of itself by an offset of 1 to get the "next" timestamp (and only a single one).
  • Alternatively, you could use GROUP BY 1,2 instead of DISTINCT for y. I'd expect DISTINCT to be faster here, because the sort order agrees with the ORDER BY of the window function. But check with EXPLAIN ANALYZE and see for yourself.

Upvotes: 4

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28541

You can get the "next" value with subselect like this:

SELECT "Key", 
       t1.Timestamp as "Begin", 
       (SELECT min(t2."TimeStamp") 
        FROM the_table t2
        WHERE t2."TimeStamp" > t1."TimeStamp") as "End",
       column3, ...
FROM the_table t1

But this query can be slow. If you need it to be fast - just write a simple PL/SQL function.

Upvotes: 1

Related Questions