如何在BigQuery中按每个Store随机抽取Product样本（低成本方案）

问题场景 #

• 存在一张超10亿行的BigQuery表product_timeline，每个门店（Store ID）每天对应多条商品（Product）条目，包含property1、property2等属性
• 需求：抽取所有门店在表中最后一天数据里的随机商品样本
• 当前方案缺陷：原SQL通过全局rand()<0.01抽样，会随机排除部分门店，无法实现“每个门店内抽取商品”的需求；若逐个查询1200+门店，成本过高

解决方案 #

核心思路是通过窗口函数给每个门店下的最后日期数据单独生成随机值，实现门店内独立抽样，同时保持单查询的低成本特性。

修改后的SQL代码 #

WITH maxdate AS (
    SELECT MAX(DATE(product_timestamp)) AS maxdate 
    FROM `MyProject.DataSet1.product_timeline`
),
last_day_data AS (
    SELECT 
        t.*,
        t2.ProductDescription,
        t2.ProductName,
        t2.CreatedDate,
        -- 按门店分组生成随机值，确保每个门店内抽样独立
        RAND() OVER (PARTITION BY t.store) AS rand_val
    FROM `MyProject.DataSet1.product_timeline` AS t
    JOIN `MyProject.DataSet2.LableStore` AS t2
        ON t.store = t2.store AND t.barcode = t2.barcode
    JOIN maxdate 
        ON maxdate.maxdate = DATE(t.product_timestamp)
    JOIN (SELECT store FROM `MyProject.DataSet1.stores`) AS stores
        ON stores.store = t.store
)
SELECT * EXCEPT(rand_val)
FROM last_day_data
WHERE rand_val < 0.01 -- 每个门店内抽取约1%的样本

代码说明 #

• RAND() OVER (PARTITION BY t.store)：为每个门店下的最后日期数据单独生成0-1之间的随机值，保证每个门店的抽样逻辑独立，不会出现门店被完全排除的情况
• 对比原SQL的全局rand()，该方式能确保所有门店都有样本入选，符合需求
• 单查询模式复用BigQuery批量处理能力，避免循环查询单个门店的高成本

可选调整：固定数量抽样 #

如果需要每个门店抽取固定数量的样本（比如每个门店抽5条），可修改为以下逻辑：

WITH maxdate AS (
    SELECT MAX(DATE(product_timestamp)) AS maxdate 
    FROM `MyProject.DataSet1.product_timeline`
),
last_day_data AS (
    SELECT 
        t.*,
        t2.ProductDescription,
        t2.ProductName,
        t2.CreatedDate,
        -- 按门店分组随机排序，生成行号
        ROW_NUMBER() OVER (PARTITION BY t.store ORDER BY RAND()) AS row_num
    FROM `MyProject.DataSet1.product_timeline` AS t
    JOIN `MyProject.DataSet2.LableStore` AS t2
        ON t.store = t2.store AND t.barcode = t2.barcode
    JOIN maxdate 
        ON maxdate.maxdate = DATE(t.product_timestamp)
    JOIN (SELECT store FROM `MyProject.DataSet1.stores`) AS stores
        ON stores.store = t.store
)
SELECT * EXCEPT(row_num)
FROM last_day_data
WHERE row_num <= 5 -- 每个门店抽取前5条随机样本

对应Python执行代码 #

query_random_sample = """ 
WITH maxdate AS (
    SELECT MAX(DATE(product_timestamp)) AS maxdate 
    FROM `MyProject.DataSet1.product_timeline`
),
last_day_data AS (
    SELECT 
        t.*,
        t2.ProductDescription,
        t2.ProductName,
        t2.CreatedDate,
        RAND() OVER (PARTITION BY t.store) AS rand_val
    FROM `MyProject.DataSet1.product_timeline` AS t
    JOIN `MyProject.DataSet2.LableStore` AS t2
        ON t.store = t2.store AND t.barcode = t2.barcode
    JOIN maxdate 
        ON maxdate.maxdate = DATE(t.product_timestamp)
    JOIN (SELECT store FROM `MyProject.DataSet1.stores`) AS stores
        ON stores.store = t.store
)
SELECT * EXCEPT(rand_val)
FROM last_day_data
WHERE rand_val < 0.01
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
    ]
)

sampled_labels = bigquery_client.query(query_random_sample, job_config=job_config).to_dataframe()

内容的提问来源于stack exchange，提问作者Serge de Gosson de Varennes