Oracle新手求教：ETL测试中检查目标系统数据错误的示例SQL查询

Hey there! Since you're new to Oracle and looking to spot typos or inaccurate data in your ETL target tables (T1 and T2), I’ll walk you through practical, easy-to-understand query examples tailored to common scenarios. Let’s dive in!

If you suspect duplicate entities with different spellings (like "John Doe" vs "Jon Doe"), you can use Oracle’s SOUNDEX function to match phonetically similar values, plus UTL_MATCH to get a similarity score:

-- Find phonetically similar but differently spelled names in T1 (case-insensitive)
SELECT 
    UPPER(c1.customer_name) AS name_variant_1,
    UPPER(c2.customer_name) AS name_variant_2,
    UTL_MATCH.JARO_WINKLER_SIMILARITY(c1.customer_name, c2.customer_name) AS similarity_score
FROM 
    T1 c1
JOIN 
    T1 c2 ON c1.rowid < c2.rowid  -- Avoid duplicate pairs (e.g., A-B and B-A)
WHERE 
    SOUNDEX(c1.customer_name) = SOUNDEX(c2.customer_name)  -- Same pronunciation
    AND UPPER(c1.customer_name) != UPPER(c2.customer_name)  -- Different spelling
ORDER BY 
    similarity_score DESC;

• How it works: SOUNDEX converts text to a phonetic code, so similar-sounding words get the same code. The Jaro-Winkler score (0-100) tells you how close the spellings are—higher scores mean more likely to be typos.

If T1 and T2 share a common key (like customer_id), you can compare corresponding fields to find mismatches that might signal inaccurate ETL loading:

-- Compare customer names across T1 and T2 for the same customer_id
SELECT 
    t1.customer_id,
    t1.customer_name AS name_from_t1,
    t2.customer_name AS name_from_t2
FROM 
    T1 t1
JOIN 
    T2 t2 ON t1.customer_id = t2.customer_id
WHERE 
    UPPER(t1.customer_name) != UPPER(t2.customer_name)  -- Case-insensitive mismatch
ORDER BY 
    t1.customer_id;

• Use case: This helps catch if the same entity has different values in two tables after ETL—maybe a typo was introduced in one table during loading.

Inaccurate data often comes in the form of invalid formats (e.g., non-numeric phone numbers, malformed dates). Here are two common checks:

Invalid Phone Numbers (T1) #

Suppose phone_number should only contain digits and optional hyphens:

-- Find phone numbers with non-valid characters in T1
SELECT 
    customer_id,
    phone_number
FROM 
    T1
WHERE 
    REGEXP_LIKE(phone_number, '[^0-9\-]')  -- Exclude anything that's not a digit or hyphen
ORDER BY 
    customer_id;

Invalid Date Values (T1) #

If transaction_date is stored as VARCHAR2 (common in ETL staging), check for values that don’t match the expected YYYY-MM-DD format:

-- Find invalid transaction dates in T1
SELECT 
    transaction_id,
    transaction_date
FROM 
    T1
WHERE 
    -- First check format pattern
    NOT REGEXP_LIKE(transaction_date, '^\d{4}-\d{2}-\d{2}$')
    -- Then verify it can be converted to a valid date
    OR TO_DATE(transaction_date, 'YYYY-MM-DD') IS NULL;

• Start small: Add AND ROWNUM <= 10 to your queries to test with a small sample before scanning the entire table.
• Use UPPER() or LOWER() to avoid case-sensitive false mismatches.
• For more advanced fuzzy matching, explore Oracle’s UTL_MATCH package (it has other similarity functions like EDIT_DISTANCE).

内容的提问来源于stack exchange，提问作者lifeofpy