会計ソフトの作成 12 試算表SQLの改良

試算表を算出するところまで来ると、今度はかかる時間の短縮をめざすようになります。
その度に、Viewからクエリーが発生し、テーブルアクセスが起こるなら、何らかの方法でViewをキャッシュしてしまえば良いと考えることになります。
Viewのキャッシュなら、今ならマテリアライズドビューはどうなのだろうと考える訳です。目新しいものは使って見たくなるものですし、Viewの定義に一単語入れれば終わってしまう簡単な作業ですし。
ただこのマテリアライズドビューは、現状標準ではREFRESH MATERIALIZED VIEW を発行し、全Viewのリフレッシュという重い処理を行う事になり、尚かつ、アプリに実装して、他の人にも利用してもらう場合、そのけっこう重い処理をどのタイミングで行うのかも問題になります。内部でデータとして保持するViewって、これは元データよりもディクス領域使ったりしてしまうことがあるんじゃないだろうかとも。

そこで、試行錯誤中気が付いたのですが、
１　試算表においては、年度とか、締日とか、日付とかの単位で、全勘定科目の科目ごとの金額増減の集計値があればよいのだから、view_仕訳_明細_増減や、view_仕訳_明細_税集計の金額部分を合計したワーク表を作って、そちらからデータを取得すれば良いのでは？
２　会計に関わるDBなのだから、当期から前の期のデータは、もう決して変更されない（むしろしてはダメ）特性をもっているのだから、ワーク表のリフレッシュは、常に当期に関わる部分だけで良いはずでは？
と思い当たりました。

そこで、

create table kaikei.work_仕訳_明細_増減 as
select * from kaikei.kaikei.view_仕訳_明細_増減;

create table kaikei.work_仕訳_明細_税集計 as
select kaikei.view_仕訳_明細_税集計;

で受けとめるワーク表を作成して、

begin;
delete from kaikei.work_仕訳_明細_増減 where 年度 = NNNN;

insert into kaikei.work_仕訳_明細_増減
(年度, 締日, 伝票日付, 科目コード, 補助コード, 税区分コード, 要素コード,
要素名, 金額, 内消費税, 税抜増減, 内消費税増減, 仕訳貸借, 要素貸借, 科目貸借)
select 年度, 締日, 伝票日付, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名,
sum(金額), sum(内消費税), sum(税抜増減), sum(内消費税増減),
仕訳貸借, 要素貸借, 科目貸借 
from kaikei.view_仕訳_明細_増減
where 年度 = NNNN
group by 年度, 締日, 伝票日付, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名,
仕訳貸借, 要素貸借, 科目貸借;

delete from kaikei.work_仕訳_明細_税集計
where 年度 = NNNN;

insert into kaikei.work_仕訳_明細_税集計
(年度, 締日, 伝票日付, 仕訳貸借, 科目コード, 補助コード, 税区分コード,
要素コード, 要素名, 金額, 内消費税)
select 年度, 締日, 伝票日付, 仕訳貸借, 科目コード, 補助コード, 税区分コード,
要素コード, 要素名, sum(金額) as 金額, sum(内消費税) as 内消費税
from kaikei.view_仕訳_明細_税集計
where 年度 = NNNN
group by 年度, 締日, 伝票日付, 仕訳貸借, 科目コード, 補助コード,
税区分コード, 要素コード, 要素名;

commit; 

を NNNNを年度の分だけ変更しながら実行、
試算表算出SQLは

begin;
 
delete 
from kaikei.work_仕訳_明細_増減 where 年度 = NNNN;

insert into kaikei.work_仕訳_明細_増減
(年度, 締日, 伝票日付, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名, 金額, 内消費税, 税抜増減, 
内消費税増減, 仕訳貸借, 要素貸借, 科目貸借)
select 年度, 締日, 伝票日付, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名, sum(金額), sum(内消費税), sum(税抜増減), 
sum(内消費税増減), 仕訳貸借, 要素貸借, 科目貸借 
from kaikei.view_仕訳_明細_増減
where 年度 = NNNN
group by 年度, 締日, 伝票日付, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名, 仕訳貸借, 要素貸借, 科目貸借;

delete from kaikei.work_仕訳_明細_税集計
where 年度 = NNNN;

insert into kaikei.work_仕訳_明細_税集計
(年度, 締日, 伝票日付, 仕訳貸借, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名, 金額, 内消費税)
select 年度, 締日, 伝票日付, 仕訳貸借, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名, sum(金額) as 金額, sum(内消費税) as 内消費税
from kaikei.view_仕訳_明細_税集計
where 年度 = NNNN
group by 年度, 締日, 伝票日付, 仕訳貸借, 科目コード, 補助コード, 税区分コード, 要素コード, 要素名;

