Поиск запроса для группировки по часам и сбора информации о пользователях

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

У меня есть таблица часов с 0-23 часами, у меня есть таблица истории с сообщениями, которые пользователи опубликовали + дата_добавлена.

Теперь я хочу создать результат, который покажет мне:

  • Каждый час должен иметь ВСЕХ пользователей из пользовательской таблицы

  • Количество сообщений, отправленных этим пользователем, даже если в таблице истории нет записей с идентификатором пользователя

Я пробовал этот запрос, единственное, что неправильно, это то, что он не показывает каждый час userId 

SELECT COUNT(*) AS messages, h.hour AS `Hour`, c.user_id, p.name
FROM history c
  INNER JOIN users p ON c.user_id = p.user_id
  RIGHT JOIN hours h ON h.hour = HOUR(c.`date_added`)
    AND YEAR(`date_added`) = 񟭓' AND MONTH(`date_added`) = ༿' AND DAY(`date_added`) = ག'
GROUP BY h.hour, c.user_id
ORDER BY h.hour ASC;

Скрипка: http://sqlfiddle.com/#!9/5c60d5b

- РЕДАКТИРОВАТЬ спасибо за все запросы!

Всего 4 ответа

С кросс-объединением для hours и users и левым присоединением к history : 

select 
  t.hour, t.user_id, t.name, sum(history_id is not null) messages
from (select * from hours cross join users) t
left join (
  select * from history
  where
    year(date_added) = 2019 and month(date_added) = 3 and day(date_added) = 6
) h 
on h.user_id = t.user_id and t.hour = hour(h.date_added)
group by t.hour, t.user_id, t.name

Смотрите демо .
Результаты: 

> hour | user_id | name  | messages
>   .. |........ |...... |.........
>   14 |       4 | test4 |        0
>   15 |       1 | test  |        0
>   15 |       2 | test2 |        0
>   15 |       3 | test3 |        0
>   15 |       4 | test4 |        0
>   16 |       1 | test  |        1
>   16 |       2 | test2 |        0
>   16 |       3 | test3 |        5
>   16 |       4 | test4 |        8
>   17 |       1 | test  |        0
>   .. |........ |...... |.........

Я добавил user_id = 4 в таблицу users ваших примеров данных, потому что они были включены в history .

Для получения всех возможных комбинаций между двумя несвязанными таблицами вы можете использовать CROSS JOIN . Таким образом, запрос ниже достигает: «Каждый час должен иметь ВСЕХ пользователей из таблицы пользователей» : 

SELECT h.hour, u.user_id
FROM hours h
CROSS JOIN users u

Попробуйте использовать левое присоединение и время до даты в группе по 

SELECT COUNT(*) AS messages,  hour(date_added)  , c.user_id, p.name
FROM history c
 INNER JOIN users p ON c.user_id = p.user_id
  LEFT JOIN hours h ON h.hour = hour(date_added) 
AND YEAR(`date_added`) = 񟭓' AND MONTH(`date_added`) = ༿' AND DAY(`date_added`) = ག'
GROUP BY hour(date_added) , c.user_id
ORDER BY h.hour ASC;

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

SELECT messages, h.hour AS `hour`, h.user_id, h.name
FROM (
  SELECT COUNT(*) AS messages, HOUR(c.`date_added`) as `hour`, c.user_id, p.name
  FROM users p
  INNER JOIN history  c
  ON c.user_id = p.user_id
  WHERE YEAR(`date_added`) = 񟭓' AND MONTH(`date_added`) = ༿' AND DAY(`date_added`) = ག'
  GROUP BY HOUR(c.`date_added`), c.user_id, p.name
  ) z
  right join
  (
    SELECT h.hour, u.user_id, u.name
    FROM hours h
    CROSS JOIN users u
  ) h ON h.hour = z.hour
      AND h.user_id = z.user_id
 ORDER BY hour

Есть идеи?

10000