MySqlにて累積平均を求めるには？

tokupon

①のようなローデータがあった場合に、日付ごとに累積で平均値を求め②のような出力を行うには、どのような構文を組めばよいか？
SQLに詳しい方、ご教授いただけますと幸いです。
よろしくお願いいたします。

①ローデータ
--------------
Date,種類,値
2018/9/1,A,50
2018/9/1,B,43
2018/9/2,A,70
2018/9/2,B,68

②出力形式
--------------
日付, 累積平均A, 累積平均B
2018/9/1, 50, 43
2018/9/2, 60, 55.5

※小数点第一まで表示

honda

こんにちは。こんな感じでもできます。

※間違っている場合はどなたかご助言おねがいします～

 

select
`Date`,
max(累積A)/max(カウントA) 累積平均A,
max(累積B)/max(カウントB) 累積平均B
from
(
SELECT
Date,種類,
case when 種類 = 'A' then @i := @i+1 end as `カウントA`,
case when 種類 = 'B' then @j := @j+1 end as `カウントB`,
case when 種類 = 'A' then @k := @k+`値` end as `累積A`,
case when 種類 = 'B' then @l := @l+`値` end as `累積B`
FROM
`XXX`,
(select @i:=0)as a,
(select @j:=0)as b,
(select @k:=0)as c,
(select @l:=0)as d
)e
group by Date

honda

こんにちは。

 

いろいろやり方はあるかと思いますが、一案として。以下ではいかがでしょうか。

＃種類を絞らずに一気にやる方法もありましたが複雑かなと思ったので、一旦説明しやすいこちらで失礼します。。。

＃AとBを分けてgroup byしなくてもcase whenを使うと1文でシンプルにできます

 

■手順

１．種類をAに絞って、日付と種類でGROUP BY。累積値から累積平均を算出する

２．Bも同様に算出

３．1と2のテーブルをJOIN

- - - - - - - - 

１．種類をAに絞って、日付と種類でGROUP BY。累積値から累積平均を算出する

- - - - - - - - 

SELECT
*,`累積値`/`カウント` AS `累積平均A`
FROM
(
SELECT
`Date`,`種類`,`値`,
@i:=@i+`値` AS `累積値`,
@j:=@j+1 AS `カウント`
FROM
`ローデータ`,
(SELECT @i:=0)AS r,
(SELECT @j:=0) AS no
WHERE
`種類`='A'
GROUP BY
`Date`,
`種類`
ORDER BY
`Date`

)a

 

 

 

 

 

 

- - - - - - - - 

２．Bも同様に算出

- - - - - - - - 

SELECT
*,`累積値`/`カウント` AS `累積平均B`
FROM
(
SELECT
`Date`,`種類`,`値`,
@i:=@i+`値` AS `累積値`,
@j:=@j+1 AS `カウント`
FROM
`ローデータ`,
(SELECT @i:=0)AS r,
(SELECT @j:=0) AS no
WHERE
`種類`='B'
GROUP BY
`Date`,
`種類`
ORDER BY
`Date`
)b

- - - - - - - - 

３．1と2のテーブルをJOIN

- - - - - - - - 

SELECT a.`Date`,a.`累積平均A`,b.`累積平均B`
FROM `tmp1` a
LEFT OUTER JOIN `tmp2` b
ON a.`Date` = b.`Date`

tokupon

hondaさん
こんにちは。
ロジックのご解説ありがとうございます。
case when文で1つにまとめたバージョンのソースもいただけますと幸いです。
お手数ですが、よろしくお願いいたします。

honda

こんにちは。こんな感じでもできます。

※間違っている場合はどなたかご助言おねがいします～

 

select
`Date`,
max(累積A)/max(カウントA) 累積平均A,
max(累積B)/max(カウントB) 累積平均B
from
(
SELECT
Date,種類,
case when 種類 = 'A' then @i := @i+1 end as `カウントA`,
case when 種類 = 'B' then @j := @j+1 end as `カウントB`,
case when 種類 = 'A' then @k := @k+`値` end as `累積A`,
case when 種類 = 'B' then @l := @l+`値` end as `累積B`
FROM
`XXX`,
(select @i:=0)as a,
(select @j:=0)as b,
(select @k:=0)as c,
(select @l:=0)as d
)e
group by Date

tokupon

ありがとうございます。
こちらのソースで問題なく、処理できました。
感謝申し上げます。

honda

よかったです～