ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

InfoPathとSQL Serverで「学校評価」の集計をする

leave a comment »

年度末に近づき、多くの学校では一年間のまとめをする時期になったと思う。本校でもこの一年の取り組みのまとめとして、学校評価を行うことになった。

本校の学校評価では、いくつかの大項目の下に、小項目として50弱の項目を目標として設定した。これらの小項目に対して、それぞれ教員が1から4までの評価を与えることにしている。このようなアンケート式のデータ集計は、InfoPathとSQL Serverが最も得意とするところである。しかしテーブル構造や集計については、少し工夫を要するところがある。

InfoPathフォームはSQL Serverのテーブルに対して1対1の対応が得意である。そこでやや強引ではあるが、テーブル構造として次のようなものを作成した。

———————-(ここからテーブル作成SQL)———————-

CREATE TABLE gakkohyoka_t_hyoka (
    [shokuinbango] [char] (6) ,
    [inputtime] [datetime] ,
    [01] [char] (2) ,
    [02] [char] (2) ,
    [03] [char] (2) ,
    [04] [char] (2) ,
    [05] [char] (2) ,
    [06] [char] (2) ,
    [07] [char] (2) ,
    [08] [char] (2) ,
    [09] [char] (2) ,
    [10] [char] (2) ,
    [11] [char] (2) ,
    [12] [char] (2) ,
    [13] [char] (2) ,
    [14] [char] (2) ,
    [15] [char] (2) ,
    [16] [char] (2) ,
    [17] [char] (2) ,
    [18] [char] (2) ,
    [19] [char] (2) ,
    [20] [char] (2) ,
    [21] [char] (2) ,
    [22] [char] (2) ,
    [23] [char] (2) ,
    [24] [char] (2) ,
    [25] [char] (2) ,
    [26] [char] (2) ,
    [27] [char] (2) ,
    [28] [char] (2) ,
    [29] [char] (2) ,
    [30] [char] (2) ,
    [31] [char] (2) ,
    [32] [char] (2) ,
    [33] [char] (2) ,
    [34] [char] (2) ,
    [35] [char] (2) ,
    [36] [char] (2) ,
    [37] [char] (2) ,
    [38] [char] (2) ,
    [39] [char] (2) ,
    [40] [char] (2) ,
    [41] [char] (2) ,
    [42] [char] (2) ,
    [43] [char] (2) ,
    [44] [char] (2) ,
    [45] [char] (2) ,
    [46] [char] (2) ,
    [47] [char] (2) ,
    [48] [char] (2) ,
    [49] [char] (2) ,
    [50] [char] (2) ,
    [hyokaiken] [varchar] (2000) ,
    [unneiiken] [varchar] (2000) 
)

———————-(ここまでテーブル作成SQL)———————-

フィールド[shokuinbango]に職員番号を生成しておき、[inputtime]にInfoPathフォームで入力したときの日付を入れる、フィールド[01]から[50]までにそれぞれの項目の1から4評価をストアし、最後の[hyokaiken]と[unneiiken]には自由記述で文を入力できるようにする。このようなテーブルにすると、InfoPathフォームは作りやすい。

しかし問題は集計の方法だ。これがExcelの表ならば、各列の最後にcountif()文を使って各評価の個数を集計するところだが、SQL Serverではそうはいかない。

そこで、まず、このテーブルの値を正規化するビューを作る。次のようなビューだ。

———————-(ここからビューのSQL)———————-

