数据质量校验常用SQL模板

SELECT
    SUM(CASEWHEN user_id ISNULLTHEN1ELSE0END) AS user_id_null,
    SUM(CASEWHEN order_id ISNULLTHEN1ELSE0END) AS order_id_null,
    SUM(CASEWHEN amount ISNULLTHEN1ELSE0END) AS amount_null
FROM dwd_order
WHERE dt = '${bizdate}';
-- 任一字段 > 0 则阻断

SELECT
    COUNT(1) AS total,
    ROUND((COUNT(1) - COUNT(phone)) / COUNT(1) * 100, 2) AS phone_null_pct,
    ROUND((COUNT(1) - COUNT(address)) / COUNT(1) * 100, 2) AS address_null_pct,
    ROUND((COUNT(1) - COUNT(city)) / COUNT(1) * 100, 2) AS city_null_pct
FROM ods_user_info
WHERE dt = '${bizdate}';
-- 超过设定阈值（如 phone > 5%）则告警

SELECT
    CASEWHEN cnt = 0THEN'BLOCK'ELSE'PASS'ENDASresult
FROM (
    SELECTCOUNT(1) AS cnt
    FROM ods_order
    WHERE dt = '${bizdate}'
) t;

SELECT pk_col, COUNT(1) AS dup_count
FROM dwd_order
WHERE dt = '${bizdate}'
GROUP BY pk_col
HAVING COUNT(1) > 1
LIMIT 10;
-- 有结果则阻断

SELECT
    COUNT(1) AS total,
    COUNT(DISTINCT order_id) AS distinct_cnt,
    ROUND((COUNT(1) - COUNT(DISTINCT order_id)) / COUNT(1) * 100, 4) AS dup_rate_pct
FROM dwd_order
WHERE dt = '${bizdate}';
-- dup_rate_pct > 0 需排查

SELECT
    CASEWHEN after_cnt > before_cnt * 1.05THEN'WARN'ELSE'PASS'ENDASresult,
    before_cnt,
    after_cnt
FROM (
    SELECT
        (SELECTCOUNT(1) FROM ods_order WHERE dt = '${bizdate}') AS before_cnt,
        (SELECTCOUNT(1) FROM dwd_order WHERE dt = '${bizdate}') AS after_cnt
) t;

SELECT status, COUNT(1) AS cnt
FROM dwd_order
WHERE dt = '${bizdate}'
  AND status NOT IN ('created', 'paid', 'shipped', 'completed', 'cancelled')
GROUP BY status;
-- 有结果则告警

SELECT
    COUNT(CASEWHEN amount < 0THEN1END) AS negative_amt,
    COUNT(CASEWHEN amount > 10000000THEN1END) AS extreme_amt,
    COUNT(CASEWHEN age < 0OR age > 150THEN1END) AS invalid_age
FROM dwd_order_detail
WHERE dt = '${bizdate}';

SELECT COUNT(1) AS conflict_count
FROM dwd_event
WHERE dt = '${bizdate}'
  AND end_time < start_time;
-- > 0 则阻断

SELECT COUNT(1) AS invalid_count
FROM ads_funnel
WHERE dt = '${bizdate}'
  AND (convert_rate > 1 OR convert_rate < 0);

SELECT
    src.cnt AS dwd_count,
    tgt.cnt AS dws_count,
    src.cnt - tgt.cnt AS diff
FROM
    (SELECT COUNT(DISTINCT order_id) AS cnt FROM dwd_order WHERE dt = '${bizdate}') src,
    (SELECT SUM(order_cnt) AS cnt FROM dws_shop_daily WHERE dt = '${bizdate}') tgt;
-- diff 不为 0 需排查

SELECT
    ABS(a.total_amt - b.total_amt) AS amt_diff,
    CASE
        WHENABS(a.total_amt - b.total_amt) / a.total_amt > 0.001THEN'BLOCK'
        ELSE'PASS'
    ENDASresult
