如何在BigQuery SQL中按CPIRating规则筛选记录子集？

实现代码 #

WITH high_cpi_records AS (
    -- 筛选所有CPIRating≥3.0的记录
    SELECT *
    FROM `your-project.your-dataset.your-table`
    WHERE CPIRating >= 3.0
),
high_cpi_count AS (
    -- 统计高评分记录的数量
    SELECT COUNT(*) AS total_high
    FROM high_cpi_records
)
-- 合并高评分记录和筛选后的低评分记录
SELECT * FROM high_cpi_records
UNION ALL
SELECT *
FROM (
    -- 低评分记录按CPIRating从小到大排序
    SELECT *
    FROM `your-project.your-dataset.your-table`
    WHERE CPIRating < 3.0
    ORDER BY CPIRating ASC
)
-- 选取前4倍高评分记录数量的低评分记录
WHERE ROW_NUMBER() OVER () <= (SELECT total_high * 4 FROM high_cpi_count)

代码说明 #

• high_cpi_records：提取所有满足CPIRating≥3.0的记录，这部分直接纳入最终子集。
• high_cpi_count：统计高评分记录的总数total_high，用于确定低评分记录的选取数量。
• 低评分记录部分先按CPIRating升序排列，再通过ROW_NUMBER()窗口函数限制选取条数为4*total_high，确保只取数值最小的前N条。
• 最后用UNION ALL合并两部分数据，得到符合要求的记录子集。

注意：请将代码中的your-project.your-dataset.your-table替换为你实际的BigQuery项目、数据集和表名。

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