SQL 在 Teradata 中，ROWS UNBOUNDED PRECEDING 用于什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30861919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is ROWS UNBOUNDED PRECEDING used for in Teradata?
提问by StrugglingCoder
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 中遇到了一个名为“无界行数”的有序分析函数。我尝试了几个站点来了解该功能，但所有站点都使用了一个复杂的示例来解释相同的内容。你能否给我提供一个简单的例子，以便我能够清楚地了解基础知识？
回答by Lukas Eder
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).
如您所见，每个平均值都是在由前一行 ( ) 和后续行 ( )之间的范围组成的有序框架上“计算”的。1 preceding1 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...
屈服...
t a sum
---------
1 1 1
2 5 6
3 3 9
4 5 14
5 4 18
6 11 29
Here's another very good explanations of SQL window functions.
回答by dnoeth
ROWS UNBOUNDED PRECEDINGis no Teradata-specific syntax, it's Standard SQL. Together with the ORDER BYit 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.
逻辑上，基于起始行和结束行之间的所有 ROWS，为 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 FOLLOWING, all rows after the current row -> fixed
- x PRECEDING, x rows before the current row -> relative
- y FOLLOWING, y rows after the current row -> relative
- UNBOUNDED PRECEDING，当前行之前的所有行 -> 固定
- UNBOUNDED FOLLOWING，当前行之后的所有行 -> 固定
- x PRECEDING, 当前行之前的 x 行 -> 相对
- y FOLLOWING，当前行之后的 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 nrows
- 开始和结束行都是固定的，窗口由一个分区的所有行组成，例如一个组总和，即聚合加明细行
- 一端固定，另一端相对于当前行，行数增加或减少，例如运行总计，剩余总和
- 起始行和结束行相对于当前行，窗口内的行数是固定的，例如n行的移动平均线
So SUM(x) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING)results in a Cumulative Sumor Running Total
所以SUM(x) OVER (ORDER BY 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
