当前位置：文江博客话题详情

relational-algebra max aggregate-functions

聚合关系代数（最大值）

发布于 2024-10-16 20:41:52 字数 124 浏览 2 评论 0 原文

我目前正在做一项家庭作业，该作业需要进行选择，以提取包含与所有其他记录相比具有最大值的特定属性的元素。我读过许多在线资料，其中引用了称为最大值的“聚合”关系代数函数，但它们没有描述它如何使用基本运算符来工作。如何选择包含最大值的属性？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

森末i 2024-10-23 20:41:52

仅使用基本运算符就可以很好地表达聚合函数。这是一件非常漂亮的事情。

假设我们有一个表 T，我们想找到它的“value”字段的最大值。首先，我们应该计算 T 与其自身的笛卡尔积——或者更确切地说，与其自身的副本 T2 的笛卡尔积。然后，我们选择 T.value 小于 T2.value 的行：这会筛选出所有不需要的行，这些行的值小于其他行的值。为了获得最大值，我们应该从所有行的集合中减去这些不需要的行。就是这样。至少这是基本的想法，我们还需要使用投影来获得正确的尺寸。

不幸的是，我不知道如何在这里插入 Latex，但使用关系代数符号，它会是这样的：

π(T.a1...Tan, T.value)(T)
    -
π(T.a1...Tan, T.value)(
    σ(T.value<T2.value)( ρ(T, T2) x T )
)

其中 π 是投影运算符，- 是集合差值，σ 是选择运算符，ρ 是重命名运算符。

SQLishly：

SELECT T.* FROM T
    MINUS
SELECT T.* FROM T, T as T2 WHERE T.value<T2.value

更实际的是：

SELECT T.* FROM T LEFT JOIN T as T2 ON T.value<T2.value WHERE T2.value IS NULL

当然，所有这些大多只是出于学术兴趣，即它表明关系代数有效。

You can very well express aggregate functions with only basic operators. It's a pretty neat thing.

Suppose we have a table T, and we'd like to find the maximum of its "value" field. First, we should take the cartesian product of T with itself -- or rather with a copy of itself, T2. Then we select the rows where T.value is smaller than T2.value: this nets us all the unwanted rows, whose value is less than the value of some other row. To get the maximal values, we should subtract these unwanted rows from the set of all rows. And that's it. At least that's the basic idea, we also need to use projections to get the dimensions right.

Unfortunately I have no idea how to insert Latex here, but using relational algebra notation, it'd be something like this:

π(T.a1...Tan, T.value)(T)
    -
π(T.a1...Tan, T.value)(
    σ(T.value<T2.value)( ρ(T, T2) x T )
)

where π is the projection operator, - is the set difference, σ is the selection operator and ρ is the rename operator.

SQLishly:

SELECT T.* FROM T
    MINUS
SELECT T.* FROM T, T as T2 WHERE T.value<T2.value

And more practically:

SELECT T.* FROM T LEFT JOIN T as T2 ON T.value<T2.value WHERE T2.value IS NULL

Of course, all this is mostly only of academic interest, i.e. that it shows that the relational algebra works.

回复收藏 0 原文

Hello爱情风 2024-10-23 20:41:52

假设我们有表 T ，其属性为 a1, a2, ..., an, v 并且我们需要查找属性 v 与所有其他行相比具有最大值的行。

首先，我们需要 T 和 T' 的叉积（T 的副本，其中 v 重命名为 v1），这样我们就可以比较 v 的值：

T x ρ{a1, a2, ..., an, v1}T

其次，选择 v v v 的行。 v1，我们得到 v 值小于 v 的所有行 至少另一行中的值。这些是我们稍后需要排除的行：

σ{v < v1}(T x ρ{a1, a2, ..., an, v1}T)

然后使用 T 的原始属性（列名称）投影列，这样我们就有一个具有 T 架构的表，其中包含所有不需要的行，这些行将被从 T 中排除在下一步中：

π{a1, a2, ..., an, v}(σ{v < v1}(T x ρ{a1, a2, ..., an, v1}T))

