From 37fb7a1d7c8ee1bad50335873a515e849dd3dff2 Mon Sep 17 00:00:00 2001 From: emkael Date: Wed, 29 Oct 2014 16:59:54 +0100 Subject: * top ranking progression optimized --- doc/sql.txt | 25 +++++++++++++++++++++---- 1 file changed, 21 insertions(+), 4 deletions(-) (limited to 'doc') diff --git a/doc/sql.txt b/doc/sql.txt index 3a67dd3..32e7fa3 100644 --- a/doc/sql.txt +++ b/doc/sql.txt @@ -2,12 +2,29 @@ Useful queries: - selects top rating progression +CREATE OR REPLACE VIEW max_date_rankings AS + SELECT MAX(ranking) max_ranking, + rank_date max_rank_date + FROM rankings + GROUP BY rank_date; +SELECT drivers.driver, + max_date_rankings.max_rank_date, + max_date_rankings.max_ranking +FROM max_date_rankings +INNER JOIN rankings ON (rankings.ranking = max_date_rankings.max_ranking) + AND (rankings.rank_date = max_date_rankings.max_rank_date) +LEFT JOIN drivers ON rankings._driver = drivers.id +WHERE max_ranking > ( + SELECT MAX(mr.max_ranking) FROM max_date_rankings mr + WHERE mr.max_rank_date < max_date_rankings.max_rank_date +); + SELECT * FROM rankings JOIN drivers ON rankings._driver = drivers.id WHERE ranking >= ( SELECT MAX(r.ranking) FROM rankings r WHERE r.rank_date <= rankings.rank_date -) ORDER BY rank_date ASC +) ORDER BY rank_date ASC; - overall top peak ratings @@ -20,7 +37,7 @@ INNER JOIN ( GROUP BY _driver ) r ON r.ranking=rankings.ranking AND r._driver=rankings._driver JOIN drivers ON rankings._driver = drivers.id -ORDER BY rankings.ranking DESC +ORDER BY rankings.ranking DESC; - highest exit ratings @@ -33,7 +50,7 @@ INNER JOIN ( ) r ON r.rank_date=rankings.rank_date AND r._driver=rankings._driver JOIN drivers ON rankings._driver = drivers.id WHERE rankings.rank_date < CONCAT(YEAR(CURDATE()),'-01-01') -ORDER BY rankings.ranking DESC +ORDER BY rankings.ranking DESC; - year-by-year rating inflation @@ -45,4 +62,4 @@ SELECT YEAR(rank_date), AVG(ranking)-STDDEV(ranking) FROM rankings GROUP BY YEAR(rank_date) -ORDER BY YEAR(rank_date) ASC +ORDER BY YEAR(rank_date) ASC; -- cgit v1.2.3