PostgreSQL 0をNULLに変換する完全ガイド – NULLIF活用から実践テクニックまで

「0で除算してエラーになってしまう…」

「平均計算で0を除外したいけど、どうすれば？」

「0は『値なし』の意味で使われているのでNULLにしたい」

「レガシーシステムの0をNULLとして扱いたい」

こんな場面で必要になるのが0をNULLに変換する処理です！

実は、データベース設計において0とNULLは全く異なる意味を持ちます。0は「ゼロという値がある」状態、NULLは「値が存在しない」状態です。

この記事では、PostgreSQLで0をNULLに変換する様々な方法と、実務で役立つ活用パターンを詳しく解説していきます！

0とNULLの違いを理解しよう

0とNULLの重要な違い

-- 0とNULLの動作の違い
SELECT 
    100 / 0,        -- エラー: division by zero
    100 / NULL,     -- 結果: NULL（エラーにならない）
    0 + 100,        -- 結果: 100
    NULL + 100,     -- 結果: NULL
    AVG(0),         -- 結果: 0（0を含めて計算）
    AVG(NULL);      -- 結果: NULL（NULLは無視される）

-- 集計での違い
CREATE TEMP TABLE test_values (value INTEGER);
INSERT INTO test_values VALUES (10), (20), (0), (30);

SELECT 
    AVG(value) as avg_with_zero,           -- 結果: 15（0を含む）
    AVG(NULLIF(value, 0)) as avg_no_zero;  -- 結果: 20（0を除外）

いつ0をNULLに変換すべき？

変換が必要な場面：

• 0除算を回避したい
• 集計計算で0を除外したい
• 0が「データなし」を意味する場合
• レガシーデータの正規化

NULLIF関数 – 最もシンプルな変換方法

NULLIF関数の基本

-- 基本構文
NULLIF(値1, 値2)  -- 値1と値2が等しい場合NULLを返す

-- 0をNULLに変換
SELECT NULLIF(0, 0);        -- 結果: NULL
SELECT NULLIF(100, 0);      -- 結果: 100
SELECT NULLIF(0, 1);        -- 結果: 0

-- カラムに適用
SELECT 
    price,
    quantity,
    NULLIF(quantity, 0) as quantity_null_if_zero
FROM products;

実践的な使用例

-- 売上テーブルでの活用
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    revenue DECIMAL(10,2),
    quantity INTEGER,
    returns INTEGER DEFAULT 0
);

-- 単価計算（0除算エラー回避）
SELECT 
    product_name,
    revenue,
    quantity,
    revenue / NULLIF(quantity, 0) as unit_price,
    COALESCE(revenue / NULLIF(quantity, 0), 0) as unit_price_safe
FROM sales;

-- 返品率計算
SELECT 
    product_name,
    quantity,
    returns,
    ROUND(
        returns::NUMERIC * 100.0 / NULLIF(quantity, 0), 
        2
    ) as return_rate_percent
FROM sales;

-- 0をNULLにしてから平均を計算
SELECT 
    AVG(NULLIF(returns, 0)) as avg_returns_excluding_zero,
    AVG(returns) as avg_returns_including_zero
FROM sales;

CASE文を使った条件付き変換

より柔軟な変換パターン

-- CASE文での0→NULL変換
SELECT 
    value,
    CASE 
        WHEN value = 0 THEN NULL
        ELSE value
    END as value_null_if_zero
FROM data_table;

-- 複数の値をNULLに変換
SELECT 
    score,
    CASE 
        WHEN score IN (0, -1, -999) THEN NULL  -- 0, -1, -999を無効値として扱う
        ELSE score
    END as cleaned_score
FROM exam_results;

-- 条件付き変換
SELECT 
    amount,
    CASE 
        WHEN amount = 0 AND category = 'revenue' THEN NULL  -- 収益の0のみNULLに
        WHEN amount = 0 AND category = 'expense' THEN 0      -- 経費の0はそのまま
        ELSE amount
    END as adjusted_amount
FROM transactions;

複雑な条件での活用

-- 範囲指定での変換
SELECT 
    temperature,
    CASE 
        WHEN temperature BETWEEN -0.01 AND 0.01 THEN NULL  -- ほぼ0の値をNULLに
        ELSE temperature
    END as temperature_adjusted
