【SQL】計算完全ガイド｜算術演算子・数値関数・NULL計算の罠・型変換・条件付き計算・集計関数まで解説

-- orders テーブル（注文）
-- order_id | product | qty | unit_price | discount | tax_rate
--        1 | りんご  |   5 |        200 |       10 |     0.10
--        2 | みかん  |  12 |        150 |        0 |     0.10
--        3 | ぶどう  |   3 |        500 |       50 |     0.10
--        4 | メロン  |   1 |       3000 |     NULL |     0.10

-- employees テーブル（従業員）
-- id | name      | salary | bonus | department
--  1 | 田中 太郎 |  55000 |  5000 | 営業
--  2 | 鈴木 花子 |  70000 |  8000 | 開発
--  3 | 高橋 美咲 |  80000 | 10000 | 開発
--  4 | 伊藤 健二 |  52000 |  NULL | 人事

-- 小計（数量 × 単価）、割引後金額、税込金額を一度に計算
SELECT
    product                              AS 商品名,
    qty                                  AS 数量,
    unit_price                           AS 単価,
    qty * unit_price                     AS 小計,
    qty * unit_price - discount          AS 割引後,
    (qty * unit_price - discount) * (1 + tax_rate) AS 税込金額
FROM orders;

-- 結果:
-- 商品名  | 数量 | 単価 | 小計  | 割引後 | 税込金額
-- ------+------+------+-------+--------+----------
-- りんご  |   5  |  200 |  1000 |    990 |   1089.0
-- みかん  |  12  |  150 |  1800 |   1800 |   1980.0
-- ぶどう  |   3  |  500 |  1500 |   1450 |   1595.0
-- メロン  |   1  | 3000 |  3000 |   NULL |      NULL  ← discount が NULL

-- 偶数の order_id だけを取得
SELECT order_id, product FROM orders WHERE order_id % 2 = 0;
-- または: WHERE MOD(order_id, 2) = 0

-- ページネーションの総ページ数を計算
-- 総件数 7 件をページあたり 3 件表示する場合: CEIL(7/3.0) = 3
SELECT CEIL(COUNT(*) / 3.0) AS total_pages FROM orders;

-- 10個単位でのカート数確認（バラ（端数）の数量）
SELECT product, qty, MOD(qty, 10) AS remainder FROM orders;

-- NG: 整数どうしの除算（MySQL では小数が返るが RDBMS 依存）
SELECT 5 / 2;        -- MySQL: 2.5000  PostgreSQL: 2（切り捨て！）

-- OK: 一方を DECIMAL / FLOAT にキャストする
SELECT CAST(5 AS DECIMAL) / 2;   -- 2.5（すべての RDBMS で安全）
SELECT 5 / 2.0;                  -- 2.5（数値リテラルに小数点をつける）
SELECT 5 * 1.0 / 2;             -- 2.5（乗算で先に実数化する）

-- 実務例: 平均単価（整数割算に注意）
SELECT
    SUM(qty * unit_price)                        AS 総売上,
    COUNT(*)                                     AS 件数,
    CAST(SUM(qty * unit_price) AS DECIMAL) / COUNT(*) AS 平均単価
FROM orders;

-- discount が NULL のメロンは割引後も税込金額も NULL になる
SELECT
    product,
    unit_price - discount          AS 割引後,   -- 3000 - NULL = NULL
    unit_price * (1 + tax_rate)    AS 税込       -- 3000 * 1.1 = 3300（discount未使用なのでOK）
FROM orders
WHERE product = 'メロン';

-- salary + bonus でボーナスが NULL の場合も NULL になる
SELECT name, salary, bonus, salary + bonus AS 合計
FROM employees
WHERE name = '伊藤 健二';
-- → 合計: NULL（bonus が NULL のため）

-- COALESCE(値, NULL のときの代替値)
-- bonus が NULL なら 0 として計算する
SELECT
    name,
    salary,
    bonus,
    salary + COALESCE(bonus, 0)     AS 合計    -- NULL を 0 に置換
FROM employees;

-- 結果:
-- 氏名        | salary | bonus | 合計
-- ----------+--------+-------+-------
-- 田中 太郎  |  55000 |  5000 |  60000
-- 鈴木 花子  |  70000 |  8000 |  78000
-- 高橋 美咲  |  80000 | 10000 |  90000
-- 伊藤 健二  |  52000 |  NULL |  52000  ← COALESCE で 0 に変換

-- discount が NULL のとき 0 として割引計算
SELECT
    product,
    unit_price - COALESCE(discount, 0) AS 割引後単価
FROM orders;

-- 税込価格を 1 円単位に丸める（四捨五入・切り上げ・切り捨て）
SELECT
    product,
    unit_price * 1.1                     AS 税込（小数あり）,
    ROUND(unit_price * 1.1, 0)           AS 四捨五入,
    CEIL(unit_price * 1.1)               AS 切り上げ,
    FLOOR(unit_price * 1.1)              AS 切り捨て
FROM orders;

-- 結果:
-- 商品名 | 税込（小数あり）| 四捨五入 | 切り上げ | 切り捨て
-- りんご |          220.0 |      220 |      220 |      220
-- みかん |          165.0 |      165 |      165 |      165
-- ぶどう |          550.0 |      550 |      550 |      550

-- ページ数の計算（7件を3件ずつ表示する場合の総ページ数）
SELECT CEIL(COUNT(*) / 3.0) AS total_pages FROM orders;  -- 結果: 3

-- 差分の絶対値でソート（目標値との乖離が小さい順）
SELECT
    name, salary,
    ABS(salary - 60000) AS 目標との差
