Oracle 按结果排序的Max() 分析

我不明白为什么当使用最大或最小分析函数时，使用order by会改变结果-我希望所有的最大调用都有相同的结果，所有的最小调用都有相同的结果。
文档也没有给我任何理由相信它们会有所不同。

with
td as (select 'X1' as grp1, to_date('09/03/2020 09:00:00','MM/DD/YYYY HH24:MI:SS') as stime, to_date('09/03/2020 10:59:59','MM/DD/YYYY HH24:MI:SS') as etime from dual
         union all
         select 'X1', to_date('09/03/2020 07:30:00','MM/DD/YYYY HH24:MI:SS'), to_date('09/03/2020 09:32:00','MM/DD/YYYY HH24:MI:SS') from dual
         union all
         select 'X1', to_date('09/03/2020 11:00:00','MM/DD/YYYY HH24:MI:SS'), to_date('09/03/2020 18:45:00','MM/DD/YYYY HH24:MI:SS') from dual)
select  td.*,
        max(td.etime) over (partition by td.grp1) as max_etime,
        max(td.etime) over (partition by td.grp1 order by td.stime asc) as max_etime_asc,
        max(td.etime) over (partition by td.grp1 order by td.stime desc) as max_etime_desc,
        min(td.etime) over (partition by td.grp1) as min_etime,
        min(td.etime) over (partition by td.grp1 order by td.stime asc) as min_etime_asc,
        min(td.etime) over (partition by td.grp1 order by td.stime desc) as min_etime_desc
from    td
复制

GR STIME               ETIME               MAX_ETIME           MAX_ETIME_ASC       MAX_ETIME_DESC      MIN_ETIME           MIN_ETIME_ASC       MIN_ETIME_DESC     
-- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
X1 09/03/2020 07:30:00 09/03/2020 09:32:00 09/03/2020 18:45:00 09/03/2020 09:32:00 09/03/2020 18:45:00 09/03/2020 09:32:00 09/03/2020 09:32:00 09/03/2020 09:32:00
X1 09/03/2020 09:00:00 09/03/2020 10:59:59 09/03/2020 18:45:00 09/03/2020 10:59:59 09/03/2020 18:45:00 09/03/2020 09:32:00 09/03/2020 09:32:00 09/03/2020 10:59:59
X1 09/03/2020 11:00:00 09/03/2020 18:45:00 09/03/2020 18:45:00 09/03/2020 18:45:00 09/03/2020 18:45:00 09/03/2020 09:32:00 09/03/2020 09:32:00 09/03/2020 18:45:00
复制

当您在 “过度” 子句中有order by时，默认窗口子句为:

range between unbounded preceding and current row
复制

这意味着:

计算从数据设置开始到与当前行具有相同值的所有行的函数。所以任何值在当前之后的行都是excluded从计算。

因此，预计min/max的值可以在您处理数据集时更改。

如果要获得整个分区的最小值/最大值，请删除顺序。

或者您可以在排序后添加此窗口:

rows between unbounded preceding and unbounded following 
复制

我想不出一个很好的理由来做到最小/最大。这是很多额外的代码，以获得与单独分区相同的效果。