commit; 
--以下は、view_ が work_ に変わっただけ
with
cte_zenki
as(
select 科目コード, sum(
case
when kaikei.work_仕訳_明細_増減.要素名 ='収益' and 科目貸借 = '借方' then - 税抜増減
when kaikei.work_仕訳_明細_増減.要素名 ='費用' and 科目貸借 = '貸方' then - 税抜増減
else 税抜増減 end 
) as 前残
from kaikei.work_仕訳_明細_増減
where 年度 < XXXX
group by 科目コード), 
cte_touki
as(
select 科目コード, sum(
case
when kaikei.work_仕訳_明細_増減.要素名 ='収益' and 科目貸借 = '借方' then - 税抜増減
when kaikei.work_仕訳_明細_増減.要素名 ='費用' and 科目貸借 = '貸方' then - 税抜増減
else 税抜増減 end 
) as 当期残
from kaikei.work_仕訳_明細_増減
where 年度 <= XXXX
group by 科目コード), 
cte_zenki_touki
as(
select distinct XXXX::integer as 年度, 
科目コード, 
case when 要素名 in('収益', '費用') then 0 else 前残 end as 前残, 
case when 要素名 in('収益', '費用') then 当期残 -前残 else 当期残 end as 残高
from cte_zenki
join kaikei.view_勘定科目補助科目 kk on 科目コード = kk.勘定科目コード
full outer join cte_touki using( 科目コード)
), 
cte_kari
as(
select 年度, 
 科目コード, sum(金額-内消費税) as 借方
from kaikei.work_仕訳_明細_税集計
where 年度 = XXXX
and 仕訳貸借 in('借方')
group by 年度, 科目コード), 
cte_kasi
as(
select 年度, 
 科目コード, sum(金額-内消費税) as 貸方
from kaikei.work_仕訳_明細_税集計
where 年度 = XXXX
and 仕訳貸借 in('貸方')
group by 年度, 科目コード
)

select 
COALESCE(cte_zenki_touki.年度, COALESCE(cte_zenki_touki.年度, COALESCE(cte_kasi.年度, cte_kari.年度))) as 年度, 
COALESCE(cte_zenki_touki.科目コード, COALESCE(cte_kasi.科目コード, cte_kari.科目コード)) as 科目コード, 
勘定科目名, 
COALESCE(cte_zenki_touki.前残, 0) as 前残, 
COALESCE(借方, 0) as 借方, 
COALESCE(貸方, 0) as 貸方, 
COALESCE(cte_zenki_touki.残高, 0) as 残高, 
case m.要素コード
when '1' then COALESCE(cte_zenki_touki.残高, 0)
when '2' then (COALESCE(貸方, 0) - COALESCE(借方, 0))
when '3' then COALESCE(cte_zenki_touki.残高, 0)
when '4' then COALESCE(cte_zenki_touki.残高, 0)
when '5' then COALESCE(貸方, 0) - COALESCE(借方, 0)
when 'X' then 0
end 評価
from kaikei.ma_勘定科目 m
left outer join cte_kari on m.勘定科目コード = cte_kari.科目コード
left outer join cte_kasi on m.勘定科目コード = cte_kasi.科目コード
left outer join cte_zenki_touki on m.勘定科目コード = cte_zenki_touki.科目コード
where not (cte_kasi.年度 is null and cte_kari.年度 is null and cte_zenki_touki.年度 is null)
and not ( 前残 = 0 and 借方 = 0 and 貸方 = 0 and 残高 = 0)
order by 科目コード

としました。
前処理にあたるワーク表のリフレッシュを含めても、年度が2024で全体200msec程度収まるようになりました。
アプリに実装する場合、年度、あるいは期間が当期を含んでいないなら、ワーク表リフレッシュを省略することができるはずなので、この構成で、Postgresql あるいは DB側での試算表の準備は出来たと思います。

ちなみに

select 'kaikei.view_仕訳_明細_増減'::text as テーブル名, count(*) as 件数 from kaikei.view_仕訳_明細_増減
union all
select 'kaikei.work_仕訳_明細_増減',count(*) from kaikei.work_仕訳_明細_増減
union all
select 'kaikei.view_仕訳_明細_税集計' ,count(*) from kaikei.view_仕訳_明細_税集計
union all
select 'kaikei.work_仕訳_明細_税集計' ,count(*) from kaikei.work_仕訳_明細_税集計
union all
select 'kaikei.view_仕訳_明細',count(*) from kaikei.view_仕訳_明細

の結果、

テーブル OR View	件数
kaikei.work_仕訳_明細_税集計	56416
kaikei.work_仕訳_明細_増減	56416
kaikei.view_仕訳_明細	151664
kaikei.view_仕訳_明細_税集計	201695
kaikei.view_仕訳_明細_増減	201695

なので、作業テーブルの方が実テーブルより大きい（たまにある状態）と言うことは無さそうだし、work_　の作業テーブルは、例えば１０年以上前のデータについては、日付を強制的に同じ日（毎月１日とか）にしてしまえば、もっと行を減らせると思います。
この結果からは、内消費税ということで隠れていた隠れていた仮受仮払消費税の行数が五万行位あることもわかりました。