使用CnosDB执行SQL报错：sum(...)非字段名问题求助

问题原因 #

报错sum(...) ... Not a "field name"是因为CnosDB的SQL解析器无法直接处理连续对窗口函数结果进行除法运算的写法，解析时会误将窗口函数表达式当作字段名处理，导致识别失败。

解决方案 #

可以通过两种方式修正SQL：

方式1：用CTE/子查询提前计算窗口函数结果 #

将窗口函数的计算逻辑抽离到CTE（公共表表达式）中，给每个窗口函数结果指定别名，再基于别名完成除法运算，避免解析器的识别歧义：

WITH window_calcs AS (
    SELECT
        driver,
        date_trunc('day', time) AS c_date,
        time,
        load_capacity,
        fuel_capacity,
        sum(CASE WHEN elevation < 50 THEN elevation ELSE 0 END) OVER (PARTITION BY driver) AS low_elev_sum,
        sum(elevation) OVER (PARTITION BY driver) AS total_elev_sum
    FROM readings
    WHERE driver IN ('Seth','Albert')
        AND time >= '2017-01-01T00:00:00'
        AND time <= '2017-01-02T00:00:00'
)
SELECT
    driver,
    c_date,
    time,
    load_capacity,
    fuel_capacity,
    low_elev_sum / total_elev_sum / 2 AS elev_ratio
FROM window_calcs;

方式2：给每个窗口函数表达式添加括号 #

通过括号明确每个窗口函数的计算边界，让解析器正确识别运算逻辑：

SELECT
    driver,
    date_trunc('day', time) AS c_date,
    time,
    load_capacity,
    fuel_capacity,
    (sum(CASE WHEN elevation < 50 THEN elevation ELSE 0 END) OVER (PARTITION BY driver)) 
    / (sum(elevation) OVER (PARTITION BY driver)) 
    / 2
FROM readings
WHERE driver IN ('Seth','Albert')
    AND time >= '2017-01-01T00:00:00'
    AND time <= '2017-01-02T00:00:00';

额外说明 #

如果使用的是较旧版本的CnosDB，建议升级到最新稳定版，新版本对窗口函数的运算支持更完善，能减少这类解析歧义问题。

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