[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

how to apply lead and lag function to a group of records in oracle

Posted on 2012-03-14
6
Medium Priority
?
730 Views
Last Modified: 2012-08-13
Hi

I have a set of data with the following results - how do I evaluate the previous record data for bunch of columns based on a group value.

Group_id    customer     flag1    flag2       flag3
1                 Netflix          Y         Auto       Template
1                  Netflix         N        Auto       Template
2                Amazon         Y        Auto        Template
2                Amazon         Y        Manual    Template
3                ABC.com        N       Auto        NonStandard
3                ABC.com        N       Auto        Template
4                Blockbuster    Y       Auto         Template
4                Blockbuster    Y       Auto         Template
4                 Blockbuster   N      Auto          NonStandard
5               Nice.com         Y      Auto          Template
5               Nice.com         Y      Auto          Template

From the results above I want to write a sql to give me the group ids that do not have the flag values same  - for example easy case will be group id value = 5 wher the flag1, flag2 and flag3 values are same then I want to categorize them as "PERFECT" but if there are any changes to flag1 of first record to flag1 of second record or third record then I want to categorize them as flag1value change - from my example above group id value = 1, similarly to categorize as flag2 change for group id value = 2 and flag3 change for group id value = 3.
More complex case is group id value = 4 where both flag1 and flag3 value change when compared to 3 records -

group id value can be same for 2 or more records - I dont know how I can write a sql to categorize them based on flag value comparing the first record to second , third and so on -

any help will be greatly appreciated
0
Comment
Question by:mahjag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 1200 total points
ID: 37722824
with t1
as
(
select 1 group_id,'Netflix' customer ,'Y' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 1 group_id,'Netflix' customer ,'N' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 2 group_id,'Amazon' customer ,'Y' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 2 group_id,'Amazon' customer ,'Y' flag1 ,'Manual' flag2 ,'Template' flag3 from dual
union select 3 group_id,'ABC.com' customer ,'N' flag1 ,'Auto' flag2 ,'NonStandard' flag3 from dual
union select 3 group_id,'ABC.com' customer ,'N' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 4 group_id,'Blockbuster' customer ,'Y' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 4 group_id,'Blockbuster' customer ,'Y' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 4 group_id,'Blockbuster' customer ,'N' flag1 ,'Auto' flag2 ,'NonStandard' flag3 from dual
union select 5 group_id,'Nice.com' customer ,'Y' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
union select 5 group_id,'Nice.com' customer ,'Y' flag1 ,'Auto' flag2 ,'Template' flag3 from dual
)
select group_id,
        nvl(replace(flag1 || flag2 || flag3,'changedflag','changed and flag'),'Perfect') flags
from
(
select group_id,
--nvl(
case
 when min(flag1) = max(flag1) then
   null
 else
   'flag1 changed'
 end flag1
,
case
 when min(flag2) = max(flag2) then
   null
 else
   'flag2 changed'
 end flag2
,
case
 when min(flag3) = max(flag3) then
   null
 else
   'flag3 changed'
 end flag3
-- , 'Perfect')
from t1
group by group_id
)
order by group_id
/

-- though not using lead or lag
0
 

Author Comment

by:mahjag
ID: 37723168
Hi flow01

Great answer - I applied and got the results - I have 2 questions -

1. for more complex case group id value = 4 both flag1 and flag 3 changed I got the flags categorized as flag1changedflag3changed - without a space in between I would have expected flag1 changed and flag3 changed - if I understand the replace fuction that you used

2. also I forgot to mention in my sample data I need to add contract id and bunch of columns that gets repeated for every group id value for example

Group_id    customer     flag1    flag2       flag3          contract id     customer_id
1                 Netflix          Y         Auto       Template    1-123              cust01
1                  Netflix         N        Auto       Template     1-123              cust01

how can I incorporate these extra columns in the sql - since I need them as well -
0
 
LVL 32

Accepted Solution

by:
awking00 earned 800 total points
ID: 37724921
SQL> select * from t1;

GROUP_ID CUSTOMER     F FLAG2    FLAG3        CONTR CUSTOM
-------- ------------ - -------- ------------ ----- ------
       1 Netflix      Y Auto     Template     1-123 cust01
       1 Netflix      N Auto     Template     1-123 cust01
       2 Amazon       Y Auto     Template     2-456 cust02
       2 Amazon       Y Manual   Template     2-456 cust02
       3 ABC.com      N Auto     NonStandard  3-789 cust03
       3 ABC.com      N Auto     Template     3-789 cust03
       4 Blockbuster  Y Auto     Template     4-888 cust04
       4 Blockbuster  Y Auto     Template     4-888 cust04
       4 Blockbuster  N Auto     NonStandard  4-888 cust04
       5 Nice.com     Y Auto     Template     5-999 cust05
       5 Nice.com     Y Auto     Template     5-999 cust05

select group_id, contract_id, customer_id,
case when least(cat1, cat2,cat3) = 'same' then 'Perfect'
     when greatest(cat1, cat2,cat3) = 'change' then 'flag1 changed, flag2 changed, flag3 changed'
     when cat1 = 'change' and least(cat2, cat3) = 'same' then 'flag1 changed'
     when cat2 = 'change' and least(cat1, cat3) = 'same' then 'flag2 changed'
     when cat3 = 'change' and least(cat1, cat2) = 'same' then 'flag3 changed'
     when cat1 = 'change' and cat2 = 'change' then 'flag1 changed, flag2 changed'
     when cat1 = 'change' and cat3 = 'change' then 'flag1 changed, flag3 changed'
     when cat2 = 'change' and cat3 = 'change' then 'flag2 changed, flag3 changed'
end as category
from
(select group_id,min(contract_id) contract_id, min(customer_id) customer_id,
        min(comp1) cat1, min(comp2) cat2, min(comp3) cat3 from
 (select group_id,contract_id, customer_id,
  flag1,case when flag1 = next1 then 'same' else 'change' end comp1,
  flag2,case when flag2 = next2 then 'same' else 'change' end comp2,
  flag3,case when flag3 = next3 then 'same' else 'change' end comp3 from
  (select group_id,contract_id, customer_id,
   flag1, nvl(lead(flag1) over (partition by group_id order by rownum),flag1) next1,
   flag2, nvl(lead(flag2) over (partition by group_id order by rownum),flag2) next2,
   flag3, nvl(lead(flag3) over (partition by group_id order by rownum),flag3) next3
   from t1))
 group by group_id);

GROUP_ID CONTR CUSTOM CATEGORY
-------- ----- ------ ------------------------------
       1 1-123 cust01 flag1 changed
       2 2-456 cust02 flag2 changed
       3 3-789 cust03 flag3 changed
       4 4-888 cust04 flag1 changed, flag3 changed
       5 5-999 cust05 Perfect
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 20

Expert Comment

by:flow01
ID: 37725753
by: mahjagPosted on 2012-03-15 at 02:48:23ID: 37723168

1) yes, that would be the purpose : in my execution of the query i got  yesterday and get also today
as result
  GROUP_ID FLAGS
