Re: Identifying trends

Jan Hidders wrote:
> John Winterbottom wrote:
> >
> > The problem is common in quality control. We have a process from
> > which we take a sample every day and run a test. We wish to identify
> > regions in which 4 out of 5 successive test results lie on an
> > increasing trend, [...]
> >
> > CREATE TABLE test_results
> > (
> > test_id INT PRIMARY KEY,
> > test_value DECIMAL(9,5)
> > )
>
> SELECT t1.test_id, t2.test_id
> FROM test_results AS t1, test_results AS t2
> WHERE t2.test_id = t1.test_id + 4
> AND EXISTS (
> SELECT *
> FROM test_results AS t3, test_results AS t4
> WHERE t1.test_id <= t3.test_id AND t3.test_id = t4.test_id - 1
> AND t4.test_id <= t2.test_id
> AND t3.test_value >= t4.test_value /* > for strict incr. */
> )
> AND EXISTS (
> SELECT *
> FROM test_results AS t3
> WHERE t1.test_id <= t3.test_id AND t3.test_id <= t2.test_id
> AND NOT EXISTS (
> SELECT *
> FROM test_results AS t4, test_results AS t5
> WHERE t1.test_id <= t4.test_id
> AND t4.test_id = t5.test_id - 1
> AND t5.test_id <= t2.test_id
> AND ( NOT(t4.test_id = t3.test_id) AND
> NOT(t5.test_id = t3.test_id) AND
> t4.test_value >= t5.test_value /* > for s.i. */
> )
> )
> );

AAAARGH!! Sorry sorry sorry. This doesn't work because it also finds a series like

  1 2 1 1 2

by assuming that the second 1 is the exception. This is because all it checks is if every successor is larger than or equal to the predecessor unless one of the two is the supposed exception t3. However, what needs to be done is to compare the test before the exception t3 with the test after the exception (if they both exist). To remedy this replace

> AND t4.test_id = t5.test_id - 1

with

|                      AND ( ( t3.test_id = t4.test_id + 1
|                              AND t5.test_id = t4.test_id + 2
|                            ) OR (
|                              t3.test_id <> t4.test_id + 1
|                              AND t5.test_id = t4.test_id + 1
|                            )
|                          )

You can probably optimize this but I left it this way because then you can see more easily what is going on.

... or you can take Mikito's solution. :-) Unless you want to use it to check series of, say, a 100 tests.

-- 
  Jan Hidders