Tableau×経理 ~SQLを使って仕訳パターン抽出~
経理システムの更新するにあたっては仕訳パターンを把握することが必須。膨大な仕訳データをパターン化する手法の1つを備忘も兼ねて。
大まかな流れ
SQLで伝票IDごとに仕訳情報を圧縮
TableauPrepで圧縮情報を解凍
Tableauで可視化
仕訳データの前提
伝票IDに対して複数の仕訳情報が紐づく
資産、負債、資本、収益、費用を勘定科目1桁で把握できる
伝票IDの中で何件目のデータか分かる
それぞれの仕訳の貸借が分かる
多分第3正規形
Chat GPTでSQLを書かせる
プロンプト:
OracleのSQLで答えなさい。 T1というテーブルにCol1、Col2、Col3、Col4があります。 Col1は国、Col2は地域、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
勘定科目1桁、所属コードは考慮しない、くらいでちょうどいい
(貸借と資産~費用の区分と行数が分かればなんとなくイメージつく)属性に起案部門とかもっておくといいかも。増やした分だけGroup Byの対象増やすだけなのでお手軽。
この記事が気に入ったらサポートをしてみませんか?