Snowflake聚合查询运行过慢问题排查与优化求助

DevOps 小满

2026-6-28

问题

我需要统计EXPO_MASTER.category_tree_master中各category_id对应的独立stands数量和独立products数量，筛选条件为EXPO_MASTER.category_product属于指定值列表。使用以下Snowflake查询时，未使用聚合函数运行正常，但添加COUNT(DISTINCT)结合PARTITION BY或GROUP BY后，查询耗时长达6-8分钟：

SELECT 
    category_tree_master__."category_id" AS category_id,
    --searchchildrenmaster.children,
    --ARRAY_CONTAINS(category_product_nb_stand_nb_product."category_id" ::varchar:: VARIANT ,TO_ARRAY(SPLIT(CAST(searchchildrenmaster."category_id" AS VARCHAR) || COALESCE (', ' || searchchildrenmaster.children, ''), ','))) AS check_,
    --category_product_nb_stand_nb_product.stands AS nb_stands,
    --category_product_nb_stand_nb_product.products AS nb_products
    COUNT(DISTINCT category_product_nb_stand_nb_product.stands) OVER (PARTITION BY category_tree_master__."category_id"),
    COUNT(DISTINCT category_product_nb_stand_nb_product.products) OVER (PARTITION BY category_tree_master__."category_id")
    --IFF (count (DISTINCT category_product_nb_stand_nb_product.stands)  IS NOT NULL , count (DISTINCT category_product_nb_stand_nb_product.stands) , 0) AS nb_stands,
    --IFF (count (DISTINCT category_product_nb_stand_nb_product.products)  IS NOT NULL , count (DISTINCT category_product_nb_stand_nb_product.products) , 0) AS nb_products
FROM 
    (SELECT 
         src."category_id",
         CAT_CHILDREN_LIST AS children
     FROM 
         (SELECT 
              EXPO_MASTER.category_tree_master."category_tree_master_id", 
              EXPO_MASTER.category_tree_master."category_id"
          FROM 
              EXPO_MASTER.category_tree_master
          INNER JOIN 
              (SELECT 
                   EXPO_MASTER.category_lang."category_id",  
                   EXPO_MASTER.category_lang."lang"
               FROM 
                   EXPO_MASTER.category_lang
               WHERE 
                   EXPO_MASTER.category_lang."lang" = 'fr') AS tmp
            ON EXPO_MASTER.category_tree_master."category_id" = tmp."category_id") src
            LEFT JOIN PERFORMANCE.SEARCH_CHILDREN_MASTER  children_cat
                ON CHILDREN_CAT."category_id"  = src."category_id" ) searchchildrenmaster
        INNER JOIN 
        (
        SELECT 
          EXPO_MASTER.category_tree_master."category_tree_master_id", 
          EXPO_MASTER.category_tree_master."category_id", 
          EXPO_MASTER.category_tree_master."parent_id",
          EXPO_MASTER.category_tree_master."gauche", 
          EXPO_MASTER.category_tree_master."droite", 
          EXPO_MASTER.category_tree_master."niveau", 
          EXPO_MASTER.category_tree_master_ref_plateforme_selection."selection_leadgen",
          EXPO_MASTER.category_tree_master_ref_plateforme_selection."selection_stand"
        FROM 
            EXPO_MASTER.category_tree_master
        JOIN 
            EXPO_MASTER.category_tree_master_ref_plateforme_selection 
            ON 
            (
                EXPO_MASTER.category_tree_master_ref_plateforme_selection."category_tree_master_id" = EXPO_MASTER.category_tree_master."category_tree_master_id" AND 
                EXPO_MASTER.category_tree_master_ref_plateforme_selection."ref_plateforme_id" = 1
            )
        )category_tree_master__ ON category_tree_master__."category_id" = 
        CASE WHEN searchchildrenmaster."category_id" IS NOT NULL THEN searchchildrenmaster."category_id" ELSE 1 END
         LEFT JOIN LATERAL
         (
            SELECT
              EXPO_MASTER.category_product."category_id",
              EXPO_MASTER.product."stand_id" AS stands,
              EXPO_MASTER.product."product_id" AS products
            FROM 
                EXPO_MASTER.category_product
            JOIN 
                EXPO_MASTER.product ON (EXPO_MASTER.product."product_id" = EXPO_MASTER.category_product."product_id")
                WHERE 
                ARRAY_CONTAINS(EXPO_MASTER.category_product."category_id" ::varchar:: VARIANT ,TO_ARRAY(SPLIT(CAST(searchchildrenmaster."category_id" AS VARCHAR) || COALESCE (', ' || searchchildrenmaster.children, ''), ',')))
                    )category_product_nb_stand_nb_product
                --GROUP BY category_tree_master__."category_id"

