如何重构PostgreSQL查询以避免重复使用extract(YEAR from date)表达式？

下面是几种可行的重构方式，帮你避免重复编写extract(YEAR from date)：

方法一：使用列别名（最简洁） #

PostgreSQL允许在GROUP BY和ORDER BY中直接引用SELECT子句里定义的列别名，这是最直观的写法：

SELECT extract(YEAR from date) AS transaction_year, AVG(amount) 
FROM transactions 
GROUP BY transaction_year 
ORDER BY transaction_year

方法二：使用CTE（公共表表达式） #

如果后续需要扩展查询逻辑，用CTE先预处理数据会更清晰，把提取年份的逻辑单独抽离：

WITH yearly_transactions AS (
    SELECT 
        extract(YEAR from date) AS transaction_year,
        amount
    FROM transactions
)
SELECT transaction_year, AVG(amount)
FROM yearly_transactions
GROUP BY transaction_year
ORDER BY transaction_year

方法三：使用子查询嵌套 #

和CTE效果类似，只是写法更紧凑，适合不需要复用预处理结果的场景：

SELECT transaction_year, AVG(amount)
FROM (
    SELECT 
        extract(YEAR from date) AS transaction_year,
        amount
    FROM transactions
) AS sub_query
GROUP BY transaction_year
ORDER BY transaction_year

不推荐的方法：使用位置序号 #

虽然可以通过列的位置序号来简化，但可读性极差，列顺序变动时容易出错，仅适合临时快速查询：

SELECT extract(YEAR from date), AVG(amount) 
FROM transactions 
GROUP BY 1 
ORDER BY 1

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