任意の区間で平均したデータを過去n件という形で取得するには

Question

MySQLにて、1秒毎にメモリ使用率が記録されていくテーブルを想定し、これを5分ずつや1時間ずつの適当な単位で平均した上でn件取得するということを行いたいのですが、より良い方法は何でしょうか？

• 例として、レコードが120件の場合、2時間分のデータが分単位で記録されています
• 単位が5分毎で取得件数が10の場合、50件のレコードを5件毎に平均して10件取得したいということです
• カラムはオートインクリメントされるidと日付型と、メモリ使用率のみですが、増やすこともできます
• サーバースクリプトから叩くので、クエリは動的に何行でも生成できます

---

過去に1時間毎の取得を以下のクエリで試みましたが、ONLY_FULL_GROUP_BYの警告が出るため止めました。

select avg(used_memory) from table group by date_format(created_at, '%Y%m%d%H');

最悪は力技ですが、以下文のbetween句を書き換えつつ、n回繰り返し取得するという方式にしようかと考えています。（1時間毎の例）

select avg(used_memory) from table where created_at between '2018-05-15 00:00:00' AND '2018-05-15 00:59:59';

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

Answer 1

MySQLの場合、SELECT句で付けたエイリアスでgroup byやorder by できます

select 
date_format(created_at, '%Y%m%d%H') as term 
from table 
group by term
oder by term desc
limit 10

Comments

[tmgnut]

ご回答ありがとうございます。
なるほど、バグ予防のためにも、エイリアスは積極的に使っていきたいと思います。

Answer 2

ONLY_FULL_GROUP_BY の警告がなんなのかしらないけど
GROUP BY に指定した項目が一個も入ってないよとかなんじゃないの？

select 
   date_format(created_at, '%Y%m%d%H'), // 重要だとおもうが
   avg(used_memory),
   max(used_memory),
   min(used_memory),
   count(used_memory)
from table 
group by date_format(created_at, '%Y%m%d%H');

的な SQLで 警告はでないきがするが

Comments

[tmgnut]

ご回答ありがとうございます。
再度試したところ、ご掲示いただいたコードで希望通りに動作いたしました。
以前、同様のコードを試して失敗していたのですが、おそらく別の要因があったのだと思います。