Presto下稀疏字段场景无需UNNEST统计商品收藏量的SQL实现方法

核心思路 #

利用Presto内置的数组匹配函数直接做表关联，避免UNNEST展开数组导致的行数爆炸，更适配大用户量、收藏字段稀疏的场景。

实现SQL #

SELECT
  i.id AS `item id`,
  COUNT(u.id) AS `favorite count`
FROM Item i
LEFT JOIN User u
  ON contains(u.favorite_itemset, i.id)
GROUP BY i.id
ORDER BY i.id

方案说明 #

• 关联逻辑通过contains(array<T>, T)函数完成，直接判断当前商品id是否属于用户的收藏集合，全程不需要对数组做UNNEST操作
• 左连接保证未被任何用户收藏的商品也会被纳入统计，计数为0，完全匹配期望的输出结果
• 针对大User表的优化建议：可先对User表做前置过滤，比如排除注销用户、空收藏数组的用户，减少参与关联的数据集大小，进一步提升执行效率
• 类型对齐注意：若Item表id字段的类型和favorite_itemset内元素的类型不一致，需先做类型转换再调用contains函数，避免匹配失败，示例：contains(cast(u.favorite_itemset as array<bigint>), cast(i.id as bigint))

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