MySQLで連続した行を集計するSQL

Question

MySQLで、連続したデータを集計する方法についてです。
以下のようなテーブルがあるとすると、

epoc_sec	sensor_id
1628236711	0
1628236712	2
1628236713	2
1628236714	2
1628236715	2
1628236716	1
1628236717	1
1628236718	1
1628236719	1

area_idの件数をまとめたいです。
理想の結果としては、

epoc_sec	sensor_id	count
1628236711	0	1
1628236712	2	4
1628236716	1	4

上記のようにしたいのですが、
（※epoc_secは最初に見つかった値とします。）
色々調べてみたところ旅人算メソッドというものがヒットしたのですが、
内容を確認してもピンと来ず、試行錯誤中です。

select p2.epoch_sec, min(p2.sensor_id)  , max(p2.sensor_id)  ,count(*)
from (select p1.epoch_sec, p1.sensor_id,
       Row_Number() over(order by p1.epoch_sec)
      -Row_Number() over(order by p1.epoch_sec)
      as buff
      from persons p1
      ) p2
group by buff
order by min(p2.epoch_sec);

MySQL8.0です。
よろしくお願いいたします。

Answer 1

誤字？が多い…area_idなのかsensor_idなのか、epoch_secなのかepoc_secなのか…

旅人算メソッドというのは「連続した番号から連番を減算すると一定値になる」ことを
利用したグループ化の手段だと思います。名前は初めて聞きましたがやってることを見るとそういうことかと。

select p1.epoc_sec, p1.area_id
      ,Row_Number() over(partition by p1.area_id order by p1.epoc_sec) rn
      ,p1.epoc_sec-Row_Number() over(partition by p1.area_id order by p1.epoc_sec)+1 as buff
from tbl p1

↑これをグループ化やカウントすれば目的にたどり着けます。

select min(epoc_sec) as epoc_sec,area_id,count(*),buff
from (
select p1.epoc_sec, p1.area_id
      ,Row_Number() over(partition by p1.area_id order by p1.epoc_sec) rn
      ,p1.epoc_sec-Row_Number() over(partition by p1.area_id order by p1.epoc_sec)+1 as buff
from tbl p1
) p2
group by area_id,buff
order by buff,area_id

Comments

[blue0524]

誤字の件、色々やっていた途中のＳＱＬを貼り付けてしまったので、よろしくない状態でした。
ご指摘ありがとうございます。
また本題ですが、意図していた結果を返すことが概ね達成することができ、感謝申し上げます。
大変助かりました！！！

Answer 2

アバウトです

create table tbl(epoc_sec int primary key,area_id int);
insert into tbl values
(1628236711,0),
(1628236712,2),
(1628236713,2),
(1628236714,2),
(1628236715,2),
(1628236716,1),
(1628236717,1),
(1628236718,1),
(1628236719,1);

集計

set @a:=-999,@b:=-999; -- 適当な初期値
select epoc_sec,max(area_id ),count(*) from (
select case @b when area_id then @a else @a:=epoc_sec end as epoc_sec,@b:=area_id as area_id 
from tbl
order by epoc_sec
) as dummy
group by epoc_sec

Comments

[blue0524]

ありがとうございます。
申し訳ありませんが確認させてください。
先頭行のsetで設定する初期値というのはどのような役割をしているのでしょうか。

[yambejp]

変数の初期値を設定しないで比較するとおかしな挙動になります
set構文を書きたくないなら無理やりこんな感じでも処理できます

select epoc_sec,max(area_id ),count(*) from (
select case (select @b from (select @b:=-999) as dummy) when area_id then (select @a from (select @a:=-999) as dummy) else @a:=epoc_sec end as epoc_sec,@b:=area_id as area_id
from tbl
order by epoc_sec
) as dummy
group by epoc_sec