计数连续行出现次数

Question

我有一个MySQL表，它有三列：takenOn (datetime - primary key)、sleepDay (date)和type (int)。这个表包含了我从睡觉到起床的睡眠数据(间隔一分钟)。

举个例子，如果我在10月29日晚上11:00睡觉，在10月30日早上6:00起床，我将有420项记录(7小时* 60分钟)。takenOn的范围从2016-10-29 23:00:00到2016-10-30 06:00:00。sleepDay将成为所有420个记录的2016-10-30。type是我睡眠的“质量”(1=asleep，2=restless，3=awake)。我试图得到我不安/清醒的次数，这可以通过计算我连续看到type=2 (或type=3)的次数来计算。

到目前为止，我必须跟踪查询，该查询只工作一天。这是否是正确/“有效”的方法(因为这种方法要求我在takenOn中没有任何“空白”)？此外，如何将其扩展为计算所有可能的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
        ON s2.takenOn = ADDTIME(s1.takenOn, '00:01:00')
    WHERE
        (s2.type <> s1.type OR s2.takenOn IS NULL)
        AND s1.sleepDay = '2016-10-30'
    ORDER BY s1.takenOn) a

我创建了一个SQL http://sqlfiddle.com/#!9/b33b4/3

谢谢!

Answer 1

考虑到您所知道的假设，您自己的解决方案是很好的。

我在这里提出了另一种解决方案，它可以很好地解决系列中的空白，一次可以使用超过一天。

缺点是它更多地依赖于非标准的MySql特性(变量的内联使用)：

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

要了解它是如何工作的，它可以帮助自己选择第二个select语句。最内部的select必须有order by子句，以确保中间查询将按照该顺序处理记录，这对于发生在那里的变量赋值非常重要。

请参阅您更新的SQL小提琴。