在postgresql中选择一行中的前k列值

Question

我有下面的表格结构

CREATE TABLE myTable (
  id1 INTEGER,

  key1 VARCHAR,
  key2 VARCHAR,
  key3 VARCHAR,
  key4 VARCHAR,
  key5 VARCHAR,

  val1 DOUBLE PRECISION,
  val2 DOUBLE PRECISION,
  val3 DOUBLE PRECISION,
  val4 DOUBLE PRECISION,
  val5 DOUBLE PRECISION
);

我试图编写查询，以选择前3个瓦尔列和它们对应的关键字之间给定的5个关键字-瓦尔对。

类似id1，key1，val1，key2，val2，key3，val3。

SELECT CASE WHEN val1 = GREATEST(val1, val2, val3, val4, val5) THEN key1 
WHEN val2 = .... THEN val2
.....
END AS top_key

  ,GREATEST(val1, val2, val3, val4, val5) AS top_val
FROM myTable
WHERE id1 = ?

Answer 1

试试这个。它基本上为每一行创建一个(key，value)对的“临时”表，然后为每一行选择前三个对。

SELECT t.id, 
    (array_agg((a.k, a.v) ORDER BY a.v DESC))[1] AS kv1,
    (array_agg((a.k, a.v) ORDER BY a.v DESC))[2] AS kv2,
    (array_agg((a.k, a.v) ORDER BY a.v DESC))[3] AS kv3
FROM t
CROSS JOIN LATERAL (
    SELECT * FROM (VALUES (t.k1, t.v1), (t.k2, t.v2), (t.k3, t.v3), (t.k4, t.v4), (t.k5, t.v5)) AS a(k, v)
) AS a
GROUP BY t.id

请点击这里：http://sqlfiddle.com/#！17/6 B0C5/2