如何统计SQL分桶数组各区间内包含的记录行数

核心逻辑是先将一维分桶数组转换为带上下边界的区间表，再关联交易数据匹配所属区间，最后聚合计数，全程不需要硬编码区间边界，修改分桶基数数组即可自动适配。

完整可执行代码（保留空桶） #

这个版本会返回所有分桶的统计结果，即使某个区间内没有记录也会显示计数为0，不会遗漏分桶：

WITH global_metrics AS (
  -- 替换成你的实际交易表名即可
  SELECT AVG(tx_value) AS avg_tx_value FROM your_transaction_table
),
bucket_with_range AS (
  SELECT
    bucket_val AS upper_bound,
    -- 取上一个分桶值作为当前区间下界，最小区间下界默认设为0
    LAG(bucket_val, 1, 0) OVER (ORDER BY bucket_val) AS lower_bound
  FROM global_metrics,
  UNNEST(ARRAY(
    SELECT tx_value_range * avg_tx_value
    FROM UNNEST([0.001, 0.01, 0.1, 1, 10, 100]) AS tx_value_range
  )) AS bucket_val
)
SELECT
  upper_bound AS bucket,
  COUNT(t.id) AS record_count
FROM bucket_with_range b
LEFT JOIN your_transaction_table t
  -- 默认区间规则：(lower_bound, upper_bound]，可根据需求调整不等号方向
  ON t.tx_value > b.lower_bound
  AND t.tx_value <= b.upper_bound
GROUP BY upper_bound
ORDER BY upper_bound;

简洁版本（不保留空桶） #

如果不需要展示计数为0的空桶，可以用更短的写法，直接通过交叉连接匹配每条记录所属的最小符合条件的分桶：

WITH global_metrics AS (
  SELECT AVG(tx_value) AS avg_tx_value FROM your_transaction_table
)
SELECT
  MIN(bucket_val) AS bucket,
  COUNT(*) AS record_count
FROM your_transaction_table t,
global_metrics,
UNNEST(ARRAY(
  SELECT tx_value_range * avg_tx_value
  FROM UNNEST([0.001, 0.01, 0.1, 1, 10, 100]) AS tx_value_range
)) AS bucket_val
WHERE t.tx_value <= bucket_val
GROUP BY bucket
ORDER BY bucket;

规则说明 #

• 默认分桶为左开右闭区间，如果你需要左闭右开或者其他区间规则，直接调整LEFT JOIN里的不等号即可
• 如果需要承接超过最大分桶值的记录，可以在bucket_with_range里追加一行UNION ALL SELECT 300.0 AS lower_bound, CAST('inf' AS FLOAT64) AS upper_bound（数值根据你的实际分桶最大值调整）
• 基于你给出的示例数据集，当avg_tx_value=3（即你举例的分桶数组[0.003, 0.03, 0.30, 3.0, 30.0, 300.0]）时，运行第一个版本的返回结果如下：

你问题里贴的期望结果为格式示意，实际数值可以根据你的分桶规则、平均交易值计算逻辑调整。

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