特定の1分間単位でデータを見るSQLを書きたい

Question

id列、time列、data列のある、テーブルAがあるとします。
id列には数字でidが、time列にはUnixtimeが、data列には1か0が入っているとします。

どこか特定の1分間で、dataが1の行が8割以上を占めている場合、その1分間のidをすべて取得する

というSQLを書くことは可能でしょうか？

分析関数(window関数)を使っても大丈夫です。

Answer 1

time列を適当に返還した上でgroup byしてdata列を集計すれば良さそうですが
分単位の検索が確定しているなら最初から分用のカラムを作っておくと
効率的でしょう
あとはSQLの種類次第です

Comments

[kario]

time列の処理は自分の技術でできるのですが、”特定の1分間単位でデータを見る”というSQLの書き方が調べてもわからない（そもそもできるものなのか）ので、教えていただきたいです…

[yambejp]

回答にも書きましたがSQL次第なのでなんとも・・・

[kario]

すみません、BigQueryで使えるSQLであれば、なんでも大丈夫です！

[yambejp]

ごめんなさい、ふと思いなおしたのですが、
仕様の再確認

> 特定の1分間

とは、12:01とか12:02とか分単位もしくは
13:10:10～13:11:09のように秒単位なのでしょうか？
具体的なサンプルを提示していただいたほうが回答しやすいです

[kario]

13:10:10～13:11:09のように秒単位です。

より具体的には、例えばtimeに
13:10:10
13:11:09
13:11:20
13:11:30
13:11:40
13:11:50
13:12:00
13:12:10
13:12:20
13:12:30
が入っているとしたら、

13:10:10～13:11:10 の範囲でもdataが1になっている割合を見るし(この場合は2つの行が計算対象内)、
13:11:20～13:12:20も同時に見ます(この場合は7つの行が計算対象内)。
1分の集計範囲の枠を、上から順に移動させていくイメージです。

[yambejp]

あー秒単位なんですね、そうなるとちょっと面倒ですね
ちょっとやってみます

create table tbl(id int primary key,t time,data tinyint);
insert into tbl values
(1,'13:10:10',1),
(2,'13:10:11',1),
(3,'13:11:09',1),
(4,'13:11:20',0),
(5,'13:11:30',0),
(6,'13:11:40',0),
(7,'13:11:50',0),
(8,'13:12:00',0),
(9,'13:12:10',0),
(10,'13:12:20',0),
(11,'13:12:30',0);

[yambejp]

select * from tbl as t4
where exists (select 1 from (
select min(t) as t from(
select t1.id,t1.t,t2.data from tbl as t1
inner join tbl as t2 on t2.t between t1.t and t1.t + interval 1 minute
) as t3
group by id
having sum(data=1)/count(*)>0.8
) as t5 where t4.t between t5.t and t5.t+interval 1 minute)

[kario]

クエリありがとうございます！
すみません、少し説明が悪かったのですが、

例えば
(1,'13:10:10',0),
(2,'13:10:30',0),
(3,'13:11:10',0),
(4,'13:11:20',0),
(5,'13:11:35',0),
(6,'13:11:40',0),
(7,'13:11:50',0),
(8,'13:12:00',0),
(9,'13:12:10',0),
(10,'13:12:20',0),
(11,'13:12:30',1)
の場合、13:11:30～13:12:30 のdata=1割合は8割を超えないので、”結果なし”
としたいです。

[yambejp]

前方向にも1分間とるのですか？
前方向に１分で80%以上かつ後方向に１分で80%以上
ということでしょうか？
ID=11は最終データなので後ろ方向には100%ですし・・・

[kario]

この例だと、
13:10:10(最初の行)～13:12:30(最後の行)
のどこか特定の1分間の枠での80%を計算します。
最初の枠が13:10:10～13:11:10
次の枠が、1秒後にずらして13:10:11～13:11:11
・
・
・
最後の枠が13:11:30～13:12:30
になります。

[yambejp]

ああ、テーブルにある値ではなく常に全秒数を検索するのですね？
なんかクソ重そうなので運用方法を考え直した方が良さそうですが
開始時間、終了時間（実際には終了時間の1分前）を指定すればできないことはないでしょう

プロシージャでテンポラリテーブルでも作ってやるとかですかね・・・

[kario]

運用を考え直し、全秒検索ではなく全行の後ろ方向のみ取ることになりました。
なので、いただいたクエリを参考にして解決することができました。

ありがとうございました！