SQL如何统计每个分组中前10%的数据_利用NTILE窗口函数

SQL如何统计每个分组中前10%的数据

在数据分析里，经常遇到一个需求：在每个分组里，精确地取出排名最靠前的那10%的数据。比如，找出每个产品类别里销售额最高的前10%的订单，或者筛选出每个部门里绩效最好的前10%的员工。乍一看，这似乎正是窗口函数NTILE(10)的用武之地——但事实果真如此吗？

NTILE(10) 能不能直接拿到前10%？

答案是：不能。这里有个常见的误解，需要先澄清一下。

NTILE(10)的工作机制，是“等频分桶”。它会把数据尽可能平均地分成10组，并给每组打上1到10的编号。问题就出在“尽可能平均”上。如果数据总量不能被10整除，它就会让编号靠前的桶多“吃”进一行数据。

举个例子就明白了：假设某个分组里总共只有13行数据。13除以10，商1余3。那么，NTILE(10)分配的结果就是：编号为1、2、3的桶各装2行数据，剩下的7个桶各装1行。此时，编号为1的桶里数据量是2行，占整个分组比例约为15.4%，这显然已经偏离了“严格10%”的目标。

所以，NTILE(10) = 1代表的并不是“前10%”，而是“第一段”。当数据量不大或分布不均时，这个“段”的大小会浮动，用它来卡比例，结果就不精确了。

那么，正确的思路是什么？其实核心就三步：先算出每组的总行数，再计算出10%对应的具体行数上界，最后用行号筛选出不超过这个上界的记录。

用 ROW_NUMBER() + COUNT(*) OVER() 算精确前10%

这才是可靠的做法。我们通过组合两个窗口函数来实现精确控制。

• 首先，用COUNT(*) OVER (PARTITION BY ...)在组内计算总行数。
• 接着，用ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)为组内每一行生成一个唯一的、连续的排序编号。
• 最后，在筛选时，只保留那些行号小于等于“总行数 × 10%”的记录。这里注意，通常使用<=而不是=，并且对计算结果向上取整，以确保至少能取到一行。

来看一个具体的例子：假设我们有一张销售表sales，想找出每个category（类别）中score（分数）最高的前10%的记录。

SELECT category, score, name
FROM (
  SELECT category, score, name,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn,
    CEILING(COUNT(*) OVER (PARTITION BY category) * 0.1) AS ceiling_cnt
  FROM sales
) t
WHERE rn <= ceiling_cnt;

这段代码的逻辑很清晰：内层子查询为每行数据同时计算了其组内排名rn和该组应取的行数上限ceiling_cnt，外层查询直接根据行号进行过滤。这样一来，无论每个类别有多少条数据，都能精确地取出前10%（向上取整）。

NTILE(10) 的适用场景和陷阱

既然NTILE(10)不能用于精确截取，那它有什么用呢？它更适合做“粗略分层”或“相对排名归类”。

比如，在用户分析中，我们想快速将所有用户按消费额从高到低划分为“第一梯队”、“第二梯队”……直到“第十梯队”，这时用NTILE(10)就非常方便，我们并不关心每个梯队是否严格占10%，只需要一个大致的分层。

但在追求精确比例的场合，使用NTILE就需要警惕几个陷阱：

• 桶大小不均：如前所述，当总行数不是桶数的整数倍时，前面的桶会更大。
• 重复值处理：如果排序字段存在大量相同值，NTILE会机械地将其拆分到不同的桶中，这可能会破坏业务上的逻辑一致性（比如几个并列第一的分数被硬生生分开了）。
• 兼容性：虽然主流数据库如MySQL 8.0+、PostgreSQL等都支持NTILE，但在一些大数据平台（如某些版本的Hive/Spark SQL）中，窗口函数的支持程度需要事先确认。

性能与兼容性提醒

有人可能会担心，ROW_NUMBER()配合COUNT(*) OVER()的写法会不会更耗性能？从逻辑上看，它似乎需要两次扫描数据，但现代的数据库优化器通常非常智能，能够将其优化为高效的执行计划。而NTILE函数内部同样需要进行排序操作，两者在性能上的实际差异往往不大。

相比之下，在实际应用中，更值得关注的是以下几个边界细节：

• 极小样本处理：当一个分组只有1行或0行数据时，CEILING(总数 * 0.1)的结果是1。取前1行对于只有1条数据的分组是合理的，但如果想避免在数据极少时取出全部结果，可以考虑使用FLOOR函数向下取整，但这又可能导致小样本分组一条都取不出来，需要根据业务权衡。
• 空值排序：如果排序字段score存在NULL值，不同数据库的默认排序规则不同（有的把NULL视为最小，有的视为最大）。为了结果可控，最好显式指定，例如使用ORDER BY score DESC NULLS LAST（PostgreSQL语法），或者用COALESCE(score, -999999)之类的函数给NULL一个默认值。
• 并列排名：如果业务要求“分数相同则排名相同”，那就得换用RANK()或DENSE_RANK()函数。但请注意，一旦允许并列，最终筛选出的行数就可能超过“总行数×10%”的精确范围了。

总而言之，NTILE这个函数，名字听起来像是为百分位而生的，但其本质是一个“分段计数器”。在处理需要精确比例的业务时，最踏实的办法，还是用行号配合总数来计算。这个方法虽然看起来多了一两步，但胜在结果准确，心里踏实。