Home » SQL & PL/SQL » SQL & PL/SQL » is this a bug? (about analytic function) and "order by" in partitioning clause (merged)
is this a bug? (about analytic function) and "order by" in partitioning clause (merged) [message #305754] |
Tue, 11 March 2008 21:21 |
kang
Messages: 89 Registered: November 2007
|
Member |
|
|
create table emp (
empno varchar2(10),
deptno varchar2(10),
sal number(10)
)
insert into emp(empno,deptno,sal) values('1','10',101);
insert into emp(empno,deptno,sal) values('2','20',102);
insert into emp(empno,deptno,sal) values('3','20',103);
insert into emp(empno,deptno,sal) values('4','10',104);
insert into emp(empno,deptno,sal) values('5','30',105);
insert into emp(empno,deptno,sal) values('6','30',106);
insert into emp(empno,deptno,sal) values('7','30',107);
insert into emp(empno,deptno,sal) values('8','40',108);
insert into emp(empno,deptno,sal) values('9','30',109);
insert into emp(empno,deptno,sal) values('10','30',110);
SELECT empno, deptno, sal,
last_value(sal)
OVER (PARTITION BY deptno) max_sal
FROM emp
SELECT empno, deptno, sal,
last_value(sal)
OVER (PARTITION BY deptno order by sal) max_sal
FROM emp
SELECT empno, deptno, sal,
max(sal)
OVER (PARTITION BY deptno) max_sal
FROM emp
SELECT empno, deptno, sal,
max(sal)
OVER (PARTITION BY deptno order by sal) max_sal
FROM emp
when "order by" is used in partitinging-clause the analytic function gives the result until the current value?
|
|
|
|
|
|
|
|
Re: "order by" in partitioning-clause [message #305774 is a reply to message #305754] |
Wed, 12 March 2008 00:00 |
kang
Messages: 89 Registered: November 2007
|
Member |
|
|
thanks.
let me check.
SELECT empno, deptno, sal,
last_value(sal)
OVER (PARTITION BY deptno order by sal desc) col1,
first_value(sal)
OVER (PARTITION BY deptno order by sal asc) col2,
first_value(sal)
OVER (PARTITION BY deptno order by sal desc) col3,
last_value(sal)
OVER (PARTITION BY deptno order by sal asc) col4
FROM emp
I don't know why col1 and col4 return the these kinds of results.
[Updated on: Wed, 12 March 2008 00:03] Report message to a moderator
|
|
|
|
|
is this a bug? (about analytic function) [message #306119 is a reply to message #305754] |
Wed, 12 March 2008 22:07 |
kang
Messages: 89 Registered: November 2007
|
Member |
|
|
create table emp (
empno varchar2(10),
deptno varchar2(10),
sal number(10)
)
insert into emp(empno,deptno,sal) values('1','10',101);
insert into emp(empno,deptno,sal) values('2','20',102);
insert into emp(empno,deptno,sal) values('3','20',103);
insert into emp(empno,deptno,sal) values('4','10',104);
insert into emp(empno,deptno,sal) values('5','30',105);
insert into emp(empno,deptno,sal) values('6','30',106);
insert into emp(empno,deptno,sal) values('7','30',107);
insert into emp(empno,deptno,sal) values('8','40',108);
insert into emp(empno,deptno,sal) values('9','30',109);
insert into emp(empno,deptno,sal) values('10','30',110);
insert into emp(empno,deptno,sal) values('11','30',100);
SELECT empno, deptno, sal,
last_value(sal)
OVER (PARTITION BY deptno order by sal desc) col1,
first_value(sal)
OVER (PARTITION BY deptno order by sal asc) col2,
first_value(sal)
OVER (PARTITION BY deptno order by sal desc) col3,
last_value(sal)
OVER (PARTITION BY deptno order by sal asc) col4
FROM emp
col2, col3 return what I expect.
I don't know why col1 and col4 return the these kinds of results.
|
|
|
|
|
Re: is this a bug? (about analytic function) and "order by" in partitioning clause (merged [message #306373 is a reply to message #305754] |
Thu, 13 March 2008 23:47 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
I don't think I entirely understand your question, but let me offer this as a description of first_value,last_value. First, you should check out the links suggests in above posts. From them we get the following:
Quote: | FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.
LAST_VALUE is an analytic function. It returns the last value in an ordered set of values.
|
But, using these functions we soon discover that these simple descriptions do not give us enough information to really understand the behavior we see. So next I would like to point you here from which I take another quote:
http://www.akadia.com/services/ora_analytic_functions.html#Windows
Quote: | The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. The default window is an anchored window that simply starts at the first row of a group an continues to the current row.
|
As you know, analytic functions work with three different expressions, the partition by clause, the order by clause, and a window clause. In your examples, you do not provide a window clause so I ask you a question: what is the window clause that is used given you did not supply one. A: the default of course, and it is described above (assuming above is correct (I make that assumption)). The following example should help to clarify:
create table somejunk (a number)
/
insert into somejunk
select rownum
from dual
connect by level <= 5
/
select a
from somejunk
/
select
a
,first_value(a) over (order by a) thefirst
,last_value(a) over (order by a) thelast
from somejunk
/
select
a
,first_value(a) over (order by a rows unbounded preceding) thefirst
,last_value(a) over (order by a rows unbounded preceding) thelast
from somejunk
/
select
a
,first_value(a) over (order by a rows between unbounded preceding and unbounded following) thefirst
,last_value(a) over (order by a rows between unbounded preceding and unbounded following) thelast
from somejunk
/
Running this code will give you a vision through which to understand what is happening. Let us examine in particular your version of the select as applied to this data (eg. no window clause is specified and thus we are using the default window clause).
SQL> select
2 a
3 ,first_value(a) over (order by a) thefirst
4 ,last_value(a) over (order by a) thelast
5 from somejunk
6 /
A THEFIRST THELAST
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
SQL>
The default windowing clause is restricting rows that are available to be evaluated during each invocation of each analytic. This is what it is supposed to do. Remember, the default window clause (that used when no window clause is explicitly given), is from the first row to the current row.
Thus, when evaluating row#1, the default window clause exposes only from the first row (row#1) to the current row (row#1) to the analytic. Hence the analytic:
LAST_VALUE(A) OVER (ORDER BY A)
sees row#1 thru row#1 and thus the last value it saw is 1.
Similarly, when we are evaluating the analytic for row#2 given an ordering by a, the default window clause exposes row#1 thru row#2 to the analytic and hence last_value takes the last value from 1,2. When evaluating analytics on row#3 the default window clause exposes row#1 thru row#3 to the analytic and hence last_value takes the last value from 1,2,3. And so on.
If you understand what was just said, then you should understand why LAST_VALUE(A) OVER (ORDER BY A) returns the results above given no explicit window clause.
When using last_value, what most people want however, is the last value from all rows in the partition, not the last value up through the current row as is enforced by the default window clause. When we use an explicit window clause in our analytic expression that tells the analytic to consider all rows in the partition as its window, then we get the result we seek.
SQL> select
2 a
3 ,first_value(a) over (order by a rows between unbounded preceding and unbounded following) thefirst
4 ,last_value(a) over (order by a rows between unbounded preceding and unbounded following) thelast
5 from somejunk
6 /
A THEFIRST THELAST
---------- ---------- ----------
1 1 5
2 1 5
3 1 5
4 1 5
5 1 5
SQL>
The mistake here is that most people assume the default window clause is all rows in the partition sorted by the order by clause. But in fact the default window clause appears to be from the first row in the partion to current row as defined by the sort order of the order by clause.
Given the default window clause as first to current, the last row in the window keeps changing as we march through our rows, but the first row never changes. The the evaluation of LAST_VALUE keeps changing as we march through our rows but FIRST_VALUE remains the same.
So, the quotes from the Oracle Manuals are not entirely complete.
Quote: | FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.
LAST_VALUE is an analytic function. It returns the last value in an ordered set of values.
|
last value should read more like this:
Quote: | LAST_VALUE is an analytic function. It retuns the last value in an ordered set of values but only from rows viewable as specificed by the window clause in use which in most cases is the default window clause.
|
Maybe this will help if you are still lost:
SQL> select
2 a
3 ,first_value(a) over (order by a desc) thefirst
4 ,last_value(a) over (order by a desc) thelast
5 from somejunk
6 /
A THEFIRST THELAST
---------- ---------- ----------
5 5 5
4 5 4
3 5 3
2 5 2
1 5 1
SQL>
I hope this is telling you what you want to know. Think about it some. It is hard to get your head around it. Also, look here:
http://www.orafaq.com/node/1874
Kevin
|
|
|
Goto Forum:
Current Time: Mon Nov 04 04:56:14 CST 2024
|