会計ソフトの作成 10 いくつかのview の作成　試算表作成　1

Posted at 2025-07-01

最初に汎用的に使えそうなViewです。


-- 勘定科目補助科目が目視できるview
-- その勘定科目の貸借上の意味を表現する列を追加

CREATE VIEW kaikei.view_勘定科目補助科目 AS
 SELECT h.id,
 h.勘定科目コード,
 k.勘定科目名,
 h.補助科目コード,
 h.補助科目名,
 t.貸借 AS 科目貸借,
 y.要素コード,
 y.要素名,
 y.貸借 AS 要素貸借コード,
 CASE y.貸借
 WHEN '1'::text THEN '借方'::text
 ELSE '貸方'::text
 END AS 要素貸借
 FROM (((kaikei.ma_補助科目 h
 JOIN kaikei.ma_勘定科目 k USING (勘定科目コード))
 JOIN kaikei.type_貸借 t ON ((k.貸借 = t.id)))
 JOIN kaikei.type_勘定要素 y ON ((y.要素コード = k.要素コード)));

勘定科目補助科目を同時に取得出来るので、このViewとの結合を行った方が楽なはず。

次は、仕訳の原則から必須なView


-- 仕訳において、その仕訳の行われた日付が問題にされないことは無いので必須
-- その仕訳の貸借上の意味を表現する列を追加

CREATE VIEW kaikei.view_仕訳_明細 AS
 SELECT m.id,
 m.created_at, m.updated_at, m.deleted_at,
 m.年度, m.締日, m.伝票日付, m.旧伝票番号,
 d.行,
 d.科目コード, k.勘定科目名, d.補助コード, k.補助科目名,
 d.税区分コード,
 d.金額, d.内消費税,
 d.摘要,
 CASE d.貸借
 WHEN '借方'::text THEN '1'::text
 WHEN '貸方'::text THEN '2'::text
 ELSE '3'::text
 END AS 仕訳貸借コード,
 d.貸借 AS 仕訳貸借,
 CASE k.科目貸借
 WHEN '借方'::text THEN '1'::text
 WHEN '貸方'::text THEN '2'::text
 ELSE '3'::text
 END AS 科目貸借コード,
 k.科目貸借,
 y.要素コード,
 y.要素名,
 y.貸借 AS 要素貸借コード,
 CASE y.貸借
 WHEN '1'::text THEN '借方'::text
 WHEN '2'::text THEN '貸方'::text
 ELSE '不定'::text
 END AS 要素貸借
 FROM (((kaikei.trans_仕訳 m
 JOIN kaikei.trans_仕訳明細 d ON ((m.id = d.仕訳番号)))
 JOIN kaikei.view_勘定科目補助科目 k 
 ON (((d.科目コード = k.勘定科目コード) AND (d.補助コード = k.補助科目コード))))
 JOIN kaikei.type_勘定要素 y USING (要素コード));

Viewを作成する材料にViewを使う事の問題については後で。

次は、試算表を算出する元になるView


-- 明細金額をプラスマイナスに修正することで、前残、当残の算出に利用する
-- 加えて、隠れている列を勘定科目と同等の行として返す
-- 999999999 は例外が合った際の適当な代替案

