SQLite按小数点第一位分组统计数值区间数量的问题

问题分析 #

你之前用CASE语句全归到'other numbers'，大概率是没正确提取到小数点后第一位的数字，导致所有条件都不匹配，直接走到ELSE分支。下面给两种靠谱的实现方案，按需选择。

方案一：数学计算法（推荐，精度更可靠） #

通过数学运算提取小数点后第一位数字，再生成对应的区间字符串：

SELECT
    CASE
        WHEN digit BETWEEN 0 AND 9 THEN 
            CAST(digit / 10.0 AS TEXT) || ':' || CAST((digit + 1) / 10.0 AS TEXT)
        ELSE 'other numbers'
    END AS number_range,
    COUNT(*) AS count
FROM (
    -- 子查询：提取小数点后第一位的整数数字
    SELECT
        CAST((number - FLOOR(number)) * 10 AS INTEGER) AS digit
    FROM your_table_name -- 替换成你的表名
) AS subquery
GROUP BY number_range
ORDER BY digit;

逻辑说明：

1. 子查询里，number - FLOOR(number) 得到纯小数部分（比如0.18得到0.18，1.23得到0.23）
2. 乘以10后取整，得到小数点后第一位数字（0.18*10=1.8→取整为1）
3. 主查询根据digit生成区间字符串（digit=1时，就是'0.1:0.2'）
4. 最后按区间分组统计数量，按digit排序保证区间顺序正确

如果你的数据包含负数，比如-0.18，这个方法会得到digit=-1，生成'-0.1:-0.0'区间。如果需要把负数按绝对值分组，可以把number改成ABS(number)。

方案二：字符串处理法（适合需要处理整数的场景） #

如果表中存在整数（比如2、5这类没有小数点的数），可以用字符串截取的方式处理：

SELECT
    CASE
        WHEN decimal_digit BETWEEN '0' AND '9' THEN 
            CAST(CAST(decimal_digit AS INTEGER)/10.0 AS TEXT) || ':' || CAST((CAST(decimal_digit AS INTEGER)+1)/10.0 AS TEXT)
        ELSE 'other numbers'
    END AS number_range,
    COUNT(*) AS count
FROM (
    -- 处理有小数的记录，截取小数点后第一位
    SELECT
        SUBSTR(CAST(number AS TEXT), INSTR(CAST(number AS TEXT), '.') + 1, 1) AS decimal_digit
    FROM your_table_name
    WHERE INSTR(CAST(number AS TEXT), '.') > 0
    UNION ALL
    -- 处理整数，默认小数点后第一位为0，归到0.0:1.0区间
    SELECT '0' AS decimal_digit
    FROM your_table_name
    WHERE INSTR(CAST(number AS TEXT), '.') = 0
) AS subquery
GROUP BY number_range
ORDER BY CAST(decimal_digit AS INTEGER);

逻辑说明：

1. 用INSTR找到小数点的位置，SUBSTR截取小数点后第一个字符
2. 整数单独处理，把它们的小数点后第一位视为0，归到0.0:1.0区间
3. 后续分组统计逻辑和方案一一致

为什么之前的CASE语句失效？ #

常见的坑包括：

• 直接用number做范围判断（比如WHEN number >=0.1 AND number <0.2），但浮点数精度问题导致匹配失败
• 提取小数部分时用了错误的函数（比如没有用FLOOR，而是直接截断，导致负数处理出错）
• 条件判断的范围覆盖不全，比如漏了某些digit值

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