Solved

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

Posted on 2012-03-14
6
704 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 300 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 200 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 60
plsql job on oracle 18 106
PL/SQl Expanding the WHERE statement in query 3 53
Select the 2 most recent visit dates 5 29
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 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