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 Go to next message
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 #305755 is a reply to message #305754] Tue, 11 March 2008 21:44 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Yes
Re: "order by" in partitioning-clause [message #305767 is a reply to message #305755] Tue, 11 March 2008 23:14 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
when I want last_value,
how can I know the value without sorting operations?

SELECT empno, deptno, sal,
last_value(sal)
OVER (PARTITION BY deptno order by sal) max_sal
FROM emp

Re: "order by" in partitioning-clause [message #305768 is a reply to message #305767] Tue, 11 March 2008 23:20 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
For last_value you need it. For max you don't. Order by works differently for each.
Re: "order by" in partitioning-clause [message #305771 is a reply to message #305768] Tue, 11 March 2008 23:33 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
I'm confused.
tell me more detail on order by in partitioning-clause.
Re: "order by" in partitioning-clause [message #305772 is a reply to message #305754] Tue, 11 March 2008 23:46 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
useful note for you.

http://www.orafaq.com/node/55

regards,

[Updated on: Tue, 11 March 2008 23:48]

Report message to a moderator

Re: "order by" in partitioning-clause [message #305774 is a reply to message #305754] Wed, 12 March 2008 00:00 Go to previous messageGo to next message
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

Re: "order by" in partitioning-clause [message #305782 is a reply to message #305754] Wed, 12 March 2008 00:42 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/functions46a.htm

http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/functions57.htm

regards,
Re: "order by" in partitioning-clause [message #306118 is a reply to message #305782] Wed, 12 March 2008 22:05 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member

these do not explain my problems.
is this a bug? (about analytic function) [message #306119 is a reply to message #305754] Wed, 12 March 2008 22:07 Go to previous messageGo to next message
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 #306149 is a reply to message #305754] Thu, 13 March 2008 01:17 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
col2, col3 return what I expect.

I don't know why col1 and col4 return the these kinds of results.



may you explain more, why do you think so?

regards,

[Updated on: Thu, 13 March 2008 01:18]

Report message to a moderator

Re: is this a bug? (about analytic function) and "order by" in partitioning clause (merged [message #306364 is a reply to message #306149] Thu, 13 March 2008 21:54 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
I think col1=col2 and col3=col4.
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 Go to previous message
Kevin Meade
Messages: 2101
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

Previous Topic: Stored procedure compilation error
Next Topic: Sql Query
Goto Forum:
  


Current Time: Sun Dec 11 06:35:16 CST 2016

Total time taken to generate the page: 0.18844 seconds