PostgreSQL窗口函数使用COUNT(DISTINCT)报错，求解决方案

报错的核心原因是你使用的数据库不支持在窗口函数中使用COUNT(DISTINCT)语法。要解决这个问题，我们可以提前用普通的GROUP BY查询计算出需要的distinct计数，再关联到主查询中完成score的计算。

修改后的SQL（CTE版本，可读性更高） #

WITH question_count AS (
    -- 预计算每个分区下的distinct question_id数量
    SELECT s.first_field,
           s.second_field,
           s.fourth_field,
           COUNT(DISTINCT mt.question_id) AS distinct_question_count
    FROM my_table mt
    LEFT JOIN second s 
           ON mt.second_id = s._id
    WHERE s.fourth_field IS NOT NULL
      AND s.fifth_field IS NOT NULL
      AND s.fifth_field = 'Test'
    GROUP BY s.first_field, s.second_field, s.fourth_field
)
SELECT s.first_field,
       s.second_field,
       t.third_field,
       s.fourth_field,
       ROUND(
               -- 用预计算的distinct计数替代原窗口函数
               (SUM(mt.volume) * 100.0 / SUM(mt.volume)) / qc.distinct_question_count,
               2
       ) AS score,
       mt.volume  -- 原查询中的fsd.volume应为笔误，这里修正为mt.volume
FROM my_table mt
LEFT JOIN second s 
       ON mt.second_id = s._id
LEFT JOIN third t 
       ON mt.third_id = t._id
-- 关联预计算的计数结果
JOIN question_count qc 
    ON s.first_field = qc.first_field
    AND s.second_field = qc.second_field
    AND s.fourth_field = qc.fourth_field
WHERE s.fourth_field IS NOT NULL
  AND s.fifth_field IS NOT NULL
  AND s.fifth_field = 'Test'
GROUP BY s.first_field, s.second_field, t.third_field, s.fourth_field, mt.volume, qc.distinct_question_count;

替代方案（子查询版本，兼容更多旧版数据库） #

如果你的数据库不支持CTE，可以改用子查询实现相同逻辑：

SELECT s.first_field,
       s.second_field,
       t.third_field,
       s.fourth_field,
       ROUND(
               (SUM(mt.volume) * 100.0 / SUM(mt.volume)) / qc.distinct_question_count,
               2
       ) AS score,
       mt.volume
FROM my_table mt
LEFT JOIN second s 
       ON mt.second_id = s._id
LEFT JOIN third t 
       ON mt.third_id = t._id
JOIN (
    SELECT s.first_field,
           s.second_field,
           s.fourth_field,
           COUNT(DISTINCT mt.question_id) AS distinct_question_count
    FROM my_table mt
    LEFT JOIN second s 
           ON mt.second_id = s._id
    WHERE s.fourth_field IS NOT NULL
      AND s.fifth_field IS NOT NULL
      AND s.fifth_field = 'Test'
    GROUP BY s.first_field, s.second_field, s.fourth_field
) qc 
    ON s.first_field = qc.first_field
    AND s.second_field = qc.second_field
    AND s.fourth_field = qc.fourth_field
WHERE s.fourth_field IS NOT NULL
  AND s.fifth_field IS NOT NULL
  AND s.fifth_field = 'Test'
GROUP BY s.first_field, s.second_field, t.third_field, s.fourth_field, mt.volume, qc.distinct_question_count;

额外注意点 #

1. 原查询中的fsd.volume应该是笔误，因为查询中没有关联名为fsd的表，这里修正为mt.volume，如果实际是其他表，请自行调整。
2. 你的score计算式中SUM(mt.volume) * 100.0 / SUM(mt.volume)等价于100.0，如果这不是你的真实业务逻辑，可能需要重新梳理这部分的计算规则。

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