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
.
所以,这是一个包含网站的表格,我希望得到6个不同类别的网站(cat_id,共36行),每个类别的评分最高。评级按访问/显示计算。
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.
我应该得到36行,其中包含6个*类别(我们可以通过使用AVG(访问/显示)进行排序找到它们),以及这6个类别中的6个*站点。
If you have any ideas how this might happen differently, please tell me.
如果你有任何不同的想法,请告诉我。
2 个解决方案
#1
2
This should get you what you want using MySQL Variables, the inner query will pre-calculate the rank of visited / shown, and using an order by by the condition you want... Per Category, the highest ranks... and then using @vars will keep the @RankSeq sequentially 1-? per category. From that Prequery (aliased PQ), the OUTER query just queries the PreQuery where the URL's Rank Sequence is <= 6
这将得到你想要的MySQL变量,内部查询将预先计算访问/显示的级别,并根据你想要的条件使用order by…每一类,最高等级……然后使用@vars将保持@RankSeq连续1-?每一类。从这个Prequery(别名PQ)中,外部查询只查询URL的秩序列<= 6的Prequery
To further ensure you are only getting the top 6 categories, the inner PreQuery also has a pre-query / limit for the "TopCategories" (alias)
为了进一步确保您只获得前6个类别,内部预查询还对“TopCategories”(别名)进行预查询/限制
select
PQ.URL,
PQ.Cat_ID,
PQ.Rank,
PQ.URLRankSeq
from
( select
CS.cat_id,
(CS.visited / CS.shown ) as Rank,
CS.url,
@RankSeq := if( @LastCat = CS.Cat_ID, @RankSeq +1, 1 ) URLRankSeq,
@LastCat := CS.Cat_ID as ignoreIt
from
( select cat_id,
avg( visited / shown )
from catalog_sites
group by 1
order by 2 desc
limit 6 ) TopCategories
JOIN catalog_sites CS
on TopCategories.Cat_ID = CS.Cat_ID,
(select @RankSeq := 0, @LastCat = 0 ) SQLVars
order by
CS.cat_id,
Rank ) PQ
where
PQ.URLRankSeq <= 6
#2
1
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.
我已经试过你的例子了,但是对我来说并没有什么用,或者我只是不知道如何适应我的情况。无论如何,就SQL而言,我还是一个新手,所以我无法理解您的查询。
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
#1
2
This should get you what you want using MySQL Variables, the inner query will pre-calculate the rank of visited / shown, and using an order by by the condition you want... Per Category, the highest ranks... and then using @vars will keep the @RankSeq sequentially 1-? per category. From that Prequery (aliased PQ), the OUTER query just queries the PreQuery where the URL's Rank Sequence is <= 6
这将得到你想要的MySQL变量,内部查询将预先计算访问/显示的级别,并根据你想要的条件使用order by…每一类,最高等级……然后使用@vars将保持@RankSeq连续1-?每一类。从这个Prequery(别名PQ)中,外部查询只查询URL的秩序列<= 6的Prequery
To further ensure you are only getting the top 6 categories, the inner PreQuery also has a pre-query / limit for the "TopCategories" (alias)
为了进一步确保您只获得前6个类别,内部预查询还对“TopCategories”(别名)进行预查询/限制
select
PQ.URL,
PQ.Cat_ID,
PQ.Rank,
PQ.URLRankSeq
from
( select
CS.cat_id,
(CS.visited / CS.shown ) as Rank,
CS.url,
@RankSeq := if( @LastCat = CS.Cat_ID, @RankSeq +1, 1 ) URLRankSeq,
@LastCat := CS.Cat_ID as ignoreIt
from
( select cat_id,
avg( visited / shown )
from catalog_sites
group by 1
order by 2 desc
limit 6 ) TopCategories
JOIN catalog_sites CS
on TopCategories.Cat_ID = CS.Cat_ID,
(select @RankSeq := 0, @LastCat = 0 ) SQLVars
order by
CS.cat_id,
Rank ) PQ
where
PQ.URLRankSeq <= 6
#2
1
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.
我已经试过你的例子了,但是对我来说并没有什么用,或者我只是不知道如何适应我的情况。无论如何,就SQL而言,我还是一个新手,所以我无法理解您的查询。
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