【実行環境】
Windows2008R2
【データベース】
Oracle Database
【実現したいこと】
同じテーブルが存在しているメインとサブ2つのデータベースから同じSQLを実行してデータを取得し、取得データを主キーでメインとサブのデータを比較して共通のものはすべて合算してExcelに出力したい。DBLINKは使えません。
【例】
メインDB:10件取得
サブDB:5件取得
主キーの値が同じ:4件存在
上記の場合、主キーが同じ4件はメインとサブで足算をしてExcelに書き込み、残りはそのままExcelに書き込んでファイルを作成。作成されるExcelの行は11行になる。
というのを実現したいのですが、私の知識では実現できず、足算して書き込みまでは可能だったのですが、その次に計算する前のデータも出力され結局、計算だけされたメイン10件とサブ5件の15行のファイルになってしまいました。そこでどう処理をするのが良いでしょうか?ぜひとも知恵をお貸ししていただきたく。よろしくお願いいたします。
考え方だけですが、2通りほど。
Dim 計算したフラグの配列 大きさはサブDB のレコード数 計算したフラグの配列を、0で埋める For i メインDB のループ For j サブDB のループ If メインDBのプライマリキー(i) = サブDBのプライマリキー Then 計算をして、メインDB のデータを書き換え 計算したフラグ(j) = 1 ' サブDB のデータを計算した、という目印 End If Next j Next i ' データの書き出し For i メインDB のループ メインDB のデータを Excel に出力 Next i For j メインDB のループ If 計算したフラグ(j) = 0 Then ' 計算のときの目印が立ってない サブDB のデータを Excel に出力 End If Next j
出力結果は、メインDB のデータ、サブDB の残りのデータの順番になる。
ロジックとしては、分かりやすい(バグも出にくい)。
二重のループなので、データ数が多くなると遅くなりそう。
プライマリキーが昇順で並べ替えられていると想定。
i = 1 ' メインDB の添え字 j = 1 ' サブDB の添え字 Do Until i > メインDBのレコード数 Or j > サブDB のレコード数 If メインDB のプライマリキー(i) = サブDB のプライマリキー(j) Then 計算をして、計算結果を Excel に出力 i = i + 1 j = j + 1 Else If メインDB のプライマリキー(i) > サブDB のプライマリキー(j) Then サブDB のデータを Excel に出力 j = j + 1 Else ' メインDB のプライマリキー(i) < サブDB のプライマリキー(j) のはず メインDB のデータを Excel に出力 i = i + 1 End If Loop If i > メインDBのレコード数 Then For k = j To サブDB のレコード数 ' 残りのサブDB のデータを出力 サブDB のデータを Excel に出力 Next k Else If j > サブDB のレコード数 Then For k = j To メインDB のレコード数 ' 残りのメインDB のデータを出力 メインDB のデータを Excel に出力 Next k End If
汎用的な方法に比べて、早い。
プライマリキーが複数列で構成されている場合には、比較用の関数を作って対応する。
出力されたデータも、プライマリキーの順序になって出力される。
汎用的な方法に比べると、バグが紛れ込みやすい。
イメージ的には、こんな感じのロジックです。
左にメインDB のデータが縦に並んでる。
右にサブDB のデータが縦に並んでる。
左手の指で、メインDB の最初のデータを押さえる。
右手の指で、サブDB の最初のデータを押さえる。
指で押さえているデータを比較して、キーが小さい方を書き出して、書き出した方の指をひとつ下にずらす。
もし、キーが同じだったら計算したデータを書き出して、両方の指をひとつずつ下にずらす。
どちらか片方の指がデータの列を超えたら、もう片方のデータの指でさしている部分から下までをデータに書き出す。
指が指しているところのデータが、まだ Excel に出力されていないデータになってます。
少し邪道かも知れないですが、一旦、2つのテーブルを CSV 出力し、VBScript から ADO + Jet データベース(OS 組み込みのデータベースエンジン)にて、サマリするのは如何でしょうか。
Jet データベースを用いることで、CSV に対して、UNION や SUM 関数が使えたりします。
以下のコードは、Oracle より 2テーブルが CSV 出力(TABLE_MAIN.csv / TABLE_SUB.csv)されている前提のコードとなっています。サンプルテーブルは ID が主キーの想定です。
' File : CalcCsvTable.vbs ' Usage : CScript //Nologo CalcCsvTable.vbs Option Explicit ' ADO StringFormatEnum Const adClipString = 2 Call Main() Sub Main() Dim cn Set cn = CreateObject("ADODB.Connection") ' Jet データベースエンジンを用いて CSV に接続する場合の接続文字列 cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=.\;Extended Properties=""text;HDR=Yes;FMT=Delimited;"";" Dim strSQL ' TABLE_MAIN.csv と TABLE_SUB.csv を UNION ALL で結合し SUM 関数により集計する。 ' ' [TABLE_MAIN] ' ID NAME SCORE ' -- ---- ------ ' 1 田中 10 ' 2 山本 20 ' 3 佐藤 30 ' ' [TABLE_SUB] ' ID NAME SCORE ' -- ---- ------ ' 2 山本 40 ' 3 佐藤 50 ' 4 鈴木 60 ' ' [集計結果] ' ID NAME SCORE ' -- ---- ------ ' 1 田中 10 ' 2 山本 60 ' 3 佐藤 80 ' 4 鈴木 60 ' strSQL = _ "SELECT ID, NAME, SUM(SCORE) FROM (" & _ "SELECT * FROM TABLE_MAIN.csv " & _ "UNION ALL "& _ "SELECT * FROM TABLE_SUB.csv " & _ ") AS U " & _ "GROUP BY ID, NAME " & _ "ORDER BY ID" Dim rs ' SQL を実行しレコードセットとして取得する Set rs = cn.Execute(strSQL) ' レコードセットを CSV 形式で出力する WScript.Echo rs.GetString(adClipString, , ",", vbCrLf) End Sub
注意事項としては、64bit OS にプレインストールされている、Jet データベースは 32bit 版のみであある為、VBScript の実行は、32bit 版のコマンドプロンプト
C:\Windows\SysWOW64\cmd.exe
より、実行お願いします。
コメント(0件)