Snowflake SQL实现用户已完成任务组合统计的方案问询

核心思路 #

利用Snowflake内置的ARRAY_COMBINATIONS函数自动生成所有2项、3项任务组合，替代递归或手动CASE WHEN的繁琐方案，既避免内存溢出，又大幅简化代码。

假设表结构 #

假设你的任务历史表名为user_task_history，字段包括：

• user_id：用户唯一标识
• task_id：任务ID（格式如'Task 1'、'Task 2'）
• completed：布尔值，标记任务是否完成（若表中仅存已完成任务，可去掉该筛选条件）

完整SQL代码 #

-- 第一步：聚合每个用户的已完成任务为有序数组
WITH user_completed_tasks AS (
    SELECT
        user_id,
        ARRAY_AGG(DISTINCT task_id ORDER BY task_id) AS completed_tasks_array
    FROM user_task_history
    WHERE completed = TRUE  -- 仅保留已完成任务，若表中只有已完成记录可删除此行
    GROUP BY user_id
),
-- 生成所有2项任务组合
task_pairs AS (
    SELECT
        user_id,
        ARRAY_TO_STRING(comb, ', ') AS task_combination
    FROM user_completed_tasks,
         LATERAL FLATTEN(INPUT => ARRAY_COMBINATIONS(completed_tasks_array, 2)) comb
),
-- 生成所有3项任务组合
task_triples AS (
    SELECT
        user_id,
        ARRAY_TO_STRING(comb, ', ') AS task_combination
    FROM user_completed_tasks,
         LATERAL FLATTEN(INPUT => ARRAY_COMBINATIONS(completed_tasks_array, 3)) comb
),
-- 合并两种组合类型
all_task_combinations AS (
    SELECT '2-task' AS combination_type, task_combination, user_id FROM task_pairs
    UNION ALL
    SELECT '3-task' AS combination_type, task_combination, user_id FROM task_triples
)
-- 统计每个组合的完成用户数
SELECT
    combination_type,
    task_combination,
    COUNT(DISTINCT user_id) AS completed_user_count
FROM all_task_combinations
GROUP BY combination_type, task_combination
ORDER BY combination_type, completed_user_count DESC;

方案优势说明 #

1. 避免递归内存溢出：ARRAY_COMBINATIONS是Snowflake原生优化函数，底层采用高效算法生成组合，相比递归SQL大幅降低内存占用，适合大用户量场景。
2. 替代繁琐CASE WHEN：9个任务的2项组合共36种、3项组合共84种，手动编写CASE WHEN需120+条件，本方案自动生成所有合法组合，代码简洁易维护。
3. 组合去重保证准确性：ARRAY_AGG时按task_id排序，确保生成的组合字符串（如'Task 1, Task 2'）唯一，不会出现同一组合的不同顺序重复统计。

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