FC2ブログ

スポンサーサイト 


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

クエリ 過去平均フィールド追加の模索 


テーブル「TBL_A」が、日付、売上 で構成されているとした時、
その日以前の4日分の平均をフィールドとして追加した表示をしたい・・・・ってなった時、どうすればよい?
4日を限定しないで、4件分を・・・
また、4件なかったらある分だけで・・・

日付売上平均
2011/07/01100 
2011/07/02200 7/1 の値
2011/07/03300 7/1 ~ 7/2 の平均
2011/07/04400 7/1 ~ 7/3 の平均
2011/07/05500 7/1 ~ 7/4 の平均
2011/07/06600 7/2 ~ 7/5 の平均
2011/07/07700 7/3 ~ 7/6 の平均
2011/07/08800 7/4 ~ 7/7 の平均
2011/07/09900 7/5 ~ 7/8 の平均
2011/07/101000 7/6 ~ 7/9 の平均
 
まず、「TBL_A」を
TBL_A AS T1, TBL_A AS T2
と直積で結合して、T2.日付 < T1.日付 とすると、その時と過去のものを結び付けられますね。
SELECT T1.日付, T2.日付, T2.売上
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 < T1.日付
ORDER BY T1.日付, T2.日付;

この結果は以下になります。
T1.日付T2.日付売上
2011/07/022011/07/01100
2011/07/032011/07/01100
2011/07/032011/07/02200
2011/07/042011/07/01100
2011/07/042011/07/02200
2011/07/042011/07/03300
2011/07/052011/07/01100
2011/07/052011/07/02200
2011/07/052011/07/03300
2011/07/052011/07/04400
2011/07/062011/07/01100
2011/07/062011/07/02200
2011/07/062011/07/03300
2011/07/062011/07/04400
2011/07/062011/07/05500
2011/07/072011/07/01100
2011/07/072011/07/02200
2011/07/072011/07/03300
2011/07/072011/07/04400
2011/07/072011/07/05500
2011/07/072011/07/06600
2011/07/082011/07/01100
2011/07/082011/07/02200
2011/07/082011/07/03300
2011/07/082011/07/04400
2011/07/082011/07/05500
2011/07/082011/07/06600
2011/07/082011/07/07700
2011/07/092011/07/01100
2011/07/092011/07/02200
2011/07/092011/07/03300
2011/07/092011/07/04400
2011/07/092011/07/05500
2011/07/092011/07/06600
2011/07/092011/07/07700
2011/07/092011/07/08800
2011/07/102011/07/01100
2011/07/102011/07/02200
2011/07/102011/07/03300
2011/07/102011/07/04400
2011/07/102011/07/05500
2011/07/102011/07/06600
2011/07/102011/07/07700
2011/07/102011/07/08800
2011/07/102011/07/09900


4件以上あるものについて、4件分をどうやって絞りましょうか・・・

WHERE T2.日付 < T1.日付
部分を
WHERE T2.日付 IN
(SELECT TOP 4 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
のようにしてみたら・・・
その時としてみている T1.日付 以前の 日付降順で4つ分が T2.日付 と同じだったら・・・
SELECT T1.日付, T2.日付, T2.売上
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 4 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
ORDER BY T1.日付, T2.日付;

この結果は以下になります。
T1.日付T2.日付売上
2011/07/022011/07/01100
2011/07/032011/07/01100
2011/07/032011/07/02200
2011/07/042011/07/01100
2011/07/042011/07/02200
2011/07/042011/07/03300
2011/07/052011/07/01100
2011/07/052011/07/02200
2011/07/052011/07/03300
2011/07/052011/07/04400
2011/07/062011/07/02200
2011/07/062011/07/03300
2011/07/062011/07/04400
2011/07/062011/07/05500
2011/07/072011/07/03300
2011/07/072011/07/04400
2011/07/072011/07/05500
2011/07/072011/07/06600
2011/07/082011/07/04400
2011/07/082011/07/05500
2011/07/082011/07/06600
2011/07/082011/07/07700
2011/07/092011/07/05500
2011/07/092011/07/06600
2011/07/092011/07/07700
2011/07/092011/07/08800
2011/07/102011/07/06600
2011/07/102011/07/07700
2011/07/102011/07/08800
2011/07/102011/07/09900


良さそうなので、ここで平均を求めておきましょう。(ここの記述を※1とします)
SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 4 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付;
 (平均値を Round で丸めるようにしてました・・・・)
この結果は以下になります。
日付平均
2011/07/02100
2011/07/03150
2011/07/04200
2011/07/05250
2011/07/06350
2011/07/07450
2011/07/08550
2011/07/09650
2011/07/10750


このままでは、先頭の 7/1 分のデータがなくなるので、
元の「TBL_A」とこの結果を LEFT JOIN させるようにしてみます。
SELECT TBL_A.日付, TBL_A.売上, Q4.平均
FROM TBL_A LEFT JOIN
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均 FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 4 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付) AS Q4 ON TBL_A.日付=Q4.日付
ORDER BY TBL_A.日付;

