Tableau×経理 ～SQLを使って仕訳パターン抽出～

経理システムの更新するにあたっては仕訳パターンを把握することが必須。膨大な仕訳データをパターン化する手法の１つを備忘も兼ねて。

大まかな流れ

• SQLで伝票IDごとに仕訳情報を圧縮

• TableauPrepで圧縮情報を解凍

• Tableauで可視化

仕訳データの前提

• 伝票IDに対して複数の仕訳情報が紐づく

• 資産、負債、資本、収益、費用を勘定科目１桁で把握できる

• 伝票IDの中で何件目のデータか分かる

• それぞれの仕訳の貸借が分かる

• 多分第３正規形

Chat GPTでSQLを書かせる

プロンプト：
OracleのSQLで答えなさい。 T1というテーブルにCol1、Col2、Col3、Col4があります。 Col1は国、Col２は地域、Col3は都道府県、Col4は県庁所在地です。 Col1とCo2でグループ化して、Col3とCol4をLISTAGGでまとめなさい。Col3とCol4は区切り文字なし、異なる行の結合はカンマで結合します。LISTAGG内はCol3の昇順、抽出結果はCol1の昇順でソートします。

SELECT
 Col1,
 Col2,
 LISTAGG(Col3 || Col4, ',') WITHIN GROUP (ORDER BY Col3 ASC)
FROM T1
GROUP BY Col1, Col2
ORDER BY Col1 ASC;

出力はプロンプトを微調整しながら行います。（一発で想定通りのコードというより、ほぼほぼあっているコードをカスタマイズするイメージ）
事前にSQLでGROUPCONCATがあり、OracleではLISTAGGが対応するコマンドであることを調べておきました。いやぁ、便利便利。
Col1～Col4やOrder Byの箇所を適宜書き換えます。
仕訳データなので、Col1に伝票ID、Col2～に各種属性などをセットします。

TableauPrepで解凍

• LISTAGGの箇所を複製（＝仕訳パターン）

• LISTAGGの箇所を区切り文字（今回は「，」カンマ）で分割

• 分割された箇所を列から行にピボット処理

• ピボット後の不要列を削除

• NULLや空欄をフィルタ

Tableauで可視化

• 未解凍部分の情報をLEFT、MID、RIGHTなどで取り出す。

• マスタでリレーションはってコードを読み替える。

• 多分、このあたりの数値を見ていく

  • 明細データは全部で何件か？（データ量）

  • 伝票IDのCOUNTDは何件か？（伝票件数）

  • 仕訳パターンのCOUNTDは何件か？（仕訳パターン数）

  • 何行の伝票データか？（各仕訳が保有する情報量の分布）

  • 仕訳パターンを多い順にソートして上から眺めていく

ちょっとTIPS

• 勘定科目１桁、所属コードは考慮しない、くらいでちょうどいい
  （貸借と資産～費用の区分と行数が分かればなんとなくイメージつく）

• 属性に起案部門とかもっておくといいかも。増やした分だけGroup Byの対象増やすだけなのでお手軽。