SQL报错1054：字段'daily_distance_per_dollar'不存在，求CTE写法及错误分析

错误原因 #

你遇到的Unknown column 'daily_distance_per_dollar' in 'field list'错误，是因为CTE ct2 的查询结果里没有包含 daily_distance_per_dollar 列。在ct2的SELECT语句中，只选择了MONTH和计算出的average_distance_per_dollar，但后续查询ct2时却试图引用daily_distance_per_dollar，导致数据库找不到该列。

修正后的CTE实现代码 #

我们只需要在ct2中保留daily_distance_per_dollar列，就能正常计算每日值与月度平均值的绝对差值的平均值：

WITH
ct1 AS (
    SELECT 
        *,
        DATE_FORMAT(request_date, '%Y-%m') AS MONTH,
        distance_to_travel / monetary_cost AS daily_distance_per_dollar
    FROM uber_request_logs
),
ct2 AS (
    SELECT 
        MONTH,
        daily_distance_per_dollar,
        AVG(daily_distance_per_dollar) OVER(PARTITION BY MONTH) AS average_distance_per_dollar
    FROM ct1
)
SELECT
    MONTH,
    AVG(ABS(daily_distance_per_dollar - average_distance_per_dollar)) AS answer
FROM ct2
GROUP BY MONTH;

代码说明 #

1. ct1：提取原始数据，格式化日期为年月格式，同时计算每日的daily_distance_per_dollar（每美元行驶距离）。
2. ct2：基于ct1的数据，通过窗口函数OVER(PARTITION BY MONTH)计算每个月的平均每美元行驶距离，同时保留每日的daily_distance_per_dollar列。
3. 最终查询：按月份分组，计算每日值与月度平均值的绝对差值的平均值，得到所需结果。

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