I have a MySQL table with three columns:
takenOn (datetime - primary key)
SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS TimesRestless,
SUM(CASE WHEN type = 3 THEN 1 ELSE 0 END) AS TimesAwake
(SELECT s1.sleepDay, s1.type
FROM sleep s1
LEFT JOIN sleep s2
ON s2.takenOn = ADDTIME(s1.takenOn, '00:01:00')
(s2.type <> s1.type OR s2.takenOn IS NULL)
AND s1.sleepDay = '2016-10-30'
ORDER BY s1.takenOn) a
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.
See your updated SQL fiddle.