クロス集計クエリー

最近では、アクセス以外のデータベースでも一応は対応出来ているようだ。
ただ、コーディングが綺麗に見えないのは仕方ないのか？

この機能は、MSがどっかから買い取ったとかその当時言われていたのを思い出した。
そうだ、それを聞いた私はどんだけのものか？と興味を持って調べて覚えたものだった。

どっちがその技術を応用した物かは分かっていないが、ピボットテーブルも同じ技術的背景を持っていて作られている様だ。
イスラエルのソフト屋からだったか、技術買収してそれを応用したものだったと記憶している。15年以上前の話だからか？ソースは出てこなかった。

ピボットテーブルは制限なしでフィールドやレコード・データを扱えるのに対して、クロス集計クエリーはそれぞれ、３つ、１つ・１つという制限がある。これって差が大きすぎだ。

作った経験から言うと、出力するレコードのデータを考えて作っていないと、どちらも分かりにくい表になってしまう。

私の作ったプログラムでは、両方を採用している。又、どちらの長所も取り入れた作り方にしている。
その前処理と後処理を高速化して前述の制限も何のそので、上手く使えていると思う。見栄えも、最終的な出力でもないので、クロス集計クエリーでの見栄えのいい作表が苦手という点も気にならない。速度的にも、他のSQLでの実行に比べても、この部分に関しては遜色ないと思う。

大雑把に流れを整理すると、テキストファイル　ー＞　削除クエリー・追加クエリー　ー＞　テーブル　ー＞　不一致クエリー

テーブル　ー＞　クロス集計クエリー　ー＞　削除クエリー・追加クエリー　ー＞　テーブル　ー＞　クエリー　ー＞　テーブル（ライン・製品毎）

と、最後のテーブルが、原料・製品毎の集計した別バージョンも作って、それぞれをピボットテーブルで参照している形だ。
途中に桁やデータ型の変換や正規化などやったりして細かい処理が幾重にも重なってされているが、こちらは高速化の為だけに考えられたやり方なので、大筋には関係ない話になる。

アクセスの速度は、遅いように思われがちだが、開発スピードや最適化を上手くやれば結構使える物に仕上がる様だ。
PC単体での使用がほとんどだが、ODBCを活用するとネットワーク越しでもデータの再利用が可能になるので、SQLサーバなどにアップグレードしなくても使えている。今の所はデータ容量を見ても限界になったりすることも無いと言いきれるので、当分は現状維持になりそうだ。

クエリー（SQLを発行した後のデータの形として捉えてみる）

目次を、予告も兼ねてここで書いておこう
前回　テーブルをいかに作るか考える
今回　クエリー（SQLを発行した後のデータの形として捉えてみる）
次回　フォーム（入出力を画面などに行える、その場合のデータの取扱いや持ち方などについて）

それ以降　
　　　レポート（主に作表した結果を印刷するのに用いる）
　　　マクロ（アプリケーション内蔵の機能を呼び出すものがほとんど、アプリケーションを熟知すると自然に使えるようになってゆく。一部マクロ独自の機能もある）

　　　モジュール（複雑な条件分岐や、詳細な記述、変数など特殊なデータを扱いたい場合に向く、細かいエラーの出力を隠したりしたい時に扱いが楽になる。全体的にマクロを使うより更に高速に動作することも期待出来る。VBAは、イベントドリブン方式で書かれるので、タイミングに気をつけないと動きがつかみにくい点だけが困る。）

ここまで書くと、いかにもアクセス限定での話に見られがちだが、まだまだその種類は問わない。分かりやすいので、アクセスのデーターベースファイルの内容に合わせて説明したい。
データベース管理システム（DBMS）の仕様によっては、派手な作表を苦手としたり独自のマクロやモジュールを持たないSQL言語のみのアプリケーションもある。それら動作部分は別の所で説明されているものを参照されたし。

クエリーの種類には、色々有るがデータの動き・流れで分けると
・クエリー（通常の動きとしては止まっているが、参照データの更新やファイルオープンのタイミングで連想ゲームや表計算のセルの内容の様に、関係する先のデータが自動で更新される）
・追加クエリー
・削除クエリー
・更新クエリー
・テーブル作成クエリー
と５つに分けられる。

形で言えば、
・フィルターなどもその意味合いからは含まれる選択クエリー
・集計クエリー（同一データを集計して一つのデータに変更するクエリー）
・重複クエリー（データが重複しているかチェックしてその重複を抽出するクエリー）
・不一致クエリー（単純なパターンとして言えば、テーブルAとテーブルBの不一致データを抽出するクエリー、「AにあってBにない。」「BにあってAにない。」の2通りがある。）

