如何在SQL查询中去除重复的offer_request.id行？SUM函数尝试未果

你的查询出现重复行的原因是：offer_request通过中间表关联多个industry_sector后，同一个offer_request.id会生成多条重复记录，导致聚合统计时把同一个请求多次计入，最终比率计算失真。

核心解决思路是先对每个offer_request.id去重，标记每个请求是否符合分子/分母的条件，再按月份聚合计算比率，修改后的SQL如下：

WITH filtered_offers AS (
    SELECT
        ofr.id,
        to_char(ofr.created, 'YYYY-MM') AS "month",
        -- 标记该请求是否出现过从New到UserUnreachable的状态变更
        MAX(CASE WHEN ae."type" = 'OfferRequestStatusChanged'
                 AND ae.data ->> 'oldStatus' = 'New'
                 AND ae.data ->> 'status' = 'UserUnreachable' THEN 1 ELSE 0 END) AS is_user_unreachable,
        -- 标记该请求是否出现过从New到目标状态组的变更
        MAX(CASE WHEN ae."type" = 'OfferRequestStatusChanged'
                 AND ae.data ->> 'oldStatus' = 'New'
                 AND ae.data ->> 'status' IN ('Validated', 'Cancelled', 'OnHold') THEN 1 ELSE 0 END) AS is_valid_group
    FROM 
        "application_event" ae
        JOIN "offer_request" ofr ON ofr."id" = (ae.data ->> 'offerRequestId')::int
        LEFT JOIN "offer_request_industry_sectors_industry_sector" oris ON oris."offerRequestId" = ofr."id"
        LEFT JOIN "industry_sector" ids ON ids."id" = oris."industrySectorId"
    WHERE
        ae."type" = 'OfferRequestStatusChanged'
        AND ids."key" NOT IN ('DEVICE_SERVICE', 'ESTATE_AGENT', 'MORTGAGES', 'FINANCE_CONSULTING', 
                              'SEARCH_MANDATE', 'RENOVATION_COACH', 'RENT_TO_BUY_CONSULTING')
        AND ofr."status" != 'Deleted'
        AND ofr."userEmail" NOT LIKE '%@houzy.ch'
        AND ofr."userEmail" NOT LIKE '%@mailinator.com'
        AND ofr."created" > '2021-06-01'
    GROUP BY ofr.id, to_char(ofr.created, 'YYYY-MM') -- 按请求ID去重，确保每个请求只保留一条记录
)
SELECT
    "month",
    SUM(is_user_unreachable)::decimal / SUM(is_valid_group)::decimal AS "ratio"
FROM filtered_offers
WHERE is_valid_group = 1 -- 过滤掉分母为0的情况，避免报错
GROUP BY "month";

关键说明： #

• 通过GROUP BY ofr.id确保每个offer_request只被统计一次，不受关联行业分类数量影响
• MAX(CASE...)用来判断该请求是否存在符合条件的状态变更事件（只要有一次符合，就标记为1）
• 外层查询按月份聚合，统计符合分子条件的请求总数除以符合分母条件的请求总数，得到真实比率

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