異常値・重複データをSQLで検知する方法｜データ品質チェックの実務クエリ

-- チェック1: 注文件数の前日比チェック

WITH daily_count AS (
    SELECT
        order_date,
        COUNT(order_id)  AS order_count
    FROM orders
    WHERE order_date >= DATE_ADD('day', -7, CURRENT_DATE)  -- 直近7日
    GROUP BY order_date
),
with_prev AS (
    SELECT
        order_date,
        order_count,
        LAG(order_count, 1) OVER (ORDER BY order_date)  AS prev_day_count
    FROM daily_count
)
SELECT
    order_date,
    order_count,
    prev_day_count,
    -- 前日比（%）
    ROUND(
        (order_count - prev_day_count) * 100.0
        / NULLIF(prev_day_count, 0)
    , 1)                     AS day_over_day_pct,
    -- 異常フラグ：前日比 -50% 以下、または +200% 以上
    CASE
        WHEN (order_count - prev_day_count) * 100.0
             / NULLIF(prev_day_count, 0) <= -50  THEN '⚠ 急減'
        WHEN (order_count - prev_day_count) * 100.0
             / NULLIF(prev_day_count, 0) >= 200  THEN '⚠ 急増'
        ELSE '正常'
    END                      AS anomaly_flag
FROM with_prev
WHERE prev_day_count IS NOT NULL  -- 初日は比較対象なしで除外
ORDER BY order_date DESC;

-- チェック2: 金額異常チェック

SELECT
    '金額がゼロ以下'           AS check_type,
    COUNT(*)                   AS anomaly_count,
    MIN(total_amount)          AS min_value,
    MAX(total_amount)          AS max_value
FROM orders
WHERE total_amount <= 0
AND order_date >= DATE_ADD('day', -7, CURRENT_DATE)

UNION ALL

SELECT
    '金額が異常に高額（50万円超）'  AS check_type,
    COUNT(*)                        AS anomaly_count,
    MIN(total_amount)               AS min_value,
    MAX(total_amount)               AS max_value
FROM orders
WHERE total_amount > 500000
AND order_date >= DATE_ADD('day', -7, CURRENT_DATE)

UNION ALL

SELECT
    'total_amountがNULL'       AS check_type,
    COUNT(*)                   AS anomaly_count,
    NULL                       AS min_value,
    NULL                       AS max_value
FROM orders
WHERE total_amount IS NULL
AND order_date >= DATE_ADD('day', -7, CURRENT_DATE);

-- チェック3: 日付異常チェック

SELECT
    '未来日付の注文'                AS check_type,
    COUNT(*)                        AS anomaly_count,
    MIN(order_date)                 AS min_date,
    MAX(order_date)                 AS max_date
FROM orders
WHERE order_date > CURRENT_DATE

UNION ALL

SELECT
    '登録日より前の注文日'          AS check_type,
    COUNT(*)                        AS anomaly_count,
    MIN(o.order_date)               AS min_date,
    MAX(o.order_date)               AS max_date
FROM orders     o
JOIN customers  c  ON o.customer_id = c.customer_id
WHERE o.order_date < c.registered_at  -- 登録前に注文している（論理矛盾）

UNION ALL

SELECT
    '5年以上前の注文（データ移行ミスの疑い）'  AS check_type,
    COUNT(*)                                    AS anomaly_count,
    MIN(order_date)                             AS min_date,
    MAX(order_date)                             AS max_date
FROM orders
WHERE order_date < DATE_ADD('year', -5, CURRENT_DATE);

-- チェック4: 主キー重複チェック

WITH duplicate_orders AS (
    SELECT
        order_id,
        COUNT(*)  AS row_count
    FROM orders
    GROUP BY order_id
    HAVING COUNT(*) > 1  -- 2件以上存在するorder_idだけ残す
)
SELECT
    COUNT(*)                    AS duplicate_key_count,  -- 重複しているorder_idの種類数
    SUM(row_count)              AS total_duplicate_rows, -- 重複行の総数
    MIN(row_count)              AS min_duplication,
    MAX(row_count)              AS max_duplication
FROM duplicate_orders;

-- 重複しているorder_idの具体的な内容を確認する

WITH duplicate_ids AS (
    SELECT order_id
    FROM orders
    GROUP BY order_id
    HAVING COUNT(*) > 1
)
SELECT
    o.*
FROM orders         o
JOIN duplicate_ids  d  ON o.order_id = d.order_id
ORDER BY o.order_id, o.customer_id;

-- チェック5: 参照整合性チェック（孤立レコード）