FROM sensor_data;

-- 文脈に応じた変換
SELECT 
    product_id,
    stock_quantity,
    CASE 
        WHEN stock_quantity = 0 AND discontinued = true THEN NULL  -- 廃番商品の0
        WHEN stock_quantity = 0 AND on_order > 0 THEN 0           -- 発注中なので0維持
        WHEN stock_quantity = 0 THEN NULL                         -- その他の0
        ELSE stock_quantity
    END as effective_stock
FROM inventory;

UPDATE文での0→NULL変換

既存データの一括変換

-- シンプルな一括更新
UPDATE products
SET quantity = NULLIF(quantity, 0)
WHERE category = 'discontinued';

-- 条件付き更新
UPDATE sales_data
SET 
    revenue = NULLIF(revenue, 0),
    profit = CASE 
        WHEN profit = 0 AND revenue > 0 THEN NULL  -- 売上があるのに利益0は異常
        ELSE profit
    END
WHERE year = 2024;

-- 複数カラムの同時更新
UPDATE metrics
SET 
    clicks = NULLIF(clicks, 0),
    impressions = NULLIF(impressions, 0),
    conversions = NULLIF(conversions, 0),
    -- CTR再計算（0除算対策済み）
    ctr = clicks::NUMERIC / NULLIF(impressions, 0),
    -- CVR再計算
    cvr = conversions::NUMERIC / NULLIF(clicks, 0)
WHERE campaign_id = 100;

データクリーニング

-- レガシーデータのクリーニング
BEGIN;

-- バックアップ作成
CREATE TABLE customer_data_backup AS 
SELECT * FROM customer_data;

-- 0を意味のあるNULLに変換
UPDATE customer_data
SET 
    phone = CASE 
        WHEN phone = '0' OR phone = '00000000' THEN NULL
        ELSE phone
    END,
    age = NULLIF(age, 0),  -- 年齢0は入力ミス
    income = CASE 
        WHEN income = 0 AND employment_status = 'employed' THEN NULL  -- 就業者で収入0は異常
        ELSE income
    END,
    last_purchase_amount = NULLIF(last_purchase_amount, 0)
WHERE updated_at IS NULL;  -- 未処理のレコードのみ

-- 結果確認
SELECT 
    COUNT(*) as total_records,
    COUNT(phone) as valid_phones,
    COUNT(age) as valid_ages,
    COUNT(income) as valid_incomes
FROM customer_data;

COMMIT;

計算処理での活用

安全な除算処理

-- 各種比率計算での0除算対策
CREATE OR REPLACE FUNCTION safe_divide(
    numerator NUMERIC, 
    denominator NUMERIC,
    default_value NUMERIC DEFAULT NULL
) RETURNS NUMERIC AS $$
BEGIN
    RETURN COALESCE(numerator / NULLIF(denominator, 0), default_value);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例
SELECT 
    product_name,
    revenue,
    quantity,
    safe_divide(revenue, quantity, 0) as unit_price,
    safe_divide(profit, revenue, 0) * 100 as profit_margin_percent,
    safe_divide(returns, shipped, 0) * 100 as return_rate_percent
FROM sales_summary;

-- パフォーマンス指標の計算
SELECT 
    campaign_name,
    impressions,
    clicks,
    conversions,
    cost,
    -- CTR（クリック率）
    ROUND(clicks::NUMERIC * 100 / NULLIF(impressions, 0), 2) as ctr,
    -- CVR（コンバージョン率）
    ROUND(conversions::NUMERIC * 100 / NULLIF(clicks, 0), 2) as cvr,
    -- CPC（クリック単価）
    ROUND(cost / NULLIF(clicks, 0), 2) as cpc,
    -- CPA（獲得単価）
    ROUND(cost / NULLIF(conversions, 0), 2) as cpa
FROM campaign_metrics;

集計関数での活用

-- 0を除外した集計
CREATE TABLE daily_metrics (
    date DATE,
    page_views INTEGER,
    unique_visitors INTEGER,
    bounce_rate DECIMAL(5,2),
    session_duration INTEGER  -- 秒単位、0は計測エラー
);

