CODEWITHSUNDEEP
mysqlselect
sharkyenergy
sharkyenergy

Reputation: 4173

Count value variation from 0 to 1 in mysql table

I have a column with two columns. one is TIMESTAMP and the other DIGITAL_BIT.

The value digital bit can be either 0 or 1 and changes a few times during the day. Every minute of the day is stored in this table. I would need to read somehow how many times a day this value changed from 0 to 1.

Is it possible to make a query that returns the count of this changes? What I have in mind is something like this:

select * from mytable where digital_bit = 1 and digital_bit (of previous row) = 0 order by timestamp

Can this be done with a query or do i have to process all data in my program?

Thanks

SAMPLE

timestamp | digital_bit

100000    | 0
100001    | 0
100002    | 1
100003    | 1
100004    | 0
100005    | 1
100006    | 0
100007    | 0
100008    | 1

the above should return 3 because for 3 times the value digital passed from 0 to 1. i need to count how often the value digital CHANGES from 0 to 1.

Upvotes: 3

Views: 1779

Answers (5)

Narek
Narek

Reputation: 3823

As I understood you have one query every minute. So you have no problem with performance.

You can add flag:

timestamp | digital_bit | changed

100000    | 0           | 0
100001    | 0           | 0
100002    | 1           | 1
100003    | 1           | 0
100004    | 0           | 1
100005    | 1           | 1
100006    | 0           | 1
100007    | 0           | 0
100008    | 1           | 1

And make check before insert:

SELECT digital_bit
FROM table
ORDER BY timestamp DESC
LIMIT 1

and if digital_bit is different insert new row with flag.

And then you just can take COUNT of flags:

SELECT COUNT(*)
FROM table
WHERE DATE BETWEEN (start, end)
      AND changed = 1

Hope will see in answers better solution.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

This isn't likely to be efficient with a lot of data, but you can get all the rows and calculate a sequence number for them, then do the same again but with the sequence number offset by 1. Then join the 2 lots together where those calculated sequence numbers match but the first one has a digital bit of 0 and the other a digital bit of 1:-

SELECT COUNT(*)
FROM
(
    SELECT mytable.timestamp, mytable.digital_bit, @aCount1:=@aCount1+1 AS SeqCount
    FROM mytable 
    CROSS JOIN (SELECT @aCount1:=1) sub1
    ORDER BY timestamp
) a
INNER JOIN
(
    SELECT mytable.timestamp, mytable.digital_bit, @aCount2:=@aCount2+1 AS SeqCount
    FROM mytable 
    CROSS JOIN (SELECT @aCount2:=0) sub1
    ORDER BY timestamp
) b
ON a.SeqCount = b.SeqCount
AND a.digital_bit = 0
AND b.digital_bit = 1

EDIT - alternative solution and I would be interested to see how this performs. It avoids the need for adding a sequence number and also avoids a correlated sub query:-

SELECT COUNT(*)
FROM
(
    SELECT curr.timestamp, MAX(curr2.timestamp) AS MaxTimeStamp
    FROM mytable curr
    INNER JOIN mytable curr2
    ON curr.timestamp > curr2.timestamp
    AND curr.digital_bit = 1
    GROUP BY curr.timestamp
) Sub1
INNER JOIN mytable curr
ON Sub1.MaxTimeStamp = curr.timestamp
AND curr.digital_bit = 0

Upvotes: 1

Shai
Shai

Reputation: 7317

Here you go. This will get you a count of how many times digital_bit switched from 0 to 1 (in your example, this will return 3).

SELECT COUNT(*)
FROM mytable curr
WHERE curr.digital_bit = 1
AND (
    SELECT digital_bit
    FROM mytable prev
    WHERE prev.timestamp < curr.timestamp
    ORDER BY timestamp DESC
    LIMIT 1
) = 0

SQLFiddle link

(Original answer relied on the timestamps being sequential: e.g. no jumps from 100001 to 100003. Answer has now been updated not to have that restriction.)

Upvotes: 3

OGHaza
OGHaza

Reputation: 4795

Adapted from: How do I query distinct values within multiple sub record sets

select count(*)
from (select t1.*,
             (select digital_bit
              from table t2
              where t2.timestamp < t1.timestamp
              order by timestamp desc LIMIT 1
             ) as prevvalue
      from table t1
     ) t1
where prevvalue <> digital_bit and digital_bit = 1;

Upvotes: 1

dognose
dognose

Reputation: 20899

IF you have a result once per minte, you can simple join the table with itself, and use timestamp+1 as well as leftbit != rightbit as join condition.

http://sqlfiddle.com/#!8/791c0/6

ALL Changes:

SELECT 
  COUNT(*)
FROM 
  test a
INNER JOIN
  test b
ON 
  a.digital_bit != b.digital_bit
  AND b.timestamp = a.timestamp+1;

Changes from 0 to 1

SELECT 
  COUNT(*)
FROM 
  test a
INNER JOIN
  test b
ON 
  a.digital_bit = 0 AND
  a.digital_bit != b.digital_bit
  AND b.timestamp = a.timestamp+1;

Changes from 1 to 0

SELECT 
  COUNT(*)
FROM 
  test a
INNER JOIN
  test b
ON 
  a.digital_bit = 1 AND
  a.digital_bit != b.digital_bit
  AND b.timestamp = a.timestamp+1;

Upvotes: 1

Related Questions