-- 孤立した注文（顧客マスタに存在しないcustomer_id）
SELECT
    '顧客マスタに存在しないcustomer_id'  AS check_type,
    COUNT(DISTINCT o.customer_id)         AS anomaly_count
FROM orders     o
LEFT JOIN customers  c  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL

UNION ALL

-- 孤立した注文明細（注文テーブルに存在しないorder_id）
SELECT
    '注文テーブルに存在しないorder_id'  AS check_type,
    COUNT(DISTINCT oi.order_id)          AS anomaly_count
FROM order_items  oi
LEFT JOIN orders  o  ON oi.order_id = o.order_id
WHERE o.order_id IS NULL

UNION ALL

-- 孤立したクーポン使用（クーポンマスタに存在しないcoupon_id）
SELECT
    'クーポンマスタに存在しないcoupon_id'  AS check_type,
    COUNT(*)                                AS anomaly_count
FROM orders
WHERE coupon_id IS NOT NULL
AND coupon_id NOT IN (SELECT coupon_id FROM coupons);

-- チェック6: 必須カラムのNULLチェック（直近7日分）

SELECT
    'orders.customer_id'          AS column_name,
    COUNT(*) FILTER (WHERE customer_id IS NULL)   AS null_count,
    COUNT(*)                                       AS total_count
FROM orders
WHERE order_date >= DATE_ADD('day', -7, CURRENT_DATE)

UNION ALL

SELECT
    'orders.order_date',
    COUNT(*) FILTER (WHERE order_date IS NULL),
    COUNT(*)
FROM orders
WHERE created_at >= DATE_ADD('day', -7, CURRENT_DATE)

UNION ALL

SELECT
    'orders.total_amount',
    COUNT(*) FILTER (WHERE total_amount IS NULL),
    COUNT(*)
FROM orders
WHERE order_date >= DATE_ADD('day', -7, CURRENT_DATE)

UNION ALL

SELECT
    'orders.status',
    COUNT(*) FILTER (WHERE status IS NULL),
    COUNT(*)
FROM orders
WHERE order_date >= DATE_ADD('day', -7, CURRENT_DATE);

-- 日次データ品質ヘルスチェック（サマリ版）

WITH checks AS (

    -- チェック1: 昨日の注文件数が一昨日から50%以上減少
    SELECT
        'order_count_drop'       AS check_name,
        '注文件数の急減（前日比-50%以下）'  AS description,
        CASE
            WHEN (
                SELECT COUNT(*) FROM orders
                WHERE order_date = DATE_ADD('day', -1, CURRENT_DATE)
            ) * 1.0
            / NULLIF(
                SELECT COUNT(*) FROM orders
                WHERE order_date = DATE_ADD('day', -2, CURRENT_DATE)
            , 0) < 0.5
            THEN 1 ELSE 0
        END  AS is_anomaly

    UNION ALL

    -- チェック2: 金額がゼロ以下の注文
    SELECT
        'negative_amount',
        '金額ゼロ以下の注文あり',
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM orders
    WHERE total_amount <= 0
    AND order_date >= DATE_ADD('day', -1, CURRENT_DATE)

    UNION ALL

    -- チェック3: 未来日付の注文
    SELECT
        'future_date',
        '未来日付の注文あり',
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM orders
    WHERE order_date > CURRENT_DATE

    UNION ALL

    -- チェック4: 主キー重複
    SELECT
        'duplicate_order_id',
        '注文IDの重複あり',
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM (
        SELECT order_id FROM orders
        GROUP BY order_id HAVING COUNT(*) > 1
    )

    UNION ALL

    -- チェック5: 孤立した注文
    SELECT
        'orphan_orders',
        '顧客マスタに存在しない注文あり',
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM orders     o
    LEFT JOIN customers  c  ON o.customer_id = c.customer_id
    WHERE c.customer_id IS NULL
    AND o.order_date >= DATE_ADD('day', -1, CURRENT_DATE)

    UNION ALL

    -- チェック6: 必須カラムのNULL
    SELECT
        'null_in_required_columns',
        'customer_idまたはtotal_amountにNULLあり',
        CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM orders
    WHERE (customer_id IS NULL OR total_amount IS NULL)
    AND order_date >= DATE_ADD('day', -1, CURRENT_DATE)
)
SELECT
    check_name,
    description,
    CASE WHEN is_anomaly = 1 THEN '🚨 異常検知' ELSE '✅ 正常' END  AS status,
    CURRENT_DATE  AS checked_at
FROM checks
ORDER BY is_anomaly DESC, check_name;