Home » SQL & PL/SQL » SQL & PL/SQL » curly analytics question (ora 9)
curly analytics question [message #419691] Tue, 25 August 2009 03:57 Go to next message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
Hi, I've been trying to figure out analytics for the past few hrs without much luck. Here is my problem:

Table X has the following data (a subset shown):
UK_VAL MODIFIED
------ --------
123 0
124 0
... (thru to 567)
567 0
568 1
569 1
... (thru to 600)
600 1
601 0
602 0
... (thru to 610)
610 0
611 1
612 1
613 0
... (thru to 700)

How do I write a query that will group the data by the MODIFIED column whenever it changes from 1 to 0 and back and also tell me the from and to UK_VAL values in that group, so it looks like below???

MODIFIED From To
-------- ---- ----
0 123 567
1 568 600
0 601 610
1 611 612
0 613 700

I'm sure I need to use analytic functions/clauses for this, but don't know how - is there a guru out there that can help and also explain how the solution works???
Re: curly analytics question [message #419695 is a reply to message #419691] Tue, 25 August 2009 04:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
First learn, how to post your question here.
Explain your problem properly.

regards,
Delna
Re: curly analytics question [message #419698 is a reply to message #419695] Tue, 25 August 2009 04:18 Go to previous messageGo to next message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
The table has sequential numeric values (UK_VAL) and a column indicating 0 or one for each value.
The subset of data shown, shows numeric values from 123 thru to 700.

I've been playing with various SQL queries, to no avail. Below is an example:
SELECT modified, MIN(uk_val) OVER(PARTITION BY modified order by uk_val) AS From,
MAX(uk_val) OVER(PARTITION BY modified order by uk_val) AS To
FROM X;
Re: curly analytics question [message #419703 is a reply to message #419691] Tue, 25 August 2009 04:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't know exactly but analytic function - LAG/LEAD, may help you.

regards,
Delna
Re: curly analytics question [message #419704 is a reply to message #419691] Tue, 25 August 2009 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: curly analytics question [message #419740 is a reply to message #419704] Tue, 25 August 2009 07:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select modified, min(uk_val), max(uk_val)
from (
    select uk_val
    ,      modified 
    ,      row_number() over (partition by modified order by uk_val) grp_seq
    from (
        select rownum as uk_val, round(dbms_random.value) as modified
        from dual
        connect by level <= 20
    )
    order by uk_val
)
group by modified, rownum - grp_seq
order by 2


Ross Leishman
Re: curly analytics question [message #419876 is a reply to message #419740] Tue, 25 August 2009 19:01 Go to previous messageGo to next message
JoeyD
Messages: 7
Registered: August 2009
Junior Member
Fantastic, thanks Ross.

All other comments taken on board.
cheers
Re: curly analytics question [message #419894 is a reply to message #419876] Wed, 26 August 2009 01:26 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  data as (
    select rownum as uk_val, round(dbms_random.value) as modified
    from dual
    connect by level <= 20
  ),
  flagged as (
    select uk_val, modified, 
           case when nvl(lag(modified) over (order by uk_val),-1) != modified 
                then row_number() over (order by uk_val) -- or rownum
           end flag
    from data
  ),
  grouped as (
    select uk_val, modified, max(flag) over (order by uk_val) grp
    from flagged
  )
select modified, min(uk_val) min_val, max(uk_val) max_val
from grouped
group by modified, grp
order by 2
/

Regards
Michel
Previous Topic: Need Output in Singel Row
Next Topic: sql problem
Goto Forum:
  


Current Time: Sat Dec 03 14:04:35 CST 2016

Total time taken to generate the page: 0.14108 seconds