yumi-ii: 2009-09-06 SQLite3のデータベースをExcelのピボットテーブルで分析する

2009-09-06 SQLite3のデータベースをExcelのピボットテーブルで分析する

SQLite3の日付をExcelの日付に変換し，ピボットテーブルで日付をグループ化することに成功しました。

Excelのピボットテーブルはとても便利な機能なのですが，使っている人はあまりいないのかもしれません。さらに，SQLite3から取り込んだデータをピボットテーブルで解析したことのある人は，皆無なのかもしれません。この文章は，そんな一握りの人たちのための情報です。

＜画像の説明＞今回のお題です。SQLite3のデータベースをExcelのピボットテーブルで分析します。

日付の扱いで悩む

私は，SQLite3のデータベースをExcelのピボットテーブルで分析することがよくあります。

悩みの種は，日付の扱いでした。ピボットテーブルの外部データソースをSQLite3にすると，取り込まれたデータは数値あるいは文字列として扱われます。日付も文字列として扱われてしまいます。

この動作の何が問題なのかというと，フィールドをグループ化できなくなってしまうのです（ピボットテーブルでは，データの表示書式が不適切だったり，グループ化に不適な値が混ざっていたりするとグループ化できなくなります）。

ピボットテーブルの便利なところは，フィールドをグループ化して，開始日を調整したり，日単位の集計を月単位や年単位で集計したりできる点です。グループ化ができないと，ピボットテーブルの魅力は半減してしまいます。

今回は，この「日付の問題」を解決することに成功しました。

概要

Excelで外部データソースに接続する際のSQLクエリを修正して，SQLite3の日付をExcel形式の日付に変換する
SQLite3のレコードはワークシートに書き出す（ピボットテーブルと直接接続しない）
日付列の書式を設定する
ピボットテーブルのデータソースをワークシートのセル範囲にする

重要な点は，ピボットテーブルのデータソースをワークシートのセル範囲にすることです。データソースをSQLite3のデータベースにしてしまうと，うまくいきません。

設定方法

例として，あるネット広告の表示回数とクリック数をピボットテーブルで分析する手順を説明します。ネット広告の実績を集計するためのデータベースには，SQLite3を使っています。

Excelのバージョンは2007ですが，古いバージョンのExcelでも同じです。

はじめての人向けに，ODBCドライバのインストールから話を始めています。「もう分かっている」という人は，読み飛ばしてください。

（1） ODBCドライバのインストール。「SQLite ODBC Driver」のページからEXEファイルをダウンロードし，実行する。

――以後はExcelを起動してからの操作です。

（2）新規データソースの作成（その1）。リボンより「データ→その他のデータソース→Microsoft Query」をクリックする。（↓画像）

（3）新規データソースの作成（その2）。「新規データソース」を選択しOKをクリックする。（↓画像）

（4）新規データソースの作成（その3）。新規データソース名に任意の名前（この例では「AdResult」），ドライバは「SQLite3 ODBC Driver」を選択する。接続(C)をクリックする。（↓画像）

（5） SQLite3 ODBC Driver Connect。「Database Name」にSQLiteのデータベースファイルを入力する。そのほかの入力項目は空欄でも問題なさそうです。（画像↓）

（6）データソースの選択。データベースの一覧に「AdResult」が現れる。「AdResult」を選択してOKをクリックする。（画像↓）

（7）エラーメッセージ。「このデータソースには、表示できるテーブルはありません。」が表示される。OKをクリックする。（画像↓）

（8）クエリーウィザード列の選択。たしかにテーブルが空である。オプション(O)をクリックする。（画像↓）

（9）テーブルオプション。「表示」領域の「テーブル(T)，ビュー(V)，システムテーブル(S)，同義語(Y)」のチェックを外して，OKをクリックする。「クエリーウィザード列の選択」に戻ったらもう一度，オプション(O)をクリックする。今度はテーブル(T)をチェックし，OKをクリックする。 ――チェック項目はどれでも構いません。ようはテーブルオプションの更新を通知すればよいみたいです。（画像↓）

（10）クエリーウィーザード列の選択。「使用可能なテーブルと列」が表示される（列名はあくまで例です）。「次へ」を何度かクリックしてウィザードを進める。（画像↓）

（11）データのインポート。プロパティをクリックする。（画像↓）

（12）接続のプロパティ。コマンド文字列（SQLクエリ）を修正する。（画像↓）

＜修正前＞
SELECT
log30d_0.date, log30d_0.clk, log30d_0.vw
FROM log30d log30d_0

＜修正後＞
SELECT
(strftime('%s',datetime(log30d_0.date))/(24*60*60)+25569)
AS exceldate,
 log30d_0.clk, log30d_0.vw
FROM log30d log30d_0

日付の列の修正が済んだら，OKをクリックします。

何をやっているのか少し説明します。まず，文字列で表された日付を1970年1月1日から経過した秒数に変換します。つぎに，数値の単位を「秒」から「日」に変換し，さらに定数25569を加算して日付の基点を調節します。修正した日付の列の名前を，exceldateにします。

注意しなければならない点は，タイムゾーンです。この例では，SQLite3の日付列のタイムゾーンはグリニッジ標準時です。日本時間を採用している場合は，時差を修正しなければなりません。以下のようになるはずです。（datetime関数の第2引数に'localtime'をつけます。）

＜日本時間の場合＞
SELECT
(strftime('%s',datetime(log30d_0.date,'localtime'))/(24*60*60)+25569)
AS exceldate,
log30d_0.clk, log30d_0.vw
FROM log30d log30d_0

なお，（当たり前ですが）データベースに日付の列が存在しない場合，SQLクエリを修正する必要はありません。

（13）データをテーブルとして返した結果です。exceldate列は数値になります。（画像↓）

（14）セルの書式設定。exceldate列の書式を変更します。exceldate列を選択したら「セルの書式設定」を開き，分類を「日付」にします（画像↓）。OKをクリックするとexceldate列の数値が日付に変わります。

（15）ピボットテーブルの作成。リボンより「テーブル→ピボットテーブル」をクリックします。「テーブルまたは範囲を選択(S)」を選択し，「テーブル/範囲(T)」には手順（13）で返したセル範囲を指定します。セル範囲は，ピボットテーブルにしたいセル範囲をアクティブセルにしておけば，自動的に入力されます。OKをクリックします。（画像↓）