mysql - выбрать недавнюю активность для каждого пользователя в столбце

Question

Изменить - я нашел это отличается от сводной таблицы. Потому что в моем случае это основано на дате

У меня есть таблица со связанными данными. Я хочу вернуть в столбец самую последнюю активность для каждого клиента, например, временные данные для целей интеллектуального анализа данных.

Итак, у меня есть эти столбцы в моей таблице:

id_user | item_id | date

      1 |       2 | 2016-11-10
      1 |       3 | 2016-9-9
      1 |      23 | 2016-8-8
      1 |      21 | 2016-6-6
      1 |       5 | 2016-7-7
      1 |       4 | 2016-10-10
      2 |       3 | 2016-9-9
      2 |       4 | 2016-10-10
      2 |      21 | 2016-5-4
      3 |       4 | 2016-10-10
      3 |       4 | 2016-9-9

Первый - я хочу отфильтровать данные, чтобы получить активность " последние 5 ". Тогда я придумаю этот код

set @num := 0, @group := '';

select x.`id_user`, x.`item_id`, x.`date`
from
(
   select `id_user`, `item_id`, `date`,
      @num := if(@group = `id_user`, @num + 1, 1) as row_number,
      @group := `id_user` as dummy
  from ratings
  order by `id_user`, `date` desc, `item_id`
) as x,
where x.row_number <= 5
ORDER BY x.`user_id`, x.date DESC;

Эти коды дают мне эту таблицу:

 id_user | item_id | date

       1 |       2 | 2016-11-10
       1 |       4 | 2016-10-10
       1 |       3 | 2016-9-9
       1 |      23 | 2016-8-8
       1 |       5 | 2016-7-7
       2 |       4 | 2016-10-10
       2 |       3 | 2016-9-9
       2 |      21 | 2016-5-4
       3 |       4 | 2016-10-10
       3 |       4 | 2016-9-9

НО - я хочу что-то вроде этого для интеллектуального анализа данных

Id_user | item_1 | Item_2 | Item_3 | Item_4 | Item_5

      1 |      2 |      4 |      3 |     23 |      5 |
      2 |      4 |      3 |     21 |   NULL |   NULL |
      3 |      4 |      4 |   NULL |   NULL |   NULL |

Вы поняли мою идею? Извините, если я не могу объяснить это ясно, надеюсь, вы понимаете, что я хочу.

Вопрос

1. Как я могу сделать запрос SQL для этих проблем? (в настоящее время я получил RAW Data из базы данных MySQL)
2. Есть ли лучший подход к этому вопросу? Я надеюсь, что это лучшая практика.

Answer 1

Во-первых, при использовании переменных не назначайте и не используйте переменные в разных выражениях. MySQL не гарантирует порядок вычисления выражений в SELECT, И, фактически, при некоторых обстоятельствах оценивает их в другом порядке.

Итак, это должен быть ваш запрос:

select r.`id_user`, r.`item_id`, r.`date`
from (select `id_user`, `item_id`, `date`,
             (@num := if(@u = id_user, @num + 1,
                         if(@u := id_user, 1, 1)
                        )
              ) as num
      from ratings r cross join
           (select @num := 0, @u := -1) params
      order by `id_user`, `date` desc, `item_id`
     ) r
where x.row_number <= 5
order by x.`user_id`, x.date desc;

Затем просто используйте условное агрегирование:

select r.`id_user`,
       max(case when num = 1 then r.item_id end) as item_id_1,
       max(case when num = 2 then r.item_id end) as item_id_2,
       max(case when num = 3 then r.item_id end) as item_id_3,
       max(case when num = 4 then r.item_id end) as item_id_4,
       max(case when num = 5 then r.item_id end) as item_id_5
from (select `id_user`, `item_id`, `date`,
             (@num := if(@u = id_user, @num + 1,
                         if(@u := id_user, 1, 1)
                        )
              ) as num
      from ratings r cross join
           (select @num := 0, @u := -1) params
      order by `id_user`, `date` desc, `item_id`
     ) r
where x.row_number <= 5
group by x.`user_id`;