选择组内具有第二大值的行

Question

我有一个这样的表(tbl)：

+----+-----+------+-----+
| pk | grp | attr | val |
+----+-----+------+-----+
|  0 |   0 | ohif |   4 |
|  1 |   0 | foha |  56 |
|  2 |   0 | slns |   2 |
|  3 |   1 | faso |  11 |
|  4 |   1 | tepj |   4 |
|  5 |   2 | bnda |  12 |
|  6 |   2 | ojdf |   9 |
|  7 |   2 | anaw |   1 |
+----+-----+------+-----+

我想从每个组中选择一行，特别是每个组的val第二高的那一行。

也就是说，我想要这个表：

+----+-----+------+-----+
| pk | grp | attr | val |
+----+-----+------+-----+
|  1 |   0 | ohif |   4 |
|  3 |   1 | tepj |   4 |
|  5 |   2 | ojdf |   9 |
+----+-----+------+-----+

这是我想出的解决方案：

SELECT DISTINCT ON (grp)
pk,
(
    SELECT innertbl.grp
    FROM tbl AS innertbl
    WHERE innertbl.grp = tbl.grp
    ORDER BY innertbl.val DESC
    LIMIT 1 OFFSET 1
) AS grp,
(
    SELECT innertbl.attr
    FROM tbl AS innertbl
    WHERE innertbl.grp = tbl.grp
    ORDER BY innertbl.val DESC
    LIMIT 1 OFFSET 1
) AS attr,
(
    SELECT innertbl.val
    FROM tbl AS innertbl
    WHERE innertbl.grp = tbl.grp
    ORDER BY innertbl.val DESC
    LIMIT 1 OFFSET 1
) AS val
FROM tbl

然而，这是低效的，因为它需要为每个组的每个列选择一个子查询。

我在Postgres 10号。

Answer 1

您可以使用子查询中的窗口函数来获取所需的内容：

SELECT
    pk, grp, attr, val
FROM (
    SELECT
        pk, grp, attr, val,
        row_number() OVER(PARTITION BY grp ORDER BY val DESC) AS seq
    FROM
        tbl
    ) data
WHERE
    seq = 2

row_number()提供组内的序列(使用PARTITION BY)。

Answer 2

@Nick的答案就是我要发布的，但我想补充一点，如果你想跳过重复的内容，你可以使用dense_rank函数：

SELECT pk, grp, attr, val FROM (
  SELECT
    *,
    DENSE_RANK() OVER (PARTITION BY grp ORDER BY val DESC) AS seqnum
  FROM
    mytable
) t WHERE seqnum = 2;