Рассчитать ранг строки эффективным способом
У меня есть следующая таблица с данными примерно 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;
Есть идеи, как решить эту проблему или есть какая-то статистическая формула для расчета ранга?
Решение
Одна ошибка, которую вы совершаете, заключается в том, что вы вычисляете рейтинг для всех сайтов, а затем выбираете результат этого сайта.
Вместо этого вы должны рассчитать рейтинг только для этого сайта.
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Их можно удалить, но это зависит от того, что вы хотите сделать с данными.
Другие решения
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;