この結果は以下になります。
日付売上平均
2011/07/01100 
2011/07/02200100
2011/07/03300150
2011/07/04400200
2011/07/05500250
2011/07/06600350
2011/07/07700450
2011/07/08800550
2011/07/09900650
2011/07/101000750


過去何件分かは、※1で記述した TOP 4 の 4 部分を変更することで対応できます。
3件分なら 3 、5件分なら 5 と変更します。
ちなみに、
3件分の平均を "平均3" 、4件分の平均を "平均4" 、5件分の平均を "平均5" として表示するには、
SELECT TBL_A.日付, TBL_A.売上, Q3.平均 AS 平均3, Q4.平均 AS 平均4, Q5.平均 AS 平均5
FROM ((TBL_A
LEFT JOIN
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 3 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付) AS Q3 ON TBL_A.日付=Q3.日付)
LEFT JOIN
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 4 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付) AS Q4 ON TBL_A.日付=Q4.日付)
LEFT JOIN
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 5 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付) AS Q5 ON TBL_A.日付=Q5.日付
ORDER BY TBL_A.日付;

この結果は以下になります。
日付売上平均3平均4平均5
2011/07/01100   
2011/07/02200100100100
2011/07/03300150150150
2011/07/04400200200200
2011/07/05500300250250
2011/07/06600400350300
2011/07/07700500450400
2011/07/08800600550500
2011/07/09900700650600
2011/07/101000800750700



直積でやってみたけど、データ量が多くなると動き的にはどうなんだろう。

レコードに番号をつけて、ワークテーブルに書き出して・・・ってな方法が良い??
テーブルに書き出すまでもなく、レコードに番号を振るクエリ「Q_TBL_A」を以下で作ったとします。
SELECT 日付, 売上, DCount("*","TBL_A","日付 <= #" & 日付 & "#") AS CT
FROM TBL_A;
これの表示は以下のようになります。
日付売上CT
2011/07/011001
2011/07/022002
2011/07/033003
2011/07/044004
2011/07/055005
2011/07/066006
2011/07/077007
2011/07/088008
2011/07/099009
2011/07/10100010

そして、このクエリを使った表示をしてみます。(過去4件分の平均とすると)
SELECT
日付, 売上, DAvg("売上","Q_TBL_A","CT >= " & CT-4 & " AND CT < " & CT) AS 平均
FROM Q_TBL_A;

この結果は以下になります。
日付売上平均
2011/07/01100 
2011/07/02200 100
2011/07/03300 150
2011/07/04400 200
2011/07/05500 250
2011/07/06600 350
2011/07/07700 450
2011/07/08800 550
2011/07/09900 650
2011/07/101000 750

平均部分は文字列扱いかな? 表示が左に寄ってます。


必要になった時に考えてみよう・・・・


余談)

平均3 ~ 平均5 を表示するものを、クロス集計でやってみようとしましたが、できませんでした。
「 'T1.日付' を有効なフィールド名、または式として認識できません。 」
のエラーになってしまいます。

