关于分析函数OVER(PARTITION BY)的困惑

yinan9 2012-02-13 04:49:03

今天偶然做了一个实验

SELECT E.*,

       MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) A,

       MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL DESC) B

  FROM EMP E;

查询结果如图

我的疑惑是为什么相同JOB组，返回确有不同。
还请大家指教。

可用分不多了，请见谅！

...全文

504 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

samfeng_2003 2012-02-17

打赏
举报

很简单，楼主，你这样拆分一哈应该就明白了，我这里举例一下：
建表
create table myFirst
(
ENAME VARCHAR2(50),
JOB VARCHAR2(50),
SAL NUMBER(10,2),
SALE_DATE DATE
);

--查看数据
select * from myFirst;

SMITH CLERK 800 2004-10-1 2:01:01
BLAKE MANAGER 100 2004-10-2 2:01:05
JONES MANAGER 200 2004-10-1 2:01:08
WARD SALESMAN 200 2004-10-3 2:01:08
MARTIN SALESMAN 100 2014-12-1 2:01:08
ALLEN SALESMAN 300 2008-9-1 2:01:08

--用AVG的倒序分析
SELECT
A.*,
avg(SAL) OVER(PARTITION BY JOB ORDER BY SAL desc) SUMA
FROM
myFirst A;

SMITH CLERK 800 2004-10-1 2:01:01 800
JONES MANAGER 200 2004-10-1 2:01:08 200
BLAKE MANAGER 100 2004-10-2 2:01:05 150
ALLEN SALESMAN 300 2008-9-1 2:01:08 300
WARD SALESMAN 200 2004-10-3 2:01:08 250
MARTIN SALESMAN 100 2014-12-1 2:01:08 200

--再用正序分析
SELECT
A.*,
avg(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) SUMA
FROM
myFirst A;

SMITH CLERK 800 2004-10-1 2:01:01 800
BLAKE MANAGER 100 2004-10-2 2:01:05 100
JONES MANAGER 200 2004-10-1 2:01:08 150
MARTIN SALESMAN 100 2014-12-1 2:01:08 100
WARD SALESMAN 200 2004-10-3 2:01:08 150
ALLEN SALESMAN 300 2008-9-1 2:01:08 200

楼主可以仔细看哈数据就明白了其中的机制，关注顺序。出现你那种情况其实主要是ASC起作用后，你没有理解到DESC的作用。

yinan9 2012-02-16

打赏
举报

[Quote=引用 12 楼 forgetsam 的回复:]

max min avg sum count 不应该在over中用order by 字句否则统计效果是从首行到当前行

如果非要用在后面加上 rows between unbounded preceding and unbounded following
[/Quote] 谢谢！到点上了！

tongtian24 2012-02-16

打赏
举报

MAX() OVER(PARTITION BY ORDER BY ) 中有2种情况：
1.当不加order by时，就是一个组里的所有值sum的结果。
2.当加上order by 时，从一个组里的第一行加，第二行就是第一行加第二行，一直加到此组里最后一行。所有你分别升序和降序的结果不同。

forgetsam 2012-02-15

打赏
举报

max min avg sum count 不应该在over中用order by 字句否则统计效果是从首行到当前行

如果非要用在后面加上 rows between unbounded preceding and unbounded following

yinan9 2012-02-14

打赏
举报

[Quote=引用 9 楼 java3344520 的回复:]
我记得哪里看过这个原理，记得那次的表达式是这样的

SELECT E.*,
MIN(SAL) OVER(PARTITION BY JOB ORDER BY SAL ) A,
MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL ) B
FROM EMP E;
想要的结果是同时取最大和最小，结果取不出来，默认升序，有一种结果只能取到当前行，原……
[/Quote]好的谢谢！对于取最值没有问题。

xpingping 2012-02-14

打赏
举报

求和，就知道了啊……

SELECT E.*,

       MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) A,

       MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL DESC) B,

        SUM(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) C,

       SUM(SAL) OVER(PARTITION BY JOB ORDER BY SAL DESC) D

FROM EMP E;

iqlife 2012-02-14

打赏
举报

我记得哪里看过这个原理，记得那次的表达式是这样的

SELECT E.*,
MIN(SAL) OVER(PARTITION BY JOB ORDER BY SAL ) A,
MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL ) B
FROM EMP E;
想要的结果是同时取最大和最小，结果取不出来，默认升序，有一种结果只能取到当前行，原理好像是通过TRACE事件跟踪出来的，自己试试吧
要取得最大和最小，要使用下面的语句

SELECT E.*,
MIN(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) A,
MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL DESC) B
FROM EMP E;

yinan9 2012-02-14

打赏
举报

[Quote=引用 7 楼 notebook800 的回复:]

MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) A,
MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL DESC) B

你这两句是矛盾的,不可能按照JOB分组后,并同时按照SAL正序和倒序排序.
只有b列的分组排序是有效的,a列的max(sal)其实只是针对一条记录.
[/Quote]尽管如此，这种逻辑还是不甚理解

notebook800 2012-02-14

打赏
举报

MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL ASC) A,
MAX(SAL) OVER(PARTITION BY JOB ORDER BY SAL DESC) B

你这两句是矛盾的,不可能按照JOB分组后,并同时按照SAL正序和倒序排序.
只有b列的分组排序是有效的,a列的max(sal)其实只是针对一条记录.

yinan9 2012-02-14