PostgreSQL SELECT语句中如何遵循DRY原则避免重复窗口函数？

首先修正你原查询里的一个问题：两个CASE语句的别名和前面的avg_unit_price、median_unit_price重复了，运行会报错，得改成不同的名字，比如avg_price_status和median_price_status。

下面提供两种遵循DRY原则的方案，避免重复编写窗口函数表达式：

方案一：使用CTE（公共表表达式）预计算窗口值 #

先在CTE里把需要的聚合字段、窗口计算值都算好，主查询直接引用这些预计算的字段，不用重复写窗口逻辑：

WITH product_category_stats AS (
    SELECT
        C.category_name,
        P.product_name,
        P.unit_price,
        -- 预计算分类下的平均单价（未四舍五入，用于后续比较）
        AVG(P.unit_price) OVER (PARTITION BY C.category_name) AS cat_avg_price,
        -- 预计算分类下的中位数（未四舍五入，用于后续比较）
        (MAX(P.unit_price) OVER (PARTITION BY C.category_name) + MIN(P.unit_price) OVER (PARTITION BY C.category_name)) / 2 AS cat_median_price
    FROM
        products as P
        JOIN categories as C USING(category_id)
    WHERE
        P.discontinued = 0
)
SELECT
    category_name,
    product_name,
    SUM(unit_price) AS total_unit_price, -- 原别名unit_price容易混淆，改成更清晰的名字
    ROUND(cat_avg_price::numeric, 2) AS avg_unit_price,
    ROUND(cat_median_price::numeric, 2) AS median_unit_price,
    CASE
        WHEN SUM(unit_price) < cat_avg_price THEN 'BELOW AVERAGE'
        WHEN SUM(unit_price) > cat_avg_price THEN 'OVER AVERAGE'
        ELSE 'AVERAGE'
    END AS avg_price_status,
    CASE
        WHEN SUM(unit_price) < cat_median_price THEN 'BELOW MEDIAN'
        WHEN SUM(unit_price) > cat_median_price THEN 'OVER MEDIAN'
        ELSE 'MEDIAN'
    END AS median_price_status
FROM product_category_stats
GROUP BY
    product_name,
    category_name,
    unit_price,
    cat_avg_price,
    cat_median_price
ORDER BY
    category_name ASC;

方案二：使用WINDOW子句简化窗口定义 #

如果只是想减少PARTITION BY C.category_name的重复书写，可以用WINDOW子句先定义窗口，再在计算中引用：

SELECT
    C.category_name,
    P.product_name,
    SUM(P.unit_price) AS total_unit_price,
    ROUND(AVG(P.unit_price) OVER cat_window::numeric, 2) AS avg_unit_price,
    ROUND(((MAX(P.unit_price) OVER cat_window + MIN(P.unit_price) OVER cat_window)/2)::numeric, 2) AS median_unit_price,
    CASE
        WHEN SUM(P.unit_price) < AVG(P.unit_price) OVER cat_window THEN 'BELOW AVERAGE'
        WHEN SUM(P.unit_price) > AVG(P.unit_price) OVER cat_window THEN 'OVER AVERAGE'
        ELSE 'AVERAGE'
    END AS avg_price_status,
    CASE
        WHEN SUM(P.unit_price) < ((MAX(P.unit_price) OVER cat_window + MIN(P.unit_price) OVER cat_window)/2) THEN 'BELOW MEDIAN'
        WHEN SUM(P.unit_price) > ((MAX(P.unit_price) OVER cat_window + MIN(P.unit_price) OVER cat_window)/2) THEN 'OVER MEDIAN'
        ELSE 'MEDIAN'
    END AS median_price_status
FROM
    products as P
    JOIN categories as C USING(category_id)
WHERE
    P.discontinued = 0
WINDOW cat_window AS (PARTITION BY C.category_name)
GROUP BY
    P.product_name,
    C.category_name,
    P.unit_price
ORDER BY
    C.category_name ASC;

注意事项 #

• 原查询中SUM(P.unit_price)按P.product_name, C.category_name, p.unit_price分组，其实SUM(P.unit_price)结果就是p.unit_price本身，因为每个分组对应唯一的product_name和unit_price，可根据实际需求调整分组逻辑。
• 你用(MAX+MIN)/2计算的是范围中位数，不是统计学上的真正中位数，如果需要准确中位数，PostgreSQL 14+可以用PERCENTILE_CONT(0.5)窗口函数，比如PERCENTILE_CONT(0.5) OVER (PARTITION BY C.category_name ORDER BY P.unit_price)。

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