・パラメータクエリー（動的に、実行と同時にボックス表示してそこへ入力されたデータ変更によって、抽出するクエリー）
・クロス集計クエリー（縦横で集計する、特殊なクエリー）
・ユニオンクエリー（複数のテーブルやクエリーの集合を一本化するクエリー、各テーブルなどのデータ型が揃っているとやりやすい。一方、元データをクエリー・テーブルと異なったものにすると、クエリーの持つ大雑把なデータ型合わせの必要が出てくる。そうでないとエラーや桁落ちが発生してしまう。例に上げているプログラムの一部クエリーでもクエリー・テーブルの2種類を対象にしたものが有ったので、その時はデータ型の一致に苦労したものだ）

中でも、このフィルターやソート・選択クエリーは各SQL言語の間でもそれほどコーディング量や方言などの差が見られない機能であり、速度やその他の機能との組み合わせの自由度だけに差がある。

重複クエリー、不一致クエリーはマスターデータを作成中には必要不可欠なクエリーだが、一度データが固定してしまうと使わないかも知れないクエリーだった。最短では、マスター作成時に一度は全件手入力必要が有ったりして、手入力では防止し難い二重登録してしまうなどのミスを防ぐのに役立つ。そんな場合に最低１回だけは、重複クエリーを実行する必要がある。その後は、テーブルの該当キーをユニーク指定にして追加時にエラーではねる処理で十分だ。定期的にデータのチェックをしたりで毎回処理を正確にするデータ間の整合性を保つのには重要な事だ。

では、クエリーの説明を例によって私が作成をしたプログラムを参考に似たような例を使ってしたい。テキストデータのインポートを削除クエリー、追加クエリーとやる必要があるが、これをマクロでそれぞれのクエリーを呼んでやってたら、当時は僅かに時間的がかかる処理だった。それらをモジュール変換をしてそれぞれSQLコールという形で実行した。通常マクロでの追加時にインポートエラーが有ると表示され、処理がストップするが、その表示なしで、連続処理させる事も出来た。

実際のデータ項目としては、ページ番号、短縮製品名、入り目（容量と小分包の数）、生産量合計、１から３１日の日々の生産数量、備考欄だ。

不要なデータは、ほとんど入ってこないように厳選しているのだがそれでもインポート時に残っているエラーを発生される原因のデータがある。それはインポートエラーで別にチェック出来るようにしている。（不具合発生時しか見ることも無いのだが）
インポート時には、テキスト（CHARタイプ）で取り込んで、その後に数値型変換するのとインポートエラーも減らす事が出来るようだ。これは、2クッションでデータを扱って変換するので、エラーデータを能動的に外せるのが理由。
インポート時に、桁数で取り込む固定位置データ入力方法と区切り文字で入れる方法がある。元のデータに区切り文字が混入したりするとデータの位置が不安定でおかしな場所になる。こういった区切り文字の選定をする場合は、入力データもそれに合わせる必要がある。

他にもメタ文字など、データの中にあったらばデータベースの動作時に不具合発生の原因となる文字が多々ある。それらをデータベースに入力してから削除したりするよりも、前処理としてテキストエディターのMIFESなどで一括して変更をかけておくと扱い易くなる。
エクセルからテキストデータが出された時には、タブ区切りが標準だったと思うが、タブを使うとエクセルのセルにはタブを入力するのが逆に困難なのでうまくいくようだった。
このプログラムでの禁止事項は、当然だが暦上にない日付けを入れてしまうと後で使用する追加プログラムでエラーを吐くことになる。例えば、2月31日とかだ（当然、閏年でも２９日しかない）。これをやると、後で月日を日のデータから興しているので、不具合になる。
書くのも何度目かになる所だが、全部で３１項目を日付で持っていないと、これもデータ項目のズレが発生するので、無い日付は元の予定表の上では該当セルの表示をしないか、表示0桁に変更という設定で逃げてもらうことにしている。
テキストファイルの連結した後にページの数値のコピーを全レコードの最初の桁に見つかった数値を使っている関係で、製品名の最初に数値を使ってしまうデータの不具合も有った、メモ書きなどを無駄にエクセルの表に入れてしまっているとこんな症状も発生した。こちらは、データベースよりもMIFES側でのMIL言語のプログラムの内容に合っていなかっただけで、単純にコピーと貼り付けをしている関係でこういったことも発生したのだ。この処理は、他の変更の処理とほぼ同時のタイミングで実行されている。表示を消してしまって実感が沸かないのが残念だが、文字数にして２万文字中の４００文字ほど、種類にして20文字ほどの変換を正確に一瞬でやってしまっているMIFESは凄い。
デバック中に処理結果を一々表示して、処理も一行づつ実行していたらかなりの時間を要す。作ったものにしか解らない凄さなのだが。。。
MIL言語での変換や計算を通して、かなりデータベースの様式になった。クエリーの説明のつもりが、その前段階のテキストデータの変換の話に変わって話が逸れてしまっている。話を元に戻す。

