FC2ブログ

スポンサーサイト 


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

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

クロス集計での(平均)行付加方法の模索 


旧記事掲載:2011/01/20

クロス集計で、横方向の「合計」や「平均」の列を追加することは簡単ですが、
縦方向の「合計」「平均」の行を追加するには?
「合計」の例は Web 上で検索できましたが、「平均」の例は探せませんでした。
(検索は下手です)

テーブルの内容は、以下のようになっているとします。
an:オートナンバ 
番号:数値型でもテキスト型でも 2007確認時は数値型
点数:数値型(長整数でも整数でも) 2007確認時は整数

テーブル「TBL_A」
an番号教科点数
1111英語10
2222国語30
3222数学50
4111国語70
5333数学90
6222英語80
7111数学60
8333英語40
9333国語20

このテーブルを元にクロス集計で

単なるクロス集計+平均行
番号合計英語国語数学
111140107060
222160803050
333150402090
番号合計英語国語数学
111140107060
222160803050
333150402090
平均150434066

としたいとします。


前提条件として
・テーブル名は「TBL_A」
・「番号」と「教科」の組み合わせで重複がない。
  (例えば、番号「111」で 教科「英語」は1件だけ。他も同様に)

クエリ1つで行う方法を考えてみました


テーブル「TBL_A」を
S番号教科点数
0111英語10
222国語30
222数学50
111国語70
333数学90
222英語80
111数学60
333英語40
333国語20
+
S番号教科点数
1平均英語平均値
国語平均値
数学平均値

構成にしてからクロス集計させます。

クエリのSQLビューは以下の通り。
TRANSFORM Int(First(点数)) AS 点数の値
SELECT 番号, Int(Sum(点数)) AS 合計
FROM (
SELECT 0 AS S, 番号, 教科, 点数 FROM TBL_A
UNION ALL
SELECT 1, '平均' , 教科, Avg(点数) FROM TBL_A GROUP BY 教科
) AS Q1
GROUP BY S, 番号
PIVOT 教科;


付加しているフィールド「S」は、0 or 1 にて、
UNION で追加した「平均」を別扱いにして最後になるようにしている
だけです。
(これをしておかないと、元々のテーブルの番号がテキスト型で、
たいらひとしさん(平均)の名前があった場合、別扱いする必要があるし・・・)
上記クエリの実行結果は以下になります。
番号合計英語国語数学
111140107060
222160803050
333150402090
平均150434066

平均の行は、小数点以下は切り捨てしていますが、切り捨てる場所を注意します。
以下のSQLビューでは、各教科ごとの平均を求めた時点で切り捨てを行っています。
TRANSFORM First(点数) AS 点数の値
SELECT 番号, Sum(点数) AS 合計
FROM (
SELECT 0 AS S, 番号, 教科, 点数 FROM TBL_A
UNION ALL
SELECT 1, '平均' , 教科, Int(Avg(点数)) FROM TBL_A GROUP BY 教科
) AS Q1
GROUP BY S, 番号
PIVOT 教科;


 
番号合計英語国語数学
111140107060
222160803050
333150402090
平均149434066

この時、平均の合計部分は狂ってくることが多そうです。
平均の合計部分は、各教科の平均を Sum しているので・・・

それゆえ、最後の最後で、小数点以下切り捨てします。
それでも、平均の合計部分は狂うかも・・・しれません。

表示を小数点以下2桁(3桁目を四捨五入)表示にする時には、
Int(x) 部分を、Round(x,2) に変更します。
TRANSFORM Round(First(点数),2) AS 点数の値
SELECT 番号, Round(Sum(点数),2) AS 合計
FROM (
SELECT 0 AS S, 番号, 教科, 点数 FROM TBL_A
UNION ALL
SELECT 1, '平均' , 教科, Avg(点数) FROM TBL_A GROUP BY 教科
) AS Q1
GROUP BY S, 番号
PIVOT 教科;


 
番号合計英語国語数学
111140107060
222160803050
333150402090
平均15043.334066.67

前提条件で書いていませんでしたが、データが欠落している時、
例えば、番号「333」の教科「国語」のデータがなかった場合、
以下のような結果となり、平均の合計は狂ってきます。
平均の合計部分は、各教科の平均を Sum しているので・・・
(本来は、143.33 になるはず)

