Oracle查询：找出累计占总积分75%的顶级用户及报错解决

首先咱们先拆解下你原查询的问题：

• 你的内层子查询SELECT users, SUM (points), RANK () OVER (ORDER BY SUM (points) DESC) r FROM points_tbl里，只返回了users、SUM(points)（还没给别名）和r三个字段，根本没有points列，所以外层查询里的o.points自然是无效标识符，这就是ORA-00904错误的根源。
• 另外原查询的逻辑完全没贴合需求：你要的是累计占总积分75%的顶级用户，但原查询既没计算总积分，也没做累计占比统计，完全没达到目标。

接下来给你正确的查询方案，咱们用CTE（公共表表达式）分步实现，逻辑更清晰：

WITH user_total_points AS (
    -- 第一步：计算每个用户的总积分，同时算出所有用户的总积分
    SELECT 
        users,
        SUM(points) AS user_total,
        SUM(SUM(points)) OVER () AS grand_total
    FROM points_tbl
    GROUP BY users
),
cumulative_point_ratios AS (
    -- 第二步：按用户积分从高到低排序，计算累计积分占总积分的比例
    SELECT 
        users,
        user_total,
        -- 累计积分 = 从最高分到当前用户的积分总和
        SUM(user_total) OVER (ORDER BY user_total DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / grand_total AS cumulative_ratio
    FROM user_total_points
)
-- 第三步：筛选累计占比符合要求的用户
SELECT users
FROM cumulative_point_ratios
-- 需求1：累计占比不超过75%的用户
WHERE cumulative_ratio <= 0.75
-- 需求2：如果要确保覆盖至少75%（比如前3个用户累计70%，第4个加上到80%，需包含第4个），用下面的条件
-- WHERE cumulative_ratio - (user_total / grand_total) < 0.75
ORDER BY user_total DESC;

逻辑细节解释： #

1. user_total_points：先按用户分组计算单个用户的总积分user_total，再用SUM(SUM(points)) OVER ()算出所有用户的总积分grand_total（这个窗口函数不需要分组，直接全局求和）。
2. cumulative_point_ratios：按用户积分降序排列，用窗口函数计算从最高分到当前用户的累计积分，再除以总积分得到累计占比cumulative_ratio。
3. 最后根据你的具体需求选择筛选条件：
   • 用cumulative_ratio <= 0.75会选出累计占比不超过75%的用户；
   • 用cumulative_ratio - (user_total / grand_total) < 0.75则会确保选出的用户累计积分至少覆盖75%（即使最后一个用户加入后超过75%，也会包含他）。

比如你示例里的dick、mary、jack和sam，假设他们的累计积分刚好达到或覆盖75%，这个查询就能正确返回他们。

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