Блог

У меня возникли проблемы с созданием эффективного запроса, который сравнивает две таблицы с различными атрибутами. Это для отчета для интернет-магазина, у которого есть несколько сотен тысяч 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
  

Таблица Marketplace:

 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 содержит более 2 миллионов строк, выполнение этого запроса занимает целую вечность.

С точки зрения индексации, столбец id является первичным ключом для каждой. Таблица Variations имеет индекс в sku (должен быть уникальным), а MarketplaceData индексируется в sku marketplace.

В конечном счете, что мне нужно, так это список уникальных parent_id, которые соответствуют критериям.

Буду признателен за любую помощь или руководство.

Спасибо!

вместо 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
  

Почему, если вы используете только один подзапрос?

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

Как насчет простой агрегации для получения артикулов?

 select mpd.sku
from MarketplaceData mpd
where mpd.marketplace in ('website1', 'website2', 'website3', 'website4')
group by mpd.sku
having count(*) filter (where mpd.marketplace in ('website1', 'website2')) > 0 and
       count(*) filter (where mpd.marketplace in ('website3', 'website4')) = 0;
  

Затем, чтобы получить родительские идентификаторы:

 select distinct v.parent_id
from variations v join
     (select mpd.sku
      from MarketplaceData mpd
      where mpd.marketplace in ('website1', 'website2', 'website3', 'website4')
      group by mpd.sku
      having count(*) filter (where mpd.marketplace in ('website1', 'website2')) > 0 and
             count(*) filter (where mpd.marketplace in ('website3', 'website4')) = 0
     ) m
     on m.sku = v.sku;