Given:

with T (s,t) as (
values (1,1), (2,2), (3,3), (2,4),
(2,5), (3,6), (2,7), (0,8)
)
select s, t from T order by t

S T
----------- -----------
1 1
2 2
3 3
2 4
2 5
3 6
2 7
0 8

Which is sort of a state machine (S=State, T=Time). What I would like to
do is to find all States and Time where state is 2 together with the
next state where State <> 2, i.e.

S T GOAL_S GOAL_T
----------- ----------- ----------- -----------
2 2 3 3
2 4 3 6
2 7 0 8


As a start I've got:

with T (s,t) as (
values (1,1), (2,2), (3,3), (2,4),
(2,5), (3,6), (2,7), (0,8)
)
select s,t, goal_s, goal_t from (
select s, t,
lead( decode(s,2, cast(null as int), s), 1)
over (order by t) as goal_s,
lead( decode(s,2, cast(null as int), t), 1)
over (order by t) as goal_t
from T order by t
) where s=2

S T GOAL_S GOAL_T
----------- ----------- ----------- -----------
2 2 3 3
2 4 - -
2 5 3 6
2 7 0 8

Which can be further mangled to achieve what I want, but I get the
feeling that I oversee something, and that there exists a much more
elegant solution. Any suggestions?


/Lennart

This may be a solution.

------------------------------ Commands Entered
------------------------------
WITH t(s,t) as (
VALUES (1,1), (2,2), (3,3), (2,4)
, (2,5), (3,6), (2,7), (0,8)
)
SELECT MIN(start_s) AS s
, MIN(t) AS t
, MAX(CASE t
WHEN goal_t THEN s
END
) AS goal_s
, goal_t
FROM (SELECT s , t
, CASE
WHEN s = 2
AND LAG(s) OVER(ORDER BY t) <> s THEN
s
END AS start_s
, MIN(CASE
WHEN s <> 2 THEN t
END
)
OVER( ORDER BY t
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
) AS goal_t
FROM t
)
GROUP BY
goal_t
HAVING
MIN(start_s) IS NOT NULL
ORDER BY
t
;
------------------------------------------------------------------------------

S T GOAL_S GOAL_T
----------- ----------- ----------- -----------
2 2 3 3
2 4 3 6
2 7 0 8

3 record(s) selected.

"s" in result sets was fixed to 2.
So, start_s in my previous example is not necessary.

Here is another simplified example:
------------------------------ Commands Entered
------------------------------
WITH t(s,t) as (
VALUES (1,1), (2,2), (3,3), (2,4)
, (2,5), (3,6), (2,7), (0,8)
)
SELECT 2 AS s
, MIN(t) AS t
, MAX( CASE t
WHEN goal_t THEN s
END
) AS goal_s
, goal_t
FROM (SELECT s , t
, MIN( CASE WHEN s <> 2 THEN t END )
OVER( ORDER BY t
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
) AS goal_t
FROM t
)
GROUP BY
goal_t
HAVING
COUNT(goal_t) > 1
ORDER BY
t
;
------------------------------------------------------------------------------

S T GOAL_S GOAL_T
----------- ----------- ----------- -----------
2 2 3 3
2 4 3 6
2 7 0 8

3 record(s) selected.

On 04/16/2011 03:31 PM, Tonkuma wrote:
Very nice Tonkuma. The real problem I'm facing is a bit more complicated
than shown here, but I will definitely find use for some of the
techniques that you demonstrated. Many thanks


/Lennart

On 2011-04-16 10:05, Tonkuma wrote:
In case s is first in sequence (no predessors, like 1), LAG will result
in null, and start_s will become null. A generalization might be:

... AND coalesce(LAG(s) OVER(ORDER BY t),s+1) <> s THEN ...

/Lennart

... AND LAG(s , 1 , s+1) OVER(ORDER BY t) <> s THEN ...

On 2011-04-18 15:46, Tonkuma wrote:
Ah yes, clearly better


/Lennart

As a generalization, I tried to find all of state changes.

Here is an example with modified sample data.

Note:
I used LAG(s , 1 , s + 1) in goal_t and goal_s instead of LAG(s).
Because the cost got slightly higher,
if LAG(s) was used in goal_t and goal_s.
I guessed the reason:
If LAG(s) was used, DB2 need to calculate both of LAG(s , 1 , s + 1)
and LAG(s).

------------------------------ Commands Entered
------------------------------
WITH t(s,t) as (
VALUES (1,1), (2,2), (2,4), (3,5)
, (2,7), (2,8), (2,9)
, (3,11), (3,13), (2,15)
, (0,16), (0,17), (0,19)
)
SELECT s
, t
, goal_s
, goal_t
FROM (SELECT t
, NULLIF( s , LAG(s , 1 , s + 1)
OVER(ORDER BY t)
) AS s
, MIN( CASE
WHEN s <> LAG(s , 1 , s + 1)
OVER(ORDER BY t)
THEN t
END
)
OVER(ORDER BY t
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING
) AS goal_t
, FIRST_VALUE
( NULLIF( s , LAG(s , 1 , s + 1)
OVER(ORDER BY t)
) , 'IGNORE NULLS'
)
OVER(ORDER BY t
ROWS BETWEEN 1 FOLLOWING
AND UNBOUNDED FOLLOWING
) AS goal_s
FROM t
)
WHERE s IS NOT NULL
ORDER BY
t
;
------------------------------------------------------------------------------

S T GOAL_S GOAL_T
----------- ----------- ----------- -----------
1 1 2 2
2 2 3 5
3 5 2 7
2 7 3 11
3 11 2 15
2 15 0 16
0 16 - -

7 record(s) selected.


------------------------------ Commands Entered
------------------------------
WITH t(s,t) as (
VALUES (1,1), (2,2), (2,4), (3,5)
, (2,7), (2,8), (2,9)
, (3,11), (3,13), (2,15)
, (0,16), (0,17), (0,19)
)
SELECT t.*
, CASE s
WHEN LAG(s) OVER(ORDER BY t) THEN
''
ELSE '<---'
END state_change
FROM t
;
------------------------------------------------------------------------------

S T STATE_CHANGE
----------- ----------- ------------
1 1 <---
2 2 <---
2 4
3 5 <---
2 7 <---
2 8
2 9
3 11 <---
3 13
2 15 <---
0 16 <---
0 17
0 19

13 record(s) selected.

SELECT s
, t
, goal_s
, goal_t
FROM (SELECT t , s
, LAG(s , 1 , 0)
OVER( ORDER BY t ) AS lag_s
, MIN( CASE WHEN s <> 2 THEN t END )
OVER( ORDER BY t
ROWS UNBOUNDED FOLLOWING
) AS goal_t
, FIRST_VALUE
( NULLIF(s , 2) , 'IGNORE NULLS' )
OVER( ORDER BY t
ROWS UNBOUNDED FOLLOWING
) AS goal_s
FROM t
) q
WHERE s = 2
AND lag_s <> 2
ORDER BY
t
;

This might be of some help:

http://www.simple-talk.com/sql/t-sql...n-constraints/