MySQLでの一定時間間隔でのデータ抽出について

Question

sqlについて質問があります。
ひとつのテーブルに以下のような大量の時系列データがあります。時間は1分間隔のときもあれば5分間隔のときもありランダムです。

テーブル名:test01

計測時間	データ	データid
2022/06/06 0:00:00	1.001	0
2022/06/06 0:01:00	1.003	0
:	:	:
2022/12/01 0:00:00	2.005	0
2022/06/06 0:00:00	2.001	1
2022/06/06 0:01:00	2.003	1
:	:	:
2022/12/01 0:00:00	1.005	1
2022/06/06 0:00:00	3.001	2
:	:	:

このテーブルから一つのデータid(以下ではデータid=0)の1時間間隔で高速にデータを抽出しようとしており、いろいろ試しているのですが、
以下のコマンドを入力した場合に比較的速くデータが抽出できました。

set  @dt := '2022-06-05 23:00:00';
select(@dt := @dt  + interval 1 hour) as time, データ, データid from test01 where 計測時間 = @dt  + interval 1 hour and データid =0 and @dt<='2022-12-01 0:00:00';

sql結果

time	データ	データid
2022/06/06 0:00:00	1.001	0
:	:	:
2022/06/06 0:00:00	2.005	0

しかし以下のようにデータがない期間をセットする場合にそこで検索が止まってしまい、それ以降の結果が得られませんでした。

set  @dt := '2022-06-05 22:00:00';  #←23:00:00から22:00:00に変更
select(@dt := @dt  + interval 1 hour) as time, データ, データid from test01 where 計測時間 = @dt  + interval 1 hour and データid =0 and @dt<='2022-12-01 0:00:00';

sql結果

time	データ	データid

実際にはwhere句でマッチしない時間については、無視して出力してほしいのですが、もし改善案がありましたらご教授いただけませんでしょうか。

よろしくお願い致します。

Answer 1

information_schema.columnsを利用して範囲を指定して等間隔の表を入手し
元テーブルの方は生成列をつくってインデックスを貼っておいて処理すれば
効率的に集計できると思います

参考

2022-06-01 1時から10時まで1時間ごとのdatetime

SELECT (select @a := @a +  interval 1 hour from (select @a:='2022-06-01 0:00:00') as dummy) AS tm
FROM information_schema.COLUMNS
where @a<='2022-06-01 10:00:00'

テーブルのサンプル

create table test01
(
cal_time datetime, # 計測時間
data real, # データ
data_id int, # データid
cal_time2 varchar(20) as (cal_time - interval minute(cal_time) minute - interval second(cal_time) second),
  index(cal_time2)
);

insert into test01(cal_time,data,data_id) values
('2022-06-06 0:00:00',1.001,0),
('2022-06-06 0:01:00',1.003,0),
('2022-12-01 0:00:00',2.005,0),
('2022-06-06 0:00:00',2.001,1),
('2022-06-06 0:01:00',2.003,1),
('2022-12-01 0:00:00',1.005,1),
('2022-06-06 0:00:00',3.001,2),
('2022-06-06 1:01:00',1.001,1),
('2022-06-06 2:05:00',1.002,1),
('2022-06-06 2:10:00',1.003,2);

上記cal_time2が時間単位のデータになっているのでそれをキーに集計すればそこそこ効率的です

select cal_time2,count(*) from test01
group by cal_time2

Comments

[shimotani1028]

回答ありがとうございます。
一度以下のように等間隔の表を作成して処理したのですが、３０分くらいかかってしまって使い物になりませんでした。
インデックスは"データid"に貼っています。
1億行ほどあるので、それが普通なのか、そもそもmysqlにこういった処理が向いていないとかもあるんでしょうか。

```mysql
select time,データ from (
SELECT @dt := '2022-06-01 0:00:00' AS time
UNION
SELECT @dt := @dt + interval 1 hour
FROM information_schema.COLUMNS
WHERE @dt<='2022-12-01 0:00:00'
) as A left join (select * from test01 where データid= 0) B on A.time = B.計測時間;
```

[yambejp]

集計したいサンプルデータを提示ください
（create table+insert形式で）
現状アバウトすぎてsampleが提示できません

[shimotani1028]

もとがcsvをload dataしたものでして、少し時間ください。

[yambejp]

あんまり大量のデータじゃないほうがいいと思います。
10～20レコードが目安ですかね

[shimotani1028]

初心者で時間がかかってしまい、申し訳ございません。
以下のコードでサンプルのテーブル(test01)が作成できると思います。
実際にはデータ数は数10万、データid数は500以上あります。
```mysql
drop table IF EXISTS test01;
create table test01
(
cal_time datetime, # 計測時間
data real, # データ
data_id int # データid
);

DROP PROCEDURE IF EXISTS insert_data;
delimiter //
create procedure insert_data(in count int, in data_count int)
begin
declare i int;
declare j int;
set i = 0;
set @time = '2022-05-01 00:00'; # 計測時間の開始時間
while i < count do # データ数のループ
set j = 0; # データidの開始id
while j < data_count do # データid数のループ
set @data_val = rand(); # データ値(仮)
set @query = concat('insert into test01 values("',@time,'",',@data_val,',',j,')');
prepare stmt from @query;
execute stmt;
set j = j + 1;
end while;
set i = i + 1;
set @time = @time + interval 2 minute; # 今回は２分間隔でデータがあると仮定
end while;

end //
delimiter ;

call insert_data(10,3); # 列の追加(引数=データ数,データid数)
alter table test01 add index (data_id);
select * from test01;
```

[yambejp]

初心者といいつつprocedureを使いこなすのはなかなか向上心がある方ですね
一応サンプルをつけてあります
cal_time2のところが仮想生成列=virtual generated columnと呼ばれるカラムで
インデックスを設定できるので効率的な処理ができます
あとはどういう集計をしたいかによります
たとえば最初に提示したinformation_schemaで作った定期的なデータに
集計データをサブクエリにしてleft joinしてやるとか・・・

[shimotani1028]

サンプルありがとうございます。
仮想生成列を使う方法があるんですね。
この方法だと速く処理できそうです。
ただ、後出しになって申し訳ないのですが、実際には抽出する間隔は１時間の場合だけでなく、２分とか５分とか３時間とか、様々な間隔で抽出できることが要件でして、そうなるとその都度仮想列を生成するか、別の方法を考えるかしないといけないと思っております。

[yambejp]

集計単位があるならその分だけ生成列が必要かもしれません
もちろん計算結果を利用してgroup by も実行できるので実態に合わせて
生成列を作成するかどうかを個別に検討されるとよいでしょう