Рассчитать ранг строки эффективным способом

У меня есть следующая таблица с данными примерно 10M:

CREATE TABLE IF NOT EXISTS `tbl_site_visit` (
`id` int(11) NOT NULL,
`siteURL` varchar(50) NOT NULL,
`userHash` varchar(32) NOT NULL,
`timestamp` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Также siteURL, userUID, timestamp имеет индексы.

Теперь я хочу рассчитать рейтинг сайта на основе приведенных выше данных, но быстро и эффективно. Я написал следующий запрос, но рейтинг не является правильным, и это также занимает около 40~60sec рассчитать ранг:

SET @prevRank := -1, @rankCounter := 0;

SELECT siteURL, siteRank FROM (
SELECT *, @rankCounter := IF(@prevRank = visitCount, @rankCounter, @rankCounter + 1) AS siteRank, @prevRank := visitCount AS dummyRank FROM (
SELECT siteURL, COUNT(1) AS visitCount
FROM tbl_site_visit
GROUP BY siteURL
ORDER BY visitCount DESC
) coreTable
) rankedTable
WHERE rankedTable.siteURL = "www.google.com"LIMIT 1;

Есть идеи, как решить эту проблему или есть какая-то статистическая формула для расчета ранга?

0

Решение

Одна ошибка, которую вы совершаете, заключается в том, что вы вычисляете рейтинг для всех сайтов, а затем выбираете результат этого сайта.

Вместо этого вы должны рассчитать рейтинг только для этого сайта.

SET @prevRank := -1, @rankCounter := 0;

SELECT siteURL, siteRank FROM (
SELECT *, @rankCounter := IF(@prevRank = visitCount, @rankCounter, @rankCounter + 1) AS appRank, @prevRank := visitCount AS dummyRank FROM (
SELECT siteURL, COUNT(1) AS visitCount
FROM tbl_site_visit WHERE siteURL = "www.google.com"GROUP BY siteURL
ORDER BY visitCount DESC
) coreTable
) rankedTable

Это должно рассчитать рейтинг только для «www.google.com» и вернуть его данные. Некоторые из внешних SELECTИх можно удалить, но это зависит от того, что вы хотите сделать с данными.

0

Другие решения

SELECT  @rank := @rank+1 AS rank, siteURL, visitCount
FROM
( SELECT  @rank := 0 ) AS z
JOIN
( SELECT  siteURL, COUNT(*) AS visitCount
FROM  tbl_site_visit
GROUP BY  siteURL
) AS x
ORDER BY  visitCount DESC;
0

По вопросам рекламы [email protected]