使用别名在ROW_NUMBER的ORDER BY中生成行号时遇42703错误的解决方法

这个报错是PostgreSQL的SQL解析规则导致的——在同一个SELECT子句里，列别名不能被窗口函数的OVER子句直接引用。PostgreSQL会先解析窗口函数，再处理SELECT里的列别名定义，所以当窗口函数尝试读取total_comments时，这个别名还没被系统识别，就会抛出column "total_comments" does not exist的错误。

两种可行的解决方法： #

1. 直接重复计算表达式

如果你的计算逻辑比较简单，直接把(product_positive + product_negative)放到OVER子句的ORDER BY中即可，不需要依赖别名：

select 
    cr_seller_history_id, 
    c.created_at, 
    company_name, 
    business_name, 
    brand, 
    kep_mail, 
    address, 
    phone, 
    mail, 
    slug, 
    name, 
    point, 
    contact_positive, 
    contact_negative, 
    product_number, 
    (product_positive + product_negative) as total_comments, 
    ROW_NUMBER() OVER(ORDER BY (product_positive + product_negative)) as rank 
from cr_companies a 
INNER JOIN cr_sellers b ON a.cr_company_id = b.cr_company_id 
INNER JOIN cr_seller_histories c ON b.cr_seller_id = c.cr_seller_id 
WHERE DATE(c.created_at) = DATE 'yesterday' 
ORDER BY total_comments DESC NULLS LAST

2. 用子查询/CTE封装计算结果

如果计算逻辑复杂，重复写会降低可读性，推荐先把total_comments的计算放到子查询或者CTE里，再在外层使用窗口函数：

子查询版本：

select 
    cr_seller_history_id, 
    created_at, 
    company_name, 
    business_name, 
    brand, 
    kep_mail, 
    address, 
    phone, 
    mail, 
    slug, 
    name, 
    point, 
    contact_positive, 
    contact_negative, 
    product_number, 
    total_comments, 
    ROW_NUMBER() OVER(ORDER BY total_comments) as rank 
from (
    select 
        cr_seller_history_id, 
        c.created_at, 
        company_name, 
        business_name, 
        brand, 
        kep_mail, 
        address, 
        phone, 
        mail, 
        slug, 
        name, 
        point, 
        contact_positive, 
        contact_negative, 
        product_number, 
        (product_positive + product_negative) as total_comments
    from cr_companies a 
    INNER JOIN cr_sellers b ON a.cr_company_id = b.cr_company_id 
    INNER JOIN cr_seller_histories c ON b.cr_seller_id = c.cr_seller_id 
    WHERE DATE(c.created_at) = DATE 'yesterday'
) as sub
ORDER BY total_comments DESC NULLS LAST

CTE版本（可读性更强）：

WITH company_seller_data AS (
    select 
        cr_seller_history_id, 
        c.created_at, 
        company_name, 
        business_name, 
        brand, 
        kep_mail, 
        address, 
        phone, 
        mail, 
        slug, 
        name, 
        point, 
        contact_positive, 
        contact_negative, 
        product_number, 
        (product_positive + product_negative) as total_comments
    from cr_companies a 
    INNER JOIN cr_sellers b ON a.cr_company_id = b.cr_company_id 
    INNER JOIN cr_seller_histories c ON b.cr_seller_id = c.cr_seller_id 
    WHERE DATE(c.created_at) = DATE 'yesterday'
)
select 
    *,
    ROW_NUMBER() OVER(ORDER BY total_comments) as rank 
from company_seller_data
ORDER BY total_comments DESC NULLS LAST

小提示： #

如果你的排序需求和最终的ORDER BY total_comments DESC NULLS LAST一致，记得把窗口函数里的ORDER BY也改成同样规则，不然生成的rank可能不符合预期，比如：

ROW_NUMBER() OVER(ORDER BY total_comments DESC NULLS LAST) as rank

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