PostgreSQL中后续CTE引用前置CTE报错问题求助

核心错误原因 #

PostgreSQL对标识符（列名、表名）的大小写处理规则和SQL Server/Synapse不同：

• 如果用双引号定义列名（比如"MOST_ORDERED_DAY"），该列名会被保留大小写，后续引用必须同样用双引号包裹。
• 如果不用双引号，PostgreSQL会自动将标识符转为小写。

你的报错是因为在CTE_pd_or_dt中用双引号定义了"MOST_ORDERED_DAY"列，但在CTE_or_dt关联时直接写CTE_pd_or_dt.MOST_ORDERED_DAY，PostgreSQL会自动转为小写most_ordered_day，导致找不到对应列。

额外需要修正的问题 #

1. 主查询中关联CTE_or_dt时，错误引用了不存在的C.order_date列，CTE_or_dt的对应列是"MOST_ORDERED_DAY"。
2. 子查询中的top_product未定义，应该使用主查询中CTE_mst_rv_prod的别名A的total_reviews字段。
3. PostgreSQL中判断空值的标准语法是IS NULL，而非ISNULL。

修正后的完整SQL代码 #

WITH RECURSIVE CTE_mst_rv_prod AS (
    -- 获取评论最多的产品
        SELECT product_id, COUNT(review) AS "total_reviews", 
        RANK() OVER(ORDER BY COUNT(review) DESC) AS "Rank"
        FROM public.reviews
        GROUP BY product_id
        ORDER BY "Rank"
        LIMIT 1
    ),
    CTE_pd_or_dt AS (
    -- 获取该产品订单量最多的日期
        SELECT P.product_id, O.order_date AS "MOST_ORDERED_DAY", COUNT(order_id) AS "COUNT_OF_ORDERS"
        FROM CTE_mst_rv_prod P
        JOIN public.orders O ON P.product_id = O.product_id
        GROUP BY P.product_id, O.order_date
        ORDER BY "COUNT_OF_ORDERS" DESC
        LIMIT 1
    ),
    CTE_or_dt AS(
    -- 判断该日期是否为公共假日
        SELECT D.calendar_dt AS "MOST_ORDERED_DAY",
            CASE 
                WHEN D.day_of_the_week_num IN (1,5) AND D.working_day = false THEN TRUE
                ELSE FALSE
            END AS is_public_holiday
        FROM CTE_pd_or_dt 
        INNER JOIN public.dim_dates D ON
        "CTE_pd_or_dt"."MOST_ORDERED_DAY" = D.calendar_dt
    )
    -- 注：CTE_shp_cond未在主查询中使用，可根据需要保留或删除
    -- CTE_shp_cond AS(
    -- SELECT O.order_date, O.product_id, S.shipment_date, S.delivery_date,
    --     O.order_id, CASE WHEN S.shipment_date - O.order_date >= 6 AND
    --     delivery_date IS NULL THEN 'late' ELSE 'early'
    --     END AS "shipment_condition"
    -- FROM public.orders O
    -- JOIN public.shipments_deliveries S ON S.order_id = O.order_id
    -- JOIN CTE_mst_rv_prod RV ON O.product_id = RV.product_id
    -- )
    
SELECT 
    A.product_id, 
    B."MOST_ORDERED_DAY", 
    C.is_public_holiday, 
    A."total_reviews",

    (SELECT ROUND((COUNT(*) * 100.00)/ A."total_reviews", 4)
     FROM public.reviews AS r2 
     WHERE r2.product_id = A.product_id AND r2.review = 1) AS "pct_one_star_review",
     
     (SELECT ROUND((COUNT(*) * 100.00)/ A."total_reviews", 4)
     FROM public.reviews AS r2
     WHERE r2.product_id = A.product_id AND r2.review = 2)   AS "pct_two_star_review",
     
     (SELECT ROUND((COUNT(*) * 100.00)/ A."total_reviews", 4)
     FROM public.reviews AS r2
     WHERE r2.product_id = A.product_id AND r2.review = 3)   AS "pct_three_star_review",
     
     (SELECT ROUND((COUNT(*) * 100.00)/ A."total_reviews", 4)
     FROM public.reviews AS r2
     WHERE r2.product_id = A.product_id AND r2.review = 4)   AS "pct_four_star_review",
     
     (SELECT ROUND((COUNT(*) * 100.00)/ A."total_reviews", 4)
     FROM public.reviews AS r2
     WHERE r2.product_id = A.product_id AND r2.review = 5)   AS "pct_five_star_review"

FROM CTE_mst_rv_prod A
LEFT JOIN CTE_pd_or_dt B ON A.product_id = B.product_id
LEFT JOIN CTE_or_dt C ON C."MOST_ORDERED_DAY" = B."MOST_ORDERED_DAY"

关键修正点说明 #

1. 列名引用修正：所有用双引号定义的列（如"MOST_ORDERED_DAY"、"total_reviews"），后续引用时都加上双引号，保持大小写一致。
2. 关联条件修正：主查询中CTE_or_dt与CTE_pd_or_dt的关联条件改为C."MOST_ORDERED_DAY" = B."MOST_ORDERED_DAY"，匹配实际存在的列。
3. 子查询优化：子查询直接使用主查询的A.product_id关联，避免重复关联CTE_mst_rv_prod，同时用A."total_reviews"替代未定义的top_product.total_reviews。
4. 语法细节修正：将delivery_date ISNULL改为PostgreSQL标准的delivery_date IS NULL。

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