【PostgreSQL】合計がNULLの場合に0で返す方法
概要
PostgreSQLで、SUMの結果がNULLになる場合に0で返す方法として、以下の2パターンが考えられるが、どちらが良いか検証した。
COALESCE(SUM(value), 0)
SUM(COALESCE(Stock, 0))
結論
集約する前の時点で0件の場合を考慮すると、COALESCE(SUM(value), 0)の方がいい。
(ケースバイケースかもですが…)
検証方法
実行SQL
code:example.sql
-- INIT database
CREATE TABLE Product (
ProductID SERIAL PRIMARY KEY,
Name VARCHAR(100),
Kbn VARCHAR(3),
Stock bigint
);
INSERT INTO Product(Name, Kbn, Stock) VALUES('AZIHURAI', 'A', 400);
INSERT INTO Product(Name, Kbn, Stock) VALUES('IKAHURAI', 'A', NULL);
INSERT INTO Product(Name, Kbn, Stock) VALUES('WHINNA-', 'B', NULL);
INSERT INTO Product(Name, Kbn, Stock) VALUES('EBIHURAI', 'B', NULL);
-- QUERY database
SELECT COALESCE(SUM(Stock), 0) AS COALESCE_SUM_A FROM Product WHERE Kbn = 'A';
SELECT COALESCE(SUM(Stock), 0) AS COALESCE_SUM_B FROM Product WHERE Kbn = 'B';
SELECT COALESCE(SUM(Stock), 0) AS COALESCE_SUM_C FROM Product WHERE Kbn = 'C';
SELECT SUM(COALESCE(Stock, 0)) AS SUM_COALESCE_A FROM Product WHERE Kbn = 'A';
SELECT SUM(COALESCE(Stock, 0)) AS SUM_COALESCE_B FROM Product WHERE Kbn = 'B';
SELECT SUM(COALESCE(Stock, 0)) AS SUM_COALESCE_C FROM Product WHERE Kbn = 'C';
実行結果
table:coalesce_sum_a
coalesce_sum_a
400
table:coalesce_sum_b
coalesce_sum_b
0
table:coalesce_sum_c
coalesce_sum_c
0
table:sum_coalesce_a
sum_coalesce_a
700
table:sum_coalesce_b
sum_coalesce_b
0
table:sum_coalesce_c
sum_coalesce_c
参考