如何修正Snowflake SQL中多项式系数提取的无效标识符错误

错误核心原因 #

报错invalid identifier 'engine_temperature_1'的直接原因是：

• 内层子查询仅返回segment_id、x、TIME三个字段，外层查询计算AVG(engine_temperature_1)时找不到该字段。
• 内层子查询中计算x的AVG(engine_temperature_1)未按segment_id分组，会计算全局平均值，不符合分段统计需求。
• 额外存在语法错误：CAST (VIMS:_engine_temperature_2)缺少目标数据类型，会触发语法报错。
• 原多项式系数计算逻辑不符合最小二乘法，无法正确提取二次多项式的拟合系数。

具体修改点 #

• 补全CAST类型：将CAST (VIMS:_engine_temperature_2)修改为CAST (VIMS:_engine_temperature_2 AS FLOAT) AS engine_temperature_2。
• 修正分段平均值计算：用窗口函数AVG(engine_temperature_1) OVER (PARTITION BY segment_id)替代直接AVG(engine_temperature_1)，确保计算每个分段的平均温度。
• 修正字段缺失问题：在内层子查询中保留engine_temperature_1字段，或在外层单独计算分段平均温度。
• 修复多项式系数逻辑：使用最小二乘法正规方程计算二次多项式（y = ax² + bx + c）的系数，保证拟合结果准确。
• 优化分段逻辑：ROW_NUMBER()添加PARTITION BY SERIAL_NUMBER，避免多设备数据混排导致分段错误。

修正后的完整SQL #

WITH C1 AS (
SELECT
    SERIAL_NUMBER,
    EVENT_DATE,
    CAST(EVENT_TS AS FLOAT) AS TIME, -- 提前转换为FLOAT，避免重复计算
    CAST(VIMS:_engine_speed AS FLOAT) AS ENGSPD,
    CAST(VIMS:_engine_load AS FLOAT) AS Engine_load,
    CAST(VIMS:_engine_temperature_1 AS FLOAT) AS engine_temperature_1,
    CAST(VIMS:_engine_temperature_2 AS FLOAT) AS engine_temperature_2, -- 补全CAST类型
    CAST(VIMS:_engine_temperature_3 AS FLOAT) AS engine_temperature_3,
    CAST(VIMS:_engine_temperature_4 AS FLOAT) AS engine_temperature_4,
    FLOOR((ROW_NUMBER() OVER (PARTITION BY SERIAL_NUMBER ORDER BY EVENT_TS) - 1) / 100) + 1 AS segment_id
FROM "HELIOS_TDH_VIMS_PROD_DB"."BASE"."DATA_LOGGER"
WHERE
    SERIAL_NUMBER IN ('KSN00205')
    AND EVENT_DATE = '2022-11-18'
),
segment_stats AS (
    SELECT
        segment_id,
        COUNT(*) AS n,
        AVG(engine_temperature_1) AS avg_y,
        AVG(TIME) AS avg_x,
        SUM(TIME) AS sum_x,
        SUM(TIME * TIME) AS sum_x2,
        SUM(TIME * TIME * TIME) AS sum_x3,
        SUM(TIME * TIME * TIME * TIME) AS sum_x4,
        SUM(engine_temperature_1) AS sum_y,
        SUM(engine_temperature_1 * TIME) AS sum_xy,
        SUM(engine_temperature_1 * TIME * TIME) AS sum_x2y
    FROM C1
    GROUP BY segment_id
),
polynomial_coeffs AS (
    SELECT
        segment_id,
        -- 最小二乘法正规方程求解二次系数
        (n * sum_x2y - sum_x * sum_xy + sum_x2 * sum_y - n * avg_x * sum_xy + n * avg_x * avg_x * avg_y - sum_x2 * avg_y) 
        / (n * sum_x4 - sum_x2 * sum_x2) AS a,
        (sum_xy - a * sum_x3 - avg_y * sum_x + a * avg_x * sum_x2) / sum_x2 AS b,
        avg_y - a * avg_x * avg_x - b * avg_x AS c
    FROM segment_stats
)
SELECT
    C1.TIME,
    C1.segment_id,
    AVG(C1.engine_temperature_1) OVER (PARTITION BY C1.segment_id) AS avg_temp,
    C1.engine_temperature_1 - (a * POWER(C1.TIME, 2) + b * C1.TIME + c) AS residual
FROM C1
JOIN polynomial_coeffs ON C1.segment_id = polynomial_coeffs.segment_id
ORDER BY C1.segment_id, C1.TIME;

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