SELECT                  ’01’ AS 項目, [01] AS 評価, COUNT(01) AS 回答数
FROM                     gakkohyoka_t_hyoka
GROUP BY          [01]
UNION
SELECT                  ’02’ AS 項目, [02] AS 評価, COUNT(02) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_02
GROUP BY          [02]
UNION
SELECT                  ’03’ AS 項目, [03] AS 評価, COUNT(03) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_03
GROUP BY          [03]
UNION
SELECT                  ’04’ AS 項目, [04] AS 評価, COUNT(04) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_04
GROUP BY          [04]
UNION
SELECT                  ’05’ AS 項目, [05] AS 評価, COUNT(05) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_05
GROUP BY          [05]
UNION
SELECT                  ’06’ AS 項目, [06] AS 評価, COUNT(06) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_06
GROUP BY          [06]
UNION
SELECT                  ’07’ AS 項目, [07] AS 評価, COUNT(07) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_07
GROUP BY          [07]
UNION
SELECT                  ’08’ AS 項目, [08] AS 評価, COUNT(08) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_08
GROUP BY          [08]
UNION
SELECT                  ’09’ AS 項目, [09] AS 評価, COUNT(09) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_09
GROUP BY          [09]
UNION
SELECT                  ’10’ AS 項目, [10] AS 評価, COUNT(10) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_10
GROUP BY          [10]
UNION
SELECT                  ’11’ AS 項目, [11] AS 評価, COUNT(11) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_11
GROUP BY          [11]
UNION
SELECT                  ’12’ AS 項目, [12] AS 評価, COUNT(12) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_12
GROUP BY          [12]
UNION
SELECT                  ’13’ AS 項目, [13] AS 評価, COUNT(13) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_13
GROUP BY          [13]
UNION
SELECT                  ’14’ AS 項目, [14] AS 評価, COUNT(14) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_14
GROUP BY          [14]
UNION
SELECT                  ’15’ AS 項目, [15] AS 評価, COUNT(15) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_15
GROUP BY          [15]
UNION
SELECT                  ’16’ AS 項目, [16] AS 評価, COUNT(16) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_16
GROUP BY          [16]
UNION
SELECT                  ’17’ AS 項目, [17] AS 評価, COUNT(17) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_17
GROUP BY          [17]
UNION
SELECT                  ’18’ AS 項目, [18] AS 評価, COUNT(18) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_18
GROUP BY          [18]
UNION
SELECT                  ’19’ AS 項目, [19] AS 評価, COUNT(19) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_19
GROUP BY          [19]
UNION
SELECT                  ’20’ AS 項目, [20] AS 評価, COUNT(20) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_20
GROUP BY          [20]
UNION
SELECT                  ’21’ AS 項目, [21] AS 評価, COUNT(21) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_21
GROUP BY          [21]
UNION
SELECT                  ’22’ AS 項目, [22] AS 評価, COUNT(22) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_22
GROUP BY          [22]
UNION
SELECT                  ’23’ AS 項目, [23] AS 評価, COUNT(23) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_23
GROUP BY          [23]
UNION
SELECT                  ’24’ AS 項目, [24] AS 評価, COUNT(24) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_24
GROUP BY          [24]
UNION
SELECT                  ’25’ AS 項目, [25] AS 評価, COUNT(25) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_25
GROUP BY          [25]
UNION
SELECT                  ’26’ AS 項目, [26] AS 評価, COUNT(26) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_26
GROUP BY          [26]
UNION
SELECT                  ’27’ AS 項目, [27] AS 評価, COUNT(27) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_27
GROUP BY          [27]
UNION
SELECT                  ’28’ AS 項目, [28] AS 評価, COUNT(28) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_28
GROUP BY          [28]
UNION
SELECT                  ’29’ AS 項目, [29] AS 評価, COUNT(29) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_29
GROUP BY          [29]
UNION
SELECT                  ’30’ AS 項目, [30] AS 評価, COUNT(30) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_30
GROUP BY          [30]
UNION
SELECT                  ’31’ AS 項目, [31] AS 評価, COUNT(31) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_31
GROUP BY          [31]
UNION
SELECT                  ’32’ AS 項目, [32] AS 評価, COUNT(32) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_32
GROUP BY          [32]
UNION
SELECT                  ’33’ AS 項目, [33] AS 評価, COUNT(33) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_33
GROUP BY          [33]
UNION
SELECT                  ’34’ AS 項目, [34] AS 評価, COUNT(34) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_34
GROUP BY          [34]
UNION
SELECT                  ’35’ AS 項目, [35] AS 評価, COUNT(35) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_35
GROUP BY          [35]
UNION
SELECT                  ’36’ AS 項目, [36] AS 評価, COUNT(36) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_36
GROUP BY          [36]
UNION
SELECT                  ’37’ AS 項目, [37] AS 評価, COUNT(37) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_37
GROUP BY          [37]
UNION
SELECT                  ’38’ AS 項目, [38] AS 評価, COUNT(38) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_38
GROUP BY          [38]
UNION
SELECT                  ’39’ AS 項目, [39] AS 評価, COUNT(39) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_39
GROUP BY          [39]
UNION
SELECT                  ’40’ AS 項目, [40] AS 評価, COUNT(40) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_40
GROUP BY          [40]
UNION
SELECT                  ’41’ AS 項目, [41] AS 評価, COUNT(41) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_41
GROUP BY          [41]
UNION
SELECT                  ’42’ AS 項目, [42] AS 評価, COUNT(42) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_42
GROUP BY          [42]
UNION
SELECT                  ’43’ AS 項目, [43] AS 評価, COUNT(43) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_43
GROUP BY          [43]
UNION
SELECT                  ’44’ AS 項目, [44] AS 評価, COUNT(44) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_44
GROUP BY          [44]
UNION
SELECT                  ’45’ AS 項目, [45] AS 評価, COUNT(45) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_45
GROUP BY          [45]
UNION
SELECT                  ’46’ AS 項目, [46] AS 評価, COUNT(46) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_46
GROUP BY          [46]
UNION
SELECT                  ’47’ AS 項目, [47] AS 評価, COUNT(47) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_47
GROUP BY          [47]
UNION
SELECT                  ’48’ AS 項目, [48] AS 評価, COUNT(48) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_48
GROUP BY          [48]
UNION
SELECT                  ’49’ AS 項目, [49] AS 評価, COUNT(49) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_49
GROUP BY          [49]
UNION
SELECT                  ’50’ AS 項目, [50] AS 評価, COUNT(50) AS 回答数
FROM                     gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_50
GROUP BY          [50]

