One of the things I love in the SQL language is that one problem may have many different solutions, or more specifically, one functional question may be solved by different SQL queries.
It doesn’t mean that all the solutions are equivalent in all aspects. If all the solutions solve the functional question correctly, then all the result sets must be the same. But they may be different in performance, for example (it’s so satisfying reducing the execution time of a query from hours to seconds just by rewriting it).
Another example: one query may be short and elegant while another “equivalent” query may be long and cumbersome.
And not all the solutions are even legitimate in all versions of the database – many times a new feature may make a query much simpler (analytic functions are a great example for that – I still remember how their appearance [in Oracle 8.1.6 if I remember correctly] had a huge effect on my query writing – making them shorter, and many times faster – and actually I see the same effect on people today when I rewrite their queries and at the same time introduce them to analytic functions).
Oracle 12c introduced some new features that let us write less for getting the same functionality, like Lateral Inline Views or the Row Limiting clause.
Here is a nice example for one problem with several solutions.
A few days ago, Lucas Jellema from AMIS raised in this blog post the question of “packing” multiple rows that represent adjacent time periods into a single row that represents the unified period, and suggested the following solution, that demonstrates well some important features such as the LAG and RANK analytic functions, Subquery Factoring (supported since Oracle 9i), and Recursive Subquery Factoring (introduced in Oracle 11g):
with chairpeople as
( select chairperson
, date_from
, date_to
, case date_from - lag(date_to) over (partition by chairperson order by date_from asc)
when 1 then 'PACK'
end action
, rank() over (partition by chairperson order by date_from asc) rnk
from chairmanships
)
, packing_chairs (chair, date_from, date_to, lvl) as
( select chairperson, date_from, date_to, 1
from chairpeople
where action is null
union all
select p.chair, p.date_from, c.date_to, lvl+1
from chairpeople c
join
packing_chairs p
on (c.chairperson = p.chair and c.rnk = p.lvl+1)
where c.action='PACK'
)
, packed_chairs as
( select chair, date_from, nullif(max(nvl(date_to,date'9999-12-31')),date'9999-12-31') date_to
from packing_chairs
group
by chair, date_from
)
select *
from packed_chairs
order
by date_from;
-- note: this is a slightly revised version of the query from the original post
I suggested another solution, based on the LAST_VALUE analytic function:
select chairperson,
date_from,
max(date_to) keep(dense_rank last order by date_to) date_to
from (select chairperson,
last_value(new_period_date_from ignore nulls) over(partition by chairperson order by date_from) date_from,
date_to
from (select chairperson,
date_from,
date_to,
case when lnnvl(date_from – lag(date_to) over(partition by chairperson order by date_from) = 1) then date_from end new_period_date_from
from chairmanships))
group by chairperson,
date_from
order by date_from;
In another comment to Lucas’ post, Sayan Malakshinov suggested an even simpler and shorter solution:
select
chairperson
,min(date_from) keep (dense_rank first order by date_from,date_to) as date_from
,max(date_to ) keep (dense_rank last order by date_from,date_to) as date_to
from (
select
chairperson
, date_from
, date_to
, sum(flag) over(partition by chairperson order by date_from,date_to) grp
from (
select
chairperson
, date_from
, date_to
, decode( 1 + lag(date_to)over(partition by chairperson order by date_from,date_to), date_from, 0, 1) flag
from chairmanships
)
)
group by chairperson, grp
order by chairperson, grp;
Finally (for now at least), I suggested yet another solution, using a new Oracle 12c feature – Pattern Matching:
select * from chairmanships
match_recognize (
partition by chairperson
order by date_from
measures frst.date_from as date_from,
date_to as date_to
one row per match
pattern (frst nxt*)
define nxt as nxt.date_from = prev(nxt.date_to)+1)
order by chairperson,date_from;
So, there you go – one question, four very different solutions.