「売上の合計を出したい」 「カテゴリー別の集計がしたい」 「累計や移動合計を計算したい」
データベースで最もよく使う集計機能の一つが「合計（SUM）」です。一見シンプルに見えるSUM関数ですが、実は様々な使い方があり、使いこなせば複雑な分析も可能になります。
この記事では、PostgreSQLのSUM関数について、基本的な使い方から、条件付き合計、ウィンドウ関数を使った高度な集計、そしてパフォーマンス最適化まで、実例を交えながら徹底的に解説していきます。
SUM関数の基本
最もシンプルな合計計算
-- 売上テーブルの作成
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
quantity INTEGER,
unit_price DECIMAL(10, 2),
total_amount DECIMAL(10, 2),
sale_date DATE,
customer_id INTEGER
);
-- サンプルデータの挿入
INSERT INTO sales (product_name, category, quantity, unit_price, total_amount, sale_date, customer_id) VALUES
('ノートPC', '電子機器', 2, 80000, 160000, '2024-01-15', 1),
('マウス', '周辺機器', 5, 3000, 15000, '2024-01-16', 2),
('キーボード', '周辺機器', 3, 8000, 24000, '2024-01-17', 1),
('モニター', '電子機器', 1, 45000, 45000, '2024-01-18', 3),
('USBケーブル', '周辺機器', 10, 500, 5000, '2024-01-19', 2);
-- 全売上の合計を計算
SELECT SUM(total_amount) AS total_sales FROM sales;
-- 結果
-- total_sales
-- ------------
-- 249000.00
列の合計計算のバリエーション
-- 数量の合計
SELECT SUM(quantity) AS total_quantity FROM sales;
-- 計算式の合計（数量 × 単価）
SELECT SUM(quantity * unit_price) AS calculated_total FROM sales;
-- 複数の合計を同時に計算
SELECT
SUM(quantity) AS total_quantity,
SUM(total_amount) AS total_sales,
SUM(quantity * unit_price) AS calculated_total,
AVG(unit_price) AS average_price,
COUNT(*) AS transaction_count
FROM sales;
NULLの扱い
SUM関数はNULL値を自動的に無視します。
-- NULLを含むテーブルの例
CREATE TABLE expenses (
id SERIAL PRIMARY KEY,
expense_type VARCHAR(50),
amount DECIMAL(10, 2)
);
INSERT INTO expenses (expense_type, amount) VALUES
('交通費', 1500),
('会議費', NULL), -- NULLデータ
('消耗品', 3000),
('通信費', 5000),
('その他', NULL); -- NULLデータ
-- NULLは無視されて合計される
SELECT
SUM(amount) AS total_expenses,
COUNT(amount) AS count_non_null,
COUNT(*) AS count_all
FROM expenses;
-- 結果
-- total_expenses | count_non_null | count_all
-- ---------------+----------------+-----------
-- 9500.00 | 3 | 5
-- NULL を 0 として扱いたい場合
SELECT
SUM(COALESCE(amount, 0)) AS total_with_null_as_zero
FROM expenses;
GROUP BYを使った分類別合計
基本的なグループ化
-- カテゴリー別の売上合計
SELECT
category,
SUM(total_amount) AS category_total,
COUNT(*) AS item_count,
AVG(total_amount) AS average_sale
FROM sales
GROUP BY category
ORDER BY category_total DESC;
-- 結果
-- category | category_total | item_count | average_sale
-- ----------+----------------+------------+--------------
-- 電子機器 | 205000.00 | 2 | 102500.00
-- 周辺機器 | 44000.00 | 3 | 14666.67
複数列でのグループ化
-- 日付とカテゴリーでグループ化
SELECT
DATE_TRUNC('month', sale_date) AS month,
category,
SUM(total_amount) AS monthly_category_total,
SUM(quantity) AS total_quantity
FROM sales
GROUP BY DATE_TRUNC('month', sale_date), category
ORDER BY month, category;
-- 顧客別・カテゴリー別の売上
SELECT
customer_id,
category,
SUM(total_amount) AS customer_category_total,
COUNT(*) AS purchase_count
FROM sales
GROUP BY customer_id, category
ORDER BY customer_id, customer_category_total DESC;
HAVINGで合計値をフィルタリング
-- 合計売上が50000円以上のカテゴリーのみ表示
SELECT
category,
SUM(total_amount) AS category_total
FROM sales
GROUP BY category
HAVING SUM(total_amount) >= 50000
ORDER BY category_total DESC;
-- 平均単価が5000円以上で、かつ合計数量が5個以上
SELECT
category,
SUM(quantity) AS total_quantity,
AVG(unit_price) AS avg_price,
SUM(total_amount) AS total_sales
FROM sales
GROUP BY category
HAVING AVG(unit_price) >= 5000
AND SUM(quantity) >= 5;
条件付き合計（CASE文とFILTER）
CASE文を使った条件付き合計
-- 金額帯別の売上合計
SELECT
SUM(CASE WHEN total_amount < 10000 THEN total_amount ELSE 0 END) AS small_sales,
SUM(CASE WHEN total_amount BETWEEN 10000 AND 50000 THEN total_amount ELSE 0 END) AS medium_sales,
SUM(CASE WHEN total_amount > 50000 THEN total_amount ELSE 0 END) AS large_sales,
SUM(total_amount) AS total_sales
FROM sales;
-- 曜日別の売上合計
SELECT
SUM(CASE WHEN EXTRACT(DOW FROM sale_date) IN (0, 6) THEN total_amount ELSE 0 END) AS weekend_sales,
SUM(CASE WHEN EXTRACT(DOW FROM sale_date) NOT IN (0, 6) THEN total_amount ELSE 0 END) AS weekday_sales,
SUM(total_amount) AS total_sales
FROM sales;
FILTER句を使った条件付き合計（PostgreSQL 9.4以降）
-- FILTER句でより読みやすく条件付き合計
SELECT
SUM(total_amount) FILTER (WHERE category = '電子機器') AS electronics_total,
SUM(total_amount) FILTER (WHERE category = '周辺機器') AS accessories_total,
SUM(total_amount) FILTER (WHERE sale_date >= '2024-01-15') AS recent_sales,
SUM(total_amount) AS total_sales
FROM sales;
-- 複雑な条件での集計
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(total_amount) AS total,
SUM(total_amount) FILTER (WHERE customer_id = 1) AS customer1_total,
SUM(total_amount) FILTER (WHERE customer_id = 2) AS customer2_total,
SUM(total_amount) FILTER (WHERE customer_id = 3) AS customer3_total
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);
ウィンドウ関数で高度な合計計算
累計（累積合計）の計算
-- 日付順の売上累計
SELECT
sale_date,
product_name,
total_amount,
SUM(total_amount) OVER (ORDER BY sale_date) AS running_total,
SUM(total_amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_total
FROM sales
ORDER BY sale_date;
-- カテゴリー別の累計
SELECT
sale_date,
category,
product_name,
total_amount,
SUM(total_amount) OVER (PARTITION BY category ORDER BY sale_date) AS category_running_total
FROM sales
ORDER BY category, sale_date;
移動合計（Moving Sum）
-- 3日間の移動合計
SELECT
sale_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_sum_3days,
AVG(total_amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3days
FROM sales
ORDER BY sale_date;
-- 前後を含む移動合計
SELECT
sale_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS centered_moving_sum
FROM sales
ORDER BY sale_date;
パーティション内での合計と比率
-- カテゴリー内での売上比率
SELECT
product_name,
category,
total_amount,
SUM(total_amount) OVER (PARTITION BY category) AS category_total,
ROUND(
100.0 * total_amount / SUM(total_amount) OVER (PARTITION BY category),
2
) AS percentage_in_category,
ROUND(
100.0 * total_amount / SUM(total_amount) OVER (),
2
) AS percentage_of_total
FROM sales
ORDER BY category, total_amount DESC;
実践的な集計パターン
売上分析ダッシュボード
-- 包括的な売上分析
WITH sales_summary AS (
SELECT
DATE_TRUNC('month', sale_date) AS month,
category,
SUM(total_amount) AS monthly_total,
COUNT(*) AS transaction_count,
AVG(total_amount) AS avg_transaction
FROM sales
GROUP BY DATE_TRUNC('month', sale_date), category
)
SELECT
month,
category,
monthly_total,
transaction_count,
avg_transaction,
SUM(monthly_total) OVER (PARTITION BY category ORDER BY month) AS category_cumulative,
SUM(monthly_total) OVER (ORDER BY month) AS total_cumulative,
RANK() OVER (PARTITION BY month ORDER BY monthly_total DESC) AS category_rank
FROM sales_summary
ORDER BY month, category_rank;
前期比・前年比の計算
-- 前月比の売上比較
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(total_amount) AS total
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT
month,
total AS current_month,
LAG(total, 1) OVER (ORDER BY month) AS previous_month,
total - LAG(total, 1) OVER (ORDER BY month) AS month_over_month_change,
ROUND(
100.0 * (total - LAG(total, 1) OVER (ORDER BY month)) /
NULLIF(LAG(total, 1) OVER (ORDER BY month), 0),
2
) AS month_over_month_percent
FROM monthly_sales
ORDER BY month;
ピボットテーブル風の集計
-- 月別・カテゴリー別のクロス集計
SELECT
TO_CHAR(sale_date, 'YYYY-MM') AS month,
SUM(total_amount) FILTER (WHERE category = '電子機器') AS electronics,
SUM(total_amount) FILTER (WHERE category = '周辺機器') AS accessories,
SUM(total_amount) AS total
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY month;
-- 動的なピボット（crosstab関数を使用）
-- まず拡張機能を有効化
CREATE EXTENSION IF NOT EXISTS tablefunc;
-- crosstabを使った集計
SELECT * FROM crosstab(
'SELECT
customer_id::text,
category,
SUM(total_amount)
FROM sales
GROUP BY customer_id, category
ORDER BY 1, 2',
'SELECT DISTINCT category FROM sales ORDER BY 1'
) AS ct(
customer_id text,
electronics numeric,
accessories numeric
);
パフォーマンス最適化
インデックスを活用した高速化
-- 集計対象の列にインデックスを作成
CREATE INDEX idx_sales_date ON sales(sale_date);
CREATE INDEX idx_sales_category ON sales(category);
CREATE INDEX idx_sales_amount ON sales(total_amount);
-- 複合インデックスで更に高速化
CREATE INDEX idx_sales_category_date_amount
ON sales(category, sale_date, total_amount);
-- 部分インデックスで特定条件の集計を高速化
CREATE INDEX idx_sales_large_amount
ON sales(total_amount)
WHERE total_amount > 50000;
-- インデックスの効果を確認
EXPLAIN ANALYZE
SELECT category, SUM(total_amount)
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY category;
マテリアライズドビューで事前計算
-- 日別集計を事前計算
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
sale_date,
category,
SUM(total_amount) AS daily_total,
COUNT(*) AS transaction_count,
AVG(total_amount) AS avg_amount,
MIN(total_amount) AS min_amount,
MAX(total_amount) AS max_amount
FROM sales
GROUP BY sale_date, category;
-- インデックスを追加
CREATE INDEX idx_daily_summary_date
ON daily_sales_summary(sale_date);
-- 定期的にリフレッシュ
REFRESH MATERIALIZED VIEW daily_sales_summary;
-- 高速な集計クエリ
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(daily_total) AS monthly_total
FROM daily_sales_summary
GROUP BY DATE_TRUNC('month', sale_date);
パーティショニングで大規模データに対応
-- 月単位でパーティション分割
CREATE TABLE sales_partitioned (
id SERIAL,
product_name VARCHAR(100),
category VARCHAR(50),
quantity INTEGER,
unit_price DECIMAL(10, 2),
total_amount DECIMAL(10, 2),
sale_date DATE,
customer_id INTEGER
) PARTITION BY RANGE (sale_date);
-- 月別パーティションを作成
CREATE TABLE sales_2024_01 PARTITION OF sales_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE sales_2024_02 PARTITION OF sales_partitioned
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- パーティションごとの集計が高速
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(total_amount) AS total
FROM sales_partitioned
WHERE sale_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', sale_date);
並列処理の活用
-- 並列処理の設定を確認
SHOW max_parallel_workers_per_gather;
-- セッションレベルで並列度を上げる
SET max_parallel_workers_per_gather = 4;
-- 大規模テーブルでの合計（自動的に並列化）
SELECT
category,
SUM(total_amount) AS total,
COUNT(*) AS count
FROM large_sales_table
GROUP BY category;
-- 実行計画で並列処理を確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT SUM(total_amount)
FROM large_sales_table;
特殊なケースの合計計算
JSON/JSONBデータの合計
-- JSONBカラムを持つテーブル
CREATE TABLE json_sales (
id SERIAL PRIMARY KEY,
sale_data JSONB
);
INSERT INTO json_sales (sale_data) VALUES
('{"product": "PC", "amount": 100000, "quantity": 2}'),
('{"product": "Mouse", "amount": 3000, "quantity": 5}'),
('{"product": "Keyboard", "amount": 8000, "quantity": 3}');
-- JSON内の値を合計
SELECT
SUM((sale_data->>'amount')::DECIMAL) AS total_amount,
SUM((sale_data->>'quantity')::INTEGER) AS total_quantity
FROM json_sales;
-- JSON配列の合計
CREATE TABLE array_sales (
id SERIAL PRIMARY KEY,
amounts JSONB
);
INSERT INTO array_sales (amounts) VALUES
('[1000, 2000, 3000]'),
('[4000, 5000]'),
('[6000, 7000, 8000]');
-- JSON配列内の値を合計
SELECT
id,
(SELECT SUM(value::DECIMAL) FROM jsonb_array_elements_text(amounts) AS value) AS array_sum
FROM array_sales;
配列型の合計
-- 配列カラムを持つテーブル
CREATE TABLE array_data (
id SERIAL PRIMARY KEY,
values INTEGER[]
);
INSERT INTO array_data (values) VALUES
(ARRAY[10, 20, 30]),
(ARRAY[40, 50]),
(ARRAY[60, 70, 80, 90]);
-- 配列要素の合計
SELECT
id,
values,
(SELECT SUM(unnest) FROM unnest(values)) AS array_sum
FROM array_data;
-- 全配列の総合計
SELECT
SUM(value) AS total_sum
FROM array_data, unnest(values) AS value;
時間型データの合計
-- 作業時間の合計
CREATE TABLE work_hours (
id SERIAL PRIMARY KEY,
employee_id INTEGER,
work_date DATE,
hours_worked INTERVAL
);
INSERT INTO work_hours (employee_id, work_date, hours_worked) VALUES
(1, '2024-01-15', '8 hours 30 minutes'),
(1, '2024-01-16', '7 hours 45 minutes'),
(2, '2024-01-15', '9 hours'),
(2, '2024-01-16', '8 hours 15 minutes');
-- 時間の合計
SELECT
employee_id,
SUM(hours_worked) AS total_hours,
EXTRACT(EPOCH FROM SUM(hours_worked))/3600 AS total_hours_decimal
FROM work_hours
GROUP BY employee_id;
よくあるエラーと対処法
エラー1：GROUP BY句が必要
-- エラーになる例
SELECT category, SUM(total_amount) FROM sales;
-- ERROR: column "sales.category" must appear in the GROUP BY clause
-- 正しい書き方
SELECT category, SUM(total_amount)
FROM sales
GROUP BY category;
エラー2：集計関数のネスト
-- エラーになる例
SELECT SUM(AVG(total_amount)) FROM sales;
-- ERROR: aggregate function calls cannot be nested
-- 正しい書き方（サブクエリを使用）
SELECT SUM(avg_amount)
FROM (
SELECT AVG(total_amount) AS avg_amount
FROM sales
GROUP BY category
) AS category_averages;
エラー3：WHERE句での集計関数
-- エラーになる例
SELECT * FROM sales WHERE SUM(total_amount) > 100000;
-- ERROR: aggregate functions are not allowed in WHERE
-- 正しい書き方（HAVINGを使用）
SELECT category, SUM(total_amount) AS total
FROM sales
GROUP BY category
HAVING SUM(total_amount) > 100000;
ベストプラクティス
1. 適切なデータ型を使用
-- 金額には DECIMAL/NUMERIC を使用
CREATE TABLE financial_data (
amount DECIMAL(15, 2), -- 正確な計算が可能
-- amount FLOAT, -- 避ける（浮動小数点誤差）
);
-- 整数の合計は BIGINT にキャスト
SELECT SUM(quantity)::BIGINT FROM sales;
2. NULL処理を明示的に
-- NULL を 0 として扱う
SELECT
SUM(COALESCE(amount, 0)) AS total_with_null_as_zero,
SUM(amount) AS total_excluding_null
FROM expenses;
-- NULL の件数も把握
SELECT
COUNT(*) AS total_rows,
COUNT(amount) AS non_null_rows,
COUNT(*) - COUNT(amount) AS null_rows
FROM expenses;
3. パフォーマンスを意識
-- 不要なデータは事前にフィルタリング
SELECT SUM(total_amount)
FROM sales
WHERE sale_date >= '2024-01-01' -- インデックスが効く
AND category = '電子機器';
-- 大量データの場合は概算も検討
SELECT
category,
COUNT(*) * AVG(total_amount) AS estimated_total -- 高速
-- SUM(total_amount) AS exact_total -- 低速
FROM large_table
GROUP BY category;
まとめ：SUM関数を使いこなして強力な分析を
PostgreSQLのSUM関数について、基本から応用まで解説してきました。
重要なポイント：
- 基本的なSUMから条件付き合計まで段階的に理解
- GROUP BYと組み合わせて多次元分析
- ウィンドウ関数で累計や移動合計を計算
- FILTER句やCASE文で柔軟な集計
- インデックスやマテリアライズドビューで高速化
SUM関数は単純な合計だけでなく、ビジネス分析に必要な様々な集計が可能です。この記事で紹介したテクニックを組み合わせることで、複雑な要求にも対応できるようになります。
次のステップ：
- 実際のデータで様々なパターンを試す
- パフォーマンスを測定して最適化
- ビジネス要件に合わせた集計ロジックを構築
正確で高速な集計処理を実現して、データドリブンな意思決定を支援しましょう！
コメント