———————-(ここまでビューのSQL)———————-

ひたすら長いSQL文だが、構造は簡単なUNIONクエリの繰り返しなので、機械的に作ることができるはずだ。このSQL文によって、「項目」、「評価」、「回答数」というビューが得られる。結果はSQL Server Management Studioの編集画面を見てもらうと一目瞭然だろう。

学校評価SQL工夫_005_edit
<Fig.1 : UNIONクエリによるデータの途中集計>

この後はPIVOTによる集計を行う。クエリは以下のとおり。

———————-(ここからPIVOTのSQL)———————-

SELECT 項目
    ,SUM(CASE WHEN 評価 = ‘A’ THEN 回答数 ELSE 0 END) AS 評価A,
    ,SUM(CASE WHEN 評価 = ‘B’ THEN 回答数 ELSE 0 END) AS 評価B,
    ,SUM(CASE WHEN 評価 = ‘C’ THEN 回答数 ELSE 0 END) AS 評価C,
    ,SUM(CASE WHEN 評価 = ‘D’ THEN 回答数 ELSE 0 END) AS 評価D
FROM v_gakkohyoka_shu
GROUP BY 項目

———————-(ここまでPIVOTのSQL)———————-

これもSQL Server Management Studioの編集画面を見てもらうと直感的にわかるだろう。

学校評価SQL工夫_006_edit
<Fig.2 : PIVOT集計のビュー>

あとはこのビューに対して、画面に表示する単純なInfoPathフォームを作るだけだ。こうしておけば、アンケートの入力が終わった時点で、即集計結果を出すことができる。

SQL ServerのようなデータベースとExcelを単純に比べることは意味がないが、このような集計をするにはExcelは得意であるが、SQL Serverでやろうとすると手間がかかることは間違いない。しかし一度作っておけば、毎年同じ処理をこのテーブルとビューでできるので、省力化できる。項目がいくら増えても、選択肢が増えても、職員数が変わっても、ほとんど変更することなく集計ができる。

Written by Yoshio Matsumoto

2011年2月13日 @ 11:53 PM

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。