使用SQL或Teradata实现按ID筛选并移除4周内重复检测记录

To solve this problem of iteratively filtering patient records to keep only those that are at least 4 weeks apart from the last retained record for each ID, we can use a recursive CTE in SQL (fully supported in Teradata). Here's a step-by-step solution:

Approach #

1. Order Records: First, assign a row number to each record grouped by patient ID, ordered by detection date and original sequence number (to handle ties where multiple records exist on the same date).
2. Recursive Filtering:
   • Anchor Member: Start with the earliest record (row number 1) for each ID.
   • Recursive Member: For each retained record, find the next earliest record that falls more than 28 days (4 weeks) after the last retained date. Repeat this process until no more valid records remain for any ID.

Solution Code #

WITH ordered_records AS (
    SELECT 
        seq_num,
        id,
        date,
        week,
        month,
        year,
        agent,
        -- Assign row number per ID, prioritizing earlier dates and original sequence order
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date, seq_num) AS rn
    FROM patient_records
),
recursive_filter AS (
    -- Anchor: Select the first (earliest) record for each patient ID
    SELECT 
        seq_num,
        id,
        date,
        week,
        month,
        year,
        agent,
        rn
    FROM ordered_records
    WHERE rn = 1

    UNION ALL

    -- Recursive step: Find the next valid record (more than 28 days after last retained date)
    SELECT 
        or2.seq_num,
        or2.id,
        or2.date,
        or2.week,
        or2.month,
        or2.year,
        or2.agent,
        or2.rn
    FROM recursive_filter rf
    JOIN ordered_records or2 
        ON or2.id = rf.id
        AND or2.rn > rf.rn
        AND or2.date > rf.date + INTERVAL '28' DAY -- Exclude records within 4 weeks
    -- Ensure we pick the earliest valid record after the last retained entry
    WHERE NOT EXISTS (
        SELECT 1 
        FROM ordered_records or3
        WHERE or3.id = or2.id
        AND or3.rn > rf.rn
        AND or3.rn < or2.rn
        AND or3.date > rf.date + INTERVAL '28' DAY
    )
)
-- Final output, sorted for readability
SELECT seq_num, id, date, week, month, year, agent
FROM recursive_filter
ORDER BY id, date, seq_num;

Key Notes #

• Handling Duplicate Dates: The ROW_NUMBER() function ensures that if multiple records exist on the same date, only the one with the smallest original sequence number is retained first (matching your example's expected output).
• 4-Week Window: The condition or2.date > rf.date + INTERVAL '28' DAY excludes records exactly 28 days after the last retained entry. If you want to include records that are exactly 4 weeks later, adjust this to >=.
• Teradata Compatibility: This query uses Teradata-native syntax for recursive CTEs and date arithmetic, so it will run seamlessly in Teradata environments.

This approach efficiently processes each patient's records in a set-based manner, adhering strictly to your iterative filtering requirement.

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