MySQL: показывать ежемесячные продажи и включать месяцы, когда продажи не производились

Мне нужна настоящая профессиональная помощь DBA! Я не могу найти разумного решения, используя LEFT JOIN, UNION, UNION ALL или IFNULL(salesblah, 0) в таблице дат относительно таблицы данных о продажах. Запросы запросов в отношении данных о продажах не выполняются, поскольку у торгового представителя могут быть пробелы. Конечно, я мог бы создать в PHP массив для суммирования данных по дате, но должно быть элегантное решение, которого я не вижу, и этот личный вызов утомил меня. Для этого должно быть короткое и приятное решение.

С MySQL 5.5.15 на сервере выдержка из данных о продажах (в настоящее время 800 000 строк и растет ежедневно) выглядит так:

    Rep         Date         QtyOrdered     PriceEach
    --------------------------------------------------
    1         2011-06-05              4        1457.23
    1         2011-08-01              1        3342.54
    1         2011-08-11             12         112.23
    2         2011-05-02              3        2654.23
    2         2011-08-23             22         423.43
    .            ...                ...            ...

Другая таблица - это просто столбец дат с 2000-01-01 по 2034-12-31

Используя (или не используя) таблицу дат, когда я вызываю респ 1 в запросе, как я могу получить этот результат:

YYYY-MM      Total Sales
------------------------
2010-08               0
2010-09               0
2010-10               0
2010-11               0
2010-12               0
2011-01               0
2011-02               0
2011-03               0
2011-04               0
2011-05               0
2011-06         5828.92
2011-07               0
2011-08         4960.14

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

РЕДАКТИРОВАТЬ: Решение левого соединения от Дерека пропускает ненулевые месяцы. Метод подзапроса Nerf суммирует все в один месяц. Я считаю, что GROUP BY убивает ненулевые месяцы.

Спасибо! Я полагаю, что самый простой способ - запустить PHP через массив. В конечном счете, это заключается в том, чтобы построить диаграмму за несколько месяцев с помощью GD, и гибридное решение может сохранить шаг в этой части.

Еще раз спасибо!

Источник

3 ответа

У меня возникла та же проблема, и я получил решение без использования JOIN или каких-либо сложных вещей. Нужно избегать группирования по общему количеству. При этом отобразятся все заказанные продажи по годам, месяцам и дням, включая дни, когда продаж вообще не было (dayTotal будет равен 0).

SELECT Year(transactions.paymentdate) AS orderYear,Month(transactions.paymentdate) AS orderMonth, Day(transactions.paymentdate) AS orderDay,
    SUM(transactions.amount) AS dayTotal
    FROM transactions
    group by orderYear desc,orderMonth desc, orderDay desc
Источник 
SELECT CONCAT_WS("-",  YEAR(date), DATE_FORMAT(date, '%m') ) AS "YYYY-MM", SUM(Total_Sales) AS "Total Sales"
FROM(
     SELECT dt.date, SUM(QtyOrdered * PriceEach) AS Total_Sales
    FROM sales_data sd
         LEFT JOIN datetbl dt
         ON sd.Date = dt.date
    WHERE sd.rep = 1 AND dt.date BETWEEN '2011-08-01' AND '2011-08-31'
        GROUP BY dt.date
         )  AS derived_table
Источник

Это должно быть довольно близко. Не проверено, но логика должна быть правильной.

select 
   cast(year(dt.date) as varchar(4)) + '-' + cast(month(dt.date) as varchar(2)),
   sum(QtyOrdered * PriceEach)
from salesdata sd
right join datetbl dt
  on sd.date = dt.date
where sd.rep = 1 and dt.date between '8/1/2010' and '8/31/2011'
group by cast(year(dt.date) as varchar(4)) + '-' + cast(month(dt.date) as varchar(2))
Источник
Другие вопросы по тегам