Oracle如何从查询行结果中筛选数据计算平均值并关联展示

实现方案 #

可以直接在单次查询中完成需求，Oracle的分析窗口函数可以完美适配该场景，不需要额外子查询关联平均值结果。
你只需要在原查询基础上增加过滤低TOTAL行的条件，再用AVG() OVER()计算全局平均值即可，完整查询语句如下：

SELECT
    USERNAME,
    TOTAL,
    ROUND(AVG(TOTAL) OVER()) AS AVG_FROM_TOTAL,
    MATH,
    ROUND(AVG(MATH) OVER()) AS AVG_FROM_MATH
FROM 
(
    SELECT 
        B.USERNAME,     
        COUNT(CLASS_ID) TOTAL,   
        SUM(CASE WHEN ROOM_ID = '1001' THEN 1 ELSE 0 END) AS MATH
    FROM LESSON A, STUDENT B
    WHERE  
        A.USERNAME = B.USERNAME                    
        AND A.CREATED_DATE >= TO_DATE(:created_date ,'YYYYMMDDHH24MISS')
        AND A.CREATED_DATE < TO_DATE(:created_end_date ,'YYYYMMDDHH24MISS')
    GROUP BY B.USERNAME 
) A
-- 过滤TOTAL过低的用户，可根据实际业务调整阈值
WHERE TOTAL > 10
ORDER BY TOTAL DESC;

逻辑说明 #

• 内层子查询和你原有逻辑一致，先按用户聚合算出TOTAL、MATH两个指标
• 外层WHERE TOTAL > 10会过滤掉Matt、Jane这类低数值用户，避免它们参与平均值计算
• AVG(指标) OVER()是无分区的窗口函数，会把当前过滤后的整个结果集作为统计范围，计算全局平均值并把结果回填到每一行，正好匹配你需要的每行都附带相同平均值的需求
• 示例中平均值为整数，所以用ROUND()函数包裹AVG()调整了精度，你也可以根据业务需要调整保留的小数位数

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