Как проверить принадлежат ли массивы из одной таблицы массивам в другой?

Question

[dzavatra]

Есть две таблицы:

1) Таблица pairs со столбцом pair состоящий из массивов

--------------------------------------------------
|   pair 
--------------------------------------------------
|  ["ананас","апельсины"]       
|  ["ананас","арбуз"]                      
|  ["ананас","бананы"]                     
|  ["ананас","баранина"]                 
|  ["ананас","батон"]                          
|  ...............                     
|  ["шоколад черный","яблоки"]     
|  ["энергетический напиток","яблоки"] 
---------------------------------------------------

2) Таблица orders со столбцом order_list состоящий из массивов

--------------------------------------------------
|   order_list
--------------------------------------------------
|   ["сливки","бананы"]
|   ["энергетический напиток","лимонад","горох","макароны"]
|   ["сок апельсиновый","масло подсолнечное","курица"]
|   ["груши","хлеб","батон"]              
|   ...............                     
|   ["курица","сушки","батон"]
|   ["рыба соленая","баранина","груши","чай черный листовой"]
---------------------------------------------------

Необходимо определить, сколько раз каждая пара продуктов из таблицы pairs встречается в массивах из столбца order_list. Понимаю, что нужно использовать group by, count и, возможно, оператор " @> ", но нет понимания, как связать таблицы и считать вхождения одного массива в другой.

Итоговая таблица должна выглядеть так:

--------------------------------------------------
|   pair                             |  count_pair
--------------------------------------------------
|   ["апельсины","овсянка"]          |  56
|   ["батон","йогурт"]               |  51
|   ["вафли","лимонад"]              |  39
|   ["йогурт","мармелад"]            |  25
|   ...............   
|   ["кофе растворимый","мандарины"] |  1

Comments

[Dmitry Bay]

По мне - надо оптимизировать таблицы, либо просчет этих данных отдать на более маленькие запросы:
Перебираете каждую строку pair на любом ЯП и получаете count. (думаю там разберетесь). И сохраняете в Pair. При новой строке order_list - обновляете необходимые count, через обычный перебор.

В REDIS, кажется, был подобный функционал вхождения, работать должен очень быстро.

Скрипт в данном случае - это бомба замедленного действия. С каждым днем он будет работать все медленнее и медленнее, пока не сломается.

[dzavatra]

Dmitry Bay, благодарю за ответ!

Answer 1

[alexalexes]

С помощью функции json_to_recordset проводите нормализацию таблиц, чтобы на выходе pair получилась вот такая псевдотаблица-выборка:
pair
id_group, name
1, "ананас",
1, "апельсины",
2, "ананас",
2, "арбуз"
...
Дальше тоже самое делаете с order_list.
Ну, а дальше все приведено в 3-ю нормальную форму, можно, наконец, использовать SQL-ные сравнения/соединения этих псевдовыборок и высчитывать кол-ва того-сего.
PS: Такое решение годится как разовое для получения результата, например, если вам нужно срочно сделать нестандартный отчет для руководства, а структура таблиц спроектирована из рук вон плохо.
Нужно изначально планировать структуру базы так (приводить к 3-й нормальной форме), чтобы не прибегать к декомпозиции полей с помощью функций JSON, если компоненты внутри JSON будут использоваться для соединения между таблицами.

Comments

[dzavatra]

Спасибо за ответ!