如何去除SQL中Opportunity-ID重复记录并保留唯一行？

原查询语句 #

SELECT
         Min("Id") as 'Id',
         Min("Created Time") as 'Created Time',
         Min("Adjusted Amount") as 'Adjusted Amount',
         "Collection Date",
         Min("Collection Mode") as 'Collection Mode',
         Min("Collection Status") as 'Collection Status',
         Min("Invoice No.") as 'Invoice No.',
         Min("Note") as 'Note',
         "Opportunity-ID",
         sum("Collection Amount") AS 'Collection Amount',
         sum("Rep eligible Collection value") AS 'Rep Eligible Collection Value'
FROM  "Payment Collection Info" 
GROUP BY "Opportunity-ID",
      "Collection Date" 

原查询同时按Opportunity-ID和Collection Date分组，导致同一个Opportunity-ID若对应多个不同的Collection Date，会生成多条重复记录。以下是两种解决思路：

方案1：仅按Opportunity-ID分组，聚合日期字段 #

如果不需要保留所有Collection Date，可对该字段使用聚合函数（如取最早/最晚日期），仅按Opportunity-ID分组：

SELECT
         Min("Id") as 'Id',
         Min("Created Time") as 'Created Time',
         Min("Adjusted Amount") as 'Adjusted Amount',
         Min("Collection Date") as 'Collection Date', -- 取最早收款日期，替换为Max可取最晚日期
         Min("Collection Mode") as 'Collection Mode',
         Min("Collection Status") as 'Collection Status',
         Min("Invoice No.") as 'Invoice No.',
         Min("Note") as 'Note',
         "Opportunity-ID",
         sum("Collection Amount") AS 'Collection Amount',
         sum("Rep eligible Collection value") AS 'Rep Eligible Collection Value'
FROM  "Payment Collection Info" 
GROUP BY "Opportunity-ID"

方案2：使用窗口函数筛选唯一记录 #

如果需要保留特定条件的行（如最新创建时间的记录），可借助ROW_NUMBER()窗口函数实现：

WITH RankedPayments AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY "Opportunity-ID" 
            ORDER BY "Created Time" DESC -- 按创建时间倒序，取最新一行；可根据需求调整排序字段
        ) AS rn
    FROM "Payment Collection Info"
)
SELECT
    "Id",
    "Created Time",
    "Adjusted Amount",
    "Collection Date",
    "Collection Mode",
    "Collection Status",
    "Invoice No.",
    "Note",
    "Opportunity-ID",
    "Collection Amount",
    "Rep eligible Collection value" AS "Rep Eligible Collection Value"
FROM RankedPayments
WHERE rn = 1;

若需先对金额聚合再筛选唯一记录，可嵌套查询：

WITH AggregatedPayments AS (
    SELECT
         Min("Id") as 'Id',
         Min("Created Time") as 'Created Time',
         Min("Adjusted Amount") as 'Adjusted Amount',
         "Collection Date",
         Min("Collection Mode") as 'Collection Mode',
         Min("Collection Status") as 'Collection Status',
         Min("Invoice No.") as 'Invoice No.',
         Min("Note") as 'Note',
         "Opportunity-ID",
         sum("Collection Amount") AS 'Collection Amount',
         sum("Rep eligible Collection value") AS 'Rep Eligible Collection Value'
    FROM  "Payment Collection Info" 
    GROUP BY "Opportunity-ID", "Collection Date"
),
RankedAggregates AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY "Opportunity-ID" 
            ORDER BY "Created Time" DESC
        ) AS rn
    FROM AggregatedPayments
)
SELECT *
FROM RankedAggregates
WHERE rn = 1;

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