问题根源分析

窗口函数中COUNT(DISTINCT)的低效性：窗口函数内的COUNT(DISTINCT)需要对每个分区的数据做去重统计，当数据集较大时，会触发大量排序和去重计算，Snowflake优化器难以高效处理这种模式，导致资源消耗剧增。
LATERAL JOIN的中间数据膨胀：当前查询通过LATERAL JOIN关联子查询，若每个category对应大量products/stands，会生成极大的中间结果集，后续聚合操作需要处理远超必要的数据量，直接拉长执行时间。
动态字符串拼接+数组转换的额外开销：SPLIT(CAST(searchchildrenmaster."category_id" AS VARCHAR) || COALESCE (', ' || searchchildrenmaster.children, ''), ',')这种动态生成数组的方式无法利用索引，且每次执行都要做字符串操作和类型转换，增加了不必要的计算成本。
未提前聚合数据：原查询先关联所有明细数据再做窗口聚合，没有在关联前对products/stands按category预聚合，导致后续处理的数据量过大。

优化解决方案

1. 替换窗口函数为预聚合+关联

先在子查询中完成category_id维度的独立stands和products统计，再与主数据集关联，避免窗口函数的低效计算。

2. 预生成category子节点数组

避免在关联时动态拼接字符串和转换数组，提前将子节点列表转为数组类型，减少重复计算。

3. 移除不必要字段

主查询只保留统计所需字段，移除gauche、droite、niveau等无关字段，降低数据传输和处理量。

4. 优化过滤逻辑

利用Snowflake的数组操作特性，结合预生成的子节点数组优化关联条件，避免重复的字符串拆分操作。

优化后的查询语句

WITH category_child_map AS (
    -- 预生成category及其子节点的数组映射
    SELECT 
        src."category_id",
        ARRAY_CONSTRUCT(src."category_id"::VARCHAR) || COALESCE(SPLIT(children_cat.CAT_CHILDREN_LIST, ', '), ARRAY_CONSTRUCT()) AS category_ids_array
    FROM (
        SELECT 
            ctm."category_id"
        FROM EXPO_MASTER.category_tree_master ctm
        INNER JOIN EXPO_MASTER.category_lang cl 
            ON ctm."category_id" = cl."category_id"
            AND cl."lang" = 'fr'
    ) src
    LEFT JOIN PERFORMANCE.SEARCH_CHILDREN_MASTER children_cat
        ON children_cat."category_id" = src."category_id"
),
category_product_agg AS (
    -- 预聚合每个category（含子节点）的独立stands和products数量
    SELECT 
        ccm."category_id" AS parent_category_id,
        COUNT(DISTINCT p."stand_id") AS nb_stands,
        COUNT(DISTINCT p."product_id") AS nb_products
    FROM category_child_map ccm
    LEFT JOIN EXPO_MASTER.category_product cp
        ON ARRAY_CONTAINS(cp."category_id"::VARCHAR::VARIANT, ccm.category_ids_array)
    LEFT JOIN EXPO_MASTER.product p
        ON cp."product_id" = p."product_id"
    GROUP BY ccm."category_id"
),
valid_categories AS (
    -- 筛选符合平台条件的category
    SELECT DISTINCT
        ctm."category_id"
    FROM EXPO_MASTER.category_tree_master ctm
    JOIN EXPO_MASTER.category_tree_master_ref_plateforme_selection cps
        ON ctm."category_tree_master_id" = cps."category_tree_master_id"
        AND cps."ref_plateforme_id" = 1
)
-- 关联最终结果，空值补0
SELECT 
    vc."category_id",
    COALESCE(cpa.nb_stands, 0) AS nb_stands,
    COALESCE(cpa.nb_products, 0) AS nb_products
FROM valid_categories vc
LEFT JOIN category_product_agg cpa
    ON vc."category_id" = cpa.parent_category_id
ORDER BY vc."category_id";

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

Snowflake聚合查询运行过慢问题排查与优化求助

问题根源分析 #

优化解决方案 #

1. 替换窗口函数为预聚合+关联 #

2. 预生成category子节点数组 #

3. 移除不必要字段 #

4. 优化过滤逻辑 #

优化后的查询语句 #