使用SQL按组和排名识别结果

Question

我有一个具有以下结构的表格：

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

我想要做的是总结这些值，以便我可以按时间戳排序并确定每个id对应的面积。例如，如果我想要第一个面积值，它将是以下内容：

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

如果我想要获取每个id的第二个area值，那么应该这样做：

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

我知道需要按时间排序，然后识别每个id的第一个值。但我不太明白如何做到这一点。我尝试了以下方法（还不能运行，因为我还不太清楚如何使用OVER函数）。

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;

我打算使用rnk=2等方式，以便获取每个id的后续区域值。

- djq

---

没有记录与 id=2394018356。 - wildplasser

Answer 1

语法应该像这样：

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

- McGarnagle

Answer 2

使用唯一的时间戳值，row_number() 和 rank() 执行的操作相同，但速度稍快：

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;

有一个更简短的方法：

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);

应该表现类似。

nth_value(ts, 1)实际上只是一个喧闹的说法，就像first_value(ts)一样，但你也可以像你思考的那样使用nth_value(ts, 2)等。

- Erwin Brandstetter

---

谢谢Erwin - 我之前不知道窗口函数。 - djq