如何添加最近90天指标最大值作为列？SQL窗口函数实现疑问

你的写法错误在于窗口函数的OVER子句中不能直接使用WHERE条件来过滤行，WHERE是用于全局过滤整个结果集的，窗口函数内的行过滤需要用以下几种方式实现：

方案1：CASE表达式配合窗口MAX（兼容所有支持窗口函数的数据库） #

通过CASE表达式仅保留最近90天的metric值，不符合条件的设为NULL，MAX函数会自动忽略NULL值，最终得到分组内最近90天的最大值：

SELECT id1,
       id2,
       date,
       metric,
       MAX(CASE WHEN date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY) THEN metric END) 
           OVER(PARTITION BY id1, id2) AS max_l90
FROM your_table;

方案2：使用窗口函数的FILTER子句（适用于PostgreSQL、BigQuery等数据库） #

部分数据库支持窗口函数的FILTER子句，可以直接在窗口聚合中指定过滤条件，写法更简洁：

SELECT id1,
       id2,
       date,
       metric,
       MAX(metric) OVER(PARTITION BY id1, id2) 
           FILTER (WHERE date >= CURRENT_DATE - INTERVAL '90 days') AS max_l90
FROM your_table;

方案3：子查询关联（适用于所有数据库，包括不支持窗口函数的老版本） #

如果你的数据库版本较低不支持窗口函数，可以先通过子查询计算出最近90天各分组的最大值，再关联回原表：

SELECT t.id1,
       t.id2,
       t.date,
       t.metric,
       m.max_l90
FROM your_table t
LEFT JOIN (
    SELECT id1, id2, MAX(metric) AS max_l90
    FROM your_table
    WHERE date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
    GROUP BY id1, id2
) m ON t.id1 = m.id1 AND t.id2 = m.id2;

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