こういった前処理を済ませた事も手伝って、無事にインポートされたデータ。これだけでは元のデータを作業者などの人に見難くしただけのデータになっている。でも、データベースのデータとしては不足ない元データになっている。
このインポートデータのデータを、更に製品名のマスターと照合させて不一致が有ると、不一致リストとして作表するようにする。
毎回、たった数行の不一致データを印刷するのはもったいないし、修正後は不要になる。
場合によっては修正履歴を残しておく必要も発生するだろう。だが毎回必ず不一致の確認をする事になっているので、今の運用上は修正履歴として出していない。工夫した点は、レポート機能で作表した結果をプレビューで見るように、メニューのボタンにそのモジュールの割り付けをした。マクロの表示の種類にレポートのプレビューというのがあるので、それを使ってマクロ作成後にモジュール変換して取り込んだら作るのも早かった。一般的にやられているフォームをあちこちいじって表示させるよりも、シンプルになって見易かったのを良しとした。

短縮製品名の不一致があると、マスターの修正、追加、削除のパターンと元データの変更という２パターンがあるが、数件の不一致で変更も覚えておく必要もないし、次回に入力に気を付けることで対応可能ならば、中間データを生で修正かけて、一致させる方法も考えられるので、そういうパターンにも対応してる。

一件でも製品名マスターとの不一致があると、他のマスターにも不一致が発生する場合が多いので、他の関係のあるマスターとの間で照合をかける。
他のマスターの間でも不一致がなくなれば、それ以降の処理を継続する。
処理時間や処理内容で考えると、インポートで処理内容全体から見て1割ぐらいの処理が終わる。
不一致データの確認は作業者の担当だが、そこで8割以上の時間を使って、確認や修正作業の手動操作が入っているが、データーベースファイルの見えてない部分では、該当するクエリーが複数、インポート作業やら、確認作業などで手動修正されたタイミングにだけ該当テーブルの更新と関係のあるクエリーの更新がかかる。見た目では、処理内容の割に高速で処理出来ている。何度も書いたが処理の最適化の為のチューニングを済ませているのと、更新の設定回数を1回のみ、クエリーの処理の為の準備する容量の最適値にアクセスインストール後の初期設定から変更をかけているからだ。
このデータベースの実行時の使用容量を最適化する作業は、やっていないと無駄にメモリーやHDDの容量を使ってしまい、効率が悪くなる。
その後の処理は、データベースファイルを閉める直前のタイミングを狙って、一瞬で全体の処理内容の３割ぐらいの処理をして、更に、次回使用時の為にデータベースファイルの最適化した後に、ピボットテーブルを有するエクセルファイルを開く処理につなげる。

ここまでの説明で出てきたクエリーの種類（自動実行を除いた物）

・削除クエリー
・追加クエリー
・不一致クエリー

補足説明：
追加・削除クエリーの２つをまとめてテーブル作成クエリーに置き換える事も可能だが、不測のエラー出力時に実行を止めてしまう割合が高いので、データを削除して追加というやり方にしている。テーブル作成クエリーのもう一つの難点は、出力されたテーブルのデータ型が大雑把な事もある。
別の使われ方での説明すると、自動作番されたIDなどで、削除追加をやると一度削除したIDは、運用上は再発行出来なくなってしまうので、IDがらみの処理にはその都度、向いているのか判断しないと使えない。
又、テーブル作成クエリーは後述もしているが、クエリーのデータ型が標準では余裕を持ちすぎていたので、オプションの変更をかけて、できる限り少ない容量に変更して必要量を確保するようにしたりした。一度テーブル作成クエリーで様子を見てテーブルを作って、そのテーブルの項目のデータ型などを最適化したものに、削除・追加クエリーでデータを扱ったりするのもデータベースの速く作成するのには役立った。同時に、クエリーの処理内容レポート作成機能も活用した。当時はデータベースファイルの内容のレポートを全て一度に出力すると長時間かかってしまったのが残念であった。今は、それほど時間も取られないのが助かっている。

