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

tokupon
tokupon Member
編集済: 5月 2023 仲間に相談

①のようなローデータがあった場合に、日付ごとに累積で平均値を求め②のような出力を行うには、どのような構文を組めばよいか?
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
    honda Contributor
    回答済み✓

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

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

     

    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
    honda Contributor

    こんにちは。

     

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

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

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

     

    ■手順

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

    2.Bも同様に算出

    3.1と2のテーブルをJOIN

    - - - - - - - - 

    1.種類を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

    1.png

     

     

     

     

     

     

    - - - - - - - - 

    2.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

    2.PNG

    - - - - - - - - 

    3.1と2のテーブルをJOIN

    - - - - - - - - 

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

    3.PNG

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

  • honda
    honda Contributor
    回答済み✓

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

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

     

    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
    honda Contributor

    よかったです~スマイリー ハッピー