基于两列识别CSV重复项，按多规则保留目标行（求awk方案）

Hey there! Let's work through this CSV deduplication problem with a clean, well-commented awk script that's easy to follow—even if you're new to handling complex logic in awk. Here's a solution that matches all your requirements, including preserving original row order where possible:

Step-by-Step Explanation & Awk Script #

First, let's outline the core logic we need:

• Group rows by the concatenation of column 4 (identifier) and column 5 (date)
• For each group, keep the row with the highest numeric value extracted from column 14 (after N0)
• If values are tied, keep the row with the latest date in column 9

Here's the script with detailed comments to break down each part:

BEGIN {
    FS = ","  # Set field separator to comma (CSV standard)
    OFS = "," # Keep output in CSV format
}

# First pass: Scan all rows to track the best row for each group
NR == FNR {
    # Create unique key for grouping: column 4 + column 5
    group_key = $4 $5

    # Extract numeric value after "N0" from column 14
    if ($14 ~ /N0[0-9]{3}/) {
        # Use awk's match function to capture the 3-digit number after N0
        match($14, /N0([0-9]{3})/, capture)
        current_value = capture[1] + 0  # Convert to integer for proper numeric comparison
    } else {
        current_value = -1  # Assign lowest priority if no match found
    }

    # Clean up column 9 date for comparison (ISO dates can be compared as strings)
    current_date = substr($9, 1, 19)  # Trim off timezone/microseconds (YYYY-MM-DDTHH:MM:SS)

    # Determine if current row is better than the stored best row for this group
    if (!(group_key in best_value)) {
        # First time seeing this group: save current row as best
        best_value[group_key] = current_value
        best_date[group_key] = current_date
        best_line[group_key] = $0
        # Track order of first occurrence to preserve original row order
        key_order[++order_count] = group_key
    } 
    else if (current_value > best_value[group_key]) {
        # Current row has higher N0 value: replace best row
        best_value[group_key] = current_value
        best_date[group_key] = current_date
        best_line[group_key] = $0
    } 
    else if (current_value == best_value[group_key] && current_date > best_date[group_key]) {
        # Same N0 value, but current row has later date: replace best row
        best_date[group_key] = current_date
        best_line[group_key] = $0
    }

    next  # Move to next row without processing the second pass logic
}

# Second pass: Output the best row for each group in original order
{
    for (i = 1; i <= order_count; i++) {
        print best_line[key_order[i]]
        # Delete after printing to avoid duplicates (just a safety measure)
        delete best_line[key_order[i]]
    }
    exit  # Exit after outputting all results
}

How to Use This Script #

1. Save the script to a file, e.g., csv_dedup.awk
2. Run it against your input CSV file by passing the file twice (awk uses this trick for two-pass processing):

   awk -f csv_dedup.awk your_input.csv your_input.csv
   

Testing with Your Sample Data #

For your sample rows:

xyz,xyz,xyz,X08ABC,20200804,xyz,xyz,xyz,2020-08-17T20:08:27.000000Z,xyz,xyz,xyz,xyz,long_string_N0809_bla
xyz,xyz,xyz,X08ABC,20200804,xyz,xyz,xyz,2020-09-25T16:18:43.000000Z,xyz,xyz,xyz,xyz,long_string_N0809_bla

The script will output the second row, since both rows share the same group_key, have identical N0 values (809), and the second row's date is later.

Key Features #

• Readable Logic: Each step is commented and broken into clear conditional blocks, so you can see exactly how we're choosing the best row.
• Preserves Order: The key_order array tracks when each group first appears, so output maintains the original order of group occurrences.
• Robust Comparison: Converting the N0 value to an integer ensures proper numeric sorting (e.g., N0009 becomes 9, which is less than N0809's 809), and ISO date strings work perfectly for lexicographical comparison.

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