-- 0を除外した平均計算
SELECT 
    DATE_TRUNC('month', date) as month,
    -- 通常の平均（0を含む）
    AVG(session_duration) as avg_duration_with_zero,
    -- 0を除外した平均
    AVG(NULLIF(session_duration, 0)) as avg_duration_valid,
    -- 0の件数
    COUNT(*) - COUNT(NULLIF(session_duration, 0)) as zero_count,
    -- 有効データの割合
    COUNT(NULLIF(session_duration, 0))::NUMERIC / COUNT(*) * 100 as valid_data_percent
FROM daily_metrics
GROUP BY DATE_TRUNC('month', date);

-- 中央値計算での0除外
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY NULLIF(session_duration, 0)) as median_duration,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY NULLIF(session_duration, 0)) as q1_duration,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY NULLIF(session_duration, 0)) as q3_duration
FROM daily_metrics
WHERE date >= CURRENT_DATE - INTERVAL '30 days';

文字列やその他のデータ型での変換

文字列の’0’をNULLに

-- 文字列の'0'や空文字をNULLに変換
SELECT 
    code,
    NULLIF(code, '0') as code_null_if_zero,
    NULLIF(TRIM(code), '') as code_null_if_empty,
    NULLIF(NULLIF(TRIM(code), ''), '0') as code_cleaned
FROM legacy_data;

