Question on last_value
satyadeep chandrashekar, February 09, 2010 - 7:50 pm UTC
Would appreciate your help in understanding how last_value works -
N
--
1
2
3
5
6
7
The result of inner select provided by you -
N B E
- - -
1 1
2
3 x
5 5
6
7 x
The outer select with last_value is able to group 1,2,3 and get last value as 1 and group 5,6,7 and get last value as 5. The over(...) does not specify any partition. Can you please explain how last_value works? To me, it seems it always return 1 since I am not sure how the grouping is happening.
Thanks,
Satya.
February 15, 2010 - 3:01 pm UTC
last_value(begin_grp ignore nulls) over (order by n)
that has an implicit partition of "entire set"
it has an implicit window of "current row and unbounded rows preceding"
so, it takes the entire set and sorts it by N. Then, for the first row, it takes the last value (ignoring nulls) starting from the current row (row 1 after sorting by n) and all preceding rows (eg: it takes the last NON NULL value of begin_grp from the current row backwards, up, in the result set).
Another way
Martijn, February 16, 2010 - 5:08 am UTC
Using a bit of Aketi's "Tabibitosan-method" ;)
(
http://forums.oracle.com/forums/thread.jspa?messageID=3989678 )
SQL> select n
2 , case
3 when n = max(n) over (partition by grp)
4 then min(n) over (partition by grp)
5 end s
6 , case
7 when n = max(n) over (partition by grp)
8 and min(n) over (partition by grp) <> max(n) over (partition by grp)
9 then max(n) over (partition by grp)
10 end e
11 from ( select n
12 , n-row_number() over (order by n) grp
13 from rand
14 );
N S E
---------- ---------- ----------
1
2
3 1 3
5 5
7
8
9 7 9
11 11
8 rows selected.
SQL>
Another way to use the last_value function
Alan Stewart, March 11, 2010 - 10:07 am UTC
create table RAND as
select level N from dual connect by level < 12;
delete from RAND where N in (4,6,10);
with R as
(select
n,
lag (n,1,n) over (order by n) lagn,
lead(n,1,n) over (order by n) leadn,
case when lag (n,1,n) over (order by n) <> n-1 then n end gmin,
case when lead(n,1,n) over (order by n) <> n+1 then n end gmax
from RAND
)
select
n, lagn, leadn, gmin, gmax,
last_value (gmin ignore nulls) over (order by n) first_in_group,
last_value (gmax ignore nulls) over (order by n desc) last_in_group
from R
order by n;
And yet another method
isotope, May 30, 2012 - 12:27 pm UTC
SQL>
SQL>
SQL> --
SQL> SELECT *
2 FROM rand
3 /
N
----------
1
2
3
5
7
8
9
11
8 rows selected.
SQL>
SQL>
SQL> --
SQL> SELECT n,
2 CASE diff - LEAD (diff) OVER (ORDER BY 1)
3 WHEN -1
4 THEN MIN (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
5 WHEN NULL
6 THEN n
7 END AS strt,
8 CASE diff - LEAD (diff) OVER (ORDER BY 1)
9 WHEN -1
10 THEN MAX (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
11 WHEN NULL
12 THEN n
13 END AS fin
14 FROM (
15 SELECT n,
16 n - ROW_NUMBER () OVER (ORDER BY n) AS diff
17 FROM rand x
18 ) t
19 /
N STRT FIN
---------- ---------- ----------
1
2
3 1 3
5 5 5
7
8
9 7 9
11
8 rows selected.
SQL>
SQL>
SQL>
SQL>
Sorry, the one above is incorrect!
isotope, May 30, 2012 - 12:33 pm UTC
SQL>
SQL>
SQL> --
SQL> SELECT *
2 FROM rand
3 /
N
----------
1
2
3
5
7
8
9
11
8 rows selected.
SQL>
SQL>
SQL> --
SQL> SELECT n,
2 CASE
3 WHEN diff <> LEAD (diff) OVER (ORDER BY 1)
4 THEN MIN (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
5 WHEN LEAD (diff) OVER (ORDER BY 1) is null
6 THEN n
7 END AS strt,
8 CASE
9 WHEN diff <> LEAD (diff) OVER (ORDER BY 1)
10 THEN MAX (n) KEEP (DENSE_RANK FIRST ORDER BY 1) OVER (PARTITION BY diff)
11 WHEN LEAD (diff) OVER (ORDER BY 1) is null
12 THEN n
13 END AS fin
14 FROM (
15 SELECT n,
16 n - ROW_NUMBER () OVER (ORDER BY n) AS diff
17 FROM rand x
18 ) t
19 /
N STRT FIN
---------- ---------- ----------
1
2
3 1 3
5 5 5
7
8
9 7 9
11 11 11
8 rows selected.
SQL>
SQL>
SQL>
SQL>
another method
igor, February 11, 2013 - 5:57 pm UTC
just wanted to share another method - works for me in 10.2.0.4
(honestly though, I'm still not 100% sure if rownum is OK here)
SQL> create table rand as
2 select 12 - level n
3 from dual
4 connect by level < 12;
Table created.
SQL> delete from RAND where N in (4,6,10);
3 rows deleted.
SQL> select * from rand;
N
----------
11
9
8
7
5
3
2
1
8 rows selected.
SQL> select n,
2 case when
3 last_value(n) over (partition by n-rownum order by n rows
4 between unbounded preceding and unbounded following) = n
5 then
6 first_value(n) over (partition by n-rownum order by n rows
7 between unbounded preceding and unbounded following)
8 else
9 null end strt,
10 case when
11 last_value(n) over (partition by n-rownum order by n rows
12 between unbounded preceding and unbounded following) = n
13 then
14 last_value(n) over (partition by n-rownum order by n rows
15 between unbounded preceding and unbounded following)
16 else
17 null end fin
18 from (select * from rand order by n);
N STRT FIN
---------- ---------- ----------
1
2
3 1 3
5 5 5
7
8
9 7 9
11 11 11
8 rows selected.
SQL>
February 12, 2013 - 7:35 am UTC
I'm not sure about the rownum usage here either. rownum has to be assigned before the order by in the analytic - and the order by in the subquery is free to be optimized away (unless it was a top-n query - where rownum < :x)...
another method with model clause
igor, February 15, 2013 - 9:52 pm UTC
Thanks Tom. It seems I should use rownum more carefully indeed ..
By the way, I just found how simply this can be done with model clause - no aggregates, no analytics.
SQL> SELECT n, strt, fin
2 FROM rand
3 MODEL
4 DIMENSION BY (n)
5 MEASURES (0 strt, 0 fin)
6 RULES( strt[n] ORDER BY n = NVL(strt[CV()-1], CV(n)),
7 strt[n] ORDER BY n = NULLIF(strt[CV()], strt[CV()+1]),
8 fin[n] = CASE WHEN strt[CV()] IS NULL THEN NULL ELSE CV(n) END
9 )
10 ORDER BY 1;
N STRT FIN
---------- ---------- ----------
1
2
3 1 3
5 5 5
7
8
9 7 9
11 11 11
8 rows selected.
SQL>
Another method with version 12c
Stew Ashton, September 27, 2013 - 7:53 am UTC
Now that we have pattern matching available, many problems appear to be pattern matching exercises.
SELECT * FROM rand
MATCH_RECOGNIZE (
ORDER BY n
MEASURES A.n an, last(b.n) bn
PATTERN (A b*)
DEFINE b AS b.n = prev(b.n)+1
);
AN BN
---------- ----------
1 3
5
7 9
11
This answers the question with the default "one row per match". Since the OP wants every row, with nulls in all but the last row, I have to do some formatting:
SELECT * FROM rand
MATCH_RECOGNIZE (
ORDER BY n
MEASURES
decode(n, nvl(FINAL LAST(b.n), A.n), A.n) an,
decode(n, nvl(final last(b.n), a.n), final last(b.n)) bn
ALL ROWS PER MATCH
PATTERN (A b*)
DEFINE b AS b.n = prev(b.n)+1
);
N AN BN
---------- ---------- ----------
1
2
3 1 3
5 5
7
8
9 7 9
11 11
When getting "ALL ROWS PER MATCH" you have to choose between RUNNING and FINAL semantics (a sort of shorthand windowing clause).
Both times I leave out the "AFTER MATCH SKIP PAST LAST ROW" clause, which is the default.
I think Tom is going to be less motivated to learn the MODEL clause now ;)
Another method with version 12c: improvement
Stew Ashton, September 27, 2013 - 11:07 am UTC
This is a cosmetic change: the SUBSET clause lets me avoid the NVL() stuff in the MEASURES clause.
SELECT * FROM rand
MATCH_RECOGNIZE (
ORDER BY n
MEASURES
decode(n, FINAL LAST(ab.n), A.n) an,
decode(n, FINAL LAST(ab.n), final last(b.n)) bn
ALL ROWS PER MATCH
PATTERN (A B*)
SUBSET AB = (A,B)
DEFINE b AS b.n = prev(ab.n)+1
);