如何自动查找符合X%数据占比的阈值SQL查询方案

需求是找出数据集中f1+f2之和最小的前X%数据，需要自动计算对应的阈值，替代手动测试多个阈值的低效方式，同时解决之前用SEQUENCE关联导致内存占用过高的问题。

方法一：窗口函数计算累计占比（灵活控制误差范围） #

通过窗口函数计算每条记录的累计占比，筛选出符合X±ε范围的最小阈值，或最接近X的阈值。

WITH data_with_sum AS (
  -- 计算每条记录的f1+f2之和
  SELECT f1 + f2 AS sum_val
  FROM table1
),
total_records AS (
  -- 获取总行数，用于计算占比
  SELECT COUNT(*) AS total FROM data_with_sum
),
ranked_data AS (
  SELECT 
    sum_val,
    -- 按sum_val从小到大排序，计算累计占比
    1.0 * ROW_NUMBER() OVER (ORDER BY sum_val) / total AS cumulative_pct,
    -- 计算与目标X的差值，用于筛选最接近的结果
    ABS(1.0 * ROW_NUMBER() OVER (ORDER BY sum_val) / total - 0.3) AS pct_diff
  FROM data_with_sum, total_records
)
-- 选取最接近目标X的阈值，优先选sum_val更小的（保证最优）
SELECT 
  sum_val AS threshold,
  ROUND(cumulative_pct, 4) AS actual_pct
FROM ranked_data
ORDER BY pct_diff ASC, sum_val ASC
LIMIT 1;

代码说明：

1. data_with_sum：预处理每条记录的f1+f2之和，避免重复计算。
2. total_records：一次性计算总行数，提升效率。
3. ranked_data：
   • 按sum_val从小到大排序，确保我们取的是**最优（和最小）**的前X%数据。
   • cumulative_pct：当前记录及之前的所有记录占总行数的比例。
   • pct_diff：衡量当前累计占比与目标X的差距。
4. 最终排序优先取差距最小的，若有多个差距相同的结果，选sum_val更小的，保证阈值的最优性。

方法二：使用百分位数函数（简洁高效） #

如果不需要严格控制误差范围，直接用BigQuery内置的PERCENTILE_DISC函数可以快速得到阈值：

-- 离散百分位数：返回实际存在的sum_val，确保至少X%的数据≤该值
SELECT 
  PERCENTILE_DISC(0.3) WITHIN GROUP (ORDER BY f1 + f2) AS threshold
FROM table1;

函数说明：

• PERCENTILE_DISC(X)：返回排序后第X分位数对应的实际存在的值，适合需要阈值为真实数据的场景。
• 若需要插值得到的连续值（比如追求精确的X%占比），可以替换为PERCENTILE_CONT(X)，但该值可能不存在于原数据中。

为什么之前的SEQUENCE关联方案内存占用过高？ #

之前的查询用JOIN UNNEST(SEQUENCE(...))会产生笛卡尔积：每条记录都会和每个阈值关联，千万级数据乘以数十个阈值，会生成数亿条临时数据，直接导致内存溢出。而上面的两种方案都是单遍扫描数据，内存占用极低，适合千万级数据集。

示例数据测试（X=30%） #

用提供的示例数据测试方法一，会返回：

完全符合预期：前3条数据的sum_val分别为0.089、0.09、0.09，累计占比正好30%，阈值取最小的满足条件的值0.09。

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