ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 3114|回复: 5

【求解】Min、Max分析函数order by的有趣现象

[复制链接]
认证徽章
论坛徽章:
2
秀才
日期:2017-06-29 10:05:55秀才
日期:2017-06-29 10:05:55
发表于 2017-7-20 22:56 | 显示全部楼层 |阅读模式
今天意外发现Min和Max分析函数加Order by,计算规则似乎不一致,请大家来分析分析是什么原因。请看测试结果和个人的分析。

先建一个表数据:
create table test_min_max
(
YearID number,
Line number
);

insert into test_min_max(yearID,line) values(2016,1001);
insert into test_min_max(yearID,line) values(2016,1002);
insert into test_min_max(yearID,line) values(2016,1003);
insert into test_min_max(yearID,line) values(2016,1004);
insert into test_min_max(yearID,line) values(2016,1007);
insert into test_min_max(yearID,line) values(2016,1008);
insert into test_min_max(yearID,line) values(2017,1011);
insert into test_min_max(yearID,line) values(2017,1012);
insert into test_min_max(yearID,line) values(2017,1013);
insert into test_min_max(yearID,line) values(2017,1014);
insert into test_min_max(yearID,line) values(2017,1015);
insert into test_min_max(yearID,line) values(2017,1017);
Commit;


【SQL1】
SELECT t.Yearid,
       t.Line AS Cur_Line,
       Lag(t.Line) Over(PARTITION BY t.Yearid ORDER BY t.Line) Prev_Line,
       MIN(t.Line) Over(PARTITION BY t.Yearid ORDER BY t.Line) Min_Line,
       MAX(t.Line) Over(PARTITION BY t.Yearid ORDER BY t.line) Max_Line
  FROM test_min_max t;


    YEARID   CUR_LINE  PREV_LINE   MIN_LINE   MAX_LINE
    ----------    ----------     ----------        ----------      ----------
      2016       1001                                1001          1001
      2016       1002           1001             1001          1002
      2016       1003           1002             1001          1003
      2016       1004           1003             1001          1004
      2016       1007           1004             1001          1007
      2016       1008           1007             1001          1008
      2017       1011                                1011          1011
      2017       1012           1011             1011          1012
      2017       1013           1012             1011          1013
      2017       1014           1013             1011          1014
      2017       1015           1014             1011          1015
      2017       1017           1015             1011          1017

12 rows selected


由上SQL1可以看出Max_Line的值并不是按YearID分组后计算所有Line的最大值。

【SQL2】
SELECT t.Yearid,
       t.Line AS Cur_Line,
       Lag(t.Line) Over(PARTITION BY t.Yearid ORDER BY t.Line) Prev_Line,
       MIN(t.Line) Over(PARTITION BY t.Yearid) Min_Line,
       MAX(t.Line) Over(PARTITION BY t.Yearid) Max_Line
  FROM test_min_max t;



    YEARID   CUR_LINE  PREV_LINE   MIN_LINE   MAX_LINE
    ----------    ----------     ----------        ----------      ----------
      2016       1001                                1001          1008
      2016       1002           1001             1001          1008
      2016       1003           1002             1001          1008
      2016       1004           1003             1001          1008
      2016       1007           1004             1001          1008
      2016       1008           1007             1001          1008
      2017       1011                                1011          1017
      2017       1012           1011             1011          1017
      2017       1013           1012             1011          1017
      2017       1014           1013             1011          1017
      2017       1015           1014             1011          1017
      2017       1017           1015             1011          1017

12 rows selected

SQL2才查询出了个人希望的结果。
两个SQL对比,仅从结果表面来看,不然得出结论:
① Min()分析函数加不加Order by结果都一致;
② Min(),Max()分析函数加Order by计算逻辑不一致。(不知道是什么原因导致)

论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
发表于 2017-7-20 23:47 | 显示全部楼层
如果不加ORDER BY, 就没有窗口,计算范围是整个分区;
加上ORDER BY, 默认窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,就是排序后从分区第一行一直到当前行为止。
那么MIN还是第一行,而MAX就是当前行了。

使用道具 举报

回复
认证徽章
论坛徽章:
2
秀才
日期:2017-06-29 10:05:55秀才
日期:2017-06-29 10:05:55
发表于 2017-7-20 23:50 | 显示全部楼层
newkid 发表于 2017-7-20 23:47
如果不加ORDER BY, 就没有窗口,计算范围是整个分区;
加上ORDER BY, 默认窗口是RANGE BETWEEN UNBOUNDED  ...

版主,你这样的分析我瞬间明白了,ths。

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:31:22SQL大赛参与纪念
日期:2011-04-13 12:08:17ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09秀才
日期:2016-03-24 09:10:24秀才
日期:2016-12-05 11:16:06
发表于 2017-7-25 10:45 | 显示全部楼层
你还没理解透彻分析函数的用法  .  用SUM 试试这个例子 你就更明白点了.  这种奇怪的用法结果,应用中有时有奇效

使用道具 举报

回复
认证徽章
论坛徽章:
2
秀才
日期:2017-06-29 10:05:55秀才
日期:2017-06-29 10:05:55
发表于 2017-7-25 21:35 | 显示全部楼层
qqjue 发表于 2017-7-25 10:45
你还没理解透彻分析函数的用法  .  用SUM 试试这个例子 你就更明白点了.  这种奇怪的用法结果,应用中有时有 ...

嗯,还在学习中,接触不深,理解还在皮毛

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2017-10-9 08:44 | 显示全部楼层
分析的好清楚,棒棒哒

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票8.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表