Solved

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

Posted on 2012-03-14
6
670 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
  • 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 31

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 31

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now