基于数组对象聚合：统计每个描述符对应的唯一客户ID数量

问题背景 #

有如下格式的Snowflake数据（超过3万行，数组中唯一描述符数量未知）：

with data(custid, descriptors) as (
    select 1, ['Corporate', 'fun times', 'but not really']
    union all 
    select 2, ['lame times', 'Corporate', 'boring']
    union all 
    select 3, ['boring', 'Corporate', 'fun times', 'but not really']
)
    
select 
* 
from data

需要统计每个描述符对应的唯一客户ID数量，单个描述符可以用以下语句查询：

select 
count(distinct custid)
from data
where array_contains('Corporate'::variant, descriptors)

但希望一次性统计所有数组元素的对应结果，最终得到如下格式的表格：

不知道如何自动提取所有数组元素并批量统计，尝试过array_distinct(array_agg())但不知后续步骤，希望得到简便方法。

解决方案 #

使用Snowflake的UNNEST函数将数组展开为行，再分组统计即可，不需要复杂的游标或循环：

with data(custid, descriptors) as (
    select 1, ['Corporate', 'fun times', 'but not really']
    union all 
    select 2, ['lame times', 'Corporate', 'boring']
    union all 
    select 3, ['boring', 'Corporate', 'fun times', 'but not really']
)
select 
    descriptor,
    count(distinct custid) as n_custids
from data,
     unnest(descriptors) as descriptor  -- 展开数组为单个行记录
group by descriptor
order by n_custids desc, descriptor;

步骤说明 #

• UNNEST展开数组：通过unnest(descriptors)将每个客户的描述符数组拆分成多行，每个描述符对应一行原客户ID。
• 分组统计：按descriptor分组，用count(distinct custid)计算每个描述符关联的唯一客户数。
• 排序（可选）：添加order by可以让结果按客户数或描述符排序，更易查看。

这个方法高效简洁，适合处理大规模数据（3万行完全没问题），无需手动提取唯一值再逐个查询。

内容的提问来源于stack exchange，提问作者Steven