accessで移動平均する方法

質問

会社コード、品目コード、月度、出荷数の入った明細テーブルがあります
これを過去３ヶ月で出荷数を移動平均したいのです

例 元データ
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201408,10
B00000002,BBBBBBB,201408,10

出力したい値
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10←出荷数は同じ会社コード、品目コードの（１０月＋９月＋８月）÷３）
A00000001,AAAAAAA,201409,3.33←出荷数は同じ会社コード、品目コードの（９月＋８月＋７月）÷３）
A00000001,AAAAAAA,201408,3.33←出荷数は同じ会社コード、品目コードの（８月＋７月＋６月）÷３）
B00000002,BBBBBBB,201408,3.33←出荷数は同じ会社コード、品目コードの（８月＋７月＋６月）÷３）

このような事がしたいのですがどのようにしたら実現できるでしょうか
月度で移動平均なんて良く使いそうな気がしますが・・簡単なようで難しく悩んでおります
どうかよろしくお願い致します

回答No.15

山田 太郎（@f_a_007） · ベストアンサー

＞その計算の元ネタとなる過去３ヶ月間の移動平均出荷数が求めたかったのです。

私の最終回答で、出荷数=0の月も含めて求められていると思いますよ。

7月---10
8月---10
9月---0 ----->(10+10+0)/3・・・(7月+8月+9月)/3
10月--10 ---->(10+0+10)/3・・・(8月+9月+10月)/3
11月--10 ---->(0+10+10)/3・・・(9月+10月+11月)/3

添付図では、このように計算しています。こういう計算結果を9月にも出力するには、9月にも出荷数=0の行も必要。その為に、ユニオンクエリで求めた出荷数=0を全ての月に加えています。

１、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加してもDistinct句を使えば出力では重複することはありません。

２、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加しても、移動平均出荷数の計算結果は同じです。10+0=10ですから。

添付図の結果は、[会社コード], [品目コード], [月度],[集荷数]=0を追加した後に、次のSQL文を実行したもの。