番号合計英語国語数学
111140107060
222160803050
33313040 90
平均16043.335066.67

このデータの持ち方では、これが限度でしょうか?


合計の列がなければ、もう少し簡単になるんでしょうか


以下のような表示にしようとした時、データの持ち方も変えてみたいと思います。
番号英語国語数学
111107060
222803050
333402090
平均434066

どのようなデータにしてやるか考えると、以下のような感じになるんでしょうか。
S番号教科点数
0111英語10
222国語30
222数学50
111国語70
333数学90
222英語80
111数学60
333英語40
333国語20
+
S番号教科点数
1平均英語10
国語30
数学50
国語70
数学90
英語80
数学60
英語40
国語20

このデータを構成しつつ、クロス集計するには、
TRANSFORM IIF(S=0, First(点数), Int(Avg(点数))) AS 点数の値
SELECT 番号
FROM (
SELECT 0 AS S, 番号, 教科, 点数 FROM TBL_A
UNION ALL
SELECT 1, '平均', 教科, 点数 FROM TBL_A
) AS Q1
GROUP BY S, 番号
PIVOT 教科;


「S」が、0 の時は先頭の値で、1 の時には平均値を整数化

でも、合計列が欲しいとなると、記事先頭で記述した方法以外を考えてみます。
上記のデータ構成をベースに、データが欠落している時も考えて、
平均の合計部分は、(全体の合計)/(重複を除いた番号の数)を整数化
つまり、自分で Avg() の処理をするようにしてみたら・・・

S=0 の場合は、Sum(点数)
でない時には、Sum(点数) / 3

SELECT 番号

↓のところを

SELECT 番号, IIF(S=0, Sum(点数), Int(Sum(点数)/3)) AS 合計

に変更してみると
番号合計英語国語数学
111140107060
222160803050
333150402090
平均150434066

データが欠落していた場合は、
番号合計英語国語数学
111140107060
222160803050
33313040 90
平均143435066

なんか良さそうなんだけど、じゃ、この 3 をどこから持ってこようか???

3 を求めるのは、

SELECT Count(*) AS CT FROM (SELECT DISTINCT 番号 FROM TBL_A)


で良いような気がするけど、これをそのままクロス集計での 3 と置き換えると、
サブクエリでは、マルチレベル GROUP BY 句は使用できません」と怒られるし・・・

クロス集計の SELECT 部分で参照できるところに、3 を埋め込んでおければ・・・
平均用に追加した「番号」部分に 3 を入れておけばいいんじゃ???

「平均」っていう言葉は、SELECT 番号 の番号をいじれば良いか・・・
S番号教科点数
0111英語10
222国語30
222数学50
111国語70
333数学90
222英語80
111数学60
333英語40
333国語20
+
S番号教科点数
13英語10
国語30
数学50
国語70
数学90
英語80
数学60
英語40
国語20

っていう構成にしておいて、
TRANSFORM IIF(S=0, First(点数), Int(Avg(点数))) AS 点数の値
SELECT IIF(S=0, Q1.番号, '平均') AS 番号, IIF(S=0, Sum(点数), Int(Sum(点数)/Q1.番号)) AS 合計
FROM (
SELECT 0 AS S, 番号, 教科, 点数 FROM TBL_A
UNION ALL
SELECT 1, T1.CT, TBL_A.教科, TBL_A.点数 FROM TBL_A,
(SELECT Count(*) AS CT FROM (SELECT DISTINCT 番号 FROM TBL_A)) AS T1
) AS Q1
GROUP BY S, 番号
PIVOT 教科;


でやると、データが欠落していた時の結果は、
番号合計英語国語数学
111140107060
222160803050
33313040 90
平均143435066

また、データが欠落しているところを 0 と解釈させた平均値を求めるには、

TRANSFORM IIF(S=0, First(点数), Int(Avg(点数))) AS 点数の値

↓以下に変更すると出来るようです。

TRANSFORM IIF(S=0, First(点数), Int(Sum(点数)/Q1.番号)) AS 点数の値


番号合計英語国語数学
111140107060
222160803050
33313040 90
平均143433366

いやいや、3 は「S」に持たせてやると、もう少しすっきりしそう。

