PostgreSQL

PostgreSQL 入出金テーブルから月締め集計ビュー作成

投稿日:2019年12月4日 更新日:

入出金情報が入ったテーブルから月締めの集計ビューを作る方法を試します。

元データ

以下のデータを対象にします。

select * from account;

#結果

   dates    | in_amount | out_amount | balance
------------+-----------+------------+---------
 2019-10-01 |       100 |            |   10100
 2019-10-02 |           |       1000 |    9100
 2019-10-02 |      3000 |            |   12100
 2019-10-02 |     10000 |            |   22100
 2019-10-20 |           |       1000 |   21100
 2019-10-24 |      2000 |            |   23100
 2019-11-03 |           |       3000 |   20100
 2019-11-06 |           |       4000 |   16100
 2019-11-15 |     30000 |            |   46100
 2019-11-15 |           |       2000 |   44100
 2019-11-25 |           |        100 |   44000
 2019-11-26 |      3000 |            |   47000
 2019-11-27 |           |      25000 |   22000
 2019-11-28 |      1000 |            |   23000
(14 行)

一次処理

row_number() over()と年月を抽出した行を追加します。

select
	row_number() over()
	,to_char(dates,'yyyymm') as year_month
	,*
from
	account

結果

 row_number | year_month |   dates    | in_amount | out_amount | balance
------------+------------+------------+-----------+------------+---------
          1 | 201910     | 2019-10-01 |       100 |            |   10100
          2 | 201910     | 2019-10-02 |           |       1000 |    9100
          3 | 201910     | 2019-10-02 |      3000 |            |   12100
          4 | 201910     | 2019-10-02 |     10000 |            |   22100
          5 | 201910     | 2019-10-20 |           |       1000 |   21100
          6 | 201910     | 2019-10-24 |      2000 |            |   23100
          7 | 201911     | 2019-11-03 |           |       3000 |   20100
          8 | 201911     | 2019-11-06 |           |       4000 |   16100
          9 | 201911     | 2019-11-15 |     30000 |            |   46100
         10 | 201911     | 2019-11-15 |           |       2000 |   44100
         11 | 201911     | 2019-11-25 |           |        100 |   44000
         12 | 201911     | 2019-11-26 |      3000 |            |   47000
         13 | 201911     | 2019-11-27 |           |      25000 |   22000
         14 | 201911     | 2019-11-28 |      1000 |            |   23000
(14 行)

   

二次処理

一次処理で得られたyear_monthごとにgroup_byし、row_numberが最大のレコードだけ残すクエリと一次処理の結果を結合します。

SQLとしては以下の通り。

select
	t1.year_month
	,dates
	,balance
from
	(
	select
		max(row_number) as row_number
		,year_month
	from
		(
		select
			row_number() over() as row_number
			,to_char(dates,'yyyymm') as year_month
			,*
		from
			account
		) as t0
	group by year_month
	) as t1
left join
	(
	select
	row_number() over() as row_number
	,to_char(dates,'yyyymm') as year_month
	,*
from
	account
	) as t2
using(row_number)

結果

 year_month |   dates    | balance
------------+------------+---------
 201910     | 2019-10-24 |   23100
 201911     | 2019-11-28 |   23000
(2 行)

月末の値だけ取り出すことができました。

-PostgreSQL
-,

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

PostgreSQL 同一日内の入出金データのグラフ化

最近は自分の口座の残高や入出金履歴などは銀行のオンランサイトのサービスにログインしてインターネット経由で確認することができるようになっています。今銀行サイトからダウンロードした口座の入出金データをグラ …

【PostgreSQL】縦持ちデータを横持ち変換し前年同月との比較をする

Contents1 目的2 元データ3 横持にするSQL4 結果5 まとめ 目的 システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年 …

PostgreSQL 累積値を計算するSQL

Contents1 目的2 Step1 : テーブル用意3 Step2 : 累積値を計算するSQL 目的 ある値の累積値をSQLで計算する。 Step1 : テーブル用意 以下のようなテーブルを用意し …

横持ちデータを縦持ちへ変換するSQL

Contents1 目的2 環境3 使用データ4 縦持ちにするSQL 目的 BIなどでグラフを書く場合、データが横持になっているとうまくグラフ化できない、といったケースがあります。ということでSQLで …

WindowsにPostgreSQLをインストールしてサーバー化

Contents1 目的2 環境3 PostgreSQLインストール4 外部接続許可設定5 Firewall開放6 接続テスト7 まとめ 目的 自分の身の回りのデータ(特に口座残高などのお金関連)を管 …

言語切り替え

カテゴリー