如何在Spark SQL中按固定步长划分数值区间并统计频次

现有一张名为numExample的表，仅包含number字段，数据示例如下：

其中...表示存在更多数值且上限不确定。需要按左闭右开、步长为5的区间（如[0, 5)、[5, 10)）统计每个区间内数值的出现次数。原尝试用CASE子句但因数值上限不确定无法枚举所有WHEN分支，寻求Spark SQL或PySpark实现方案。

原尝试代码：

SELECT numInterval, count(DISTINCT number) AS count
FROM (
    SELECT
        number,
        CASE
            WHEN number >=0 AND number < 5 THEN '[0, 5)'
            WHEN number >=5 AND number < 10 THEN '[5, 10)'
            WHEN number >=10 AND number < 15 THEN '[10, 15)'
            ...
            ...
        END AS numInterval
    FROM numExample
)
GROUP BY numInterval;

1. Spark SQL 实现 #

通过数学计算动态生成区间，无需枚举所有分支：

• 用FLOOR(number / 5) * 5得到区间左边界
• 左边界加5得到右边界，拼接成指定格式的区间字符串
• 按区间分组统计次数（若需统计不同数值的数量，替换COUNT(number)为COUNT(DISTINCT number)）

SELECT
    CONCAT('[', interval_start, ', ', interval_start + 5, ')') AS numInterval,
    COUNT(number) AS count
FROM (
    SELECT
        number,
        FLOOR(number / 5) * 5 AS interval_start
    FROM numExample
) t
GROUP BY interval_start
ORDER BY interval_start;

逻辑说明：

• FLOOR(number /5)*5会将任意数值映射到对应5步长区间的左端点，例如6→5、12→10、50→50、400→400
• 按interval_start排序可保证区间按从小到大的顺序展示

2. PySpark DataFrame API 实现 #

如果使用DataFrame API，代码如下：

from pyspark.sql import SparkSession
from pyspark.sql.functions import floor, concat_ws, count, col

# 初始化SparkSession（若未初始化）
spark = SparkSession.builder.appName("IntervalCount").getOrCreate()

# 读取目标表为DataFrame
df = spark.table("numExample")

# 计算区间并统计
result_df = (
    df.withColumn("interval_start", floor(col("number") / 5) * 5)
    .withColumn(
        "numInterval",
        concat_ws('', '[', col("interval_start"), ', ', col("interval_start") + 5, ')')
    )
    .groupBy("numInterval", "interval_start")
    .agg(count("number").alias("count"))
    .orderBy("interval_start")
    .drop("interval_start")
)

# 查看结果
result_df.show()

逻辑说明：

• 用floor函数计算区间左边界，和Spark SQL逻辑一致
• concat_ws用于拼接区间字符串，避免手动拼接的繁琐
• 分组时带上interval_start是为了保证排序正确性，最后可删除该字段仅保留区间和计数

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