mySQL查询-显示最受欢迎的项目 [英] mySQL query - show most popular item

问题描述

我需要找到一个按日期分组的最流行的项目，并显示所有项目的总和以及该项目的名称.在单个查询中有可能发生这种情况吗?

I need to find the most popular occurrence of an item grouped by date and display the total of all items along with the name of this item. Is something like this possible in a single query?

注意:如果它们都是相等出现的(请参阅插入"中的最后3行)，那么我只能随机显示或出现第一个或最后一个出现(以最简单的为准).

note: If they are all of equal occurrence (see last 3 rows in Insert) than I can just show at random or the first or last occurrence (whichever is easiest).

如果无法在sql中完成此操作，则必须遍历结果并通过PHP对数据进行排序，这看起来会很混乱.

If this can't be done in the sql then will have to run through the result and sort the data via PHP which seems like it'll be quite messy.

对不起，我的'2009'08-04'总数有误.应该是4.

Sorry, I had the wrong total for '2009'08-04'. It should be 4.

我需要的示例:

+------------+---------------------+-------+
| date       | item                | total |
+------------+---------------------+-------+
| 2009-08-02 | Apple               |     5 |
| 2009-08-03 | Pear                |     2 |
| 2009-08-04 | Peach               |     4 |
| 2009-08-05 | Apple               |     1 |
| 2009-08-06 | Apple               |     3 |
+------------+---------------------+-------+

这是一个示例表:

CREATE TABLE IF NOT EXISTS `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`) VALUES
('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', 'Peach', 2),
('2009-08-04 00:00:00', 'Apple', 1),
('2009-08-04 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Peach', 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', 'Peach', 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排序的内部派生表的第一行.实际上，此行为是未定义的，不能保证是一致的.

This erroneously assumes that the outside aggregation (by date) will select the first row of the inner derived table which was ordered by cnt. This behavior is, in fact, undefined and not guaranteed to be consistent.

这是正确的解决方案:

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(*)子查询.请进行基准测试，看看它是否适合您的需求.如果没有，那么我建议在单独的查询中获取每日总计.您可以将这些结果合并到您的应用程序中.

I added the (SELECT COUNT(*)) AS total because the question asked for this in one query. However, this will not scale as it is a correlated subquery. This means that for every t1.date the SELECT COUNT(*) subquery will run. Please benchmark and see if it performs suitably for your needs. If not, then I suggest getting the daily totals in a separate query. You would merge these results in your application.

这篇关于mySQL查询-显示最受欢迎的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！