SQL实现数值匹配3-3-4规则区间分桶的高效查询方法

核心计算逻辑 #

这套分桶是标准的每10个数值为一个周期，按3个、3个、4个的长度循环切分的规则，不需要维护分桶维表做关联，纯数学计算就能以O(1)复杂度完成匹配，数据量越大效率优势越明显，计算步骤如下：

• 先做计数偏移：将输入值减1，把从1开始的计数对齐到0起点，适配取模、整除运算
• 计算周期位置：对偏移后的值做10的取模运算，得到当前值在所属10位周期内的位置（范围0-9）
• 匹配桶区间：
  • 周期位置0-2（对应周期内第1-3个数）：桶起点为「整除10的结果*10 +1」，终点为起点+2
  • 周期位置3-5（对应周期内第4-6个数）：桶起点为「整除10的结果*10 +4」，终点为起点+2
  • 周期位置6-9（对应周期内第7-10个数）：桶起点为「整除10的结果*10 +7」，终点为起点+3
• 拼接起点和终点就是最终的分桶名称

验证示例：输入5，偏移后是4，取模10得4属于3-5区间，整除10得0，对应桶4-6，和规则一致；输入18，偏移后17，取模10得7属于6-9区间，整除10得1，对应桶17-20，和规则一致。

SQL实现语句 #

以下写法兼容绝大多数主流SQL引擎（MySQL、Hive、Spark SQL、ClickHouse等），直接替换业务表和字段名即可使用：

SELECT
    input_num, -- 替换为你的数值字段名
    CASE
        WHEN MOD(input_num - 1, 10) BETWEEN 0 AND 2 
            THEN CONCAT( FLOOR((input_num - 1)/10)*10 + 1, '-', FLOOR((input_num - 1)/10)*10 + 3 )
        WHEN MOD(input_num - 1, 10) BETWEEN 3 AND 5 
            THEN CONCAT( FLOOR((input_num - 1)/10)*10 + 4, '-', FLOOR((input_num - 1)/10)*10 + 6 )
        WHEN MOD(input_num - 1, 10) BETWEEN 6 AND 9 
            THEN CONCAT( FLOOR((input_num - 1)/10)*10 + 7, '-', FLOOR((input_num - 1)/10)*10 + 10 )
        -- 可选：超出业务数值范围的异常值返回自定义标识，按需开启
        -- ELSE 'invalid_num'
    END AS belong_bucket
FROM your_table -- 替换为你的业务表名

测试验证结果 #

用示例值和边界值跑数结果如下，完全匹配分桶规则：

• 1 → 1-3
• 5 → 4-6
• 10 → 7-10
• 13 → 11-13
• 18 → 17-20
• 26 → 24-26
• 30 → 27-30

如果后续数值范围扩展到30以上，这套逻辑不需要任何修改，会自动按3-3-4的循环规则生成对应分桶，比如31会匹配31-33、36匹配34-36、40匹配37-40。

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