无法在Looker中使用嵌套窗口函数

Question

它类似于MYSQL，但无法理解为什么我无法在窗口函数中同时使用SUM和POWER。具体地说，就是和(幂(“DELTA”...line抛出以下错误：

SQL compilation error: Window function [AVG(CAST(VALUE AS NUMBER(38,3))) OVER (PARTITION BY ID)] may not be nested inside another window function.

删除此行或将其移到第二个select语句可以修复错误，而其他所有语句都可以修复错误。我认为这是我对SQL的一个更基本的误解。如果有任何想法，我们将不胜感激！

WITH UTILS 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",
  (VALUE-"TEMP_AVG") AS "DELTA",
  SUM(POWER("DELTA", 3)) OVER(PARTITION BY ID) AS "SKEW2"
FROM
  TABLE1
)
SELECT 
  "SKEW2"
FROM
  UTILS

Answer 1

为了清楚起见，您的问题并不完全是POWER，而是POWER引用了TEMP_AVG，这是一个涉及window function的派生列。

在标准SQL中，我们也不能在同一select list中的后续项中引用一个派生列。

这是一个问题：

SELECT a + b AS c
     , c + d AS e
  FROM t1
;

以下是标准SQL规范的一些片段，它们专门涉及窗口函数和一些支持项。

你问的是“基础SQL”。虽然您的数据库可能支持非标准行为，但理解标准SQL提供的行为可能会有所帮助，至少从基础的角度来看是这样。

这只是该规范的一个稍微老一点的版本(2011-12年的基础文档)的一小部分细节。这在很大程度上没有改变。

在部分：7.12 <query specification>中，我们有：

<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>

您可以看到，您的select list后面跟着一个table expression。

您的问题基本上是关于<select list>中允许的内容。

但是，为了理解select list行为，我们需要了解一下术语<table expression>

<table expression> ::=
   <from clause>
     [ <where clause> ]
     [ <group by clause> ]
     [ <having clause> ]
     [ <window clause> ]

在<query expression>的相应Syntax Rules中，我们可以找到以下内容：

2) Let T be the result of the <table expression> simply contained in QSPEC.

基本上，T是FROM子句的结果，该子句可以选择包括WHERE、GROUP BY等。

现在让我们跳到您所询问的表达式window function。

适用的Syntax Rule：

11) Each column reference contained in a <window function> shall unambiguously reference a column of T.

在本例中，您引用的是当前select list中的derived column，而不是T列。

这就是问题所在。该标准限制您只能使用T列。

请注意，我从庞大的规范中挑选了非常小的部分。但我认为这指出了基本的细节。

一种常见的方法是简单地将window function移动到后续查询表达式，以便它引用包含第一个派生列的T。

WITH cte1 AS (
       SELECT ...
            , AVG(...) OVER ... AS temp_avg
         FROM t1
     )
SELECT ...
     , SUM(temp_avg) OVER ...
  FROM cte1
;

在某些情况下，如果您愿意，可以只重复派生列中使用的表达式，而不使用单独的CTE术语或派生表。至少在标准SQL中，我们不能用两个窗口函数做到这一点。