PostgreSQL窗口框架中定义相对百分比范围限制的实现方案

首先，你的原查询存在两个关键问题：

• PARTITION BY sqft会将完全相同建筑面积的地点划分为一组，这和你需要的「±5%区间内的所有地点」需求不符。
• 窗口函数中RANGE BETWEEN ROUND(sqft * 0.95) AND ROUND(sqft * 1.05)的语法不成立：PostgreSQL的RANGE窗口边界仅支持相对于ORDER BY列的固定偏移量，无法直接引用当前行的动态计算值作为范围边界。

针对你的需求，推荐使用LATERAL JOIN来实现每个地点对应±5%建筑面积区间的能耗平均值计算，这种方式逻辑清晰且符合PostgreSQL的语法规范：

WITH energy_consumption_cte AS (
    SELECT
        sl.id,
        sl.sqft,
        SUM(CASE WHEN dl.log_label = 'energy use' THEN dl.value END) AS monthly_consumption
    FROM
        serv_location sl
    INNER JOIN device d ON d.service_loc_id = sl.id
    INNER JOIN device_log dl ON dl.device_id = d.id
    WHERE
        dl."timestamp" >= '2022-08-01 00:01:00.000'::timestamp
        AND dl."timestamp" < '2022-09-01 00:01:00.000'::timestamp
        AND sl.sqft IS NOT NULL
    GROUP BY
        sl.id, sl.sqft
)
SELECT
    ecc.id,
    ecc.sqft,
    ecc.monthly_consumption,
    -- 计算当前地点所在±5%sqft区间内的能耗平均值
    avg_range.avg_monthly_consumption,
    -- 可选：计算当前能耗相对于区间平均值的百分比
    ROUND((ecc.monthly_consumption / avg_range.avg_monthly_consumption) * 100, 2) AS consumption_vs_range_avg_pct
FROM
    energy_consumption_cte ecc
LATERAL (
    SELECT AVG(monthly_consumption) AS avg_monthly_consumption
    FROM energy_consumption_cte
    WHERE sqft BETWEEN ecc.sqft * 0.95 AND ecc.sqft * 1.05
) avg_range
ORDER BY
    ecc.sqft ASC;

补充说明： #

• LATERAL JOIN会为energy_consumption_cte中的每一行，动态计算出符合「sqft在当前行sqft的95%到105%之间」的所有地点的能耗平均值。
• 如果你坚持想用窗口函数实现，可以借助RANGE结合ORDER BY sqft，但需要注意这种方式仅适用于连续且无间隙的sqft值（实际场景中很少见），示例如下：

WITH energy_consumption_cte AS (
    -- 同之前的CTE部分
    SELECT
        sl.id,
        sl.sqft,
        SUM(CASE WHEN dl.log_label = 'energy use' THEN dl.value END) AS monthly_consumption
    FROM
        serv_location sl
    INNER JOIN device d ON d.service_loc_id = sl.id
    INNER JOIN device_log dl ON dl.device_id = d.id
    WHERE
        dl."timestamp" >= '2022-08-01 00:01:00.000'::timestamp
        AND dl."timestamp" < '2022-09-01 00:01:00.000'::timestamp
        AND sl.sqft IS NOT NULL
    GROUP BY
        sl.id, sl.sqft
)
SELECT
    id,
    sqft,
    monthly_consumption,
    AVG(monthly_consumption) OVER (
        ORDER BY sqft
        RANGE BETWEEN (sqft * -0.05) PRECEDING AND (sqft * 0.05) FOLLOWING
    ) AS avg_monthly_for_sqft_range,
    ROUND((monthly_consumption / AVG(monthly_consumption) OVER (
        ORDER BY sqft
        RANGE BETWEEN (sqft * -0.05) PRECEDING AND (sqft * 0.05) FOLLOWING
    )) * 100, 2) AS consumption_vs_range_avg_pct
FROM
    energy_consumption_cte
ORDER BY
    sqft ASC;

注意：这种窗口函数写法仅在sqft值连续分布时准确，如果存在sqft间隙，会导致范围计算不准确，因此更推荐第一种LATERAL JOIN的方案。

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