如何用SQL实现按行数均分的百分位区间并分配组索引？

Got it, let's tackle this problem! Your current query uses PERCENT_RANK() which calculates relative value percentiles—great for equal-value ranges, but terrible when you have skewed data like tons of Revenue=0 rows. To get equal-row count percentile groups (each bucket has roughly the same number of rows), you need to use the NTILE() function instead.

Solution Code #

SELECT 
  User, 
  Revenue, 
  -- NTILE(20) creates 20 equal-sized groups (each ~5% of total rows), subtract 1 to match your original 0-based indexing
  NTILE(20) OVER(ORDER BY Revenue) - 1 AS Percentile
FROM RevenueDistribution;

How This Works #

• NTILE(20) splits your entire result set into 20 as-equally-sized-as-possible groups. If your total row count isn't perfectly divisible by 20, some groups will have 1 extra row (no group will be off by more than 1 row).
• The -1 adjusts the group numbering to match your original setup:
  • Percentile = 0 → Bottom ~5% of rows (lowest Revenue values)
  • Percentile = 1 → Next ~5%, and so on up to Percentile = 19 → Top ~5%
• The ORDER BY Revenue ensures groups are sorted from lowest to highest revenue. If you want the highest revenue group to be 0, just reverse the sort: ORDER BY Revenue DESC.

Why This Is Better Than Your Original Query #

Your original (int)(100*(PERCENT_RANK() OVER(...)))/5 logic relies on value-based percentiles, which breaks when you have clusters of identical values (like all those Revenue=0 rows). All those 0s get crammed into the first bucket, making it way larger than the others.

NTILE() ignores value clusters and focuses solely on row count distribution, which is exactly what you need for equal-sized buckets.

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