本件について本当は先月やりたかったのですが、いつも使っているノートPCの電源が付かなくなったのもあり先週やっと上半期の支出を計算しました。
(原因はわかりませんが、しばらくACアダプタ繋いでいたら復旧しました。)
以前カード会社の月次CSVをINSERT分に作ったのですが、これのお陰でものの数分で完了してしまいました。
作っといてよかった!
環境
サブ機ノートWindows10のWSL上で動かしています。
PS C:\Users\masashi> Get-WmiObject Win32_OperatingSystem SystemDirectory : C:\WINDOWS\system32 Organization : BuildNumber : 18363 RegisteredUser : user SerialNumber : 00330-80000-00000-AA868 Version : 10.0.18363
PS C:\Users\masashi> wsl uname -a ; sqlite3 --version Linux DESKTOP-N095TND 4.4.0-18362-Microsoft #836-Microsoft Mon May 05 16:04:00 PST 2020 x86_64 x86_64 x86_64 GNU/Linux 3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
PS C:\Users\masashi> wsl cat /etc/lsb-release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=18.04 DISTRIB_CODENAME=bionic DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS"
INSERT部分は割愛しまして、
半期毎の集計スクリプトがあるので早速出してみましょう。
- 集計スクリプト
生活圏がばれますが、まあ気にしないですw
masashi@DESKTOP-N095TND:~/expenses$ cat syukei2020\ first\ half.sql .headers on .mode column select 分類 as 決済内容,sum(合計) as 合計 from (select case contents when (select contents from expenses where contents like '%ａｕ電話利用料%') then '通信費' when (select contents from expenses where contents like '%Ｊ：ＣＯＭ%') then '通信費' when 'インターネットイニシアティブ' then '通信費' when 'ＮＥＴＦＬＩＸ．ＣＯＭ' then '通信費' when 'ヤフージャパン' then '通信費' when 'DIGITALOCEAN.COM (DIGITALOCEAN.)' then '通信費' when 'ミーナ津田沼' then '食費' when 'イオンリテール' then '食費' when 'イトーヨーカ堂 （サインレス' then '食費' when 'ＪＲ東日本モバイルＳｕｉｃａ' then 'SUICA' when '家賃' then '住宅費' when '光熱費' then '住宅費' ELSE 'その他' END AS '分類', sum(payment) AS '合計' from expenses where date BETWEEN '2020/01/01' and '2020/06/30' group by contents) group by 分類 UNION ALL select '-',sum(payment) as 合計 from expenses where date BETWEEN '2020/01/01' and '2020/06/30';
sqliteの仕様で日本語でのカラムがずれてしまうのはご了承ください。
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < syukei2020\ first\ half.sql 決済内容 合計 ---------- ---------- SUICA 356250 その他 280821 住宅費 568000 通信費 69114 食費 104905 - 1379090
計137万...!?
いくらなんでもこんなに使った覚えはありません。
特に今はコロナ禍で外食等の出費は減っているはずです。
ここは去年のデータと比較してみましょう。
集計スクリプトは上記のものと日付が違うだけです。
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < syukei2019\ first\ half.sql 決済内容 合計 ---------- ---------- SUICA 185000 その他 211674 住宅費 426000 通信費 59273 食費 70291 - 952238
95万ちょいですね。
今年は40万近く増えてます。
やはり何かおかしい...
サマリだと分類をざっくりに出しすぎなので項目ごとにもう一度集計してみましょう。
COUNTとGROUP BY
項目毎の集計は難しい事なくてCOUNTとGROUP BYで出来ます。
COUNTのカラム作って項目でGROUP BYすればOKです。
ついでに金額で降順にソートすればいい感じになるかな。
- 件数集計スクリプト
masashi@DESKTOP-N095TND:~/expenses$ cat count2019\ half.sql .headers on .mode column select contents, COUNT(*) from expenses where date BETWEEN '2019/01/01' and '2019/06/30' GROUP BY contents ORDER BY COUNT(*) DESC;
昨年の件数集計
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < count2019\ half.sql contents COUNT(*) -------------- ---------- ＪＲ東日本モバイルＳｕｉｃａ 86 イトーヨーカ堂 （サインレス 14 ミーナ津田沼 10 イトーヨーカドー 食品 9 イオンリテール 7 DIGITALOCEAN.C 6 ヤフージャパン 6 光熱費 6 家賃 6 Ｊ：ＣＯＭ ２サービスパック 6 ＮＥＴＦＬＩＸ．ＣＯＭ 6 インターネットイニシアティブ 5 スウイ―ト カヴアカヴア 5 ENT-INN-CORP ( 3 イトーヨーカ堂 2 ツダヌマ パルコ 2 MOJANG.COM (ST 1 ONEDREAM (0368 1 くまざわ書店 津田沼店 1 つり具ランド 館山総合館 1 イトーヨーカドー 1 イトーヨーカドー専門店 1 ノジマ 津田沼店 1 ラクテンペイ ハリケ―ン 1 ラクテンペイ レプマ―ト 1 ０１月分 ａｕ電話利用料 1 ０２月分 ａｕ電話利用料 1 ０３月分 ａｕ電話利用料 1 ０４月分 ａｕ電話利用料 1 ０５月分 ａｕ電話利用料 1 １２月分 ａｕ電話利用料 1 ＪＲ東日本モバイルＳｕｉｃａ 1 ＪＲ西日本 ５４８９サービス 1 ＮＨＫ 放送受信料 1
まあこっちがベースになるので特にコメントなしです。
そういえば去年マイクラ買いましたねw
今年の集計
同じスクリプトで今年の日付に変更してます。
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < count2020\ half.sql contents COUNT(*) -------------- ---------- ＪＲ東日本モバイルＳｕｉｃａ 109 イトーヨーカドー 食品 25 ミーナ津田沼 12 光熱費 8 家賃 8 ＮＥＴＦＬＩＸ．ＣＯＭ 8 Ｊ：ＣＯＭ サービス利用料 7 DIGITALOCEAN.C 6 イトーヨーカドー 6 ヤフージャパン 6 自遊空間 津田沼北口店 5 スウイ—ト カヴアカヴア 4 オナマエドツトコムドメイン 3 ヒマラヤ モレラ岐阜店 3 １２月分 ａｕ電話利用料 3 ＪＲ西日本 ５４８９サービス 3 ＴＳＵＴＡＹＡ ＢＯＯＫ Ｓ 3 ONEDREAM (714- 2 イオンリテール 2 インターネットイニシアティブ 2 ＡＭＡＺＯＮ．ＣＯ．ＪＰ 2 CROP (05700018 1 ニトリ 1 ニトリネット 1 ノジマ 津田沼店 1 ヤフーかんたん決済 1 ０１月分 ａｕ電話利用料 1 ０２月分 ａｕ電話利用料 1 ０３月分 ａｕ電話利用料 1 ０４月分 ａｕ電話利用料 1 ０５月分 ａｕ電話利用料 1 ＡＸＥＳ ＨＭ 1 ＮＨＫ 放送受信料 1 ＷＩＬＤ—１幕張店 1
Suicaの件数が増えたのはリモートワークでコンビニに行く回数が増えたからかな?
しかし、ちょっと怪しいところ発見しました。
光熱費 8 家賃 8 ＮＥＴＦＬＩＸ．ＣＯＭ 8 Ｊ：ＣＯＭ サービス利用料 7 オナマエドツトコムドメイン 3 １２月分 ａｕ電話利用料 3
この辺は月次の固定費なので6回しかないはずですが多いですね...
犯人を探せ
犯人は私で、INSERT文変換スクリプトのミスでした。
ミスというか変換ツールを複数回実行してしまったようでダブったレコードがありました。
DBを修正します。(手動で)
幸いにも1月日付分だけでした。
重複のハンドリングも検知する仕組みを作らないとですね。
再集計
OK。
とりあえず固定費のカウントは戻りました。
Suicaも重複が無くなって少し減りました。
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < count2020\ half.sql contents COUNT(*) -------------- ---------- ＪＲ東日本モバイルＳｕｉｃａ 82 イトーヨーカドー 食品 20 ミーナ津田沼 10 DIGITALOCEAN.C 6 ヤフージャパン 6 光熱費 6 家賃 6
では再度集計してみます。
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < syukei2020\ first\ half.sql 決済内容 合計 ---------- ---------- SUICA 164000 その他 206920 住宅費 426000 通信費 54729 食費 99049 - 950698
いいですね。
ほぼ昨年と同じくらいの出費です。
悲しいかな、昨年とまったく同じ生活をしているということですねw
むすび
この集計ツールを作ったお陰で何年もやってこなかった収支の計算が非常に楽になりました。
キャッシュレス支払いが主体の生活ではありますが、月次の明細CSVをDBにインポートするだけで
トラッキング、集計ができるのでこれにかける時間はほとんどありません。
(明細が全角なのはアレですが)
ただ、ツールの重複チェックが無かったのでインポートをミスってしまい本記事を書くことになりました。
発見して修正出来たので結果オーライですはあります。
本件で半年ぶりにSQLいじりました。
極小規模DBですがいろいろな集計ができるので触ってて面白いですね。
あとは現金払いをうまくトラッキングできればこのツールも完ぺきになるんですがね。
どう取り入れるか悩みモノです。
ちなみに収入を含めた収支は安月給のため恥ずかしくて公開できません…
ローカルでひっそり更新します。
