はじめに
仕事でPostgreSQLのDBに保存してあるデータの統計を取っていたときに、便利なSQLに出会ったので紹介しようと思います。
準備
以下のSQL文でセットアップします。
create table items
(
id integer not null primary key,
name varchar(50) not null,
category varchar(50),
stock integer
);
insert into items (id, name, category, stock)
values (1, 'りんご', 'フルーツ', 10),
(2, 'みかん', 'フルーツ', 20),
(3, 'なし', 'フルーツ', 30),
(4, 'レタス', '野菜', 40),
(5, 'トマト', '野菜', 50),
(6, 'キャベツ', '野菜', 60);
集約関数
よく目にする集約関数といえば以下のような使い方だと思います。
- カテゴリーごとの在庫総数を求めたい
select category, sum ( stock )
from items
group by category;
- カテゴリーごとの商品の種類を求めたい
select category, count ( * )
from items
group by category;
本記事で紹介する小技
本記事では、「filter関数」を紹介したいと思います。
filter関数の例
カテゴリー「フルーツ」に占める各フルーツの割合を出す
以下のSQLにより算出が可能です。
select
category as "カテゴリー",
sum ( stock ) as "カテゴリーの総数",
sum ( stock ) filter ( where name = 'りんご' ) as "りんごの総数",
round ( sum ( stock ) filter ( where name = 'りんご' )::decimal / sum ( stock ) * 100, 2 ) as "りんごの割合",
sum ( stock ) filter ( where name = 'みかん' ) as "みかんの総数",
round ( sum ( stock ) filter ( where name = 'みかん' )::decimal / sum ( stock ) * 100, 2 ) as "みかんの割合",
sum ( stock ) filter ( where name = 'なし' ) as "なしの総数",
round ( sum ( stock ) filter ( where name = 'なし' )::decimal / sum ( stock ) * 100, 2 ) as "なしの割合"
from items
where category = 'フルーツ'
group by category;
結果は以下のとおり。
| カテゴリー | カテゴリーの総数 | りんごの総数 | りんごの割合 | みかんの総数 | みかんの割合 | なしの総数 | なしの割合 |
|---|---|---|---|---|---|---|---|
| フルーツ | 60 | 10 | 16.67% | 20 | 33.33% | 30 | 50% |
集約関数と一緒に利用できる「filter関数」、統計を取るまで知らなかったのですが実は便利な関数でした。
補足
カテゴリー「フルーツ」に占める各フルーツの割合を出す
というお題の場合
select
category as "カテゴリー",
sum ( stock ) as "カテゴリーの総数",
sum ( case name when 'りんご' then stock else 0 end ) as "りんごの総数",
round ( sum ( case name when 'りんご' then stock else 0 end )::decimal / sum(stock) * 100, 2 ) as "りんごの割合",
sum ( case name when 'みかん' then stock else 0 end) as "みかんの総数",
round ( sum ( case name when 'みかん' then stock else 0 end )::decimal / sum(stock) * 100, 2 ) as "みかんの割合",
sum (case name when 'なし' then stock else 0 end) as "なしの総数",
round ( sum(case name when 'りんご' then stock else 0 end )::decimal / sum(stock) * 100, 2 ) as "りんごの割合"
from items
where category = 'フルーツ'
group by category;
という書き方も問題なく動作します。
結論
集約関数の中にcase-whenを書くことも可能ですが、SQLが冗長になります。
filter関数を利用すると、通常のSQLと同じ条件式の書き方ができるので他のSQLの流用ができます。
まとめると以下のようになります。
| 種類 | 可読性の面 | 冗長の面 | 複数条件 |
|---|---|---|---|
| case-whenを使う | Bad | Bad | Bad |
| filter関数を使う | Good | Good | Good |