如何在同一SELECT语句中引用派生列以提升查询可读性？

我定义了如下表结构：

CREATE TABLE status_table 
(
    base_name  text      NOT NULL
    , version    smallint  NOT NULL
    , ref_time   int       NOT NULL
    , processed  bool      NOT NULL
    , processing bool      NOT NULL
    , updated    int       NOT NULL  DEFAULT (extract(epoch from now()) / 60)
    , PRIMARY KEY (base_name, version)
);

现有如下SELECT查询：

SELECT 
    ref_time
    , MAX(updated) AS max_updated
    , COUNT(*) AS total
    , COUNT(*) FILTER (WHERE processed) AS proc
    , ROUND(COUNT(*) FILTER (WHERE processed) * 100.0 / COUNT(*), 1) AS percent
    , ROUND(ROUND(COUNT(*) FILTER (WHERE processed) * 1.0 / COUNT(*), 1) * 100) AS rounded
    , COUNT(*) FILTER (WHERE processed) = COUNT(*) AS complete
    , MAX(updated) < (ROUND(EXTRACT(epoch from now()) / 60) - 200) AS settled
    , (COUNT(*) FILTER (WHERE processed) = COUNT(*)) AND (MAX(updated) < (ROUND(EXTRACT(epoch from now()) / 60) - 200)) AS ready
FROM 
    status_table
GROUP BY 
    ref_time
ORDER BY 
    ready DESC, rounded DESC, ref_time DESC

这个查询包含大量重复表达式（比如count(*) FILTER (WHERE processed)），尽管数据库会自动优化性能，但可读性极差。我希望能复用已定义的派生列，像下面这样编写：

SELECT ref_time
  , max(updated) AS max_updated
  , count(*) AS total
  , count(*) FILTER (WHERE processed) AS proc
  , round(proc * 100.0 / total, 1) AS percent
  , round(round(proc * 1.0 / total, 1) * 100) AS rounded
  , processed = total AS complete
  , round(extract(epoch from now()) / 60) AS now_mins
  , 200 AS interval_mins
  , max_updated < now_mins - interval_mins AS settled
  , complete AND settled AS ready
FROM status_table
GROUP BY ref_time
ORDER BY ready DESC, rounded DESC, ref_time DESC

但执行时会抛出column "proc" does not exist错误。我尝试过CTE但因GROUP BY逻辑混乱没搞成，请问这种场景下该如何提升查询的可读性？

在PostgreSQL中，SELECT子句定义的别名无法在同一句子中直接引用——这是因为SQL的执行顺序是先处理FROM/GROUP BY，再计算SELECT中的表达式，最后处理ORDER BY。要实现表达式复用，有几种简洁可行的方式：

方法1：派生表（子查询） #

先在子查询中完成聚合计算，得到基础统计字段，再在外层查询中基于这些字段生成派生列：

SELECT 
    ref_time,
    max_updated,
    total,
    proc,
    ROUND(proc * 100.0 / total, 1) AS percent,
    ROUND(ROUND(proc * 1.0 / total, 1) * 100) AS rounded,
    proc = total AS complete,
    now_mins,
    interval_mins,
    max_updated < now_mins - interval_mins AS settled,
    complete AND settled AS ready
FROM (
    SELECT 
        ref_time,
        MAX(updated) AS max_updated,
        COUNT(*) AS total,
        COUNT(*) FILTER (WHERE processed) AS proc,
        ROUND(EXTRACT(epoch from now()) / 60) AS now_mins,
        200 AS interval_mins
    FROM status_table
    GROUP BY ref_time
) AS aggregated
ORDER BY ready DESC, rounded DESC, ref_time DESC;

方法2：CTE（公共表表达式） #

逻辑和派生表一致，只是将聚合部分提取到CTE中，结构更清晰，可读性更强：

WITH aggregated AS (
    SELECT 
        ref_time,
        MAX(updated) AS max_updated,
        COUNT(*) AS total,
        COUNT(*) FILTER (WHERE processed) AS proc,
        ROUND(EXTRACT(epoch from now()) / 60) AS now_mins,
        200 AS interval_mins
    FROM status_table
    GROUP BY ref_time
)
SELECT 
    ref_time,
    max_updated,
    total,
    proc,
    ROUND(proc * 100.0 / total, 1) AS percent,
    ROUND(ROUND(proc * 1.0 / total, 1) * 100) AS rounded,
    proc = total AS complete,
    now_mins,
    interval_mins,
    max_updated < now_mins - interval_mins AS settled,
    complete AND settled AS ready
FROM aggregated
ORDER BY ready DESC, rounded DESC, ref_time DESC;

方法3：LATERAL子查询（适合复杂场景） #

如果需要更灵活的字段拆分计算，可以用LATERAL子查询单独处理派生列：

SELECT 
    a.ref_time,
    a.max_updated,
    a.total,
    a.proc,
    p.percent,
    p.rounded,
    a.proc = a.total AS complete,
    a.now_mins,
    a.interval_mins,
    a.max_updated < a.now_mins - a.interval_mins AS settled,
    (a.proc = a.total) AND (a.max_updated < a.now_mins - a.interval_mins) AS ready
FROM (
    SELECT 
        ref_time,
        MAX(updated) AS max_updated,
        COUNT(*) AS total,
        COUNT(*) FILTER (WHERE processed) AS proc,
        ROUND(EXTRACT(epoch from now()) / 60) AS now_mins,
        200 AS interval_mins
    FROM status_table
    GROUP BY ref_time
) AS a,
LATERAL (
    SELECT 
        ROUND(a.proc * 100.0 / a.total, 1) AS percent,
        ROUND(ROUND(a.proc * 1.0 / a.total, 1) * 100) AS rounded
) AS p
ORDER BY ready DESC, rounded DESC, ref_time DESC;

以上几种写法都能避免重复编写聚合表达式，大幅提升查询可读性，且PostgreSQL的查询优化器会自动处理这些逻辑，不会带来性能损耗。

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