如何在SQLite中用MAX函数按占MAX值百分比排序限定行

原始水果数据 #

需求说明 #

给定某一水果与价格区间，计算该水果在该价格区间内的排名（以橙子（Orange）和≤20的价格区间为例）：

• 筛选价格≤20的水果，得到橙子、桃子、蓝莓、苹果；
• 找出该区间内的最高评分（MAX(Rating)）为4；
• 计算每个水果评分占该最高评分的百分比，以此排序。

预期结果 #

SQLite兼容的完整SQL实现 #

要实现该需求，可通过子查询获取指定价格区间内的最高评分，再在主查询中计算评分占比并排序：

SELECT
    Fruit,
    Price,
    Rating,
    CONCAT(Rating, '/', max_rating, '=', ROUND((Rating * 100.0) / max_rating, 0), '%') AS "Rating Percentage"
FROM
    fruits,
    (SELECT MAX(Rating) AS max_rating FROM fruits WHERE Price <= 20) AS subquery
WHERE
    Price <= 20
ORDER BY
    (Rating * 1.0) / max_rating DESC;

关键说明 #

1. 子查询(SELECT MAX(Rating) AS max_rating FROM fruits WHERE Price <= 20)专门获取目标价格区间内的最高评分；
2. CONCAT函数用于拼接成预期的百分比显示格式，ROUND((Rating * 100.0) / max_rating, 0)负责计算百分比并取整；
3. 排序时用(Rating * 1.0) / max_rating DESC确保按占比从高到低排列，*1.0是为了避免SQLite整数除法导致的精度丢失。

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