Druid SQL如何对多值字段跨记录实现去重计数？

需求：针对多值字段实现跨不同记录的去重计数，单条记录的数组中同一值仅计数一次。

示例数据 #

shippingSpeed字段的多值数据：

[standard, standard, standard, ground]
[standard,ground]
[ground,ground]

期望结果 #

统计每个值出现的不同记录数（单条记录内重复值只算1次）：

standard 2
ground 3

错误尝试及结果 #

以下查询未对单条记录内的重复值去重，会统计数组内所有元素的总数：

SELECT
  "shippingSpeed", count(*)
FROM orders
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 2 ASC

得到错误结果：

standard 4
ground 4

解决方案 #

使用Druid的ARRAY_DISTINCT函数先对单条记录的多值字段去重，再通过UNNEST展开数组，最后分组统计：

SELECT
  speed,
  COUNT(*) AS record_count
FROM (
  SELECT
    UNNEST(ARRAY_DISTINCT("shippingSpeed")) AS speed
  FROM orders
  WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
)
GROUP BY speed
ORDER BY record_count ASC

逻辑说明

1. ARRAY_DISTINCT("shippingSpeed")：对每条记录的多值字段去重，确保单条记录内同一值仅保留一个
2. UNNEST(...)：将去重后的数组展开为单行数据，每条记录的每个唯一值对应一行
3. 外层查询按speed分组，COUNT(*)统计的就是该值在多少条不同记录中出现过，完全匹配需求

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