mysql 平均計算 指定フィールド

Question

やりたいこと
⓵為替APIを10秒間に1回取得し、mysqlに格納。
⓶その後格納された直近3つの値（計30秒）の平均を⓷mysqlに格納したい。

不明点
⓶における文の書き方。どのようにすれば平均値を獲得できるのか。
⓵，⓷はgcpとinsert intoにて確認済み。

イメージ
table
time | usd_rate |
1 | 111 |
2 | 112 |
3 | 113 | →(111+112+113)/3(ここをやりたい。）
4 | 114 | →(112+113+114)/3
・・
・・・
ウィンド関数をやはり使うべきなのでしょうか？？
お手数おかけしますが、ご教示お願いします。

Comments

[yambejp]

timeは連続しているという保証がありますか？また1,２は自分を含む前のデータ数が3に満たないためnullということでよいですか？

[syu-yu]

基本的には連続している前提です。また、1.2はデータ数が3に満たないためnull扱いで、大丈夫です！

Answer 1

条件次第ですが

• 元データ

create table tbl(time int unique,usd_rate double);
insert into tbl values(1,111),(2,112),(3,113),(4,114);

• 集計

select t1.time,(t1.usd_rate+t2.usd_rate+t3.usd_rate)/3 as avg_usd_rate from tbl as t1
left join  tbl as t2 on t1.time=t2.time+1
left join  tbl as t3 on t1.time=t3.time+2

• 追記

よくよく考えるとこうすると効率的かもしれません

select t1.time,
(select avg(usd_rate) from tbl as t2
where t2.time between t1.time -2 and t1.time 
having count(*)>2) as usd_avg
from tbl as t1;

※それもこれもtimeが連続している前提です

Comments

[syu-yu]

ありがとうございます。
色々な方法があるのですね！
試してみます！

Answer 2

こちらですが、下記のやり方であればテーブル結合不要になるのではないかと思います。

SELECT 
    CASE WHEN COUNT(*) < 3 THEN NULL ELSE AVG(usd_rate) END AS avv_usd_rage
FROM
    (SELECT * FROM `table` order by time desc limit 3) AS latest_table

※「直近３回分」のレコードを取得→３件あれば平均値を取得、２件未満ならNullを返します。

なお、２件未満の場合は「その時に存在する分の平均値」を取得するのであればCASE文は不要で

SELECT 
    AVG(usd_rate) AS avv_usd_rage
FROM
    (SELECT * FROM `table` order by time desc limit 3) AS latest_table

だけになります。

この結果を他のテーブル（table2）に入れる場合、手前にINSERT文を付ければOKです。

create table avg_table(time int unique,usd_rate double);

INSERT INTO avg_table
SELECT 
    MAX(time) AS time,
    CASE WHEN COUNT(*) < 3 THEN NULL ELSE AVG(usd_rate) END AS avg_usd_rage
FROM
    (SELECT * FROM `table` order by time desc limit 3) AS latest_table

※同じ時間帯に対して複数回実行する可能性がある場合は、
後から来たデータを優先する場合： INSERT INTO → REPLACE INTO
先に格納されていたデータを優先する場合： INSERT INTO → INSERT IGNORE INTO
に変更してください。