时间戳

首页 > 解决方案 > Prolog to SQL：有什么方法可以改进单元测试的 SQL 代码并优雅地修复边缘情况？

sql - Prolog to SQL：有什么方法可以改进单元测试的 SQL 代码并优雅地修复边缘情况？

问题描述

受这个 StackOverflow 问题的启发：

在 swi-prolog 中查找不同事实中的相互元素

我们有以下

问题陈述

给定一个“主演电影的演员”数据库（例如，starsin 是将演员“bob”与电影“a”联系起来的关系）

starsin(a,bob).
starsin(c,bob).

starsin(a,maria).
starsin(b,maria).
starsin(c,maria).

starsin(a,george).
starsin(b,george).
starsin(c,george).
starsin(d,george).

给定一组电影M，找出那些出演过M的所有电影的演员。

这个问题最初是针对 Prolog 的。

序言解决方案

在 Prolog 中，一个优雅的解决方案涉及 predicate setof/3，它将可能的变量实例收集到一个集合中（实际上是没有重复值的列表）：

actors_appearing_in_movies(MovIn,ActOut) :-
    setof(
        Ax,
        MovAx^(setof(Mx,starsin(Mx,Ax),MovAx), subset(MovIn,MovAx)),
        ActOut
    ).

我不会对此进行详细介绍，但让我们看一下测试代码，这里很感兴趣。以下是五个测试用例：

actors_appearing_in_movies([],ActOut),permutation([bob, george, maria],ActOut),!. 
actors_appearing_in_movies([a],ActOut),permutation([bob, george, maria],ActOut),!.
actors_appearing_in_movies([a,b],ActOut),permutation([george, maria],ActOut),!.
actors_appearing_in_movies([a,b,c],ActOut),permutation([george, maria],ActOut),!.
actors_appearing_in_movies([a,b,c,d],ActOut),permutation([george],ActOut),!.

测试是对谓词的调用actors_appearing_in_movies/2，它给出了电影的输入列表（例如[a,b]），并捕获了结果中的演员列表ActOut。

随后，我们只需要测试是否ActOut是预期参与者集合的排列，例如：

permutation([george, maria],ActOut)`

“ActOut列表是列表的排列[george,maria]吗？

如果该调用成功（想想，不返回false），则测试通过。

终端!是剪切操作符，用于告诉 Prolog 引擎不要重新尝试寻找更多解决方案，因为我们擅长这一点。

请注意，对于空电影集，我们获取所有演员。这可以说是正确的：每个演员都出演了空集的所有电影（Vacuous Truth）。

现在在 SQL 中。

这个问题完全属于关系代数领域，并且有 SQL，所以让我们尝试一下。在这里，我使用的是 MySQL。

首先，摆正事实。

DROP TABLE IF EXISTS starsin;

CREATE TABLE starsin (movie CHAR(20) NOT NULL, actor CHAR(20) NOT NULL);

INSERT INTO starsin VALUES
   ( "a" , "bob" ),
   ( "c" , "bob" ),
   ( "a" , "maria" ),
   ( "b" , "maria" ),
   ( "c" , "maria" ),
   ( "a" , "george" ),
   ( "b" , "george" ),
   ( "c" , "george" ),
   ( "d",  "george" );

关于作为输入给出的电影集，以（临时）表格的形式给出它们听起来很自然。在 MySQL 中，“临时表”是会话本地的。好的。

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b");

方法：

现在可以通过为每个演员获取由 表示的电影movies_in集与演员曾经出现过的电影集的交集（通过内部连接为每个演员创建），然后计数（对于每个演员) 结果集是否至少具有与集合一样多的条目movies_in。

出于实际原因，将查询包装到一个过程中。分隔符在这里很有用：

DELIMITER $$

DROP PROCEDURE IF EXISTS actors_appearing_in_movies;

CREATE PROCEDURE actors_appearing_in_movies()
BEGIN

SELECT 
     d.actor 
   FROM 
     starsin d, movies_in q
   WHERE 
     d.movie = q.movie 
   GROUP BY 
     actor 
   HAVING 
     COUNT(*) >= (SELECT COUNT(*) FROM movies_in);

END$$

DELIMITER ;

运行！

出现问题A：

有没有比编辑 + 复制粘贴表创建代码更好的方法，发出 aCALL并“手动”检查结果？

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
CALL actors_appearing_in_movies();

空集！

问题B出现：

以上是不需要的，我想要“所有参与者”，与 Prolog 解决方案相同。由于我不想在代码中添加奇怪的边缘案例异常，因此我的方法一定是错误的。是否有一个自然涵盖这种情况但不会变得太复杂的情况？ T-SQL和 PostgreSQL 单行也很好！

其他测试用例产生预期数据：

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b");
CALL actors_appearing_in_movies();
+--------+
| actor  |
+--------+
| george |
| maria  |
+--------+

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b"), ("c");
CALL actors_appearing_in_movies();
+--------+
| actor  |
+--------+
| george |
| maria  |
+--------+

DROP TABLE IF EXISTS movies_in;
CREATE TEMPORARY TABLE movies_in (movie CHAR(20) NOT NULL);
INSERT INTO movies_in VALUES ("a"), ("b"), ("c"), ("d");
CALL actors_appearing_in_movies();
+--------+
| actor  |
+--------+
| george |
+--------+

标签： sqlunit-testingprolog

解决方案

给定一组电影 M，找出那些出演过 M 的所有电影的演员。

我会使用：

select si.actor
from starsin si
where si.movie in (<M>)
group by si.actor
having count(*) = <n>;

如果你必须处理一个空集，那么你需要一个left join：

select a.actor
from actors a left join
     starsin si
     on a.actor = si.actor and si.movie in (<M>)
group by a.actor
having count(si.movie) = <n>;

<n>这是 中的电影数量<M>。

更新：扩展形式的第二种方法

create or replace temporary table 
   actor (actor char(20) primary key)
   as select distinct actor from starsin;

select 
   a.actor,
   si.actor,si.movie  -- left in for docu
from 
   actor a left join starsin si
     on a.actor = si.actor 
        and si.movie in (select * from movies_in)
group 
   by a.actor
having
   count(si.movie) = (select count(*) from movies_in);

然后为空movies_in：

+--------+-------+-------+
| actor  | actor | movie |
+--------+-------+-------+
| bob    | NULL  | NULL  |
| george | NULL  | NULL  |
| maria  | NULL  | NULL  |
+--------+-------+-------+

例如movies_in ：

+-------+
| movie |
+-------+
| a     |
| b     |
+-------+

movie这是该组的顶部：

+--------+--------+-------+
| actor  | actor  | movie |
+--------+--------+-------+
| george | george | a     |
| maria  | maria  | a     |
+--------+--------+-------+

推荐阅读