主页 / dba / 问题 / 256333
Accepted
TMilliman
Asked: 2019-12-27 16:59:39 +0800 CST

了解聚合窗口函数

  • 772

考虑下表：

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 几乎不同。

sql-server window-functions

1 个回答

  1. Best Answer

    我建议您添加一个总和以了解发生了什么：

    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 （后面没有行）

    摆弄例子

    • 3

相关问题