Как посчитать количество строк по каждой комбинации не группируя их? Mysql

Question

Есть такая таблица:

| user_id | product_id | action
+---------+------------+--------
| 1       | 1          | viewed
+---------+------------+--------
| 1       | 1          | liked
+---------+------------+--------
| 1       | 1          | bought
+---------+------------+--------
| 1       | 2          | viewed
+---------+------------+--------
| 1       | 2          | bought
+---------+------------+--------
| 2       | 1          | viewed
+---------+------------+--------
| 2       | 1          | liked
+---------+------------+--------

Необходимо посчитать количество товаров над которым пользователь совершал какое-либо действие. При этом неизвестно какие значения могут быть в таблице action. Тут конечно напрашивается банальное решение, например такое:

SELECT user_id, COUNT(product_id)
FROM (
    SELECT user_id, product_id FROM my_table GROUP BY user_id, product_id
) my_table
GROUP BY user_id

Оно вернёт мне это:

| user_id | count 
+---------+-------
| 1       | 2
+---------+-------
| 2       | 1

Но я хочу получить не только пользователя и количество товаров над которым он совершал какие-либо действия, но и сами эти товары. Я ожидаю:

| user_id | product_id | count
+---------+------------+-------
| 1       | 1          | 2
+---------+------------+-------
| 1       | 2          | 2
+---------+------------+-------
| 2       | 1          | 1
+---------+------------+-------

Т.е. проще говоря мне нужно получить уникальные комбинации user_id и product_id и сопоставить им количество таких комбинаций в результате. Если вы всё равно не совсем поняли что я хочу, могу ещё сказать так: Получить количество уникальных комбинаций user_id и product_id. К каждой такой комбинации добавить количество товаров, над которым данный user_id совершал какое-либо действие.

Казалось бы сделай 2 запроса и не парься. Но проблема в том, что эта таблица выдуманная и вместо my_table в:

SELECT ... FROM my_table

Будет сложный подзапрос, который выполнять 2 раза будет довольно ресурсозатратно. Моя версия mysql не поддерживает синтаксис WITH .. AS. Посчитать количество таких комбинаций с помощью ЯП выбрав только уникальные комбинации из БД тоже не получится =)

Answer 1

А такой вариант:

SELECT user_id, GROUP_CONCAT(product_id), COUNT(DISTINCT product_id) 
FROM (сложный запрос) AS mytable 
GROUP BY user_id

устроит?

На версии MySQL 5.7 возможно разве что решение в формате хранимой процедуры:

CREATE PROCEDURE get_output ()
BEGIN
CREATE TEMPORARY TABLE temp
    SELECT ... (Ваш сложный запрос);
SELECT user_id, product_id, COUNT(DISTINCT t2.product_id)
    FROM temp t1
    JOIN temp t2 USING (user_id, product_id)
    GROUP BY user_id, product_id;
DROP TABLE temp;
END

и потом

CALL get_output;

В процедуре после сохранения результата во временной таблице можно уже крутить её как угодно (включая ограничение выводимых товаров на юзера и пр.). Также следует посмотреть на объём данных - если он велик, лучше подстраховаться и создавать постоянную, а не временную, таблицу.

БЛОГ НА HUSL

Как посчитать количество строк по каждой комбинации не группируя их? Mysql

Ответы (1 шт):