はじめに
あるログを記録するデータベースがあるとします。そのデータベースには、”X年X月X日のY時Y分Y秒にzzzzzzといった警告が発生しました”といった情報が刻々と記録されているとします。
そのデータを”X年X月X日のY時に何件警告が発生”といった具合に集計する場合、Sharperlightでは、どのようにクエリ定義を行えばいいのかをこの記事では考察していきたいと思います。
テストデータの準備
Sharperlightのインストールされている環境では、必ずSharperlightが使用するシステムデータベースが、Micorosoft SQLデータベースサーバーにが準備されています。
今回使用するテストデータは、そのMicorosoft SQLデータベースサーバーに作成します。そのためには、Sharperlightのアプリケーション群のひとつ、”マテリアライズドクエリ”を利用します。
”マテリアライズドクエリ”は、Sharperlightのシステムデータベースがあるデータベースサーバーにデータベースを作成し、その中にテーブルを設計作成するツールです。Sharperlightクエリビルダを使用してテーブルの設計が行え、データを書き込むことも可能です。作業用データベースを作成するにはとても便利なアプリケーションです。
作成に関しては別の記事で紹介するとして、今回は、予め作成したものを使用します。
MQ44724というコードを付与されたマテリアライズドクエリデータベースと今回使用するテーブルの定義があります。
このテーブルには、ID列とDate Time列 (もう一つありますが今回は無視してください）があります。
注目するのはDate Time列で、データ型にDateTime型が指定されています。よってタイムスタンプ値は、SQLサーバーのdatetime型で保存されています。
以上が、今回使用するテストデータです。
集計クエリの作成
Sharperlightの強力なクエリ作成支援ツール”クエリビルダ”を使用します。
- 製品には、今回使用するマテリアライズドクエリデータベースを選択します。コードはMQ44724でしたが、実名は”サポートMQ”になっています
- 選択した製品内の対象テーブルを指定します。テーブルを指定すると、付随する列一覧が選択領域に表示されます
出力列の選択
選択領域のID列とDate Time列をそれぞれダブルクリックで出力領域に設定します。
これだけで、プレビューボタンを押すと、SELECT文が動的に作成および発行されてデータが戻ってきます。
集計オプション カウント
データの集計を行うにあたり、モードを”概要レポート”に設定します。これによりCOUNTやSUM等が使用できるようになります。
出力領域に設定したID列のオプションを見てみます。ダブルクリックでオプションダイアログが開きます。
関数タブで、カウントを選択します。
ID列の属性にCountが付与されます。
これにより、COUNT(ID列)、GROUP By Date Time列という意味になります。
結果を見てみます。
カウントはされていますが、秒単位で集計されており今回の目的には適していませんね。
集計オプション 書式設定
Date Time列のタイムスタンプ値を”yyyy-MM-dd HH”という形式にします。
Date Time列オプションの日付書式設定で行うことが可能です。
表示するだけであれば、このように大丈夫なのですが、集計目的では、上手くいっていませんね。もう少し手の込んだ設定が必要です。
集計オプション データセットの準備
拡張表現を使用した書式設定
そこで、先ず拡張表現を利用してタイムスタンプ値を”yyyy-MM-dd HH”という形式にします。下記の式を記述し、データ型をTextに設定します。
列の名称を、今回は”ログ日時”とします。
先に設定したDate Time列の書式設定と、ID列のカウントオプションを外します。
さらに、Date Time列を非表示にします。
プレビューで結果をみると、タイムスタンプ値は正しい形式で、ID列の値は集計値ではなくIDそのものが表示されてます。つまり生のデータがそのまま表示されています。
このクエリ結果を元に集計ができれば、今回の目的が達成できそうですね。
つまり、このクエリ結果データセットをクエリの対象として集計を行います。
それを実現する方法をこれから紹介しますね。
カスケードクエリ
このオプションは、先に定義したクエリで返されるデータセットを、別のクエリのクエリ対象として使用する機能を提供します。カスケードクエリを定義することで、実行時は一連の処理が背景で行われ、カスケードクエリの結果が最終的なクエリ結果となります。
では早速、カスケードクエリを定義します。
出力領域で右クリックメニューを開きます。
クエリビルダが立ち上がります。選択領域には親クエリの出力列が表示されています。これは、このカスケードクエリが親クエリの結果をデータソースとして使用することを意味しています。
ログ日時列とID列を出力領域に設定します。
ID列の関数-集計オプションをカウントにします。
プレビューで結果をみてみます。思い通りの集計がされていますね。
OKボタンで保存して親クエリに戻ります。カスケードというボタンが親クエリ定義上に表示されます。
このボタンで、カスケードクエリの定義を再編集できます。
さて、最終結果の確認ですが、親クエリをプレビューしても、カスケードクエリの結果は表示できません。親クエリのクエリ結果が表示されるだけです。
例えば、このクエリがWEBレポート（パブリッシュドクエリ）のクエリとして使用されれば、求める結果はこのようにブラウザに表示されます。
グラフとして表示すると。。
Excelアドインを使用したテーブル式のクエリとして使用した場合もこのようになります。
あとがき
ちょっと手が込んだかもしれませんが、元のデータの加工等が不要なわりには簡単に結果を求めることができたのではないでしょうか？