Looker无法使用嵌套窗口函数 SUM与POWER在窗口函数中组合调用报错

核心报错原因 #

• 标准SQL语法明确禁止窗口函数嵌套：不允许将一个窗口函数的输出，作为另一个窗口函数的输入参数在同一个SELECT层级使用
• 同一SELECT层级下的所有计算逻辑为并行执行：你在当前层定义的别名（包括TEMP_AVG、DELTA）无法被同层的其他计算逻辑引用，更不能嵌套进其他窗口函数内参与计算

你的代码触发限制的逻辑非常明确：SUM(POWER("DELTA",3)) OVER(PARTITION BY ID) 中的DELTA本身依赖同层窗口函数AVG(VALUE) OVER(PARTITION BY ID)的计算结果，本质就是把AVG窗口函数嵌套进了SUM窗口函数的计算逻辑中，所以触发了编译报错。

修复方案 #

只需要把依赖窗口函数结果的计算拆到下一个SELECT层级即可，推荐拆分为两层CTE，逻辑更清晰：

WITH LAYER1 AS (
SELECT
  ID,
  VALUE AS "TEMP_CELSIUS",
  AVG(VALUE) OVER(PARTITION BY ID) AS "TEMP_AVG",
  VAR_POP(VALUE) OVER(PARTITION BY ID) AS "TEMP_VAR",
  STDDEV_POP(VALUE) OVER(PARTITION BY ID) AS "TEMP_STD",
  COUNT(VALUE) OVER(PARTITION BY ID) AS "DEVICE_N"
FROM TABLE1
),
LAYER2 AS (
SELECT 
  *,
  (VALUE - "TEMP_AVG") AS "DELTA",
  SUM(POWER("DELTA", 3)) OVER(PARTITION BY ID) AS "SKEW2"
FROM LAYER1
)
SELECT "SKEW2" FROM LAYER2

额外注意原代码的两个笔误 #

• VALUE AS "TEMP_CELSIUS, 缺少右侧双引号，会触发基础语法报错
• (VALUE->"TEMP_AVG") 应为VALUE - "TEMP_AVG"，属于符号拼写错误

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