PostgreSQL中获取唯一字段组合的需求及SQL优化问询

Hey there! Let's work through your PostgreSQL deduplication requirements clearly, since your current query has some logic gaps and efficiency issues.

Your Core Requirements Recap #

Let's restate your needs to make sure we're aligned:

• Scenario 1: When Source System, Partner Party Id, and Group Number are duplicated, keep only the record with the largest Partner Last Update Date Ref and discard the rest.
• Scenario 2: When all five fields (Source System, Partner Party Id, Group Number, Partner Last Update Date Ref, Partner Creation Date Ref) are duplicated, keep only one unique instance of that full combination.

Optimized Solutions #

PostgreSQL offers two clean, efficient ways to handle this—let's break them down:

1. Use DISTINCT ON (PostgreSQL-Specific, Most Concise) #

PostgreSQL's DISTINCT ON clause is perfect for this use case: it returns the first row for each unique combination of the specified fields, and we can use ORDER BY to ensure we pick the record with the latest date for scenario 1.

SELECT DISTINCT ON ("Source System", "Partner Party Id", "Group Number", "Partner Last Update Date Ref", "Partner Creation Date Ref")
       "Source System",
       "Group Number",
       "Partner Party Id",
       "Partner Last Update Date Ref",
       "Partner Creation Date Ref"
FROM VT_PC_DE_DUP_CONSOLIDATED_JAN_22_INC
ORDER BY 
  "Source System", 
  "Partner Party Id", 
  "Group Number",
  "Partner Last Update Date Ref" DESC,  -- Prioritize latest update date for scenario 1
  "Partner Creation Date Ref" DESC;     -- Fallback if update dates are identical

• How this works:
  • For scenario 2 (full 5-field duplicates), DISTINCT ON automatically keeps only one instance of each exact combination.
  • For scenario 1 (3-field duplicates with different dates), the ORDER BY clause ensures the record with the largest Partner Last Update Date Ref is the first one in its group, so DISTINCT ON retains that single record.

2. Use Window Functions (Cross-Database Compatible) #

If you prefer a standard SQL approach that works across databases, use ROW_NUMBER() to rank records within each 3-field group:

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY "Source System", "Partner Party Id", "Group Number"
            ORDER BY "Partner Last Update Date Ref" DESC, "Partner Creation Date Ref" DESC
        ) AS record_rank
    FROM VT_PC_DE_DUP_CONSOLIDATED_JAN_22_INC
)
SELECT DISTINCT  -- Handles scenario 2 (full 5-field duplicates)
       "Source System",
       "Group Number",
       "Partner Party Id",
       "Partner Last Update Date Ref",
       "Partner Creation Date Ref"
FROM ranked_records
WHERE record_rank = 1;  -- Keeps only the latest record per 3-field group (scenario 1)

• How this works:
  • The CTE assigns a rank to each record within its Source System/Partner Party Id/Group Number group, with the latest date getting rank 1.
  • We filter for record_rank = 1 to get the top record per group (scenario 1), then use DISTINCT to eliminate any remaining full 5-field duplicates (scenario 2).

Why Your Original Query Isn't Working #

Your current query uses nested IN clauses which are inefficient and logically flawed:

• Checking if individual fields exist in subqueries with count = 1 doesn't properly identify duplicate combinations (it just checks if the field value appears in any unique combination).
• The OR conditions will include unintended records, leading to incorrect results.

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