将聚合运算符从SQL转换为关系代数 [英] Converting aggregate operators from SQL to relational algebra

问题描述

我编写了一些SQL查询，希望将其转换为关系代数。但是，某些查询使用聚合运算符，但我不知道如何转换它们。

I have several SQL queries written that I want to convert to relational algebra. However, some of the queries use aggregate operators and I don't know how to convert them. Notably they use COUNT and GROUP BY.. HAVING operators.

以下是模式：

Sailors（ sid ，sname，等级）
储备（ sid ，出价，价格）
船（出价，bname）

Sailors(sid, sname, rating) Reserves(sid, bid, price) Boats(bid, bname)

这里是我正在做的一个示例：找到恰好2个水手预定的所有船只的出价和名称。

Here is an example of what I'm doing: find the bids and bnames of all boats reserved by exactly 2 sailors.

SELECT B.bid, B.bname
FROM Boats B, Reserves R
WHERE B.bid = R.bid
GROUP BY R.bid
HAVING 2 = (SELECT COUNT(*)
FROM Reserves R2
WHERE R2.bid = B.bid);

允许的关系代数运算：选择，投影，连接，条件连接，重命名，并集，交点，叉-产品，部门

Allowable relational algebra operations: selection, projection, join, conditional join, rename, union, intersection, cross-product, division

推荐答案

这只是答案的一半...

This is only half an answer...

可以使用条件连接和投影来找到由两个或多个水手保留的船的关系，它们都在您允许的操作集中：

The relation "boats reserved by two or more sailors" can be found using conditional join and projection, which are both in your set of allowed operations:

SELECT DISTINCT R1.bid
  FROM Reserves AS R1 
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid;

可以使用条件连接（两次）找到由三个或更多水手预定的船的关系，投影，都在您允许的操作集中：

The relation "boats reserved by three or more sailors" can be found using conditional join (twice) and projection, which are both in your set of allowed operations:

SELECT DISTINCT R1.bid
  FROM Reserves AS R1
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
       JOIN Reserves AS R3
          ON R1.bid = R3.bid
          AND R2.sid < R3.sid;

如果，我们有一个负运算符，例如 EXCEPT 在标准SQL中：

If we had a minus operator e.g. EXCEPT in Standard SQL:

SELECT DISTINCT R1.bid
  FROM Reserves AS R1 
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
EXCEPT
SELECT DISTINCT R1.bid
  FROM Reserves AS R1
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
       JOIN Reserves AS R3
          ON R1.bid = R3.bid
          AND R2.sid < R3.sid;

如果我们有限制条件（在哪里）和半分运算符（也称为 antijoin ）（例如不输入在SQL中）

If we had restriction (WHERE in SQL) and a semi difference (a.k.a. antijoin) operator (e.g. NOT IN in SQL):

SELECT DISTINCT R1.bid
  FROM Reserves AS R1 
       JOIN Reserves AS R2
          ON R1.bid = R2.bid
             AND R1.sid < R2.sid
 WHERE R1.bid NOT IN (
                      SELECT DISTINCT R1.bid
                        FROM Reserves AS R1
                             JOIN Reserves AS R2
                                ON R1.bid = R2.bid
                                   AND R1.sid < R2.sid
                             JOIN Reserves AS R3
                                ON R1.bid = R3.bid
                                AND R2.sid < R3.sid
                     );

...但是您允许的操作集不包括限制，半差或减：（

...but your set of allowed operations does not include restriction, semi difference or minus :(

这篇关于将聚合运算符从SQL转换为关系代数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！