SELECT DISTINCT [会社コード], [品目コード], [月度], Format(Rounds(DSum("出荷数", "出荷履歴", WHERE 会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 BETWEEN ' MoveMonth([月度],-3) & "' AND '" & [月度] & "'")/3,0,2),"0.00") AS 平均出荷数
FROM 出荷履歴
WHERE 月度 BETWEEN "201408" AND "201410"
ORDER BY [会社コード], [品目コード], [月度];

動かないとすれば、単なる転記ミスです。

１、DISTINCT句を使っていること。
　　これで、ダミー行を出力から追放することができます。
２、表示する期間を指定していること。
　　表示する期間は、まあ、実際に運用する際に全期間の結果を求めないだろうと考えたからに過ぎません。

なお、MoveMonth([月度],-3) でBETWENNを簡略化、あるいはRounds()で四捨五入していること。これらは、枝葉の問題。

要は、[会社コード], [品目コード], [月度],[集荷数]=0を追加すればよいだけと思いますが・・・。

お礼

ありがとうございます

＞＞１、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加してもDistinct句を使えば出力では重複することはありません

この考え方は目からうろこです
そうですね、足りない月のレコードだけを追加する事にこだわって難しく考えていたかもしれません
全部の月に対して出荷数０のレコードを追加した後で
select 会社コード、品目コード、sum(出荷数） from hoge group by 会社コード、品目コード

グルーピングしてあげればもともとレコードのない月は出荷数０のレコードとして出力されますね

回答No.6

山田 太郎（@f_a_007）

訂正：

×　８～9月
〇　8～10月

回答No.5

山田 太郎（@f_a_007）

＞月次の出荷数が0の場合・・・

なぜ、追加する必要があるのですか？
無くても集計はする筈ですが・・・？
添付図のような結果ではダメってことですか？

回答No.4

hatena1989

移動平均にもいろいろありますが、

移動平均 - Wikipedia
http://ja.wikipedia.org/wiki/%E7%A7%BB%E5%8B%95%E5%B9%B3%E5%9D%87

今回の事例は、上記の「単純移動平均」と私は解釈しました。

質問のデータ例が間違っているようですが、
やりたいことは下記のようなことですよね。

元データ
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201409,10
A00000001,AAAAAAA,201408,10
B00000002,BBBBBBB,201408,10

出力
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201409,6.66
A00000001,AAAAAAA,201408,3.33
B00000002,BBBBBBB,201408,3.33

月度 201409 の移動平均は、（９月＋８月＋７月）÷３ ですが、7月がないので２０÷３=6.66・・・
になりますね。

ちなみに私の最初の回答の式にタイプミスがありました。
「Brtween」を「Between」に修正してください。

SQLも出しておきますね。

SELECT 会社コード, 品目コード, 月度,
DSum("出荷数","元データ","会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 Between " & Format(DateAdd("m",-3,Format([月度],"0000\/00")),"yyyymm") & " AND " & [月度])/3 AS 平均出荷数
FROM 元データ;

なお、丸め（四捨五入、切り捨て等）については、特に指定がないので考慮してません。
指定があるなら追記してください。

お礼

ありがとうございます
説明不足だった事に気がつきました

確かに教えて頂いた方法で計算できたのですが
問題は出荷が０件の時の扱いを説明していませんでした
最初の私の例では201409のデータは書いていませんが
これは出荷が０だったので明細データとして存在しないという意味です
そしてデータを収集を開始したのが８月から１０月までなので
７月以前の移動平均の結果は出力する必要はないのですが
出荷数０の月の移動平均の出力結果も出したいのです

元データに８月～１０月で存在しない月のデータがあったら出荷数０でデータを追加した後に
クエリを動かせば目的のものができるのですが
データ量が多いのでこの出荷数０を補完するデータをどのように作り出せばいいか今悩んでいます
追加したいデータは

A00000001,AAAAAAA,201409,0
B00000002,BBBBBBB,201410,0
B00000002,BBBBBBB,201409,0

になります
これを一気に追加することが追加クエリでできるでしょうか？

回答No.3

山田 太郎（@f_a_007）

どうやら、私の解釈は合っているようなのでSQL文全体を補足します。

SELECT
　　DISTINCT [会社コード], [品目コード],
　　Rounds(Nz(DSum(・・・・・))/3,1) AS 平均出荷数
FROM 出荷履歴
WHERE 月度 BETWEEN "201408" AND "201410";

※DSum()関数内の書き方は、既に回答が付いているので割愛します。

直近3ヶ月ということですが、11月初旬ですと8月～10月ということも考えられます。そこら辺りをどのように考えておられるのかが不明。よって、一応、回答では開始月と終了月とを埋め込んでいます。実際に運用される場合は、しかるべく修正されて下さい。

注意点１、DSum()は Null 値を戻すこともあります。
　　　　　Nz(DSum()) と Null を 0に変換しておいたが無難です。
注意点２、Select文も期間を指定する必要があります。
　　　　　でないと、指定期間以外も出力します。
注意点３、Distinct句を指定した方がいいです。
　　　　　[会社コード], [品目コード]の組合せの重複出力を回避できます。

以上、補足しておきます

お礼

ありがとうございます
出荷数０の時の扱いの説明が不足していた事に気がつきました
詳しくはＮｏ４さんのところに書いています
やりたい事の半分はできたのですが・・・

回答No.2

hatena1989

元データテーブルからクエリを作成して、フィールド欄に下記の式を設定すればいいでしょう。

平均出荷数: DSum("出荷数","元データ","会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 Brtween " & Format(DateAdd("m",-3,Format([月度],"0000\/00")),"yyyymm") & " AND " & [月度])/3

お礼

ありがとうございます
出荷数０の時の扱いの説明が不足していた事に気がつきました
詳しくはＮｏ４さんのところに書いています
やりたい事の半分はできたのですが・・・

回答No.1

山田 太郎（@f_a_007）

【出荷履歴】

1_|A00000001|AAAAAAA|201408|10
2_|A00000001|AAAAAAA|201409|20
3_|A00000001|AAAAAAA|201410|30
4_|B00000002|BBBBBBB|201408|10
5_|B00000002|BBBBBBB|201409|20
6_|B00000002|BBBBBBB|201410|30
7_|C00000003|CCCCCCC|201408|10
8_|A00000001|AAAAAAA|201411|10

【Step1】

SELECT DISTINCT 会社コード, 品目コード
FROM 出荷履歴;

＜結果＞

A00000001|AAAAAAA
B00000002|BBBBBBB
C00000003|CCCCCCC

さて、このSQL文に

同じ会社コードと商品コードを持つ行の出荷数の合計をBETWEENで期間を指定して3で除して四捨五入すれば、添付図のような結果を得ることができると思いますよ。少し課題があるとすれば、AccessのRound()は銀行型丸めで四捨五入ではないことです。ですから、仮に四捨五入したい場合は、以下のような Rounds() を標準モジュールに付加する必要があります。

Public Function Rounds(ByVal M As Currency, _
　　　　　　　　　　　 ByVal A As Integer, _
　　　　　　　　　　　 Optional D As Integer = 0) As Variant
　　Dim R As Currency

　　Select Case A
　　　　Case 0 ' 四捨五入
　　　　　　R = Fix(M * 10 ^ D + 0.5@)
　　　　Case 1 '切り捨て
　　　　　　R = Fix(M * 10 ^ D)
　　　　Case 2 ' 切り上げ
　　　　　　If Int(M) <> M Then
　　　　　　　　R = Fix(M * 10 ^ D) + 1 * 10 ^ D
　　　　　　Else
　　　　　　　　R = Fix(M * 10 ^ D)
　　　　　　End If
　　End Select
　　Rounds = Sgn(M) * (R / 10 ^ D)
End Function

なお、私の解釈で合っていれば添付図のSQL文を補足します。ただ、

月度 BETWEEN '201408' AND '201410'

と、集計月度の期間データはSQLに埋め込んでいます。

お礼

ありがとうございます
出荷数０の時の扱いの説明が不足していた事に気がつきました
詳しくはＮｏ４さんのところに書いています
やりたい事の半分はできたのですが・・・