---------- -------------------------------------------------
         1 flag1 changed
         2 flag2 changed
         3 flag3 changed
         4 flag1 changed and flag3 changed
         5 Perfect
Did you code it exactly the same ?

2)  group data
     a)  use all columns of the group in the select and the group by clause
          it should still result in 1 row per id
     b) get the min(value) of the group-columns as  suggested by awking00
          the min value should always be the only value
     c)  your example data already are denormalised
         if you have separated tables for group and details
         do the group by on the details table inline and join to the group (and other table like customer and contract)
         select *
         from group_table g,
         (select ....
          from detail_table
          group by group_id) d
         where d.group_id = g.id
    d)  or change my query to recreate the group data from the t1 table
         with t1
         ..  same as before till last order by
            d  -- alias for first part of the query
            , (select distinct id , customer, contract_id, customer_id
            from t1) g
         where g.id = d.group_id
         order by group_id
         /
        If you already have one table that contains all information I expect the the b) option to be the fasted else  the c) option wil probably be
0
 

Author Comment

by:mahjag
ID: 37725945
Hi awking00

after I applied your change I realised that I got the results but my expected rows was

Group_id    customer     flag1    flag2       flag3          contract id     customer_id
1                 Netflix          Y         Auto       Template    1-123              cust01
1                  Netflix         N        Auto       Template     1-123              cust01

but what I got is single row

Group_id    customer     flag1    flag2       flag3          contract id     customer_id category
1                 Netflix          Y         Auto       Template    1-123              cust01        flag1 changed

I think it defeats the purpose of showing all the rows for the user and I am going to be dinged on this for not getting it

It is very urgent so I try to manipulate the inner queries by taking away the min(customer_id) and min(contract_id) but that caused more issue as some of the rows shows perfect but it is not perfect for group_id = 1 which is misleading

I know I awarded the grades and scores but I would apprecite your help here
0
 
LVL 32

Expert Comment

by:awking00
ID: 37729213
>>after I applied your change I realised that I got the results but my expected rows was

Group_id    customer     flag1    flag2       flag3          contract id     customer_id
1                 Netflix          Y         Auto       Template    1-123              cust01
1                  Netflix         N        Auto       Template     1-123              cust01<<
I don't understand. Those results are precisely what is currently in the table with no mention of having flags changed or perfect.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question