min/max partition by asc和desc结果不一致的研究

之前看了个博文，讲的“partition by和group by对比”
先上原文链接：
https://www.cnblogs.com/hello-yz/p/9962356.html

具体的内容我就不重复了，关注点其实是在“第4点. sql2”这段

select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num asc) as amount
from table_temp a
group by a.cc,a.num;

select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num desc) as amount
from table_temp a
group by a.cc,a.num;

一直很费解他下面的那句话，直到查到另一篇文章
https://zhuanlan.zhihu.com/p/150812199?from_voters_page=true

“（一）标准聚合函数
1、移动平均窗口函数
里面的影响行数的范围（限定计算移动平均的范围）”
这段

rows between 2 preceding and current row  # 取当前行和前面两行


rows between unbounded preceding and current row   # 包括本行和之前所有的行


rows between current row and unbounded following  # 包括本行和之后所有的行


rows between 3 preceding and current row   # 包括本行和前面三行


rows between 3 preceding and 1 following   # 从前面三行和下面一行，总共五行

当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row.
当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following

这边可以看到其实是存在一个默认值的，对于最上面那个sql就变成了

select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num asc rows between unbounded preceding and current row) as amount
from table_temp a
group by a.cc,a.num;

select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num desc rows between unbounded preceding and current row) as amount
from table_temp a
group by a.cc,a.num;

那如果倒序的话其实应该改成

select a.cc,a.num, min(a.num) over (partition by a.cc order by a.num desc rows between current row and unbounded following) as amount
from table_temp a
group by a.cc,a.num;

当然甚至可以改成

rows between unbounded preceding and unbounded following

注意这边的preceding和following前面加1不代表第一行或者最后行，而是表示当前行的前一行或者后一行。

文中涉及的文章都挺好，都建议去学习看看。