PostgreSQL「ERROR: division by zero」の原因と対処

-- 整数／numeric はエラー
SELECT 1/0;                    -- ERROR: division by zero
SELECT 1::numeric/0::numeric;  -- ERROR: division by zero

-- 浮動小数は Infinity/NaN が返る（後工程で扱いに注意）
SELECT 1.0/0.0;  -- Infinity
SELECT 0.0/0.0;  -- NaN

-- 分母が 0 のときは NULL を返し、エラーを回避
SELECT sales::numeric / NULLIF(visits, 0) AS conv_rate
FROM metrics;

-- パーセント表示（NULL のまま扱う）
SELECT 100.0 * sales / NULLIF(visits, 0) AS conv_pct
FROM metrics;

-- 分母が 0 のとき 0% とみなす例
SELECT CASE WHEN visits = 0 THEN 0
ELSE 100.0 * sales / visits END AS conv_pct
FROM metrics;

-- 0/0 を「未定義（NULL）」にしたい場合
SELECT CASE WHEN visits = 0 THEN NULL
ELSE sales::numeric / visits END AS conv_rate
FROM metrics;

-- ✅ 典型の安全形：分子・分母を集計してから 1 回だけ割る
SELECT
campaign_id,
SUM(sales)::numeric / NULLIF(SUM(visits), 0) AS conv_rate
FROM metrics
GROUP BY campaign_id;

-- ❌ 各行で割ってから平均（AVG(sales/visits)）はバイアスに注意

SELECT
campaign_id,
SUM(sales)::numeric
/ NULLIF(SUM(visits) FILTER (WHERE visits > 0), 0) AS conv_rate
FROM metrics
GROUP BY campaign_id;

SELECT
user_id,
order_id,
amount
, amount::numeric
/ NULLIF(SUM(amount) OVER (PARTITION BY user_id), 0) AS user_share
FROM orders;

-- float の結果を NULL に置換（Infinity/NaN を避ける）
SELECT NULLIF( (1.0/0.0)::text, 'Infinity')::float8; -- → NULL

-- 実用的には numeric に寄せる or 先に NULLIF で 0 を回避するのが定石
SELECT 1.0::numeric / NULLIF(0.0::numeric, 0.0);

-- ❌ 悪例：0 を代入してから割っている
SELECT sales / COALESCE(visits, 0) FROM metrics;  -- visits が NULL→0 なら division by zero

-- ✅ 良例：割り算の結果に対してデフォルトを当てる
SELECT COALESCE(sales / NULLIF(visits, 0), 0) FROM metrics; -- 0% とする方針ならこちら

-- 分母が 0 の行を集計対象から外してしまう
SELECT AVG(100.0 * sales/visits)
FROM metrics
WHERE visits <> 0;

-- 行は残したまま値だけ NULL に（レポートで NULLS LAST などが使える）
SELECT 100.0 * sales/NULLIF(visits,0) AS conv_pct
FROM metrics
ORDER BY conv_pct NULLS LAST;

-- 明示的に numeric へ（整数同士の割り算で 0 になっても、0/0 以外はエラーではないが精度が落ちる）
SELECT ROUND( 100.0 * sales::numeric / NULLIF(visits,0), 2 ) AS conv_pct
FROM metrics;

-- 0 を NULL にする安全割り算
CREATE OR REPLACE FUNCTION safe_div(num numeric, den numeric)
RETURNS numeric LANGUAGE sql IMMUTABLE AS $$
SELECT num / NULLIF(den, 0)
$$;

-- 0 を 0% とみなす版（ポリシーがそうなら）
CREATE OR REPLACE FUNCTION safe_pct(num numeric, den numeric)
RETURNS numeric LANGUAGE sql IMMUTABLE AS $$
SELECT COALESCE(100.0 * num / NULLIF(den, 0), 0)
$$;

-- 使用例
SELECT safe_div(sales, visits), safe_pct(sales, visits) FROM metrics;

CREATE TABLE daily_metrics(
day date PRIMARY KEY,
sales  integer NOT NULL,
visits integer NOT NULL,
CHECK (visits >= 0)  -- 0 を許容するかは業務要件次第
);

-- 「必ず >0」を保証したい列なら
ALTER TABLE daily_metrics
ADD CONSTRAINT visits_positive CHECK (visits > 0);

-- 取り込み時に安全化して格納
INSERT INTO fact_metrics(day, sales, visits, conv_rate)
SELECT day, sales, visits, sales::numeric/NULLIF(visits,0)
FROM staging_metrics;

-- マテリアライズドビューで再計算
CREATE MATERIALIZED VIEW mv_conv AS
SELECT day, 100.0*sales/NULLIF(visits,0) AS conv_pct
FROM fact_metrics;

-- 分母候補が 0 の行を先に洗い出し
SELECT * FROM metrics WHERE visits = 0 LIMIT 50;

-- 実行計画で式の位置を把握
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT 100.0 * sales / NULLIF(visits,0) FROM metrics;

-- ログに式エラーを出す（開発環境限定）
ALTER SYSTEM SET client_min_messages = 'log';
SELECT pg_reload_conf();

-- P1: x / y
--  → x::numeric / NULLIF(y, 0)

-- P2: 100*x / y（%表示）
--  → ROUND( 100.0 * x::numeric / NULLIF(y, 0), 2 )

-- P3: AVG(x/y)（行ごとの比率の平均）
--  → SUM(x)::numeric / NULLIF(SUM(y), 0)

-- P4: ウィンドウ share = x / SUM(x) OVER (…)
--  → x::numeric / NULLIF(SUM(x) OVER (…), 0)

-- P5: デフォルト 0% にしたい
--  → COALESCE(100.0 * x::numeric / NULLIF(y, 0), 0)

-- P6: float を使っていて Infinity/NaN を消したい
--  → NULLIF( (x::float8 / y::float8)::text, 'Infinity')::float8
--    ただし基本は最初から NULLIF(y,0) で回避