sql - 按各种属性比较两个大表 - PostgreSQL

Question

我在想出一个比较具有不同属性的两个表的高效查询时遇到了麻烦。这是针对拥有数十万 SKU 待售的在线零售商的报告。每个 SKU 都是“父”产品的变体。他们在各个市场上销售，需要查看是否有在各个地方无法销售的商品。

有一个表包含所有父产品，另一个表包含所有变体及其对应的 SKU。在第三张表中，他们有每个 sku(变体)的完整列表及其相应的市场，其中 sku + 市场的组合是独一无二的。

数据库使用PostgreSQL

表结构如下:

产品表:

Products
id |  parent_sku  |  vendor_id
-------------------------------
 1 |     ABC      |     100
 2 |     DEF      |     200
 3 |     XYZ      |     100

变化表:

Variations
id |  parent_id  |   sku
----------------------------
 1 |     1       |   ABC-1
 2 |     1       |   ABC-2
 3 |     1       |   ABC-3
 4 |     2       |   DEF-1
 5 |     2       |   DEF-2
 6 |     3       |   XYZ-1
 7 |     3       |   XYZ-2

市场表:

MarketplaceData
 id |   sku   |   marketplace  | price
----------------------------
 1  |  ABC-1  |     website1   | 99.99
 2  |  ABC-2  |     website1   | 99.99
 3  |  ABC-3  |     website1   | 89.99
 4  |  DEF-1  |     website1   | 29.99
 5  |  DEF-2  |     website1   | 29.99
 6  |  XYZ-1  |     website1   | 39.99
 7  |  XYZ-2  |     website1   | 39.99
 8  |  ABC-1  |     website2   | 99.99
 9  |  ABC-2  |     website2   | 99.99
 10 |  ABC-3  |     website2   | 99.99
 11 |  DEF-1  |     website2   | 29.99
 12 |  DEF-2  |     website2   | 29.99
 13 |  XYZ-1  |     website2   | 34.99
 14 |  XYZ-2  |     website2   | 34.99

我有一个有效的查询，但执行起来需要很长时间，而且非常费力。

SELECT DISTINCT parent_id FROM Variations 
WHERE sku IN (SELECT sku FROM MarketplaceData WHERE marketplace IN ('website1','website2')) 
AND sku NOT IN (SELECT sku FROM MarketplaceData WHERE marketplace IN ('website3','website4')) 
LIMIT 20 OFFSET 0 

由于每个 sku + marketplace 数据集都有近 400,000 行，而 MarketplaceData 表包含超过 200 万行，因此此查询需要很长时间才能执行。

在索引方面，id 列是每个的主键。 Variations 表在 sku 上有一个索引(必须是唯一的)，MarketplaceData 在 sku + marketplace 上建立索引。

最终，我需要的是满足条件的唯一 parent_id 的列表。

如有任何帮助或指导，我们将不胜感激。

谢谢!

Answer 1

代替 IN 和 NOT In，您可以使用 INNER JOIN 和 LEFT JOIN 来检查 null

SELECT DISTINCT v.parent_id 
FROM Variations v
INNER JOIN (
 SELECT sku FROM MarketplaceData WHERE marketplace IN ('website1','website2')
) t1 on t1.sku = v.sku 
LEFT JOIN (
    SELECT sku FROM MarketplaceData WHERE marketplace IN ('website3','website4')
) t2 On  t2.sku = v.sku 
WHERE t2.sku is null