SQL:选择所有包含一组特定项目的购物篮 [英] Sql: choose all baskets containing a set of particular items

问题描述

Eddy的篮子里装满了物品.每个项目都可以属于任意数量的购物篮，也可以不属于任何一个.

Eddy has baskets with items. Each item can belong to arbitrary number of baskets or can belong to none of them.

Sql模式如下:

tbl_basket
- basketId

tbl_item
- itemId

tbl_basket_item
- pkId
- basketId
- itemId

问题:如何选择所有包含一组特定物品的购物篮?

Question: how to select all baskets containing a particular set of items?

更新.需要包含所有个项目的购物篮.否则，这将很容易解决.

UPDATE. Baskets with all the items are needed. Otherwise it would have been easy task to solve.

UPDATE B.实现了以下解决方案，包括PHP中的SQL生成:

UPDATE B. Have implemented following solution, including SQL generation in PHP:

SELECT basketId
FROM   tbl_basket
JOIN   (SELECT basketId FROM tbl_basket_item WHERE itemId = 1  ) AS t0 USING(basketId)
JOIN   (SELECT basketId FROM tbl_basket_item WHERE itemId = 15 ) AS t1 USING(basketId)
JOIN   (SELECT basketId FROM tbl_basket_item WHERE itemId = 488) AS t2 USING(basketId)

其中 JOINs 的数量等于项的数量.

where number of JOINs equals to number of items.

这很好，除非几乎每个购物篮中都包含一些物品.然后性能急剧下降.

That works good unless some of the items are included in almost every basket. Then performance drops dramatically.

UPDATE B +.为了解决性能问题，应用了启发式.首先，您选择每个项目的频率.如果超过某个阈值，则不会将其包括在 JOINs 中，并且不会:

UPDATE B+. To resolve performance issues heuristic is applied. First you select frequency of each item. If it exceeds some threshold, you don't include it in JOINs and either:

• 在PHP中应用后过滤
• 或者只是不对特定的itemId进行过滤，从而在合理的时间内为用户提供大致的结果

UPDATE B ++.似乎当前的问题在MySQL中没有好的解决方案.这提出了一个问题和一个解决方案:

UPDATE B++. Seems that current problem have no nice solution in MySQL. This point raises one question and one solution:

• (问题) PostgreSQL是否具有一些高级索引技术可以在不进行全面扫描的情况下解决此问题?
• (解决方案)似乎可以使用set和 SINTER 命令在 Redis 中很好地解决它.

• (question) Does PostgreSQL have some advanced indexing techniques which allows to solve this problem without doing a full scan?
• (solution) Seems that it could be solved nicely in Redis using sets and SINTER command to get an intersection.

推荐答案

-- the table definitions
CREATE TABLE basket ( basketid INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE item ( itemid INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE basket_item
        ( basketid INTEGER NOT NULL REFERENCES basket (basketid)
        , itemid INTEGER NOT NULL REFERENCES item (itemid)
        , PRIMARY KEY (basketid, itemid)
        );

-- the query
SELECT * FROM basket b
WHERE NOT EXISTS (
        SELECT * FROM item i
        WHERE i.itemid IN (1,15,488)
        AND NOT EXISTS (
                SELECT * FROM basket_item bi
                WHERE bi.basketid = b.basketid
                AND bi.itemid = i.itemid
                )
        );

这篇关于SQL:选择所有包含一组特定项目的购物篮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！