sql - Oracle avg in group by 未正确排序

问题描述

在 Oracle 12.2 的 group by 子句中对聚合函数使用 order by 时，我们收到不正确的结果排序。在玩弄它之后，我们发现该查询仅在以某些方式表达时才有效（见下文）。

所以这是我们的问题：

1) 为什么按 avg 的行为排序很挑剔？查询是否基于某些记录的逻辑/限制按预期运行？它与没有小数位的数字（16）的基础数据类型有关吗？

2) 为什么在查询 4 中使用别名使其工作，而查询 3 不起作用？

3) 为什么在按持续时间升序排序时查询效果更好？它没有在下面显示，但是查询 2 在 asc 时有效，即使它不工作 desc。查询 1 不适用于 asc。

在下面的示例中，请注意持续时间是一个数字 (16)。

查询 1：按功能平均功能顺序

select
    name,
    avg(duration)
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg(duration) desc

-- Query 1 result (wrong)
(some name) 1224417.83471074
(some name) 33568438.1548673
(some name) 3928150.12809406
(some name) 1434939.13464658
(some name) 269338.574638521

查询 2：按别名的平均函数顺序

-- Query 2: order by avg alias
select
    name,
    avg(duration) avg
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg desc

-- Query 2 result (wrong)
-- See query 1 result

-- Note: For some reason this query works correctly when ordered asc

查询 3：按函数转换顺序的平均函数

select
    name,
    to_number(avg(duration))
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by to_number(avg(duration)) desc

-- Query 3 result (wrong)
-- See query 1 result

查询 4：按别名转换顺序的 Avg 函数

select
    name,
    to_number(avg(duration)) avg
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg desc

-- Query 4 results (correct)
(some name) 562654936
(some name) 498804314
(some name) 263681023
(some name) 245531731
(some name) 188103278
-- the values with decimals show up later in the right order

查询 5 和 6：在外部查询中带/不带强制转换的平均函数

select * from (
    select
        name,
        to_number(avg(duration)) avg -- works without to_number as well
    from table1
    join table2 on table1.table2_id = table2.id
    where duration is not null
    group by name
) order by avg desc

-- Query 5 & 6 results (correct)
-- See query 4 results

标签： sqloracleoracle12c