【Oracle】TRUNC / FLOOR で小数点以下を切り捨てる方法｜桁指定・負の数・端数処理・実務パターンまで解説

-- 構文
TRUNC( 数値 [, 桁数] )

-- 整数への切り捨て（桁数省略 = 0）
SELECT TRUNC(123.456) AS result FROM dual;      -- 123
SELECT TRUNC(9.99)    AS result FROM dual;      -- 9
SELECT TRUNC(0.999)   AS result FROM dual;      -- 0

-- 小数第 1 位まで残す（桁数 = 1）
SELECT TRUNC(123.456, 1) AS result FROM dual;   -- 123.4

-- 小数第 2 位まで残す（桁数 = 2）
SELECT TRUNC(123.456, 2) AS result FROM dual;   -- 123.45

-- 小数第 3 位まで残す（桁数 = 3）
SELECT TRUNC(123.4567, 3) AS result FROM dual;  -- 123.456

-- 一の位を切り捨て（十の位まで残す）
SELECT TRUNC(1234.567, -1) AS result FROM dual;  -- 1230

-- 十の位を切り捨て（百の位まで残す）
SELECT TRUNC(1234.567, -2) AS result FROM dual;  -- 1200

-- 百の位を切り捨て（千の位まで残す）
SELECT TRUNC(1234.567, -3) AS result FROM dual;  -- 1000

-- 桁数が大きすぎる場合は 0 になる
SELECT TRUNC(1234.567, -4) AS result FROM dual;  -- 0

-- FLOOR は「引数以下の最大整数」を返す（床関数）
SELECT FLOOR(2.9)   AS result FROM dual;  -- 2
SELECT FLOOR(2.1)   AS result FROM dual;  -- 2
SELECT FLOOR(3.0)   AS result FROM dual;  -- 3（ちょうど整数ならそのまま）
SELECT FLOOR(0.999) AS result FROM dual;  -- 0

SELECT
    TRUNC(2.7) AS trunc_pos,   -- 2（0方向に切り捨て）
    FLOOR(2.7) AS floor_pos    -- 2（-∞方向の最大整数）
FROM dual;
-- 正の数: どちらも同じ結果

SELECT
    TRUNC(-2.3) AS trunc_neg,   -- -2（0方向に切り捨て = 小数部を除去）
    FLOOR(-2.3) AS floor_neg    -- -3（-∞方向の最大整数）
FROM dual;
-- 負の数: TRUNC=-2, FLOOR=-3 で結果が異なる!

-- 税抜価格 × 税率 の 1 円未満を切り捨て
SELECT
    product_name,
    price_excl_tax,
    TRUNC(price_excl_tax * 0.10)                   AS tax_trunc,
    price_excl_tax + TRUNC(price_excl_tax * 0.10)  AS price_incl_tax
FROM products;

-- 結果例:
-- price_excl_tax=298 → 税=TRUNC(29.8)=29 → 税込327
-- price_excl_tax=980 → 税=TRUNC(98.0)=98 → 税込1078
-- price_excl_tax=100 → 税=TRUNC(10.0)=10 → 税込110

-- 単価 × (1 - 割引率) の端数を小数第 2 位で切り捨て
SELECT
    product_name,
    unit_price,
    discount_rate,
    TRUNC(unit_price * (1 - discount_rate), 2)        AS discounted_price,
    TO_CHAR(TRUNC(unit_price * (1 - discount_rate), 2),
            'FM999,999,990.00')                         AS formatted
FROM products;

-- unit_price=1234.567, discount_rate=0.15 の場合:
-- 1234.567 * 0.85 = 1049.38195
-- TRUNC(1049.38195, 2) = 1049.38

-- 5 円単位で切り捨て（1〜4円の端数を除去）
SELECT TRUNC(298 / 5) * 5 AS result FROM dual;    -- 295
SELECT TRUNC(302 / 5) * 5 AS result FROM dual;    -- 300

-- 100 円単位で切り捨て
SELECT TRUNC(1580 / 100) * 100 AS result FROM dual; -- 1500

-- 10 分単位で時間を切り捨て（勤怠管理）
SELECT TRUNC(47 / 10) * 10 AS minutes FROM dual;    -- 40（47分→40分）

-- TRUNC の負の桁を使う方法（100円単位で切り捨て）
SELECT TRUNC(1580, -2) AS result FROM dual;         -- 1500
-- → TRUNC(n / 100) * 100 と同じ結果だが、こちらの方がシンプル

-- 1000 円単位で切り捨てて金額帯を作る
SELECT
    TRUNC(amount, -3)   AS amount_band,
    COUNT(*)             AS order_count,
    SUM(amount)          AS total_amount
FROM orders
GROUP BY TRUNC(amount, -3)
ORDER BY amount_band;

-- 結果例:
-- amount_band=0     → 1〜999円の注文
-- amount_band=1000  → 1000〜1999円の注文
-- amount_band=5000  → 5000〜5999円の注文

-- 年齢帯での集計（10歳刻みで切り捨て）
SELECT
    TRUNC(age, -1)  AS age_band,    -- 20, 30, 40 ...
    COUNT(*)         AS user_count
FROM users
GROUP BY TRUNC(age, -1)
ORDER BY age_band;

-- 小数第 2 位で切り捨て + カンマ付き表示
SELECT
    product_name,
    TRUNC(unit_price, 2) AS truncated,
    TO_CHAR(TRUNC(unit_price, 2), 'FM999,999,990.00') AS formatted
FROM products;

-- TRUNC(123.4, 2) = 123.4 → TO_CHAR で '123.40' と表示
-- TRUNC で末尾のゼロが消える場合は TO_CHAR で書式指定

-- ステップ 1: 対象データの確認
SELECT id, price,
       TRUNC(price, 2) AS truncated_price
FROM products
WHERE price <> TRUNC(price, 2);  -- 小数第3位以下がある行

-- ステップ 2: UPDATE
UPDATE products
SET price = TRUNC(price, 2)
WHERE price <> TRUNC(price, 2);

-- ステップ 3: COMMIT
COMMIT;

-- 複数カラムを同時に処理
UPDATE order_details
SET unit_price    = TRUNC(unit_price, 2),
    discount_amt  = TRUNC(discount_amt, 0),     -- 整数に切り捨て
    tax_amount    = TRUNC(tax_amount, 0)
WHERE unit_price   <> TRUNC(unit_price, 2)
   OR discount_amt <> TRUNC(discount_amt, 0)
   OR tax_amount   <> TRUNC(tax_amount, 0);