使用MULTISET来比较数据集的实例介绍

使用普通SQL可能更难解决的问题是这样的问题：

哪些电影与给定的电影X有相同的演员？

像往常一样，我们在这个例子中使用sakila数据库。用SQL（例如PostgreSQL，具体来说）解决这个问题的可能方式是什么？下面的查询给出了每部电影的演员概况：

SELECT
  film_id,
  array_agg(actor_id ORDER BY actor_id) actors
FROM film_actor
GROUP BY film_id

复制代码

它产生的结果是这样的：

|film_id|actors                            |
|-------|----------------------------------|
|1      |{1,10,20,30,40,53,108,162,188,198}|
|2      |{19,85,90,160}                    |
|3      |{2,19,24,64,123}                  |
|4      |{41,81,88,147,162}                |
|5      |{51,59,103,181,200}               |
|6      |{21,23,62,108,137,169,197}        |
|...    |...                               |
复制代码

请注意，在SQL中，数组的行为就像列表一样，也就是说，它们保持着自己的排序，所以明确地对数组进行排序对于能够相互比较演员是很重要的。现在，我们想从上面找到所有共享相同演员集的电影：

WITH t AS (
  -- Previous query
  SELECT
    film_id,
    array_agg(actor_id ORDER BY actor_id) actors
  FROM film_actor
  GROUP BY film_id
)
SELECT 
  array_agg(film_id ORDER BY film_id) AS films,
  actors
FROM t
GROUP BY actors
ORDER BY count(*) DESC, films

复制代码

结果就是现在：

|films   |actors                            |
|--------|----------------------------------|
|{97,556}|{65}