sql - 如何使用合并或变体来提取超过 1 个值？

Question

这是一个关于 PostgreSQL 中的 COALESCE 的问题。在当前 View 中，我正在使用COALESCE 获取列表中的第一个 NOT NULL 值:

COALESCE(vw_header_to_node_13.subsetname,
vw_header_to_node_12.subsetname, 
vw_header_to_node_11.subsetname,
vw_header_to_node_10.subsetname,
vw_header_to_node_9.subsetname,
vw_header_to_node_8.subsetname,
vw_header_to_node_7.subsetname,
vw_header_to_node_6.subsetname,
vw_header_to_node_5.subsetname,
vw_header_to_node_4.subsetname,
vw_header_to_node_3.subsetname,
vw_header_to_node_2.subsetname,
vw_header_to_node_1.subsetname,
vw_header_to_node.subsetname,
vw_header_to_node.setname)
AS prctr1

我刚刚被告知，我现在需要获取第一个 NOT NULL 字段和后面的两个字段，而不是只获取第一个 NOT NULL 字段第一个 NOT NULL 字段。例如，如果 vw_header_to_node_8.subsetname 是第一个 NOT NULL 字段，我想获取 vw_header_to_node_8.subsetname、vw_header_to_node_7.subsetname, & vw_header_to_node_6.subsetname。我知道这不是 COALESCE 正常运行的方式，但有谁知道实现此目的的任何变体或方法吗？

Answer 1

如果应从列表中排除所有空值，则此方法应该有效:

1. 将列表转换为数组，
2. 从数组中删除空值，
3. 选择数组的前三个元素。

例子:

with test as (
    select 
        null::text     as v1,
        'apple'::text  as v2,
        null::text     as v3,
        'banana'::text as v4,
        'pear'::text   as v5)

select a[1] val1, a[2] val2, a[3] val3
from (      
    select 
        array_remove(
            array[v1, v2, v3, v4, v5], null) a
    from test
    ) alias

 val1  |  val2  | val3
-------+--------+------
 apple | banana | pear

如果第二个和第三个值可以为空，我们应该在步骤 2 中从数组中去除第一个空值。Postgres中没有合适的函数，可以自己写。

create function array_ltrim_nulls(arr anyarray)
returns anyarray language plpgsql immutable
as $$
declare 
    i integer;
    l integer = array_length(arr, 1);
begin
    for i in 1..l loop
        if arr[i] is not null then
            return arr[i:l];
        end if;
    end loop;
    return null;
end $$;

with test as (
    select 
        null::text     as v1,
        'apple'::text  as v2,
        null::text     as v3,
        'banana'::text as v4,
        'pear'::text   as v5)

select a[1] val1, a[2] val2, a[3] val3
from (      
    select 
        array_ltrim_nulls(
            array[v1, v2, v3, v4, v5]) a
    from test
    ) alias

 val1  | val2 |  val3
-------+------+--------
 apple |      | banana      

---

为了方便使用，我在我的标准函数集中添加了以下函数。也许此功能对您最有用。函数参数的数量是可变的。您只需要确保所有参数都属于同一类型。

create function array_coalesce (variadic arr anyarray)
returns anyarray language sql immutable
as $$
    select array_remove(arr, null);
$$;

select array_coalesce(null::text, 'apple', null, 'banana', 'pear') arr;
         arr         
---------------------
 {apple,banana,pear}

select array_coalesce(null::int, 1, null, 2, 3, 4, null, 5) arr;
     arr     
-------------
 {1,2,3,4,5}

select (array_coalesce(null::int, 1, null, 2, 3, 4, null, 5))[1:3] arr;
     arr     
-------------
 {1,2,3}