在 MySQL 中查找時間線之間的發生次數和時間間隔

我有一個表，如下所示，它有一個時間線和來自不同感測器的值（如是/否）。如何執行以下操作：

觸發器發生了多少次（對於 YES 到 NO 或 NO 到 YES），在下面的範例中它發生了 2 次。

1 , A0001, NO,  2017-06-14 02:03:26.0
2 , A0001, NO,  2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO,  2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0

使用 Sql 伺服器，

declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES 
(1 , 'A0001' , 'NO',  '2017-06-14 02:03:26.0') 
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0') 
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0') 
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0') 
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0') 
,(6 , 'A0001',  'NO' ,'2017-06-19 02:03:26.0') 

;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)

, cte
AS (
   SELECT
       rn
       ,col1
       ,col2
       ,col3
       ,0 changecount
   FROM cte1
   where rn=1

   UNION ALL

   SELECT 
       t.rn
       ,t.col1
       ,t.col2
       ,t.col3
       ,CASE 
           WHEN t.col2 <> t1.col2
               THEN changecount + 1
           ELSE changecount
           END
   FROM cte1 t
   INNER JOIN cte t1 ON t.col1 = t1.col1
       AND t.rn = t1.rn + 1
   )
SELECT *
FROM cte

如果您使用的是 8+ 版本，則可以使用視窗函式：

select count(distinct grp) from (
   select row_number() over (partition by col1 order by col3) 
        - row_number() over (partition by col1, col2 order by col3) as grp
   from t
) as tt;

正如 RDFozz 在他的評論中建議的那樣，實際上有四個變化

小提琴

引用自：https://dba.stackexchange.com/questions/176227