Мне нужно найти наиболее популярное событие элемента, сгруппированного по дате, и отобразить общее количество всех элементов вместе с именем этого элемента. Возможно ли подобное в одном запросе?
Примечание. Если все они одинаковы (см. последние 3 строки в Insert), я могу просто показать случайным образом или первое или последнее событие (в зависимости от того, что проще всего).
Если это невозможно сделать в sql, тогда вам придется выполнить результат и отсортировать данные через PHP, который, похоже, будет довольно грязным.
Редактировать: Извините, у меня была неправильная сумма за '2009'08-04'. Это должно быть 4.
Пример того, что мне нужно:
+ ------------ + --------------------- + ------- + | дата | вещь | всего | + ------------ + --------------------- + ------- + | 2009-08-02 | Apple | 5 | | 2009-08-03 | Груша | 2 | | 2009-08-04 | Персик | 4 | | 2009-08-05 | Apple | 1 | | 2009-08-06 | Apple | 3 | + ------------ + --------------------- + ------- +
Вот пример таблицы:
CREATE TABLE, ЕСЛИ НЕ СУЩЕСТВУЕТ `test_popularity` ( `date` datetime NOT NULL, `item` varchar (256) NOT NULL, `item_id` int (11) NOT NULL ) ENGINE = MyISAM DEFAULT CHARSET = latin1; INSERT INTO `test_popularity` (` date`, `item`,` item_id`) ЦЕННОСТИ ('2009-08-02 00:00:00', 'Apple', 1), ('2009-08-02 00:00:00', 'Pear', 3), ('2009-08-02 00:00:00', 'Apple', 1), ('2009-08-02 00:00:00', 'Apple', 1), ('2009-08-02 00:00:00', 'Pear', 0), ('2009-08-03 00:00:00', 'Pear', 3), ('2009-08-03 00:00:00', 'Персик', 2), ('2009-08-04 00:00:00', 'Apple', 1), ('2009-08-04 00:00:00', 'Персик', 2), ('2009-08-04 00:00:00', 'Персик', 2), ('2009-08-04 00:00:00', 'Pear', 3), ('2009-08-05 00:00:00', 'Apple', 1), ('2009-08-06 00:00:00', 'Apple', 1), ('2009-08-06 00:00:00', 'Персик', 2), ('2009-08-06 00:00:00', 'Pear', 3);
Мое первоначальное предложение было неверным :
SELECT date, item, SUM(cnt) FROM ( SELECT date, item, count(item_id) AS cnt FROM test_popularity GROUP BY date, item_id ORDER BY cnt DESC ) t GROUP BY date;
Это ошибочно предполагает, что внешняя агрегация (по дате) будет выбирать первую строку внутренней производной таблицы, которая была заказана cnt. Это поведение, по сути, не определено и не гарантируется последовательностью.
Вот правильное решение:
SELECT t1.date, t1.item, (SELECT COUNT(*) FROM test_popularity WHERE date = t1.date) as total # see note! FROM test_popularity t1 JOIN ( SELECT date, item, item_id, COUNT(item_id) as count FROM test_popularity GROUP BY date, item_id ) AS t2 ON t1.date = t2.date AND t1.item_id = t2.item_id GROUP BY t1.date;
Заметка:
Я добавил (SELECT COUNT(*)) AS total
потому что вопрос задал это в одном запросе. Однако это не будет масштабироваться, поскольку это коррелированный подзапрос. Это означает, что для каждого t1.date будет выполняться подзапрос SELECT COUNT (*). Пожалуйста, проверьте и проверьте, подходит ли он для ваших нужд. Если нет, то я предлагаю получать ежедневные итоги в отдельном запросе. Вы бы объединили эти результаты в своем приложении.
благодаря hohodave за его первоначальный ответ:
SELECT date, item, cnt, ( SELECT COUNT (*) FROM test_popularity WHERE date = t.date ) AS totalCnt ИЗ ( SELECT date, item, count (item_id) AS cnt FROM test_popularity Дата GROUP BY, item_id ORDER BY cnt DESC ) т Дата GROUP BY;
Это как можно ближе …
SELECT DISTINCT p.date, ItemTotalsByDate.Item, DateTotals.Total FROM test_popularity p INNER JOIN (SELECT date, MAX(cnt) DayMax from (SELECT date, item, COUNT(*) cnt FROM dbo.test_popularity GROUP BY date, item) tbl GROUP BY date) MaxesByDate ON p. date = MaxesByDate.date INNER JOIN (SELECT date, item, COUNT(*) Total FROM dbo.test_popularity GROUP BY date, item) ItemTotalsByDate ON MaxesByDate.date = ItemTotalsByDate.date AND MaxesByDate.DayMax = ItemTotalsByDate.Total INNER JOIN (SELECT date, COUNT(*) Total FROM dbo.test_popularity GROUP BY date) DateTotals ON p.date = DateTotals.date
Единственное, что уходит на ваш PHP, это показать только первый результат, который он находит для данной даты. Я не мог найти хороший способ произвольно выбрать один предмет, когда он был галстуком. Надеюсь это поможет.