オリジナルアイデアと思う機能：
途中、便利に使えたのはデータが1項目のテーブルで、１から３１までの連番のものだ。（日付け？）他に一項目一行のデータも作ってみたが、こちらも同様に良かった。
これを挟むと、SQLで複数条件を記述して実行するパターンよりもかなり高速にSQLが戻ってきて感動した覚えが有る。
この機能は、どのSQLでも同じだと私は思っている便利なSQL機能だ。
どうしても高速化したいが、条件が複雑になったのが原因で高速化が望めない場合の苦肉の作として覚えておくと良いと思う。
ネタばれだが、どうやらSQLコマンドの　読み込み時間　＋　構文エラーのチェック　＋　実行の為の準備（メモリーや計算領域の初期化や確保）　＋　もろもろの処理　よりも、一度読み込んでしまってからのデータの処理に最適化されるどのSQLにもこんな感触があるので、こういった高速化が可能になる様だ。
もう一つついでに、人が見てデータが大量になってしまって一見処理が遅くなるだろうと考えられる抽出なしのクエリーの結果、データベースの操作で行える関係で説明すると直積だが、それを一度テーブルに書き出して、単純にフィルタをかけるだけの処理でつなぐと案外高速化する。（直積は、基本中の基本のようでデータベースの教科書の最初のあたりには出ていたなぁ。）
これは、一度に複雑な抽出クエリーを使うとその処理の中、データがある一定容量を越した瞬間、鈍いクエリーに変身してしまっている弊害があるからだろう。
早い話、メモリーに収まりきれずにスワップファイルに書き込み、読み込みを繰り返すわけだ。それより、一度書き出しだけを行ってその結果を抽出かける処理のフィルタや抽出クエリーで再使用した方が理屈が解れば速くなるわけだ。
無駄にそんな高速処理化を続けていた結果、出来上がったデータベースプログラムを見てみると、今では一瞬で処理が終わるデータベースのプログラムに大化けしてしまっている。
当時解ったのはテーブルの容量の足し算で処理時間が伸びるのではなく、掛け算の様に指数的に増加してしまうのは、この容量分をスワップファイルに移さないと無理だったからだと思う。
ただ、今の一般的PCが持っている性能からすると無駄な悪あがきになるだろうと思う。最適化しなくても、数秒で終わるであろう処理全体の時間を1秒以内にしてもさほど効果的とも言えないからだ。
今、このプログラムを動かしているPC、ＣＰＵはi5で２Gのメモリーだ。プログラムの必要な容量からすると十分過ぎる性能になっているようだ。

ちょっと解り辛い説明にしてしまったが、まとめると作業時間＋処理時間はほぼ8割以上が人手による確認作業の時間になる。
これは、パッと不一致ファイルの出力結果を見ている時間を指している。見ているだけのようだが、確認するまでに
比較的得意分野になる見栄えの良いレポートなどを作成出来るアクセスでの処理時間　＋　それを人が確認する時間　
は、通常のSQLのコマンドの処理をしているデータベースの処理時間に比べてかなり長い時間になっている。
プログラムの運用上、ほとんどの処理はデータ修正が入らないので、パッと見てOKとなっている。これは速いようにも思うが、そのパッと見るまでに8割以上の時間が経ってしまうのだ。
逆に複数の修正箇所があるようだと、印刷作業に移り、確実に修正を行えるようメニューに印刷ボタンを割り付けている。
不一致クエリーリストのレポートのプレビューを、モジュールから開いているので、通常の操作のように印刷もそこから続けて出来そうなものなのだが、プログラム編集作業中とは違って印刷ボタンが出ていなかったので、印刷はメインメニューに別に割り当てる事になった。これはアクセスの仕様なのだろう。最新版ではその辺も変わっている事も希望的観測で考えられる。
まとめると、ちょっとでもデータの修正が入れば、全体の作業時間　＋　プログラム処理時間のほぼ１００％が人手のデータ修正作業の時間になってしまう。時間がかかると言っても、通常業務の休憩時間にでも出来るほどの短時間で終わっている。

ここまで書いて全体にまとまり見られないのは、私の思い出すままに書き出しているからであって、個人個人記憶の場所や関連性に差があるのでお許し願いたい。関連があるから、記憶が蘇るのであって、逆に記憶する場合にも関係ある物事だと強く記憶されるものだとポジティブに捉えてもらえば幸いである。