S番号教科点数
0111英語10
222国語30
222数学50
111国語70
333数学90
222英語80
111数学60
333英語40
333国語20
+
S番号教科点数
3平均英語10
国語30
数学50
国語70
数学90
英語80
数学60
英語40
国語20

っていう構成にしておいて、
TRANSFORM IIF(S=0, First(点数), Int(Avg(点数))) AS 点数の値
SELECT 番号, IIF(S=0, Sum(点数), Int(Sum(点数)/S)) AS 合計
FROM (
SELECT 0 AS S, 番号, 教科, 点数 FROM TBL_A
UNION ALL
SELECT T1.CT, '平均', TBL_A.教科, TBL_A.点数 FROM TBL_A,
(SELECT Count(*) AS CT FROM (SELECT DISTINCT 番号 FROM TBL_A)) AS T1
) AS Q1
GROUP BY S, 番号
PIVOT 教科;


 
 
やってみて、できないことはないけど、めんどくさいですね。
メンテナンスが大変そう。(一見して何をやっているか時間経つと忘れちゃいそう・・・)
また、処理性能はどうなんだろう??


クエリ2つで行う方法を考えてみました


基本的なクロス集計クエリを「Q_TBL_A」で作っておきます。
TRANSFORM First(点数) AS 点数の値
SELECT 番号, Sum(点数) AS 合計
FROM TBL_A
GROUP BY 番号
PIVOT 教科;


上記の結果は、
番号合計英語国語数学
111140107060
222160803050
333150402090


クエリ「Q_TBL_A」をもとに平均部分を UNION ALL でくっつけます。
SELECT 番号, 合計, 英語, 国語, 数学 FROM Q_TBL_A
UNION ALL
SELECT '平均', Int(Avg(合計)), Int(Avg(英語)), Int(Avg(国語)), Int(Avg(数学)) FROM Q_TBL_A;


 
番号合計英語国語数学
111140107060
222160803050
333150402090
平均150434066

小数点以下2桁まで表示するのなら
SELECT 番号, 合計, 英語, 国語, 数学 FROM Q_TBL_A
UNION ALL
SELECT '平均', Round(Avg(合計),2), Round(Avg(英語),2), Round(Avg(国語),2), Round(Avg(数学),2) FROM Q_TBL_A;


 
番号合計英語国語数学
111140107060
222160803050
333150402090
平均15043.334066.67

データが欠落している時でも、平均の合計は狂うことがありません。
番号合計英語国語数学
111140107060
222160803050
33313040 90
平均143.3343.335066.67

また、国語の平均が (70+30)/2 ではなく、(70+30+(0))/3 にしたい時には、
SELECT 番号, 合計, 英語, 国語, 数学 FROM Q_TBL_A
UNION ALL
SELECT '平均', Round(Avg(Nz(合計)),2), Round(Avg(Nz(英語)),2), Round(Avg(Nz(国語)),2), Round(Avg(Nz(数学)),2) FROM Q_TBL_A;


とすると
番号合計英語国語数学
111140107060
222160803050
33313040 90
平均143.3343.3333.3366.67

また、合計の大きい順にするなら
SELECT 番号, 合計, 英語, 国語, 数学 FROM (
SELECT 0 AS S, 番号, 合計, 英語, 国語, 数学 FROM Q_TBL_A
UNION ALL
SELECT 1, '平均', Round(Avg(Nz(合計)),2), Round(Avg(Nz(英語)),2), Round(Avg(Nz(国語)),2), Round(Avg(Nz(数学)),2) FROM Q_TBL_A
) AS Q1
ORDER BY S, 合計 DESC;


とすると、
番号合計英語国語数学
222160803050
111140107060
33313040 90
平均143.3343.3333.3366.67


データ量や、テーブル「TBL_A」の内容/条件によって、どれが良い・・・になると思いますが、

クエリ1つの場合は、教科に「化学」「物理」を追加してもクエリに変更はいらないし・・・
クエリ2つの場合のソート指定も捨てがたいし・・・

クエリ2つの方が良いんでしょうか??


もっと簡単にできるよ・・・  教えてください。
関連記事

2011/05/04

Category: クエリ

TB: 0  /  CM: 0

top △

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

top △

コメントの投稿

Secret

top △

トラックバック

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

top △


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