例題設定
例題として個人の銀行口座の入出金記録の以下の構造のテーブルを考える。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> DESCRIBE account; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | date | date | YES | | NULL | | | branch | varchar(3) | YES | | NULL | | | transaction | varchar(20) | YES | | NULL | | | category | varchar(10) | YES | | NULL | | | withdrawal | int | YES | | NULL | | | deposit | int | YES | | NULL | | | balance | int | YES | | NULL | | | partner | varchar(100) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 9 rows in set (0.00 sec) |
ここでidは入出金記録の順番に付されていて、変更されないものとする。すなわち、日・月・年などの単位でグルーピングしたとき、idが最も大きいレコードのbalanceがその期の残高となっている。
合計計算のみの場合
月ごとの合計を計算するだけの場合、年月でグルーピングしてSUM関数を使う。
|
1 2 3 4 5 6 7 |
SELECT MAX(DATE_FORMAT(date, '%Y年%m月')) AS 年月, SUM(withdrawal) AS 月別出金, SUM(deposit) AS 月別入金 FROM account GROUP BY DATE_FORMAT(date, '%Y%m') ; |
要点は以下の通り。
GROUP BY句で、yyyymm形式でフォーマットされた年月単位でグルーピング- グループごとにSUM関数で出金額と入金額を合計
- 年月表示はGROUP BY句のフォーマットと異なってもよい
- ただしグルーピングされた複数レコード中のどのレコードの
dateを使うかを明示するため、MAX関数を使っている。- こうしないと(オプション指定なしでは)エラーが発生する
各月の残高も含める場合
月ごとの最終取引の抽出
各月の残高がデータに含まれていて、取引がidの昇順であることがわかっている場合、月ごとにグルーピングされたレコードのうちidが最大のレコードがその月の最後の取引になり、そのbalanceが月の残高になる。
そこで、まず各月の最終取引のidを取得する。
|
1 2 3 4 5 |
SELECT MAX(id) AS max_id FROM account GROUP BY DATE_FORMAT(date, '%Y%m') ; |
各月最終取引のレコード取得と残高表示
最終取引のidに一致するレコードのみ元のテーブルから取り出すため、INNER JOINを使う。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT id, DATE_FORMAT(date, '%Y年%m月') AS 年月, balance AS 月末残高 FROM mufj INNER JOIN ( SELECT MAX(id) AS max_id FROM mufj GROUP BY DATE_FORMAT(date, '%Y%m') ) AS monthly_final_transaction ON id = max_id ; |
要点は以下の通り。
- 最終取引を抽出したクエリーをサブクエリ―として、その
idと等しいレコードのみを元のテーブルから抽出している - サブクエリ―にはエイリアスが必須
- 抽出された最終取引の
balanceを月末残高として表示 SELECT句のidは確認用で、表示目的としては不要
月ごとの入出金額を追加
上記のクエリーに、グルーピングされた月ごとの入出金額を追加する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT DATE_FORMAT(date, '%Y年%m月') AS 年月, monthly_withdrawal AS 出金額, monthly_deposit AS 入金額, balance AS 月末残高 FROM mufj INNER JOIN ( SELECT MAX(id) AS max_id, SUM(withdrawal) AS monthly_withdrawal, SUM(deposit) AS monthly_deposit FROM mufj GROUP BY DATE_FORMAT(date, '%Y%m') ) AS monthly_final_transaction ON id = max_id ; |
要点は以下の通り。
- 月ごとにグルーピングしているサブクエリ―で、
SUM関数によって月単位の出金・入金額を計算 - それらの値をエイリアスを使って元のテーブルで表示
このクエリーのチェックは、前月残高に当月の入出金額を加減して当月残高となることで確認できる。