如何在BigQuery中标记分组内上下25分位的商家

核心计算逻辑 #

• 以time_value、time_bin作为分组维度
• 为每个分组计算三类统计值：组内商家总数、组内yoy_txn_growth的25分位阈值、75分位阈值
• 按规则生成两个布尔标识字段：
  • 若组内商家总数为1，top25perc、bot25perc均设为TRUE
  • 其余场景下，yoy_txn_growth大于等于75分位阈值时top25perc为TRUE，小于等于25分位阈值时bot25perc为TRUE

实现SQL #

将代码中的表路径替换为实际存储数据集的表名即可直接运行：

WITH group_level_stats AS (
  SELECT
    *,
    COUNT(business_id) OVER (PARTITION BY time_value, time_bin) AS group_biz_cnt,
    PERCENTILE_CONT(yoy_txn_growth, 0.25) OVER (PARTITION BY time_value, time_bin) AS p25_val,
    PERCENTILE_CONT(yoy_txn_growth, 0.75) OVER (PARTITION BY time_value, time_bin) AS p75_val
  FROM `替换为你的实际表路径`
)
SELECT
  business_id,
  business_name,
  time_value,
  time_bin,
  yoy_txn_growth,
  CASE
    WHEN group_biz_cnt = 1 THEN TRUE
    WHEN yoy_txn_growth >= p75_val THEN TRUE
    ELSE FALSE
  END AS top25perc,
  CASE
    WHEN group_biz_cnt = 1 THEN TRUE
    WHEN yoy_txn_growth <= p25_val THEN TRUE
    ELSE FALSE
  END AS bot25perc
FROM group_level_stats
-- 以下排序为对齐示例结果，不需要可删除
ORDER BY 
  time_bin DESC,
  PARSE_DATE('%B %d,%Y', time_value) DESC,
  business_id

补充说明 #

• 分位函数选择：如果要求分位阈值必须是组内真实存在的业务数值，可将PERCENTILE_CONT替换为PERCENTILE_DISC。前者为连续分位函数，会对相邻数值做插值计算；后者为离散分位函数，直接返回组内最接近分位位置的真实取值。
• 小分组适配：当分组内商家数为2、3这类不足4个的场景时，分位计算逻辑会自动适配，比如2个商家的分组中，75分位对应更高的增长值、25分位对应更低的增长值，和给出的示例输出逻辑完全匹配。

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