mysql - SQL 查询关联给定消费者拥有的每个集合集的数量

Question

假设我的客户可以获得某些奖品:

SELECT gs.claimed_by AS consumer_id, p.prize_id AS prize_id FROM 
  awarded_prizes

现在，客户 1 有三个奖品，客户 2 有一个奖品

+-------------+----------+
| consumer_id | prize_id |
+-------------+----------+
|           1 |       45 |
|           1 |       46 |
|           1 |       47 |
|           2 |       66 |
+-------------+----------+

假设我们也有收藏品，如果您收集了该收藏品的所有成员，您现在就有了一套收藏品:

SELECT set_id, member_prize_id AS prize_id FROM collectable_set_members;
+--------+----------+
| set_id | prize_id |
+--------+----------+
|      1 |       45 |
|      1 |       46 |
|      1 |       47 |
|      2 |       65 |
|      2 |       66 |
+--------+----------+

根据上表和前面的查询，我们可以看到客户 1 已经完成了第 1 组一次(他们有 45、46、47)，而客户 2 没有完成任何操作。

在某些情况下，客户可以多次完成一组(客户在 returned_prize 表中可能有 45、46、47、45、46、47。

我一直在查看pantry problem及其变体(如调酒师问题)，一直在尝试交叉连接和分组，但似乎找不到我想要的。

我正在尝试获取给定客户的结果，显示他们拥有的所有 set_ids 以及他们已完成的组数:

+-------------+---------------+--------+
| consumer_id | completed_set |  count |
+-------------+---------------+--------+
|           1 |             1 |      1 |
+-------------+---------------+--------+ 

我使用的是 mariadb:5.5

Answer 1

参见此处SqlFiddle

我的表的名称与您的不同，但这证明了这一点:

select sets_x_consumers.consumer_id, sets_x_consumers.set_id, 
  set_summary.items_in_set = consumer_summary.items_per_set_per_consumer as set_is_complete
from (
  -- build a cross-product of sets and consumers
  select distinct set_id, consumer_id
  from sets join consumers -- no join condition -> cross product
) sets_x_consumers 
inner join
( -- the total number of items in each set per set_id
  select set_id, count(*) items_in_set
  from sets 
  group by set_id
) set_summary on sets_x_consumers.set_id = set_summary.set_id
inner join
( -- the total number of items per set and customer 
  select set_id, consumer_id, count(*) items_per_set_per_consumer
  from sets 
  inner join consumers on sets.prize_id = consumers.prize_id
  group by consumer_id, set_id
) consumer_summary on sets_x_consumers.set_id = consumer_summary.set_id and sets_x_consumers.consumer_id = consumer_summary.set_id

我的基本想法是总结每组中的元素数量以及每个消费者已领取的每组元素数量。只要消费者和奖品对没有重复的条目，这应该有效(如果允许重复，我将使用 count unique(prize_id) 作为 consumer_summary )。

上面查询的输出是:

| consumer_id | set_id | set_is_complete |
|-------------|--------|-----------------|
|           1 |      1 |               1 |
|           2 |      2 |               0 |

这列出了每对消费者和消费者至少有一个奖品的集合。 (要更改此设置以列出每个消费者设置组合，请使用外部连接)

在此基础上仅列出完整集或总结完整集的数量应该很容易;-)