CODEWITHSUNDEEP
sqlpostgresqlgroupingwindow-functions
djq
djq

Reputation: 15286

Identifying results by group and rank using SQL

I have a table with the following structure:

id          timestamp       area
717416915   18:30:53.063    25.691601
717416915   18:31:34.863    31.200506
717416915   18:32:23.665    25.690088
1994018321  18:32:45.467    37.409171
1994018321  18:33:19.612    37.409171
424164505   18:36:16.634    18.22091
424164505   18:36:36.899    18.210754
424164505   18:37:08.614    19.829266
2394018356  18:37:27.231    79.31705

What I want to do is to summarize the values in such a way that I can identify the area per id, ordered by timestamp. For example if I wanted the first area value it would be the following:

id          timestamp       area_1
717416915   18:30:53.063    25.691601
1994018321  18:32:45.467    37.409171
424164505   18:36:16.634    18.22091
2394018356  18:37:27.231    79.31705

And if I wanted to get the second area value per id it would be the following:

id          timestamp       area_2
717416915   18:31:34.863    31.200506
1994018321  18:33:19.612    37.409171
424164505   18:36:36.899    18.210754

I understand that I need to sort by time, and then identify the first value per id. I don't quite understand how to do this. What I have tried doing is the following (which is not running, as I am still a little unclear on how to use the OVER function).

WITH T AS (
    SELECT * OVER(PARTITION BY a.id ORDER BY a.timestamp) AS rnk
    FROM mytable AS a
) 
SELECT area as area_1
FROM T
WHERE rnk = 1
GROUP BY a.id
ORDER BY a.timestamp;

I was planning to use rnk=2 etc, to get subsequent area values per id.

Upvotes: 7

Views: 10465

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

With unique timestamp values, row_number() does the same as rank(), a bit faster:

WITH t AS (
   SELECT *, row_number() OVER (PARTITION BY id ORDER BY ts) AS rn
   FROM   tbl
   ) 
SELECT id, ts, area AS area1
FROM   t
WHERE  rn = 1
ORDER  BY id, ts;

There is a shorter way:

SELECT DISTINCT ON (id)
       id
     , nth_value(ts  , 1) OVER w  AS ts
     , nth_value(area, 1) OVER w  AS area_n
FROM   tbl
WINDOW w AS (PARTITION BY id ORDER BY ts);

Should perform similarly.

nth_value(ts, 1) is really just a noisy way of saying first_value(ts), but you can also nth_value(ts, 2) etc, like you pondered.

Upvotes: 4

McGarnagle
McGarnagle

Reputation: 102743

The syntax should be as follows:

SELECT RANK() OVER(PARTITION BY a.id ORDER BY a.timestamp) AS rnk

Upvotes: 14

Related Questions