CREATE VIEW kaikei.view_仕訳_明細_増減 AS
 SELECT view_仕訳_明細.年度,
 view_仕訳_明細.締日,
 view_仕訳_明細.伝票日付,
 view_仕訳_明細.科目コード,
 view_仕訳_明細.補助コード,
 view_仕訳_明細.税区分コード,
 view_仕訳_明細.要素コード,
 view_仕訳_明細.要素名,
 view_仕訳_明細.金額,
 view_仕訳_明細.内消費税,
 CASE ((((view_仕訳_明細.仕訳貸借 || '/'::text) || view_仕訳_明細.要素名) || '/'::text) || view_仕訳_明細.科目貸借)
 WHEN '前期繰越/負債/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/収益/借方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/収益/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/費用/借方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/費用/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/資本/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/資産/借方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '前期繰越/資産/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '借方/収益/借方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '借方/収益/貸方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '借方/負債/貸方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '借方/収益/借方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '貸方/費用/借方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '貸方/費用/貸方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '貸方/資産/借方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '貸方/資産/貸方'::text THEN ((- view_仕訳_明細.金額) + view_仕訳_明細.内消費税)
 WHEN '借方/費用/借方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '借方/資産/借方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '貸方/収益/借方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '貸方/収益/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 WHEN '貸方/負債/貸方'::text THEN (view_仕訳_明細.金額 - view_仕訳_明細.内消費税)
 ELSE '-999999999'::integer
 END AS 税抜増減,
 CASE ((((view_仕訳_明細.仕訳貸借 || '/'::text) || view_仕訳_明細.要素名) || '/'::text) || view_仕訳_明細.科目貸借)
 WHEN '借方/収益/借方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '借方/収益/貸方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '借方/負債/貸方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '貸方/費用/借方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '貸方/費用/貸方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '貸方/資産/借方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '貸方/資産/貸方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '借方/費用/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '借方/資産/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/収益/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/収益/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/負債/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/費用/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/費用/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/資本/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/資産/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '前期繰越/資産/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '貸方/収益/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '貸方/収益/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '貸方/負債/貸方'::text THEN view_仕訳_明細.内消費税
 ELSE 999999999
 END AS 内消費税増減,
 view_仕訳_明細.仕訳貸借,
 view_仕訳_明細.要素貸借,
 view_仕訳_明細.科目貸借,
 view_仕訳_明細.摘要
 FROM kaikei.view_仕訳_明細
 UNION ALL
 SELECT view_仕訳_明細.年度,
 view_仕訳_明細.締日,
 view_仕訳_明細.伝票日付,
 CASE ((((view_仕訳_明細.仕訳貸借 || '/'::text) || view_仕訳_明細.要素名) || '/'::text) || view_仕訳_明細.科目貸借)
 WHEN '貸方/収益/貸方'::text THEN '335'::text
 WHEN '貸方/費用/借方'::text THEN '193'::text
 WHEN '借方/費用/借方'::text THEN '193'::text
 WHEN '借方/収益/借方'::text THEN '335'::text
 WHEN '借方/収益/貸方'::text THEN '335'::text
 WHEN '借方/資産/借方'::text THEN '193'::text
 WHEN '貸方/収益/借方'::text THEN '335'::text
 WHEN '貸方/資産/借方'::text THEN '193'::text
 ELSE 'xxxxxxxx'::text
 END AS 科目コード,
 'C'::text AS 補助コード,
 view_仕訳_明細.税区分コード,
 view_仕訳_明細.要素コード,
 view_仕訳_明細.要素名,
 view_仕訳_明細.内消費税 AS 金額,
 view_仕訳_明細.内消費税,
 CASE ((((view_仕訳_明細.仕訳貸借 || '/'::text) || view_仕訳_明細.要素名) || '/'::text) || view_仕訳_明細.科目貸借)
 WHEN '貸方/収益/貸方'::text THEN view_仕訳_明細.内消費税
 WHEN '貸方/費用/借方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '借方/費用/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '借方/収益/借方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '借方/収益/貸方'::text THEN (- view_仕訳_明細.内消費税)
 WHEN '借方/資産/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '貸方/収益/借方'::text THEN view_仕訳_明細.内消費税
 WHEN '貸方/資産/借方'::text THEN (- view_仕訳_明細.内消費税)
 ELSE '-99999999'::integer
 END AS 税抜増減,
 0 AS 内消費税増減,
 view_仕訳_明細.仕訳貸借,
 view_仕訳_明細.要素貸借,
 'X'::text AS 科目貸借,
 ''::text AS 摘要
 FROM kaikei.view_仕訳_明細
 WHERE (view_仕訳_明細.内消費税 <> 0);

[335][193]は仮受仮払消費税の勘定科目コードです。
このViewを使い、後に書きますsqlを発行すると、過去20年弱の合計残高試算表と同一のものが得られましたので、まあ大丈夫と思います。（と言うか、同等の結果が出るようになるまで修正を重ねた結果としてのViewです。）

このView、見るからに問題ありありのUnion All があって、簿記データベース上でどんどん増加していく仕訳へのアクセスを2回行っていて、うち１回は常にフルシーケンシャルアクセス。
union all の前の select はインデックス利用されているように見えるのですが、どうも、後半の列持ちの仮受仮払消費税を架空で行と同様に置き換えている部分は、フルシーケンシャルアクセスになっているようで、そもそも実体が無いのだからインデックス使いようも貼りようもないわけです。explainの読み込みとか能力の範囲をはるかに超えていますが、Parallel Seq Scan on "trans_仕訳明細"とかあるとそうなんだろうと。
この段々遅くなるだろうと予測される部分がどれ程吸収できるかを検討する必要がありそうです。

付記として、ここで、Viewを作成する材料としてViewを利用する事の問題について。

一つは、多段でViewを使用した場合、基底になっている方のViewの定義変更が難しくなるということです。
postgresql では create or replace view ....
という構文で行うのですが、そのViewに依存しているViewがある場合、カスケードを付けないとダメで、カスケードでpostgresql では一般に削除の意味になるので、怖くてやったことがありません。
この件は、
1　基底となっているViewの名前変更　例えば view_名前という viewは old_view_名前と変更　−＞ postgresqlのシステムカタログ上は、そのViewを利用している部分は全て名前が変更されているはず
2　修正を加えたViewを元のViewの名前で作成
3 スキーマをダンプして、当該の変更が加わっている部分を書き換え old_view_名前になっている部分を view_名前に戻し、そのダンプを読み込ませる
の手順を踏んで調整しています。

二つには、Viewは結局テーブルへ逆解釈されると言うことです。そこはパーサーやオプティマイザが適当に処理してくれるでしょうし、explainを見ても、基底となっているViewも含めてテーブルアクセスに戻して解釈し、適当なインデックスを利用してアクセス計画を作っているように見えていますが、多段にViewを重ねていけばいくほど、パーサー、オプティマイザの負担が増えるのは間違いないはずで、効率の点で問題だろうということです。
多段のViewを作る位ならCTE(Common Table Expressions)を利用した方が良いのかもしれませんし、それでは同じなのかも知れません。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

会計ソフトの作成 10 いくつかのview の作成 試算表作成 1

会計ソフトの作成 10 いくつかのview の作成　試算表作成　1