在 Teradata 中，ROWS UNBOUNDED PRECEDING 用于什么? [英] What is ROWS UNBOUNDED PRECEDING used for in Teradata?

问题描述

我刚刚开始使用 Teradata，我在 Teradata 中遇到了一个名为行无界前面"的有序分析函数.我尝试了几个站点来了解该功能，但所有站点都使用了一个复杂的示例来解释相同的内容.你能否给我提供一个简单的例子，以便我能够清楚地了解基础知识?

解决方案

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

一个简单的例子是计算三天框架内的平均金额.我在示例中使用 PostgreSQL 语法，但 Teradata 的语法是相同的:

WITH data (t, a) AS (值(1, 1),(2, 5),(3, 3),(4, 5),(5, 4),(6, 11))SELECT t, a, avg(a) OVER(按 t 行在 1 个前面和 1 个后面)来自数据按吨订购

...产生:

t a avg----------1 1 3.002 5 3.003 3 4.334 5 4.005 4 6.676 11 7.50

如您所见，每个平均值都是超过"计算的一个有序框架，该框架由前一行(1 前)和后一行(<代码>1个关注).

当您编写ROWS UNBOUNDED PRECEDING 时，框架的下限就是无限的.这在计算总和时很有用(即 "运行总计")，例如:

WITH data (t, a) AS (值(1, 1),(2, 5),(3, 3),(4, 5),(5, 4),(6, 11))SELECT t, a, sum(a) OVER(按无界前行和当前行之间的 t 行排序)来自数据按吨订购

屈服...

t 一个总和---------1 1 12 5 63 3 94 5 145 4 186 11 29

这里是 SQL 窗口函数的另一个很好的解释.>

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?

解决方案

It's the "frame" or "range" clause of window functions, which are part of the SQL standard and implemented in many databases, including 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:

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:

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.

这篇关于在 Teradata 中，ROWS UNBOUNDED PRECEDING 用于什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！