PostgresSQLで比率をSQLをまとめました。
OVER句, PARTITION BY句が使えればPostgresSQL以外でもできると思います。(未確認)
準備
create_table.sql
--販売テーブル
CREATE TABLE product (
id BIGINT
,TYPE TEXT /* 色の分類 */
,volume INTEGER /* 販売数 */
,PRIMARY KEY(id)
);
INSERT INTO product VALUES
(1, 'red', 11)
,(2, 'red', 22)
,(3, 'blue', 33)
,(4, 'green', 0)
;
Tableの状態
| id |
type |
volume |
| 1 |
red |
11 |
| 2 |
red |
22 |
| 3 |
blue |
33 |
| 4 |
green |
0 |
比率算出SQL
全体に対する販売台数の割合
SELECT
id
,ROUND( volume::NUMERIC / SUM(volume) OVER (), 2) AS ratio /* 小数点以下2桁でまとめる */
FROM product
ORDER BY id;
SELECT結果
| id |
volume |
| 1 |
0.17 |
| 2 |
0.33 |
| 3 |
0.50 |
| 4 |
0.00 |
解説
-
SUM(volume) OVER ()は、全行のvolume合計数66です。
- NUMERICでキャストしているのは、整数÷整数でゼロになるのを防ぐためです。
色の分類に対する販売数の割合
SELECT
id
, CASE WHEN
volume = 0 THEN 0 /* ゼロ除算防止対応 */
ELSE
ROUND(volume::NUMERIC / SUM(volume) OVER (PARTITION BY type),2)
END AS ratio
FROM product
ORDER BY id;
SELECT結果
| id |
volume |
| 1 |
0.33 |
| 2 |
0.67 |
| 3 |
1.00 |
| 4 |
0.00 |
解説
-
SUM(volume) OVER (PARTITION BY type)は、typeごとの合計値です。
たとえばidが1の場合typeは'red'なので、redの合計値33が取得されます。
- CASE式を使っているのはゼロ除算対応のためです。typeがgreenの合計値は0なので、CASE式を使わないとゼロ除算してしまいます。
