什么是ROWS UNBOUNDED PRECEDING用于Teradata？

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the same. Could you please provide me with a naive example so that I can get the basics clear.

我刚刚开始使用Teradata，我在Teradata遇到了一个名为“Rows unbounded preceding”的Ordered Analytical Function。我尝试了几个网站来了解这个功能，但是所有这些网站都使用了一个复杂的例子来解释它。能不能给我一个天真的例子，这样我就可以清楚地了解基础知识。

2 个解决方案

#1

It's the "frame" or "range" clause of window functions, which are part of the SQL standard and implemented in many databases, including Teradata.

它是窗口函数的“框架”或“范围”子句，它是SQL标准的一部分，并在许多数据库中实现，包括Teradata。

A simple example would be to calculate the average amount in a frame of three days. I'm using PostgreSQL syntax for the example, but it will be the same for Teradata:

一个简单的例子是计算三天帧中的平均数量。我正在使用PostgreSQL语法作为示例，但对于Teradata它将是相同的：

WITH data (t, a) AS (
  VALUES(1, 1),
        (2, 5),
        (3, 3),
        (4, 5),
        (5, 4),
        (6, 11)
)
SELECT t, a, avg(a) OVER (ORDER BY t ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM data
ORDER BY t

... which yields:

...产生：

t  a  avg
----------
1  1  3.00
2  5  3.00
3  3  4.33
4  5  4.00
5  4  6.67
6 11  7.50

As you can see, each average is calculated "over" an ordered frame consisting of the range between the previous row (1 preceding) and the subsequent row (1 following).

如您所见，每个平均值都是在一个有序帧“上方”计算的，该有序帧由前一行（前一行）和后一行（后一行）之间的范围组成。

When you write ROWS UNBOUNDED PRECEDING, then the frame's lower bound is simply infinite. This is useful when calculating sums (i.e. "running totals"), for instance:

当你编写ROWS UNBOUNDED PRECEDING时，帧的下限就是无限的。这在计算总和（即“运行总计”）时很有用，例如：

WITH data (t, a) AS (
  VALUES(1, 1),
        (2, 5),
        (3, 3),
        (4, 5),
        (5, 4),
        (6, 11)
)
SELECT t, a, sum(a) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM data
ORDER BY t

yielding...

产生...

Here's another very good explanations of SQL window functions.

这是SQL窗口函数的另一个非常好的解释。

#2

ROWS UNBOUNDED PRECEDING is no Teradata-specific syntax, it's Standard SQL. Together with the ORDER BY it defines the window on which the result is calculated.

ROWS UNBOUNDED PRECEDING没有特定于Teradata的语法，它是标准SQL。与ORDER BY一起定义了计算结果的窗口。

Logically a Windowed Aggregate Function is newly calculated for each row within the PARTITION based on all ROWS between a starting row and an ending row.

逻辑上，基于起始行和结束行之间的所有ROW，为PARTITION中的每一行新计算窗口化聚合函数。

Starting and ending rows might be fixed or relative to the current row based on the following keywords:

根据以下关键字，开始和结束行可能是固定的或相对于当前行：

UNBOUNDED PRECEDING, all rows before the current row -> fixed
UNBOUNDED PRECEDING，当前行之前的所有行 - >已修复
UNBOUNDED FOLLOWING, all rows after the current row -> fixed
UNBOUNDED FOLLOWING，当前行之后的所有行 - >已修复
x PRECEDING, x rows before the current row -> relative
x PRECEDING，当前行之前的x行 - > relative
y FOLLOWING, y rows after the current row -> relative
y关注，当前行后面的y行 - >相对

Possible kinds of calculation include:

可能的计算方式包括：

Both starting and ending row are fixed, the window consists of all rows of a partition, e.g. a Group Sum, i.e. aggregate plus detail rows
开始行和结束行都是固定的，窗口由分区的所有行组成，例如，组总和，即聚合加细节行
One end is fixed, the other relative to current row, the number of rows increases or decreases, e.g. a Running Total, Remaining Sum
一端固定，另一端相对于当前行，行数增加或减少，例如，剩余总和，剩余总和
Starting and ending row are relative to current row, the number of rows within a window is fixed, e.g. a Moving Average over n rows
开始和结束行是相对于当前行的，窗口内的行数是固定的，例如， n行的移动平均线

So SUM(x) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING) results in a Cumulative Sum or Running Total

所以SUM（x）OVER（通过col ROWS UNBOUNDED PRECEDING的顺序）导致累计总和或运行总计

11 -> 11
 2 -> 11 +  2                = 13
 3 -> 13 +  3 (or 11+2+3)    = 16
44 -> 16 + 44 (or 11+2+3+44) = 60

#1