query for finding the most frequently changing record

Question

source   |  voltage | timestamp
CIRCUIT1 | 2.4 | XXX
CIRCUIT1 | 2.4 | XXX
CIRCUIT1 | 2.5 | XXX
CIRCUIT1 | 2.4 | XXX
CIRCUIT1 | 2.4 | XXX
CIRCUIT1 | 2.6 | XXX
CIRCUIT1 | 2.4 | XXX
CIRCUIT1 | 2.4 | XXX
CIRCUIT1 | 2.4 | XXX

CIRCUIT2 | 2.4 | XXX
CIRCUIT2 | 2.5 | XXX
CIRCUIT2 | 2.4 | XXX
CIRCUIT2 | 2.4 | XXX
CIRCUIT2 | 2.5 | XXX
CIRCUIT2 | 2.4 | XXX
CIRCUIT2 | 2.5 | XXX
CIRCUIT2 | 2.4 | XXX
CIRCUIT2 | 2.5 | XXX

CIRCUIT3 | 2.4 | XXX
CIRCUIT3 | 2.4 | XXX
CIRCUIT3 | 2.8 | XXX
CIRCUIT3 | 2.4 | XXX
CIRCUIT3 | 2.4 | XXX
CIRCUIT3 | 2.8 | XXX
CIRCUIT3 | 2.4 | XXX
CIRCUIT3 | 2.4 | XXX
CIRCUIT3 | 2.4 | XXX

The output should be just CIRCUIT2

The values of voltage for CIRCUIT1 varies only 2 times The values of voltage for CIRCUIT2 varies 4 times The values of voltage for CIRCUIT3 varies only 2 times even though the variation is high

Can anyone please tell me how do I proceed to write an sql query which will output CIRCUIT2 as the answer ?

(I have added one more column TIMESTAMP)

Also there needs to be some other column present to find the order of records — Pரதீப், Jul 30 '17 at 06:33
@Pரதீப் I added sql server just to widen the response but I will delete if it goes against the rules. — Shanthi, Jul 30 '17 at 06:38
Yes you should not do that, because `Sql Server` answer will look like this http://rextester.com/YBZPB69250 but this won't even compile in `Mysql` — Pரதீப், Jul 30 '17 at 06:42
@ZoharPeled Sorry and thanks for removing the sql-server tag — Shanthi, Jul 30 '17 at 06:45
@Shanthi - Did you read the second comment ? It is needed to find the order of records — Pரதீப், Jul 30 '17 at 06:51
@Pரதீப் Thanks I have added one more column TIMESTAMP — Shanthi, Jul 30 '17 at 06:59

score 0 · Answer 1 · answered Jul 30 '17 at 07:15

Here is one way to this(not sure this is the best way to do it in Mysql)

SELECT *
FROM   tabtest
WHERE  source = (SELECT a.source
                 FROM   (SELECT a.source,a.voltage,a.timestamp,Count(*) AS rn
                         FROM   tabtest a
                                JOIN tabtest b
                                  ON a.source = b.source
                                     AND a.timestamp >= b.timestamp
                         GROUP  BY a.source,a.voltage,a.timestamp) a
                        LEFT JOIN (SELECT a.source,a.voltage,a.timestamp,Count(*) AS rn
                                   FROM   tabtest a
                                          JOIN tabtest b
                                            ON a.source = b.source
                                               AND a.timestamp >= b.timestamp
                                   GROUP  BY a.source,a.voltage,a.timestamp) b
                               ON a.source = b.source
                                  AND a.rn = b.rn + 1
                 GROUP  BY a.source
                 ORDER  BY Sum(CASE WHEN a.voltage <> COALESCE(b.voltage, a.voltage) THEN 1 ELSE 0 END) DESC Limit 1)

Rextester Demo

Idea here is finding the previous voltage value for each record in source then using conditional aggregation counting the records only when there is a change in current record from previous record.

This is much much easier with window function http://rextester.com/PJFL7743

score 0 · Answer 2 · answered Jul 30 '17 at 12:05

I would suggest the following:

Find the previous volaage
Count the number of times it is different
Aggregate by this count
Choose the "n" rows with the fewest values

This looks like:

select source, sum(prev_voltage <=> voltage) as changes
from (select t.*,
             (select t2.voltage
              from t t2
              where t2.source = t.source and t2.timestamp < t.timestamp
              order by t2.timestamp desc
              limit 1
             ) prev_voltage
      from t
     ) t
group by source
order by changes;

This orders all the sources by the number of changes. Add a limit to the outer query to get just the top 1.

score 0 · Answer 3 · answered Aug 01 '17 at 05:29

I ended up using the 2nd solution mentioned here.

https://stackoverflow.com/a/6565970/522882 and then using a group by and count() and then ordering by the count() and limiting the result to 1.

           SELECT System, Timestamp, StatusA, StatusB, count(*) as ct
FROM
  ( SELECT (@statusPre <> statusA AND @systemPre=System) AS statusChanged
         , System, Timestamp, StatusA, StatusB
         , @statusPre := StatusA
         , @systemPre := System
    FROM tableX
       , (SELECT @statusPre:=NULL, @systemPre:=NULL) AS d
    ORDER BY System
           , Timestamp
  ) AS good
WHERE statusChanged GROUP BY Systen ORDER BY ct DESC limit 1 ;

Thanks everyone for the replies.

query for finding the most frequently changing record

3 Answers3