Mi-Jack
asked on
DB2: find value pairs
Hi all,
I have a table with the following columns:
[i_year] [int] NOT NULL,
[i_month] [int] NOT NULL,
[i_day] [int] NOT NULL,
[i_hour] [int] NOT NULL,
[i_minute] [int] NOT NULL,
[i_second] [int] NOT NULL,
[i_state] [int] NOT NULL,
[i_id] [int] NOT NULL
i_state can only be 0 or 1
Every i_id starts with an i_state of 0, and then at some point the state changes to 1.
In some time, the state changes back to 0 - and so on.
How can I match i_state 0 to its corresponding i_state of 1 for a particular i_id?
I need to make sure that each i_state 0 has i_state 1, and that there are no
i_state 0 followed by i_state 0 - or i_state 1 followed by i_state 1. In other
words, that the sequence of events in time is always i_state 0 -> i_state 1.
Thank you,
Gene
I have a table with the following columns:
[i_year] [int] NOT NULL,
[i_month] [int] NOT NULL,
[i_day] [int] NOT NULL,
[i_hour] [int] NOT NULL,
[i_minute] [int] NOT NULL,
[i_second] [int] NOT NULL,
[i_state] [int] NOT NULL,
[i_id] [int] NOT NULL
i_state can only be 0 or 1
Every i_id starts with an i_state of 0, and then at some point the state changes to 1.
In some time, the state changes back to 0 - and so on.
How can I match i_state 0 to its corresponding i_state of 1 for a particular i_id?
I need to make sure that each i_state 0 has i_state 1, and that there are no
i_state 0 followed by i_state 0 - or i_state 1 followed by i_state 1. In other
words, that the sequence of events in time is always i_state 0 -> i_state 1.
Thank you,
Gene
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>this requires v9.5 or higher
I have 9.7
Almost there -
1227 0 0
1228 1 1
1233 0 0
1233 0 0
1234 1 1
1240 1 1
1242 1 1
1248 1 1
1254 1 1
1259 1 1
1268 1 1
1268 1 1
1268 1 1
1275 0 0
1276 1 1
1277 1 1
1281 1 1
1281 0 0
1282 1 1
1284 0 0
1286 1 1
1287 0 0
1287 0 0
I expected the state change from the results.
Also, can you explain this query, please? So I could start my loooong journey on the
way of becoming as smart as you are?
I have 9.7
Almost there -
1227 0 0
1228 1 1
1233 0 0
1233 0 0
1234 1 1
1240 1 1
1242 1 1
1248 1 1
1254 1 1
1259 1 1
1268 1 1
1268 1 1
1268 1 1
1275 0 0
1276 1 1
1277 1 1
1281 1 1
1281 0 0
1282 1 1
1284 0 0
1286 1 1
1287 0 0
1287 0 0
I expected the state change from the results.
Also, can you explain this query, please? So I could start my loooong journey on the
way of becoming as smart as you are?
ASKER
>>it's not clear how the changes are done
there will be a separate record for each change
there will be a separate record for each change
>>> I expected the state change from the results.
I thought you were looking for id's where the state did not change
where i_state = prev_state
if you want to see them where the state DID change
where i_state != prev_state
for explanation...
lag(i_state) looks back one row per id ("per id" is the partition part)
and "back one" is defined by the ordering (if you dont' order you don't know what's ahead or behind each row)
the first row will have the previous value as NULL because there isn't one.
easiest way to understand it is to look at the data from the inner query
and see the pattern
select i_id, i_year, i_month, i_day, i_hour, i_minute, i_second, i_state, lag(i_state) over(partition by i_id order by i_year, i_month, i_day, i_hour, i_minute, i_second) prev_state
from your_table
order by i_id, i_year, i_month, i_day, i_hour, i_minute, i_second
I thought you were looking for id's where the state did not change
where i_state = prev_state
if you want to see them where the state DID change
where i_state != prev_state
for explanation...
lag(i_state) looks back one row per id ("per id" is the partition part)
and "back one" is defined by the ordering (if you dont' order you don't know what's ahead or behind each row)
the first row will have the previous value as NULL because there isn't one.
easiest way to understand it is to look at the data from the inner query
and see the pattern
select i_id, i_year, i_month, i_day, i_hour, i_minute, i_second, i_state, lag(i_state) over(partition by i_id order by i_year, i_month, i_day, i_hour, i_minute, i_second) prev_state
from your_table
order by i_id, i_year, i_month, i_day, i_hour, i_minute, i_second
each state has only one record?
that is
one record for state0
then one record for state1
another record for state0
etc ?