FROM
    (SELECTSUM(amount) AS total_amt FROM dwd_order WHERE dt = '${bizdate}') a,
    (SELECTSUM(order_amount) AS total_amt FROM dws_shop_daily WHERE dt = '${bizdate}') b;

SELECT
    COUNT(1) AS total,
    SUM(CASEWHEN b.user_name ISNULLTHEN1ELSE0END) AS miss_count,
    ROUND(SUM(CASEWHEN b.user_name ISNULLTHEN1ELSE0END) / COUNT(1) * 100, 2) AS miss_rate_pct
FROM dwd_order a
LEFTJOIN dim_user b ON a.user_id = b.user_id
WHERE a.dt = '${bizdate}';
-- miss_rate_pct > 5% 则告警

SELECT
    a.shop_id,
    a.recalc_amt,
    b.order_amount,
    ABS(a.recalc_amt - b.order_amount) AS diff
FROM (
    SELECT shop_id, SUM(amount) AS recalc_amt
    FROM dwd_order
    WHERE dt = '${bizdate}'
    GROUP BY shop_id
) a
JOIN dws_shop_daily b ON a.shop_id = b.shop_id AND b.dt = '${bizdate}'
WHERE ABS(a.recalc_amt - b.order_amount) > 0.01;

SELECT
    MAX(event_time) AS latest_event,
    CURRENT_TIMESTAMP() AS check_time,
    (UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - UNIX_TIMESTAMP(MAX(event_time))) / 60 AS delay_minutes
FROM ods_event_log
WHERE dt = '${bizdate}';
-- delay_minutes > 阈值则告警

-- Hive
SHOW PARTITIONS table_name PARTITION (dt = '${bizdate}');
-- 无结果则阻断

SELECT
    MAX(update_time) AS latest_sync,
    CASE
        WHEN MAX(update_time) < '${bizdate} 23:00:00' THEN 'INCOMPLETE'
        ELSE 'PASS'
    END AS result
FROM ods_order
WHERE dt = '${bizdate}';

WITH daily AS (
    SELECT dt, COUNT(1) AS cnt
    FROM dwd_order
    WHERE dt BETWEENDATE_SUB('${bizdate}', 1) AND'${bizdate}'
    GROUPBY dt
)
SELECT
    t.cnt AS today,
    y.cnt AS yesterday,
    ROUND((t.cnt - y.cnt) / y.cnt * 100, 2) AS change_pct,
    CASE
        WHENABS((t.cnt - y.cnt) / y.cnt) > 0.3THEN'WARN'
        ELSE'PASS'
    ENDASresult
FROM daily t
JOIN daily y ON t.dt = DATE_ADD(y.dt, 1)
WHERE t.dt = '${bizdate}';

SELECT
    a.cnt AS this_week,
    b.cnt AS last_week,
    ROUND((a.cnt - b.cnt) / b.cnt * 100, 2) AS wow_pct
FROM
    (SELECT COUNT(1) AS cnt FROM dwd_order WHERE dt = '${bizdate}') a,
    (SELECT COUNT(1) AS cnt FROM dwd_order WHERE dt = DATE_SUB('${bizdate}', 7)) b;

WITH metrics AS (
    SELECT dt, SUM(amount) AS gmv
    FROM dws_shop_daily
    WHERE dt BETWEENDATE_SUB('${bizdate}', 7) AND'${bizdate}'
    GROUPBY dt
)
SELECT
    curr.dt,
    curr.gmv,
    prev.gmv AS prev_day_gmv,
    ROUND((curr.gmv - prev.gmv) / prev.gmv * 100, 2) AS change_pct
FROM metrics curr
JOIN metrics prev ON curr.dt = DATE_ADD(prev.dt, 1)
WHERE curr.dt = '${bizdate}'
ANDABS((curr.gmv - prev.gmv) / prev.gmv) > 0.2;
-- 有结果则告警