Группа MySQL по дате и количеству, включая отсутствующие даты

Раньше я делал следующее, чтобы получить счет за день из таблицы отчетов.

Очевидно, что это не даст мне 0 счет для отсутствующих дат.

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

<ч/" > В то время как я хочу заполнить недостающие даты с помощью 0

<ч/" > Еще одна попытка, которую я дал, заключалась в следующем:

Ответы

Ответ 1

Вам нужно OUTER JOIN, чтобы прибыть каждый день между началом и концом, потому что если вы используете INNER JOIN, он ограничивает вывод только связанными датами (то есть только те даты в таблице отчетов).

Кроме того, когда вы используете OUTER JOIN, вы должны следить за тем, чтобы условия в where clause не вызывали implicit inner join; например И domain_id = 1, если использование в предложении where подавляет любую строку, у которой не было этого условия, но при использовании в качестве условия соединения ограничивает только строки таблицы отчета.

SELECT
      COUNT(r.domain_id)
    , all_dates.Date AS the_date
    , domain_id
FROM (
        SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
        FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      ) all_dates
      LEFT OUTER JOIN reports r
                  ON all_dates.Date = r.tracked_on
                        AND domain_id = 1
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
      the_date
ORDER BY
      the_date ASC;

Я также изменил таблицу, полученную all_dates, используя DATE_ADD(), чтобы продвинуть исходную точку в будущее, и я уменьшил ее размер. Оба варианта являются опциями и могут быть изменены по своему усмотрению.

Демо в SQLfiddle

чтобы прийти к домену_id для каждой строки (как показано в вашем вопросе), вам нужно будет использовать следующее: Обратите внимание, что вы можете использовать IFNULL(), который является специфичным для MySQL, но я использовал COALESCE(), который является более общим SQL. Однако использование @parameter, как показано здесь, в любом случае зависит от MySQL.

SET @domain := 1;

SELECT
      COUNT(r.domain_id)
    , all_dates.Date AS the_date
    , coalesce(domain_id,@domain) AS domain_id
FROM (
        SELECT DATE_ADD(curdate(), INTERVAL 2 month) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
        FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      ) all_dates
      LEFT JOIN reports r
                  ON all_dates.Date = r.tracked_on
                        AND domain_id = @domain
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
      the_date
ORDER BY
      the_date ASC;

См. это в SQLfiddle

Ответ 2

Подзапрос all_dates смотрит только с текущего дня (curdate()). Если вы хотите включить будущие даты, измените первую строку подзапроса на что-то вроде:

select '2015-01-01' - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date