2024
September
05
WITH句を使った最終行に合計行を追加する方法
postgreSQLで集計した後で最終行に各項目の合計値を出したい場合があると思います。
よくある方法としてUNION ALLを使って合計行を計算するというのがありますが、
同じようなSQLを複数回書く必要があり、複雑なSQLの場合手間だし、
メンテナンス性が悪いというのがあります。
そこで今回はWITHを使った合計行の出し方を紹介します。
※この記事はPostgreSQL 13.5 で動作確認してます。
WITH句とは
サブクエリを一時テーブルのように扱えるようになる機能です。
テーブル
売上日 | 商品 | 金額 | 個数 |
2024/01/01 | みかん | 100 | 2 |
2024/01/02 | りんご | 200 | 4 |
2024/01/02 | ぶどう | 300 | 3 |
2024/01/03 | ぶどう | 300 | 10 |
WITHを使わないソース
WITH句を使わないで日別集計を行う場合は下記のようになります。
SELECT
売上日::varchar
,SUM(金額 * 個数) AS 合計金額
,SUM(個数) AS 合計個数
FROM テーブル
GROUP BY 売上日
UNION ALL
SELECT
'合計'
,SUM(金額 * 個数) AS 合計金額
,SUM(個数) AS 合計個数
FROM テーブル
ORDER BY 売上日
--※合計行で文字列を入れるため売上日をvarcharに変換しています
WITH句を使ったソース
WITH tmp AS (
SELECT
売上日::varchar
,SUM(金額 * 個数) AS 合計金額
,SUM(個数) AS 合計個数
FROM テーブル
GROUP BY 売上日
)
SELECT * FROM tmp
UNION ALL
SELECT
'合計'
,SUM(合計金額)
,SUM(合計個数)
FROM tmp
ORDER BY 売上日
結果
売上日 | 合計金額 | 合計個数 |
2024-01-01 | 200 | 2 |
2024-01-02 | 1700 | 7 |
2024-01-03 | 3000 | 10 |
合計 | 4900 | 19 |
結果は同じですが、WITH句を使う場合は短く、サブクエリを2回書く必要がなくなってます。
まとめ
WITH句で一時テーブルを作ることによって複雑な検索結果に対して簡単に合計行を追加することができます。
また、WITH句を使うことによって修正したい時に少ない修正で済むようになるので間違った出力結果を出す可能性を少なくできます。
複雑なSQLをゴリゴリ書く場合はWITH句は何かと便利に使えるかと思います!
補足
他にも合計や小計が必要な場合はGROUPING SETS関数、CUBE関数、ROLLUP関数
辺りも便利に使うことができます。
このカテゴリの最新記事
2023.06.30
【Laravel】ページネーションの2ページ目以降にクエリパラメータを引き継がせる方法
2024.04.18
【Laravel】Laravelで作成されたディレクトリのpermissionについて
2024.05.24
【Laravel】DBテーブルの主キー名を変更する方法
2024.06.21
【Linux】特定のディレクトリ配下のみアクセスできるFTPユーザーを作成する