General pattern for vertical filtering in MySQL

I have MySQL table keeping historical records of objects (every state of object is in different row) and want find there objects, which the field(column) has changed directly from one value to another, so condition is touching 2 rows.

row1_col1, row1_col2, ..., 22, ..., '2017-08-08 20:11:06.501658'
...
... -- some rows with different values than 11 and 22 
...
row21_col1, row21_col2, ..., 11, ..., '2017-08-28 18:13:16.490628'
row22_col1, row22_col2, ..., 22, ..., '2017-08-28 20:11:06.501658'
...
... -- some rows with different values than 11 and 22 
...
row101_col1, row101_col2, ..., 11, ..., '2017-11-28 18:13:16.490628'

To simplify let's say it will be a change from 11 to 22, following immediately after, so first and last row should be filtered out. (the real condition is on 4 columns)

Is any approach here beyond programmatically looping? (meaning anything which will limit hits to database and general approach on just mysql layer for such issue)

1 answer

  • answered 2017-10-11 10:23 Sławomir Lenart

    actually I think this is a good question, because I am asking for general approach, but due to limitation of aggregation in MySQL (like order is not kept and not guaranteed for each column when grouping; or interdependent conditions) almost impossible to make it in one query.

    My approach, which work is using temporary tables:

    1. prepare query(as temp table) with 1st part of condition having farthest row in recordings table (in above example it is a record having 22 from the center of data/above table)
    2. prepare query(as temp table) with 2nd part of condition having the nearest row before the (index,value) from first temporary table row in recordings table (returning 11 from the center of data). please note: this table while creation use first temp table.
    3. having above collected with additional required columns we just can compare 2 cursors (2 prepared outputs in temporary tables)

    and it will limit all to 3 queries, so it's easy to run it on bigger table.