-- 複数パターンの無効値を処理
CREATE OR REPLACE FUNCTION clean_string(input TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN CASE 
        WHEN input IN ('0', '00', 'N/A', 'NA', '-', '') THEN NULL
        WHEN TRIM(input) = '' THEN NULL
        ELSE TRIM(input)
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 電話番号のクリーニング
UPDATE contacts
SET 
    phone = NULLIF(REGEXP_REPLACE(phone, '[^0-9]', '', 'g'), ''),
    phone = CASE 
        WHEN LENGTH(phone) < 10 THEN NULL  -- 短すぎる番号
        WHEN phone = REPEAT('0', LENGTH(phone)) THEN NULL  -- 全部0
        ELSE phone
    END;

日付での特殊値処理

-- 特殊な日付値をNULLに変換
SELECT 
    event_date,
    CASE 
        WHEN event_date = '1900-01-01' THEN NULL  -- デフォルト値
        WHEN event_date = '0001-01-01' THEN NULL  -- 無効値
        WHEN event_date < '1970-01-01' THEN NULL  -- 明らかに異常
        ELSE event_date
    END as cleaned_date
FROM events;

-- タイムスタンプの0（UNIX epoch）処理
SELECT 
    created_at,
    CASE 
        WHEN EXTRACT(EPOCH FROM created_at) = 0 THEN NULL
        ELSE created_at
    END as valid_timestamp
FROM records;

実践的な応用例

KPI計算での活用

-- ECサイトのKPIダッシュボード
WITH kpi_base AS (
    SELECT 
        DATE_TRUNC('day', order_date) as date,
        COUNT(DISTINCT session_id) as sessions,
        COUNT(DISTINCT user_id) as unique_users,
        COUNT(DISTINCT CASE WHEN order_id IS NOT NULL THEN session_id END) as converting_sessions,
        COUNT(order_id) as orders,
        SUM(order_amount) as revenue,
        SUM(CASE WHEN is_return THEN 1 ELSE 0 END) as returns
    FROM user_sessions
    LEFT JOIN orders USING (session_id)
    GROUP BY DATE_TRUNC('day', order_date)
)
SELECT 
    date,
    sessions,
    orders,
    revenue,
    -- コンバージョン率（0セッションの日は NULL）
    ROUND(converting_sessions::NUMERIC * 100.0 / NULLIF(sessions, 0), 2) as conversion_rate,
    -- 平均注文額（注文0の日は NULL）
    ROUND(revenue / NULLIF(orders, 0), 2) as avg_order_value,
    -- セッションあたり収益
    ROUND(revenue / NULLIF(sessions, 0), 2) as revenue_per_session,
    -- 返品率
    ROUND(returns::NUMERIC * 100.0 / NULLIF(orders, 0), 2) as return_rate,
    -- データ品質指標
    CASE 
        WHEN sessions = 0 THEN 'No Data'
        WHEN orders = 0 THEN 'No Orders'
        ELSE 'Valid'
    END as data_quality
FROM kpi_base
ORDER BY date DESC;

財務分析での活用

-- 財務比率の計算
CREATE OR REPLACE VIEW financial_ratios AS
SELECT 
    company_id,
    fiscal_year,
    -- 収益性指標
    ROUND(net_income::NUMERIC / NULLIF(revenue, 0) * 100, 2) as profit_margin,
    ROUND(net_income::NUMERIC / NULLIF(total_assets, 0) * 100, 2) as roa,
    ROUND(net_income::NUMERIC / NULLIF(equity, 0) * 100, 2) as roe,
    
    -- 効率性指標
    ROUND(revenue::NUMERIC / NULLIF(total_assets, 0), 2) as asset_turnover,
    ROUND(revenue::NUMERIC / NULLIF(inventory, 0), 2) as inventory_turnover,
    ROUND(365.0 / NULLIF(revenue::NUMERIC / NULLIF(accounts_receivable, 0), 0), 0) as days_sales_outstanding,
    
    -- 流動性指標
    ROUND(current_assets::NUMERIC / NULLIF(current_liabilities, 0), 2) as current_ratio,
    ROUND((current_assets - inventory)::NUMERIC / NULLIF(current_liabilities, 0), 2) as quick_ratio,
    
    -- レバレッジ指標
    ROUND(total_debt::NUMERIC / NULLIF(equity, 0), 2) as debt_to_equity,
    ROUND(total_debt::NUMERIC / NULLIF(total_assets, 0), 2) as debt_ratio,
    
    -- データ完全性チェック
    CASE 
        WHEN revenue = 0 OR revenue IS NULL THEN 'Missing Revenue'
        WHEN total_assets = 0 OR total_assets IS NULL THEN 'Missing Assets'
        WHEN equity <= 0 THEN 'Negative/Zero Equity'
        ELSE 'Complete'
    END as data_status
FROM financial_statements
WHERE fiscal_year >= EXTRACT(YEAR FROM CURRENT_DATE) - 5;

A/Bテスト分析

-- A/Bテストの結果分析
WITH test_results AS (
    SELECT 
        variant,
        COUNT(*) as users,
        COUNT(CASE WHEN converted THEN 1 END) as conversions,
        SUM(revenue) as total_revenue,
        AVG(session_duration) as avg_session_duration
    FROM ab_test_data
    WHERE test_id = 'TEST_001'
    GROUP BY variant
)
SELECT 
    variant,
    users,
    conversions,
    total_revenue,
    -- コンバージョン率
    ROUND(conversions::NUMERIC * 100.0 / NULLIF(users, 0), 2) as conversion_rate,
    -- ユーザーあたり収益
    ROUND(total_revenue / NULLIF(users, 0), 2) as revenue_per_user,
    -- コンバージョンあたり収益
    ROUND(total_revenue / NULLIF(conversions, 0), 2) as revenue_per_conversion,
    -- 統計的有意性の簡易チェック
    CASE 
        WHEN users < 100 THEN 'サンプル不足'
        WHEN conversions = 0 THEN 'コンバージョンなし'
        ELSE '分析可能'
    END as analysis_status
FROM test_results
ORDER BY conversion_rate DESC NULLS LAST;

パフォーマンス考慮事項

インデックスへの影響

-- NULLIFを使った場合のインデックス利用
EXPLAIN ANALYZE
SELECT * FROM sales
WHERE NULLIF(quantity, 0) > 10;  -- インデックスが効きにくい

-- より効率的な書き方
EXPLAIN ANALYZE
SELECT * FROM sales
WHERE quantity > 10;  -- インデックスが効く

-- 関数インデックスの作成
CREATE INDEX idx_quantity_nullif ON sales(NULLIF(quantity, 0));

まとめ – 0をNULLに変換して安全なデータ処理を！

PostgreSQLで0をNULLに変換する方法について、基本から応用まで解説してきました。

重要ポイント：

• NULLIF関数が最もシンプルで強力
• 0除算エラーの回避に必須
• 集計関数と組み合わせて0を除外
• CASE文でより複雑な条件にも対応
• 財務分析やKPI計算で頻繁に使用

使いどころ：

• 比率や割合の計算
• 平均値から0を除外
• レガシーデータのクリーニング
• KPIダッシュボードの作成
• エラー値の正規化

0とNULLを適切に使い分けることで、より正確で安全なデータ処理が実現できます。

ぜひ実際のプロジェクトで活用してください！