如何在SQL（BigQuery）中基于列值创建分段评分（替代行级NTILE）

要解决NTILE()拆分同销售额数据的问题，你可以用**PERCENT_RANK()**结合条件判断，确保相同销售额的销售人员被分到同一分段，同时让最高销售额对应分段5、最低对应分段1。

示例实现 #

假设你的表是sp_sales，包含salesperson（销售人员）和sales（销售额）字段，代码如下：

WITH sp_sales AS (
  -- 替换为你的实际表数据
  SELECT 'A' AS salesperson, 100 AS sales UNION ALL
  SELECT 'B' AS salesperson, 100 AS sales UNION ALL
  SELECT 'C' AS salesperson, 200 AS sales UNION ALL
  SELECT 'D' AS salesperson, 200 AS sales UNION ALL
  SELECT 'E' AS salesperson, 200 AS sales UNION ALL
  SELECT 'F' AS salesperson, 300 AS sales UNION ALL
  SELECT 'G' AS salesperson, 300 AS sales UNION ALL
  SELECT 'H' AS salesperson, 400 AS sales UNION ALL
  SELECT 'I' AS salesperson, 400 AS sales UNION ALL
  SELECT 'J' AS salesperson, 400 AS sales UNION ALL
  SELECT 'K' AS salesperson, 400 AS sales UNION ALL
  SELECT 'L' AS salesperson, 500 AS sales UNION ALL
  SELECT 'M' AS salesperson, 500 AS sales UNION ALL
  SELECT 'N' AS salesperson, 600 AS sales UNION ALL
  SELECT 'O' AS salesperson, 600 AS sales UNION ALL
  SELECT 'P' AS salesperson, 700 AS sales UNION ALL
  SELECT 'Q' AS salesperson, 700 AS sales UNION ALL
  SELECT 'R' AS salesperson, 800 AS sales UNION ALL
  SELECT 'S' AS salesperson, 800 AS sales
)
SELECT
  salesperson,
  sales,
  CASE
    WHEN pr >= 0.8 THEN 5
    WHEN pr >= 0.6 THEN 4
    WHEN pr >= 0.4 THEN 3
    WHEN pr >= 0.2 THEN 2
    ELSE 1
  END AS segment
FROM (
  SELECT
    *,
    -- 反转升序的百分比排名，让最高销售额对应1，最低对应0
    1 - PERCENT_RANK() OVER(ORDER BY sales ASC) AS pr
  FROM sp_sales
)
ORDER BY sales DESC, salesperson;

逻辑说明 #

1. PERCENT_RANK()：按销售额升序计算每条数据的百分比排名（最低销售额为0，最高为1）。用1 - 排名值反转后，最高销售额对应1，最低对应0。
2. CASE条件：将反转后的排名区间映射到1-5的分段：
   • 排名≥0.8（原销售额最高的20%）→ 分段5
   • 排名≥0.6且<0.8 → 分段4
   • 以此类推，最低的20%销售额→分段1
3. 由于相同销售额的PERCENT_RANK()结果一致，所以会被分到同一个分段，不会出现拆分情况。

这种方式不强制均分各分段的行数，优先保证同销售额数据的分组一致性。

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