書いてみたのは以下なんですけど
TRANSFORM First(売上) AS 値
SELECT 日付
FROM (
SELECT '売上' AS 項, 日付, 売上 FROM TBL_A
UNION ALL
SELECT '平均3', 日付, 平均 FROM
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 3 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付)
UNION ALL
SELECT '平均4', 日付, 平均 FROM
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 4 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付)
UNION ALL
SELECT '平均5', 日付, 平均 FROM
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 5 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付)
) AS Q1
GROUP BY 日付
PIVOT 項;

 
TRANSFORM First(売上) AS 値
SELECT 日付
FROM (
SELECT '売上' AS 項, 日付, 売上 FROM TBL_A
) AS Q1
GROUP BY 日付
PIVOT 項;
の記述では
日付売上
2011/07/01100
2011/07/02200
2011/07/03300
2011/07/04400
2011/07/05500
2011/07/06600
2011/07/07700
2011/07/08800
2011/07/09900
2011/07/101000

になるし、
SELECT '平均3', 日付, 平均 FROM
(SELECT T1.日付, Round(Avg(T2.売上),1) AS 平均
FROM TBL_A AS T1, TBL_A AS T2
WHERE T2.日付 IN
(SELECT TOP 3 日付 FROM TBL_A WHERE TBL_A.日付 < T1.日付 ORDER BY 日付 DESC)
GROUP BY T1.日付)
の記述では、
Expr1000日付平均
平均32011/07/02100
平均32011/07/03150
平均32011/07/04200
平均32011/07/05300
平均32011/07/06400
平均32011/07/07500
平均32011/07/08600
平均32011/07/09700
平均32011/07/10800

となって、UNION でつなげる分には良さそうなんだけど・・・・

直積で、途中 GROUP BY を使っているから(?)クロス集計でできないのか・・・・
クエリ「Q_TBL_A」を使うクロス集計の記述は以下
TRANSFORM First(売上) AS 値
SELECT 日付
FROM (
SELECT '売上' AS 項, 日付, 売上 FROM Q_TBL_A
UNION ALL
SELECT '平均3', 日付, DAvg("売上","Q_TBL_A","CT >= " & CT-3 & " AND CT < " & CT) FROM Q_TBL_A
UNION ALL
SELECT '平均4', 日付, DAvg("売上","Q_TBL_A","CT >= " & CT-4 & " AND CT < " & CT) FROM Q_TBL_A
UNION ALL
SELECT '平均5', 日付, DAvg("売上","Q_TBL_A","CT >= " & CT-5 & " AND CT < " & CT) FROM Q_TBL_A
) AS Q1
GROUP BY 日付
PIVOT 項;
の記述では、
日付売上平均3平均4平均5
2011/07/01 100   
2011/07/02 200 100 100 100
2011/07/03 300 150 150 150
2011/07/04 400 200 200 200
2011/07/05 500 300 250 250
2011/07/06 600 400 350 300
2011/07/07 700 500 450 400
2011/07/08 800 600 550 500
2011/07/09 900 700 650 600
2011/07/10 1000 800 750 700

これなら、そこそこ良さそうですけど・・・・・

でも、使うクエリ数は極力少なくしたいですねぇ~ 私的には・・・
(管理が面倒になるし・・・、人に見せたくないし・・・)


1つのクエリだけでがんばってみましたが、じれったくなるとすぐに VBA に行っちゃいますね。
(過去の平均を求める部分をユーザ定義関数にしちゃいます・・・)

ん~~、 どうなんだろう・・・・

(2007にて)


そうそう、今回はサンプルファイルの添付はありません。
でも、確認は簡単にできます。
表示されているテーブル内容を、
過去記事サンプルデータを html の Table 形式に展開等するツール を使うとテーブルに簡単に取り込めます。
また、クエリ部分の記述は、クエリのSQLビューにコピー&貼り付けすれば・・・・
関連記事

2011/07/31

Category: クエリ

TB: 0  /  CM: 0

top △

この記事に対するコメント

top △

コメントの投稿

Secret

top △

トラックバック

トラックバックURL
→http://kikutips.blog13.fc2.com/tb.php/96-32c46176
この記事にトラックバックする(FC2ブログユーザー)

top △


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。