从M组中选择前N行 [英] Select top N rows out of M groups

问题描述

  CREATE TABLE IF NOT EXISTS`catalog_sites`（
`id` int（ 10）unsigned NOT NULL auto_increment，
`cat_id` int（10）unsigned NOT NULL，
`date` datetime NOT NULL，
`url` varchar（255）NOT NULL，
'title` varchar（255）NOT NULL，
`description` varchar（255）NOT NULL，
`keywords` varchar（255）NOT NULL，
`visited` int（10） unsigned NOT NULL，
`shown` int（10）unsigned NOT NULL，
`meta_try` int（1）NOT NULL，
 PRIMARY KEY（`id`），
 UNIQUE KEY`url`（`url`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1; 
  

我认为我的问题很简单，但似乎找不到合适的解决方案..

因此，这是一个包含网站的表格，我希望获得6个不同类别的6个网站（cat_id，总数：36行），并且每个类别的评分最高。评分计算为 visited / 显示。

我应该得到36行包含6个顶级类别（我们可以通过对 AVG（visited / shown））进行排序来找到它们，以及这6个类别中的每个类别中的6个顶级站点。 / p>

如果您有任何想法，可能会发生这种情况，请告诉我。

我试过你的例子，但它并不适合我，或者我只是不知道如何适应我的情况。无论如何，就SQL而言，我还是个小老鼠，所以我无法理解你的查询。

然而，我设法解决了我的问题。这很复杂，可能是最糟糕的方法。它太慢了，但我会缓存结果，所以这应该不是问题。

这是我的解决方案：

  SET @site_limit = 2; 
 SET @cat_limit = 6; 
 
 SET @row = 0; 
 SET @limiter = 0; 
 SET @last_cat = 0; 
 
选择`cat_id`，`url`，`visited` /`shown` as`rating`，@limiter：= IF（@last_cat =`cat_id`，IF（@limiter> = @ site_limit  -  1，@limiter，@limiter + 1），0）AS`limiter`，@last_cat：=`cat_id` as`last_cat` 
 FROM`catalog_sites` 
 WHERE`cat_id` 
 IN（
 SELECT`cat_id` 
 FROM（
 SELECT`cat_id`，@row：= @row + 1 AS`row` 
 FROM（
 SELECT` cat_id` 
 FROM`catalog_sites` 
 GROUP BY`cat_id` 
 ORDER BY AVG（`visited` /`shown`）DESC 
）AS派生1 
）AS派生的2 
 WHERE`row`< = @cat_limit 
）
 GROUP BY`cat_id`，`limiter` 
 ORDER BY`cat_id`，`rating` DESC 
  

I have this table:

CREATE TABLE IF NOT EXISTS `catalog_sites` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `cat_id` int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  `url` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `visited` int(10) unsigned NOT NULL,
  `shown` int(10) unsigned NOT NULL,
  `meta_try` int(1) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I think my problem is simple, but cant seem to find an appropriate solution..

So, this is a table with web-sites, I would like to get 6 sites in 6 different categories (cat_id, total: 36 rows) with the highest rating for each category. The rating is calculated as visited / shown.

I should get 36 rows containing 6 top categories (we can find them by sorting with AVG(visited / shown) ), and 6 top sites in each of these 6 categories.

If you have any ideas how this might happen differently, please tell me.

解决方案

I've tried your example, but it doesn't really work for me, or I just don't know how to adapt it to my case. Anyway, I'm still a noob as far as SQL goes, so I couldn't understand your query.

I have managed to solve my problem however. It's complicated and probably the worst possible approach. It is slow too, but I'll cache the results, so that shouldn't be a problem.

Here is my solution:

SET @site_limit = 2;
SET @cat_limit = 6;

SET @row = 0;
SET @limiter = 0;
SET @last_cat = 0;

SELECT `cat_id`, `url`, `visited` / `shown` AS `rating`, @limiter := IF(@last_cat = `cat_id`, IF(@limiter >= @site_limit - 1, @limiter, @limiter + 1), 0) AS `limiter`, @last_cat := `cat_id` AS `last_cat`
FROM `catalog_sites`
WHERE `cat_id`
IN (
    SELECT `cat_id`
    FROM (
        SELECT `cat_id`, @row := @row + 1 AS `row`
        FROM (
            SELECT `cat_id`
            FROM `catalog_sites`
            GROUP BY `cat_id`
            ORDER BY AVG(`visited` / `shown`) DESC
        ) AS derived1
    ) AS derived2
    WHERE `row` <= @cat_limit
)
GROUP BY `cat_id`, `limiter`
ORDER BY `cat_id`, `rating` DESC

这篇关于从M组中选择前N行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！