Snowflake按指定列唯一值抽取随机样本的高效SQL方案

问题背景 #

• 需求：在超3000万行的Snowflake数据表中，按指定列（如SPORT）的每个唯一值抽取70条随机记录，若有30个唯一值最终需得到2100条记录。
• 当前痛点：使用CTE结合ROW_NUMBER()的方法虽能得到预期结果，但运行耗时超40分钟，且随机抽样效率低下。现有SQL如下：

WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY SPORT ORDER BY RANDOM() desc) AS row_num
    FROM
        database.schema.table_schema1
)
SELECT
*
FROM CTE
WHERE row_num <= 70;

优化方案 #

方案1：SAMPLE预抽样+分组筛选 #

先通过SAMPLE做全局随机抽样缩小数据集，再对分组后的结果取指定数量，减少全表排序开销：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY SPORT ORDER BY RANDOM()) AS row_num
    FROM database.schema.table_schema1
    SAMPLE (10) -- 可根据分组数量调整抽样比例，确保每个分组样本量≥70
) t
WHERE row_num <=70;

• 核心优势：提前缩小计算数据集，大幅降低窗口函数的排序压力，缩短运行时间。
• 注意事项：需根据实际分组数据量调整SAMPLE比例，避免部分分组抽样后数据不足70条。

方案2：TABLESAMPLE SYSTEM块级抽样 #

针对超大规模表，使用基于存储块的TABLESAMPLE SYSTEM抽样，速度比SAMPLE更快：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY SPORT ORDER BY RANDOM()) AS row_num
    FROM database.schema.table_schema1
    TABLESAMPLE SYSTEM (5) -- 按存储块抽取5%，性能优于全表扫描
) t
WHERE row_num <=70;

• 核心优势：块级抽样跳过全表扫描的部分开销，适合亿级以上数据表，性能提升显著。
• 注意事项：抽样随机性基于数据块，均匀度略逊于SAMPLE，对随机性要求极高时优先选方案1。

方案3：预计算物化视图（适合频繁抽样场景） #

若需频繁执行该抽样操作，可预先生成带随机行号的物化视图：

1. 创建物化视图：

CREATE MATERIALIZED VIEW mv_sport_random AS
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY SPORT ORDER BY RANDOM()) AS random_row_num
FROM database.schema.table_schema1;

2. 查询时直接筛选：

SELECT * FROM mv_sport_random WHERE random_row_num <=70;

• 核心优势：物化视图定期刷新，后续查询直接读取预计算结果，响应速度极快。
• 注意事项：需配置合适的刷新策略，保证数据时效性符合业务需求。

关键优化逻辑 #

原方案的核心瓶颈是全表分组排序，大表下该操作资源消耗极大。优化思路均围绕「减少需排序的数据集大小」展开，优先利用Snowflake原生抽样函数降低计算压力，而非依赖自定义窗口函数的全表处理。

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