sql - 具有 MAX 条件的 Postgresql GROUP BY

Question

此查询计算按 channel (区域)分组的加权平均值。我想添加一个额外的过滤器，它只考虑最近的 period 进行计算。问题是最近的 period 是未知的(它不是日期列，而是一个整数，这不是我的选择)。所以我必须先解决它才能使用它。

我尝试在 WHERE 子句中使用 AND MAX(period)，但 Postgresql 不允许我在 where 子句中使用聚合函数。

SELECT
    channel, weight, label,
    (promoter  * 100) / weight AS promoter_p,
    (passive   * 100) / weight AS passive_p,
    (detractor * 100) / weight AS detractor_p,
    (promoter - detractor) * 100 / weight AS nps
FROM
    (
        SELECT
            channel,
            regions.name AS label,
            COUNT(surveys.id) AS base,
            SUM(weight),
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN weight END) AS promoter,
            SUM(CASE WHEN var BETWEEN 7 AND 8  THEN weight END) AS passive,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN weight END) AS detractor
        FROM surveys
        INNER JOIN regions ON regions.code = surveys.channel
        WHERE (var BETWEEN 0 AND 10) AND active = 1
        GROUP BY channel, label
        ORDER BY label ASC
    ) t

Answer 1

尝试添加这个条件:

...
WHERE (var BETWEEN 0 AND 10) AND active = 1
AND period = (SELECT MAX(period) FROM surveys)