如何利用LAG()函数迭代生成SQL频率分桶的上下边界？

不用纠结迭代调用LAG()，直接通过生成分桶序号结合基础计算就能高效生成目标区间，以下是适用于不同数据库的实现方案：

通用递归CTE方案（支持MySQL 8.0+、SQL Server、PostgreSQL、Oracle 11gR2+） #

假设你的源表名为zip_bin_params，代码如下：

WITH recursive_bin AS (
    -- 初始化第一个分桶
    SELECT
        Zip,
        Min_Px AS Lower_Bound,
        CASE WHEN Bin_Count = 1 THEN Max_Px ELSE Min_Px + Bin_Width END AS Upper_Bound,
        Bin_Width,
        Max_Px,
        1 AS Bin_Num,
        Bin_Count
    FROM zip_bin_params

    UNION ALL

    -- 递归生成后续分桶
    SELECT
        Zip,
        Upper_Bound AS Lower_Bound,
        -- 最后一个分桶强制用Max_Px，避免浮点累加误差
        CASE WHEN Bin_Num + 1 = Bin_Count THEN Max_Px ELSE Upper_Bound + Bin_Width END AS Upper_Bound,
        Bin_Width,
        Max_Px,
        Bin_Num + 1 AS Bin_Num,
        Bin_Count
    FROM recursive_bin
    WHERE Bin_Num < Bin_Count
)
SELECT
    Zip,
    Lower_Bound,
    Upper_Bound,
    -- 仅前3个分桶显示计算说明，与示例格式一致
    CASE WHEN Bin_Num <=3 THEN 'Lower_bound + bin_with (' || ROUND(Bin_Width,3) || ') = Upper Bound' ELSE '' END AS Math
FROM recursive_bin
ORDER BY Zip, Bin_Num;

逻辑说明 #

1. 锚点成员生成第一个分桶：直接用Min_Px作为下边界，Min_Px + Bin_Width作为上边界（若只有1个分桶则直接用Max_Px）
2. 递归成员每次用上一个分桶的上边界作为当前下边界，计算新的上边界，直到生成的分桶数等于Bin_Count
3. 最后一个分桶强制使用Max_Px作为上边界，避免多次浮点累加导致的误差
4. 按需求仅给前3个分桶显示计算说明

PostgreSQL简化方案（利用GENERATE_SERIES） #

PostgreSQL支持直接生成序列，代码更简洁高效：

SELECT
    z.Zip,
    z.Min_Px + (s.i - 1)*z.Bin_Width AS Lower_Bound,
    -- 最后一个分桶用Max_Px，否则用下边界+Bin_Width
    CASE WHEN s.i = z.Bin_Count THEN z.Max_Px ELSE z.Min_Px + s.i*z.Bin_Width END AS Upper_Bound,
    CASE WHEN s.i <=3 THEN 'Lower_bound + bin_with (' || ROUND(z.Bin_Width,3) || ') = Upper Bound' ELSE '' END AS Math
FROM zip_bin_params z
CROSS JOIN GENERATE_SERIES(1, z.Bin_Count) s(i)
ORDER BY z.Zip, s(i);

逻辑说明 #

通过CROSS JOIN生成1到Bin_Count的连续序号，直接代入公式计算每个分桶的上下边界，无需递归操作。

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