存在row_number()时ROUND函数异常及排名错误技术问询

根据用户累计步行里程做排名，数据存在walks表，每次用户步行就新增一条记录。但执行聚合后的查询时出现异常：

• 表结构语句：

create temporary table walks
(
    id       int unsigned auto_increment primary key,
    user_id  int unsigned             not null,
    miles_walked float unsigned default '0' not null,
    date date not null
);

• 测试数据插入语句：

insert into walks (user_id, miles_walked, date)
values
    (1, 10.1, '2022-12-20'),
    (2, 60.2, '2022-12-21'),
    (3, 30.3, '2022-12-22'),
    (1, 0.4, '2022-12-23'),
    (2, 10.5, '2022-12-24'),
    (3, 10.6, '2022-12-25'),
    (1, 40.7, '2022-12-26'),
    (2, 80.8, '2022-12-27'),
    (3, 30.9, '2022-12-28');

• 执行以下查询后，发现user_id=2和3的ROUND计算结果错误；实际业务里，类似ROW_NUMBER() OVER (ORDER BY (SUM(LENGTH(reports.comments)) + SUM(report_items.report_items_characters_number)) DESC) AS ranking的语句，也会导致排名结果异常：

select user_id,
       SUM(miles_walked) as miles_walked_total,
       ROUND(SUM(miles_walked), 1) as miles_walked_total_rounded,
       row_number() over (order by SUM(miles_walked) desc)  as miles_rank
from walks
group by user_id
order by user_id

1. 拆分聚合与后续计算，避免嵌套执行异常 #

问题核心是聚合函数嵌套其他函数时，数据库的执行顺序可能导致中间结果处理偏差，比如浮点聚合后的精度丢失、字符串长度聚合时的隐式转换问题。

解决方式是先用子查询或CTE（公共表达式）完成基础聚合，再在外部查询做函数处理和排名：

步行里程场景的修正查询 #

WITH user_total_miles AS (
    SELECT 
        user_id,
        SUM(miles_walked) AS miles_walked_total
    FROM walks
    GROUP BY user_id
)
SELECT 
    user_id,
    miles_walked_total,
    ROUND(miles_walked_total, 1) AS miles_walked_total_rounded,
    ROW_NUMBER() OVER (ORDER BY miles_walked_total DESC) AS miles_rank
FROM user_total_miles
ORDER BY user_id;

字符串长度聚合排名的修正查询 #

WITH total_characters AS (
    SELECT 
        user_id, -- 按实际业务调整分组字段
        SUM(LENGTH(reports.comments)) + SUM(report_items.report_items_characters_number) AS total_chars
    FROM reports
    JOIN report_items ON reports.id = report_items.report_id -- 按实际业务调整关联条件
    GROUP BY user_id
)
SELECT 
    user_id,
    total_chars,
    ROW_NUMBER() OVER (ORDER BY total_chars DESC) AS ranking
FROM total_characters;

2. 优化浮点字段精度（针对里程场景） #

如果是float类型导致的精度问题，建议把miles_walked改成decimal(10,2)（根据业务需求调整精度），避免浮点运算的精度丢失：

ALTER TABLE walks MODIFY COLUMN miles_walked DECIMAL(10,2) UNSIGNED DEFAULT '0' NOT NULL;

3. 验证执行计划排查问题 #

如果问题还存在，用数据库的执行计划工具（比如MySQL的EXPLAIN、PostgreSQL的EXPLAIN ANALYZE）查看聚合和窗口函数的执行顺序，排查是否有隐式转换或索引导致的计算偏差。

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