Re: Identifying trends

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. */
                          )
                  )
      );

This may need some explaining. :-) The first EXISTS checks if the 5 results are not increasing. So there will be at least one result that is not correct. The second EXISTS checks if we can omit a test result such that the remaining test results are increasing. If this holds then there will be at most one incorrect result. So together the EXISTS check if there is exactly one incorrect result.

Unfortunately you cannot easily adapt this solution for say 2 or 3 incorrect results, but I don't see such a solution right now.

Hope this helps.

-- 
  Jan Hidders