时间戳

首页 > 解决方案 > 选择组内具有第二高值的行

postgresql - 选择组内具有第二高值的行

问题描述

我有一个这样的表（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 上。

标签： postgresqlaggregate-functions

解决方案

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

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）。

推荐阅读