最后，从 T 中排除不需要的行，我们得到具有最大 v 值的行：（

T - π{a1, a2, ..., an, v}(σ{v < v1}(T x ρ{a1, a2, ..., an, v1}T))

我根据 SaT 的答案和测试得出了这一点与斯坦福大学的在线 RA 课程相比，由于我不太理解 SaT 的表示法，所以我将解决方案放在我的符号中，其中运算符条件在 {} 中，希望它可以对将来的人有所帮助）。

Suppose we have the table T with attributes a1, a2, ..., an, v and we need to find the row where attribute v has the maximum value compared to all other rows.

First, we need a cross product of T and T' (a copy of T where v was renamed to v1) so we can compare the values of v:

T x ρ{a1, a2, ..., an, v1}T

Second, select the rows where v < v1, and we get all the rows whose v value is less than the v value in at least one other row. These are the rows that we need to exclude later:

σ{v < v1}(T x ρ{a1, a2, ..., an, v1}T)

Then project the columns with T's original attributes(column names) so we have a table with T's schema, containing all the unwanted rows, which are to be excluded from T in the next step:

π{a1, a2, ..., an, v}(σ{v < v1}(T x ρ{a1, a2, ..., an, v1}T))

Last, exclude the unwanted rows from T and we get the row with maximum v value:

T - π{a1, a2, ..., an, v}(σ{v < v1}(T x ρ{a1, a2, ..., an, v1}T))

(I worked this out based on SaT's answer and testing with Stanford's online RA course , since I didn't really understand SaT's notation, I put the solution in my notation here, in which the operator conditions are in {}. Hope it can help someone in the future)

回复收藏 0 原文

も让我眼熟你 2024-10-23 20:41:52

table1:= project field (table);
table2 (fieldrenamed):= project field (table);
Producted:=table1 mult table2;
minors:= select fild<fieldrenamed (producted);
result:=table1 difference (project field(minors));

代码与winRDBI相对应，字段是要比较以获得最大值的属性。表是该字段所在的原始表。

table1:= project field (table);
table2 (fieldrenamed):= project field (table);
Producted:=table1 mult table2;
minors:= select fild<fieldrenamed (producted);
result:=table1 difference (project field(minors));

Code correspond with winRDBI, field is the attribute that you want to compare to get the max value. Table is the original table where that field is.

回复收藏 0 原文

森林散布 2024-10-23 20:41:52

假设我们与属性 A 和值 1,2,3 存在关系，

所以现在..

用 A1 重命名

项目 A 值并再次
项目 A 值并使用 A2 重命名，

使用 A2 加入此项目，即 \join_{A2 所以 - 输出模式：（A2整数，A1整数）

A2<A1

1|2
1|3
2|3

总是听到A2值将小于A1，因为我们像这样加入（a2）

现在投影A2输出如下所示，

A2
1
2

现在与原始属性不同

A diff A2

 diff

A2
1
2

输出为3
这是最大值

嗨，我知道必须有人帮忙编辑，才能更好看

\project_{Att}Relation

\diff

\project_{A2}(\rename_{A2}(\project_{Att}Relation)
              \join_{A2<A1}
             \rename_{A1}(\project_{Att}Relation))

lets think we have a relation with an attribute A and values 1,2,3

so now..

project A values and rename with A1

again
project A values and rename with A2

join this with A2<A1 i.e \join_{A2<A1}
so the - Output schema: (A2 integer, A1 integer)

A2<A1

1|2
1|3
2|3

hear always A2 values will be less than A1 because we join like that(a2<a1)

now project A2 the output is like below

A2
1
2

now diff with original attribute

A diff A2

 diff

A2
1
2

Output is 3
which is maximum value

Hi, i know some one have to help in editing, for better look

\project_{Att}Relation

\diff

\project_{A2}(\rename_{A2}(\project_{Att}Relation)
              \join_{A2<A1}
             \rename_{A1}(\project_{Att}Relation))

回复收藏 0 原文