Cheeku Jee - 1 year ago 59
MySQL Question

# Count consecutive row occurrences

I have a MySQL table with three columns:

`takenOn (datetime - primary key)`
,
`sleepDay (date)`
, and
`type (int)`
. This table contains my sleep data from when I go to bed to when I get up (at a minute interval).

As an example, if I go to bed on Oct 29th at 11:00pm and get up on Oct 30th at 6:00am, I will have 420 records (7 hours * 60 minutes).
`takenOn`
will range from
`2016-10-29 23:00:00`
to
`2016-10-30 06:00:00`
.
`sleepDay`
will be
`2016-10-30`
for all 420 records.
`type`
is the "quality" of my sleep (1=asleep, 2=restless, 3=awake). I'm trying to get how many times I was restless/awake, which can be calculated by counting how many times I see type=2 (or type=3) consecutively.

So far, I have to following query, which works for one day only. Is this the correct/"efficient" way of doing this (as this method requires that I have the data without any "gaps" in
`takenOn`
)? Also, how can I expand it to calculate for all possible
`sleepDays`
?

``````SELECT
sleepDay,
SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS TimesRestless,
SUM(CASE WHEN type = 3 THEN 1 ELSE 0 END) AS TimesAwake
FROM
(SELECT s1.sleepDay, s1.type
FROM sleep s1
LEFT JOIN sleep s2
WHERE
(s2.type <> s1.type OR s2.takenOn IS NULL)
AND s1.sleepDay = '2016-10-30'
ORDER BY s1.takenOn) a
``````

I have created an SQL Fiddle - http://sqlfiddle.com/#!9/b33b4/3

Thank you!

Your own solution is quite alright, given the assumptions you are aware of.

I present here an alternative solution, that will deal well with gaps in the series, and can be used for more than one day at a time.

The downside is that it relies more heavily on non-standard MySql features (inline use of variables):

``````select sleepDay,
sum(type = 2) TimesRestless,
sum(type = 3) TimesAwake
from (
select @lagDay as lagDay,
@lagType as lagType,
@lagDay := sleepDay as sleepDay,
@lagType := type as type
from   (select * from sleep order by takenOn) s1,
(select @lagDay := '',
@lagType := '') init
) s2
where   lagDay <> sleepDay
or lagType <> type
group by sleepDay
``````

To see how it works it can help to select the second `select` statement on its own. The inner-most `select` must have the `order by` clause to make sure the middle query will process the records in that order, which is important for the variable assignments that happen there.