Snowflake中多列非空值聚合计数的优化方案问询

核心问题修正 #

原方案结果异常是因为错误累加了每个用户的非空订单列，导致重复计数。正确逻辑需先将多网站列的宽表转为行级窄表，再按国家+网站维度去重，最后统计每个国家的唯一网站数。

CTE最优实现方案 #

WITH website_orders_unpivoted AS (
    -- 将多网站列转为行结构，仅保留有订单的记录
    SELECT 
        Country,
        website_name
    FROM 
        your_order_table
    UNPIVOT (
        order_value FOR website_name IN (
            website1_order, website2_order, website3_order, -- 替换为实际20个网站列名
            website4_order, website5_order, ..., website20_order
        )
    )
    WHERE 
        order_value IS NOT NULL
),
unique_country_websites AS (
    -- 按国家+网站去重，确保每个网站在对应国家仅计一次
    SELECT DISTINCT
        Country,
        website_name
    FROM 
        website_orders_unpivoted
)
-- 统计每个国家的可用网站数
SELECT 
    Country,
    COUNT(website_name) AS available_website_count
FROM 
    unique_country_websites
GROUP BY 
    Country
ORDER BY 
    available_website_count DESC;

方案优势 #

1. 可缩放性：Unpivot将宽表转为行结构，适配Snowflake列存储引擎的优化逻辑，数据量增大时仍能高效运行。
2. 结果准确：通过DISTINCT避免同一网站在同一国家被重复计数，解决原方案的数值异常问题。

临时表备选方案（超大数据集场景） #

若数据集规模极大，可将Unpivot结果存入临时表，减少CTE重复计算开销：

-- 创建临时表存储转换后的订单记录
CREATE OR REPLACE TEMPORARY TABLE temp_website_orders AS
SELECT 
    Country,
    website_name
FROM 
    your_order_table
UNPIVOT (
    order_value FOR website_name IN (
        website1_order, website2_order, ..., website20_order
    )
)
WHERE 
    order_value IS NOT NULL;

-- 统计最终结果
SELECT 
    Country,
    COUNT(DISTINCT website_name) AS available_website_count
FROM 
    temp_website_orders
GROUP BY 
    Country
ORDER BY 
    available_website_count DESC;

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