postgresql - 修改 COALESCE 函数以考虑结果的两个相邻值

Question

我目前正在使用 COALESCE 函数从字段列表中获取第一个 NOT NULL 值。示例:

 COALESCE(header_to_node_13.subsetname, header_to_node_12.subsetname,
 header_to_node_11.subsetname, header_to_node_10.subsetname, 
 header_to_node_9.subsetname, header_to_node_8.subsetname, 
 header_to_node_7.subsetname, header_to_node_6.subsetname,   
 header_to_node_5.subsetname, header_to_node_4.subsetname,
 header_to_node_3.subsetname, header_to_node_2.subsetname,     
 header_to_node_1.subsetname, 
 header_to_node.subsetname, header_to_node.setname) AS prctr1

我需要修改逻辑，以便我也获取 COALESCE 值右侧的值，以便比较这两个值以填充新创建的字段。

例如，如果 COALESCE 值为 header_to_node_5.subsetname 我还需要获取 header_to_node_4.subsetname，这样两者就可以在 CASE 语句中使用。

如果第二个值 (header_to_node_4.subsetname) 为 NULL 我想要第一个值 (header_to_node_5.subsetname)。如果第二个值不是 NULL，我想使用第二个值。

我对如何获取第二个值感到困惑。我正在使用 Greenplum/PostgreSQL 8.2

Answer 1

如果我正确理解您的要求，您有一组有序的字段，可能来自将多个表连接在一起，并且您想要获取第一个非空字段和紧随其后的字段。

在这种情况下，您可以使用 LATERAL :这允许您引用前面的 FROM 项提供的字段:

SELECT id, COALESCE(t.b, t.a) AS result
FROM mytable,
LATERAL (
  SELECT x.v, LEAD(x.v) OVER (ORDER BY x.i) 
  FROM (
    VALUES (header_to_node_13, 1), (header_to_node_12, 2),
           (header_to_node_11, 3), (header_to_node_10, 4),
           (header_to_node_9, 5), (header_to_node_8, 6),
           (header_to_node_7, 7), (header_to_node_6, 8),
           (header_to_node_5, 9), (header_to_node_4, 10),
           (header_to_node_3, 11), (header_to_node_2, 12),
           (header_to_node_1, 13)) AS x(v,i)
  ORDER BY 
     CASE WHEN x.v IS NULL THEN 1 ELSE 0 END,
     x.i
  LIMIT 1 ) AS t(a, b)  

在 LATERAL 子查询中使用 VALUES 实质上构建了一个表，其中包含查询的 COALESCE 函数引用的所有字段。顺序字段 x.i 也包含在 VALUES 中以定义字段顺序。

一旦设置了这个内联表，您就可以轻松地对所有字段执行 ORDER BY 以及 LIMIT 1，以便找到第一个非-空字段。 LEAD 窗口函数用于获取下一个字段。

注意:为了简单起见，我重命名了 OP 中的字段，例如header_to_node_13.subsetname 到 header_to_node_13。此外，我还省略了 COALESCE 中的一些字段。

Demo here