SQL怎么用窗口函数实现数值区间的分段统计频次

在业务报表里，我们常需要将一串连续数值比如用户消费额、响应延迟、分数等切为低中高三档或更多区间，并算出每档出现的次数。用SQL做这件事，很多人第一反应是写多层子查询，给每行打上区间标签后再GROUP BY。其实窗口函数可以在同一层查询中完成打标与辅助计算，让分段统计频次变得更直观且易于维护。

一、为什么用窗口函数做分段统计

分段统计的本质是先对数值做分箱（binning），再对箱号计数。普通GROUP BY写法需要先用CASE WHEN判断每个值落在哪个区间，这一步本身不难，但当区间边界依赖数据分布（例如按四分位切分）时，硬编码阈值就很麻烦。窗口函数里的NTILE、PERCENT_RANK等可以直接基于结果集排序动态分箱，不需要事先知道最大值最小值。

另一个好处是窗口函数不会减少行数，原表的明细和区段标记可以共存。后续如果还想看某区间内的具体客户名单，不必再关联回原表。从执行角度看，排序只需做一次，比先聚合再自连接要轻量。对于中等规模数据，这种写法在可读性和性能之间取得了不错平衡。

二、固定阈值区间的统计写法

当业务已经明确规定如0到100、100到500、500以上这样的区间时，可以结合CASE WHEN与COUNT OVER或者先打标再聚合。下面例子用CASE WHEN生成区段，并用子查询聚合：

SELECT
  bucket,
  COUNT(*) AS freq
FROM (
  SELECT
    CASE
      WHEN amount < 100 THEN '0-99'
      WHEN amount < 500 THEN '100-499'
      ELSE '500+'
    END AS bucket
  FROM orders
) t
GROUP BY bucket
ORDER BY bucket;

这段逻辑清晰，但打标和统计分了两层。如果想在同一行既看明细又看区间总频次，可以用窗口函数：

SELECT
  order_id,
  amount,
  CASE
    WHEN amount < 100 THEN '0-99'
    WHEN amount < 500 THEN '100-499'
    ELSE '500+'
  END AS bucket,
  COUNT(*) OVER (
    PARTITION BY
      CASE
        WHEN amount < 100 THEN '0-99'
        WHEN amount < 500 THEN '100-499'
        ELSE '500+'
      END
  ) AS bucket_freq
FROM orders;

这里COUNT(*) OVER (PARTITION BY 区段) 给每行附上了它所属区段的频次，而不折叠数据。优点是明细和频次同屏，缺点是该列在每个同区段行上重复。若只要频次表，外层再DISTINCT即可。

三、动态分箱：用NTILE做等分区间

如果区间不能提前定死，而是要把数据均分成4段统计每段的记录数与数值范围，NTILE窗口函数最合适。它按排序把行平分到指定数量的桶里。

SELECT
  bucket,
  COUNT(*) AS freq,
  MIN(amount) AS min_val,
  MAX(amount) AS max_val
FROM (
  SELECT
    amount,
    NTILE(4) OVER (ORDER BY amount) AS bucket
  FROM orders
) t
GROUP BY bucket
ORDER BY bucket;

上面的子查询给每个订单按金额升序分到1到4号桶，外层按桶聚合就得到四段各自的频次和上下界。注意NTILE在总行数不能整除桶数时，前面几个桶会多一行，这属于预期行为。

如果希望按百分比而非等行数切分，可以用PERCENT_RANK配合阈值：

SELECT
  bucket,
  COUNT(*) AS freq
FROM (
  SELECT
    CASE
      WHEN PERCENT_RANK() OVER (ORDER BY amount) < 0.25 THEN '0-25%'
      WHEN PERCENT_RANK() OVER (ORDER BY amount) < 0.50 THEN '25-50%'
      WHEN PERCENT_RANK() OVER (ORDER BY amount) < 0.75 THEN '50-75%'
      ELSE '75-100%'
    END AS bucket
  FROM orders
) t
GROUP BY bucket;

这种写法让区间边界跟着数据分布走，特别适合做分位数报告。不过PERCENT_RANK在计算时也要排序，数据量大时可考虑事先物化排序结果。

四、用SUM OVER做累计频次

分段统计有时还要看累计分布，比如小于等于当前区间的频次总和。在算出各段频次后，用SUM OVER按区间顺序累加即可：

SELECT
  bucket,
  freq,
  SUM(freq) OVER (ORDER BY bucket) AS cum_freq
FROM (
  SELECT
    CASE
      WHEN amount < 100 THEN 1
      WHEN amount < 500 THEN 2
      ELSE 3
    END AS bucket,
    COUNT(*) AS freq
  FROM orders
  GROUP BY
    CASE
      WHEN amount < 100 THEN 1
      WHEN amount < 500 THEN 2
      ELSE 3
    END
) t
ORDER BY bucket;

这里内层先按数字型桶号聚合，外层SUM OVER (ORDER BY bucket) 按桶号顺序做_running total，直接给出累计频次。相比自连接求累计，窗口函数只需一次排序扫描。

五、不同写法的取舍

硬编码CASE WHEN适合区间固定、逻辑简单的报表，写起来快，数据库优化器也容易命中索引。NTILE和PERCENT_RANK适合探索性分析，能随数据变化自动调整边界，但排序成本略高。COUNT OVER PARTITION BY适合既要明细又要频次标记的场景，而最终出频次表时记得去重。

实际项目中可以把动态分箱结果写入临时表，再让多个报表复用，避免重复排序。这样既有窗口函数的灵活，也控住了计算成本。

SQL窗口函数分段统计修改时间：2026-08-07 19:24:31