SQL: агрегирование пар без ОБЪЕДИНЕНИЯ (сложная задача)

#mysql #sql

Вопрос:

Не могли бы вы помочь мне с очень сложным вопросом?

У меня есть таблица ‘itemslog’ в СУБД MySQL с двумя столбцами: ‘userid’ и ‘itemid’, выглядит как:

 | user1 | item 1 |
| user1 | item 2 |
| user2 | item 1 |
| user2 | item 2 |
| user2 | item 3 |

Мне нужно подсчитать, сколько пользователей имеют каждую пару элементов, например, ответить так:

 | item1 | item2 | 2 |
| item1 | item3 | 1 |
| item2 | item3 | 1 |

Обычно мы можем использовать запрос на основе операций объединения, например:

 SELECT
    t1.itemname,
    t2.itemname,
    count(*)
FROM
    itemslog AS t1
    CROSS JOIN itemslog AS t2 ON t1.userid = t2.userid
WHERE
    t1.itemname < t2.itemname
GROUP BY
    t1.itemname, t2.itemname;

Но это требует много вычислений, и в моей ситуации это бесполезно (у меня около 200 тыс. строк). Можете ли вы дать мне совет, есть ли другие способы сделать это? Заранее благодарю вас!

1. Мне непонятно, что вы пытаетесь здесь посчитать. Вы хотите узнать, есть ли у данного пользователя определенные элементы? Или что-то еще?

2. У вас есть другая таблица items , в которой itemid есть первичный ключ — справочная таблица?

3. Каждый пользователь может переносить любое количество элементов. Для каждой пары элементов я хочу подсчитать количество пользователей, которые несут их оба. В примере пары item1 и item2 содержат двух пользователей, а пары item1 и item3 — только одного.

4. Нет, у меня нет справочной таблицы. Но в моей ситуации это не проблема, если это поможет.

5. Вы объединяете одни и те же таблицы и извлекаете один и тот же столбец. Это даст вам одинаковый результат в обоих столбцах

Ответ №1:

Это ваш запрос:

 SELECT t1.itemname, t2.itemname, count(*)
FROM itemslog t1 JOIN
     itemslog t2
     ON t1.userid = t2.userid AND t1.itemname < t2.itemname
GROUP BY t1.itemname, t2.itemname;

Для этого запроса вам нужен индекс на itemslog(userid, itemname) :

 create index itemslog_userid_itemname on itemslog(userid, itemname);

Предполагая, что у вас есть только несколько элементов для каждого userid , это должно иметь разумную производительность.