PostgreSQL中如何为ORDER BY中的长窗口表达式赋值变量？

方法1：直接复用SELECT里的列别名 #

你SELECT子句里已经把这个窗口表达式命名为"Cumm. Sum"了，PostgreSQL允许在ORDER BY里直接引用SELECT列表中的列别名，直接修改ORDER BY部分就能简化：

SELECT DISTINCT customer_type.type AS "Customer type",
        count(customer_bio) over(w) as "Number of type",
        cast(cast(count(customer_bio) over(w) AS DECIMAL (7,2)) / cast(count(customer_bio) over() AS DECIMAL (7,2)) * 100 AS DECIMAL (7,2)) as "Percentage",
        sum(count(customer_bio)) OVER (ORDER by type) AS "Cumm. Sum"
FROM customer_type
JOIN customer_bio
ON customer_type.type_id=customer_bio.type_id
GROUP BY type, customer_bio
WINDOW w as (PARTITION by type) 
ORDER BY "Cumm. Sum" DESC

方法2：在WINDOW子句里定义窗口别名 #

如果想把ORDER by type这个窗口逻辑单独定义成类似tp的"变量"，可以扩展WINDOW子句，之后在需要的地方直接引用这个别名：

SELECT DISTINCT customer_type.type AS "Customer type",
        count(customer_bio) over(w) as "Number of type",
        cast(cast(count(customer_bio) over(w) AS DECIMAL (7,2)) / cast(count(customer_bio) over() AS DECIMAL (7,2)) * 100 AS DECIMAL (7,2)) as "Percentage",
        sum(count(customer_bio)) OVER tp AS "Cumm. Sum"
FROM customer_type
JOIN customer_bio
ON customer_type.type_id=customer_bio.type_id
GROUP BY type, customer_bio
WINDOW w as (PARTITION by type), tp as (ORDER by type) 
ORDER BY sum(count(customer_bio)) OVER tp DESC

要是想更简洁，ORDER BY也可以直接用"Cumm. Sum"，结合窗口定义能让整个查询更清爽。

方法3：用子查询/CTE封装计算 #

如果后续逻辑更复杂，也可以把需要排序的字段先在子查询或CTE里计算好，外层直接用别名排序：

WITH customer_stats AS (
    SELECT DISTINCT customer_type.type AS "Customer type",
            count(customer_bio) over(w) as "Number of type",
            cast(cast(count(customer_bio) over(w) AS DECIMAL (7,2)) / cast(count(customer_bio) over() AS DECIMAL (7,2)) * 100 AS DECIMAL (7,2)) as "Percentage",
            sum(count(customer_bio)) OVER (ORDER by type) AS "Cumm. Sum"
    FROM customer_type
    JOIN customer_bio
    ON customer_type.type_id=customer_bio.type_id
    GROUP BY type, customer_bio
    WINDOW w as (PARTITION by type) 
)
SELECT *
FROM customer_stats
ORDER BY "Cumm. Sum" DESC

这几种方法里，方法1最直接高效——既然你已经在SELECT里定义了这个列的别名，完全没必要重复写冗长的窗口表达式。

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