FROM employees
ORDER BY 目標との差;

-- ROUND: 四捨五入
SELECT ROUND(2.55, 1);    -- 2.6（銀行丸めの場合は 2.5 になる RDBMS もある）
SELECT ROUND(2.45, 1);    -- 2.5

-- TRUNCATE: 切り捨て（0 方向に向かう）
SELECT TRUNCATE(2.99, 1); -- 2.9（小数第2位以下を切り捨て）
SELECT TRUNCATE(-2.99, 1);-- -2.9（負の数も 0 方向に切り捨て）
-- MySQL固有: PostgreSQL では TRUNC(n, d)、SQL Server では ROUND(n, d, 1)

-- 注意: ROUND の「銀行丸め」
-- 一部 RDBMS（SQL Server など）は .5 を偶数方向に丸める（例: 2.5→2, 3.5→4）
-- 明示的な丸め方法を確認してから使うこと

-- 軽減税率対応: 食品は 8%、それ以外は 10%
SELECT
    product,
    unit_price,
    CASE category
        WHEN '食品'  THEN ROUND(unit_price * 1.08, 0)
        WHEN '酒類'  THEN ROUND(unit_price * 1.10, 0)
        ELSE              ROUND(unit_price * 1.10, 0)
    END AS 税込価格
FROM products;

-- 数量による割引率の分岐（CASE WHEN）
SELECT
    product,
    qty,
    unit_price,
    CASE
        WHEN qty >= 10 THEN unit_price * 0.85   -- 10個以上は15%引き
        WHEN qty >=  5 THEN unit_price * 0.90   -- 5個以上は10%引き
        ELSE                unit_price           -- 定価
    END AS 割引後単価,
    qty * CASE
        WHEN qty >= 10 THEN unit_price * 0.85
        WHEN qty >=  5 THEN unit_price * 0.90
        ELSE                unit_price
    END AS 割引後小計
FROM orders;

-- 部門ごとの給与合計をピボット表示
SELECT
    SUM(CASE WHEN department = '営業' THEN salary ELSE 0 END) AS 営業合計,
    SUM(CASE WHEN department = '開発' THEN salary ELSE 0 END) AS 開発合計,
    SUM(CASE WHEN department = '人事' THEN salary ELSE 0 END) AS 人事合計,
    SUM(salary)                                               AS 全体合計
FROM employees;

-- 給与レンジ別の人数カウント
SELECT
    CASE
        WHEN salary < 55000 THEN '〜54,999'
        WHEN salary < 70000 THEN '55,000〜69,999'
        ELSE                     '70,000〜'
    END AS 給与レンジ,
    COUNT(*) AS 人数
FROM employees
GROUP BY
    CASE
        WHEN salary < 55000 THEN '〜54,999'
        WHEN salary < 70000 THEN '55,000〜69,999'
        ELSE                     '70,000〜'
    END
ORDER BY MIN(salary);

-- SUM(式): 売上合計（数量 × 単価）
SELECT SUM(qty * unit_price) AS 売上合計 FROM orders;
-- → 1000 + 1800 + 1500 + 3000 = 7300

-- AVG(式): 割引後平均単価
SELECT AVG(unit_price - COALESCE(discount, 0)) AS 割引後平均単価 FROM orders;

-- 注文ごとの税込小計と全体合計
SELECT
    SUM(qty * unit_price)                         AS 税抜合計,
    SUM(ROUND(qty * unit_price * (1 + tax_rate))) AS 税込合計
FROM orders;

-- GROUP BY と組み合わせた部門別給与統計
SELECT
    department,
    COUNT(*)                        AS 人数,
    SUM(salary)                     AS 給与合計,
    ROUND(AVG(salary), 0)           AS 平均給与,
    MAX(salary) - MIN(salary)       AS 最大差
FROM employees
GROUP BY department
ORDER BY 給与合計 DESC;

-- 割引率（%）を計算
SELECT
    product,
    discount,
    unit_price,
    ROUND(discount / CAST(unit_price AS DECIMAL) * 100, 1) AS 割引率
FROM orders
WHERE discount > 0;

-- 粗利と粗利率
-- cost（原価）列がある場合
SELECT
    product,
    unit_price - cost                                       AS 粗利,
    ROUND((unit_price - cost) / CAST(unit_price AS DECIMAL) * 100, 1) AS 粗利率
FROM products;

-- 達成率（目標 vs 実績）
SELECT
    name,
    actual_sales,
    target_sales,
    ROUND(actual_sales / CAST(target_sales AS DECIMAL) * 100, 1) AS 達成率
FROM sales_records;

-- 日別売上の累計（PostgreSQL / MySQL 8.0+ / SQL Server）
SELECT
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date) AS 累計売上
FROM daily_summary
ORDER BY order_date;

-- 直近3日間の移動平均
SELECT
    order_date,
    daily_sales,
    ROUND(
        AVG(daily_sales) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
    0) AS 3日移動平均
FROM daily_summary
ORDER BY order_date;

-- 詳細は SUM 完全ガイドのウィンドウ関数セクションを参照
-- https://codingls.com/sql/2277/

-- 給与のランク付け（RANK と DENSE_RANK）
SELECT
    name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS ランク,      -- 同順位あり（1,1,3）
    DENSE_RANK() OVER (ORDER BY salary DESC) AS 密ランク,    -- 同順位後も連続（1,1,2）
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS 行番号       -- 必ず連番（1,2,3）
FROM employees;

-- 4 分位（上位 25%・50%・75%・100%）に分類
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS 分位
FROM employees;