了解聚合視窗函式

考慮下表：

CREATE TABLE T1
(
 keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
 col1   VARCHAR(10) NOT NULL
);

INSERT INTO T1 VALUES
 (2, 'A'),(3, 'A'),
 (5, 'B'),(7, 'B'),(11, 'B'),
 (13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');

目前，我正在研究視窗函式並嘗試聚合視窗函式。OVER雖然我覺得我了解如何使用and子句定義視窗PARITION，但我不確定如何計算聚合視窗函式，例如AVG() OVER ().

我希望了解以下三個查詢。

-- Query 1
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg 
FROM T1 

關鍵點 | col1 | 行平均
-----: | :--- | -----:
 2 | 一個 | 2
 3 | 一個 | 2
 5 | 乙| 7
 7 | 乙| 7
 11 | 乙| 7
 13 | C | 18
 17 | C | 18
 19 | C | 18
 23 | C | 18

-- Query 2
SELECT keycol, col1, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1 

關鍵點 | col1 | 行平均
-----: | :--- | -----:
 2 | 一個 | 2
 3 | 一個 | 2
 5 | 乙| 3
 7 | 乙| 4
 11 | 乙| 5
 13 | C | 6
 17 | C | 8
 19 | C | 9
 23 | C | 11

-- Query 3
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1 

關鍵點 | col1 | 行平均
-----: | :--- | -----:
 2 | 一個 | 2
 3 | 一個 | 2
 5 | 乙| 5
 7 | 乙| 6
 11 | 乙| 7
 13 | C | 13
 17 | C | 15
 19 | C | 16
 23 | C | 18

查詢 1：我相信 RowAvg 應該是每個 col1 級別的行的平均值。數字 2 和 7 是平均數還是我的理解不正確？

查詢 2：我不太確定在這裡生成 RowAvg 正在做什麼。由於這裡沒有使用 PARTITION 或框架，我認為視窗應該是整個表，這是正確的嗎？另外，如何找到 RowAvg？

查詢 3：這是否找到了每個分區的 (FLOOR) 平均值，但是這樣做是增量的？也就是說，對於第一個分區 (‘A’) 的第 1 行，我們找到該行的平均值。然後，對於第一個分區的第 2 行，我們找到前 2 行的平均值。

一般問題：ORDER BY在聚合視窗函式中引入是否“連續”執行聚合函式，例如在查詢 1 和 2 中？有趣的是，在查詢 1 中，AVG對每個分區作為一個整體執行，而在查詢 1 和 2 中，每一行的 RowAvg 幾乎不同。

我建議您添加一個總和以了解發生了什麼：

SELECT col1
    , keycol
    , SUM(keycol)  OVER (PARTITION BY col1) AS mysum
    , AVG(keycol) OVER (PARTITION BY col1) AS RowAvg 
FROM T1
order by col1;

由於您的視窗中沒有 order by，因此聚合適用於整個分區：

col1    keycol  mysum   RowAvg
A       2       5       2
A       3       5       2
B       5       23      7
B       7       23      7
B       11      23      7
C       13      72      18
C       17      72      18
C       19      72      18
C       23      72      18

即對於分區A，對於分區中的每一行，mysum = 2+3

如果您使用 ORDER by 子句，則聚合從開頭應用到目前行：

SELECT keycol
, col1
, SUM(keycol)  OVER (ORDER BY keycol) AS mysum
, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1
order by col1;

由於您沒有分區，因此整個結果集被視為 1 個分區：

keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       10      3
7       B       17      4
11      B       28      5
...

對於第一行（根據順序） mysum = 2, rowavg = 2/1 ，第二行 mysum = 2+3, rowavg = 5/2 ，第三行 mysum = 2+3+5 rowavg = 10/3 …

如您所見， sum(…) 成為累積和

對於分區和順序，聚合適用於每個分區，但具有上述行為：

SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1 ORDER BY keycol) AS mysum
, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1
order by col1;


keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       5       5
7       B       12      6
...

對於 A，你得到 mysum 2, 2+3。對於 B，它重新啟動，因此變為 5、5+7、

此外，您可以覆蓋預設行為，即：

SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1 
                    ORDER BY keycol
                    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ...
FROM T1
order by col1;

通過聲明你自己的，例如，如果你想要一個超過 3 行的滑動平均值：

SELECT keycol
, col1
, AVG(keycol)  OVER (PARTITION BY col1 
                    ORDER BY keycol
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as ... 

...
13  C   15
17  C   16
19  C   19
23  C   21

第一行的平均值為 (13+17)/2（因為沒有前一行），第二行為 (13+17+19)/3, … 第四行變為 (19+23)/2 （後面沒有行）

擺弄例子

引用自：https://dba.stackexchange.com/questions/256333