You Asked
In the example below, how can I create a query to give me the results shown, such that for any sequence of consecutive rows, ordered by date, which have the same value for N, I can find the date values at the beginning and end of the range? I have a feeling it involves analytic functions but they hadn't been invented when I did my only SQL course in 1987!
Thanks in advance...
John
drop table jr_tmp;
create table jr_tmp (d date, n number);
insert into jr_tmp (d, n) values (date '2014-12-01', 1);
insert into jr_tmp (d, n) values (date '2014-12-02', 1);
insert into jr_tmp (d, n) values (date '2014-12-03', 1);
insert into jr_tmp (d, n) values (date '2014-12-04', 2);
insert into jr_tmp (d, n) values (date '2014-12-05', 2);
insert into jr_tmp (d, n) values (date '2014-12-06', 1);
insert into jr_tmp (d, n) values (date '2014-12-07', 1);
insert into jr_tmp (d, n) values (date '2014-12-08', 1);
<My query here>
D_FROM D_TO N
--------- --------- ----------
01-DEC-14 03-DEC-14 1
04-DEC-14 05-DEC-14 2
06-DEC-14 08-DEC-14 1
3 rows selected.
and Tom said...
there are various approaches, this is one I've used a lot:
ops$tkyte%ORA11GR2> select min(d), max(d), n, grp2
2 from (
3 select d, n, last_value(grp ignore nulls) over (order by d) grp2
4 from (
5 select d, n,
6 case when (nvl(lag(n) over (order by d),n-1) <> n) then
7 row_number() over (order by d)
8 end grp
9 from jr_tmp
10 )
11 )
12 group by n, grp2
13 order by 1
14 /
MIN(D) MAX(D) N GRP2
--------- --------- ---------- ----------
01-DEC-14 03-DEC-14 1 1
04-DEC-14 05-DEC-14 2 4
06-DEC-14 08-DEC-14 1 6
starting from the inside, what we want to do is mark the beginning of each group:
ops$tkyte%ORA11GR2> select d, n,
2 case when (nvl(lag(n) over (order by d),n-1) <> n) then
3 row_number() over (order by d)
4 end grp
5 from jr_tmp
6 /
D N GRP
--------- ---------- ----------
01-DEC-14 1 1
02-DEC-14 1
03-DEC-14 1
04-DEC-14 2 4
05-DEC-14 2
06-DEC-14 1 6
07-DEC-14 1
08-DEC-14 1
8 rows selected.
and then carry that down:
ops$tkyte%ORA11GR2> select d, n, last_value(grp ignore nulls) over (order by d) grp2
2 from (
3 select d, n,
4 case when (nvl(lag(n) over (order by d),n-1) <> n) then
5 row_number() over (order by d)
6 end grp
7 from jr_tmp
8 )
9 /
D N GRP2
--------- ---------- ----------
01-DEC-14 1 1
02-DEC-14 1 1
03-DEC-14 1 1
04-DEC-14 2 4
05-DEC-14 2 4
06-DEC-14 1 6
07-DEC-14 1 6
08-DEC-14 1 6
8 rows selected.
once we have that - aggregating is easy!
1987 is when I took my *first* SQL course. I keep taking them - it lets me do some pretty amazing things (just a hint, might be time for a refresher :) )
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment