sql - 当行值非零时获取列表聚合中的列名称

Question

您好，我在尝试从具有非零行值的表中获取列名时遇到问题。获取以下示例数据:

create table your_table
(
  XUUID VARCHAR(100), 
  COUNT1 INT,
  COUNT2 INT,
  COUNT3 INT,
  COUNT4 INT
); 

INSERT INTO your_table values 
('id1', 2, 3, 0, 0),
('id2', 0, 0, 1, 0),
('id3', 0, 0, 0, 0),
('id4', 3, 0, 0, 0)  

我希望结果的格式为:

xuuid |  non_zero_col_agg    |
------|----------------------|
id1   | ['count1', 'count2'] |
id2   | ['count3']           |
id3   | []                   |
id4   | ['count1']           |

我尝试使用以下 JavaScript 函数来执行此操作:

create or replace function labels(count1 INT, count2 INT, count3 INT, count4 INT)
    returns listagg
    language javascript 
    strict
    as
    $$
    var res = [];
    var dict = {
        'count1' : COUNT1,
        'count2' : COUNT2,
        'count3' : COUNT3,
        'count4' : COUNT4
    };
    for (var key in dictionary){
        if (dict[key] > 0) {
            res.push(key)
        }
    };
    return(res);
    $$
;

但它返回了以下错误:SQL 编译错误:不支持的数据类型“LISTAGG”。

有办法解决这个问题吗？该解决方案可以作为函数，也可以不作为函数，我选择将其作为函数，因为我不知道如何使用常规 SQL 来实现它。

谢谢

Answer 1

SQL 的表达能力足以在没有 UDF 的情况下解决这种情况。

这里是使用 OBJECT_CONSTRUCT 解决该问题的示例。该解决方案是动态的，它可以接受行级别上存在的任意数量的“countX”列:

WITH cte AS (
  SELECT xuuid, OBJECT_CONSTRUCT(*) AS c
  FROM your_table
)
SELECT XUUID, ARRAY_AGG(CASE WHEN s.KEY LIKE 'COUNT%' 
                             AND TRY_CAST(s.VALUE::STRING AS INT) > 0 THEN s.key END)
FROM cte
,LATERAL FLATTEN(input => c) s
GROUP BY XUUID
ORDER BY XUUID;

输出: