mahjag
asked on
how to apply lead and lag function to a group of records in oracle
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
>>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.
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.
ASKER
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 -