如何用Snowflake SQL生成用户点击物品的键值对或排序统计列表？

AI 小北

2026-7-28

解决方案

完全可以通过Snowflake SQL实现需求，所有计算在云端完成，无需将海量数据拉取到本地，避免超时问题。以下是针对两种表格格式的具体实现：

基础统计（通用前置步骤）

先按user_id和item_id分组，统计每个用户对每个物品的点击次数，这是后续聚合的核心基础：

WITH user_item_clicks AS (
    SELECT 
        user_id,
        item_id,
        COUNT(*) AS click_count
    FROM clickstream_table
    GROUP BY user_id, item_id
)

生成表格1（字典式点击统计列）

使用LISTAGG函数将item_id和click_count拼接成指定格式的字符串，通过WITHIN GROUP保证结果按点击次数降序排列：

SELECT 
    user_id,
    LISTAGG(CONCAT(item_id, ': ', click_count), ', ') WITHIN GROUP (ORDER BY click_count DESC) AS clicked_items
FROM user_item_clicks
GROUP BY user_id;

生成表格2（分离的物品列表与次数列表）

使用ARRAY_AGG分别聚合物品ID和对应点击次数，同样通过WITHIN GROUP维持降序顺序：

SELECT 
    user_id,
    ARRAY_AGG(item_id) WITHIN GROUP (ORDER BY click_count DESC) AS clicked_items,
    ARRAY_AGG(click_count) WITHIN GROUP (ORDER BY click_count DESC) AS num_clicks
FROM user_item_clicks
GROUP BY user_id;