diff options
| author | emkael <emkael@tlen.pl> | 2014-10-29 15:48:36 +0100 |
|---|---|---|
| committer | emkael <emkael@tlen.pl> | 2014-10-29 15:48:36 +0100 |
| commit | f271f4971aa3f2ea8718c6df353fadf7aa688cf0 (patch) | |
| tree | a90a59127d4f44bb6a1712b46644c31c47fe3916 /doc | |
| parent | a6e982ee9e229badc5357aad542b36f354a02471 (diff) | |
* useful queries re-formatted
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/sql.txt | 43 |
1 files changed, 37 insertions, 6 deletions
diff --git a/doc/sql.txt b/doc/sql.txt index 16373b3..3a67dd3 100644 --- a/doc/sql.txt +++ b/doc/sql.txt @@ -1,17 +1,48 @@ Useful queries: -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 - - selects top rating progression -SELECT drivers.driver, rankings.ranking, rankings.rank_date FROM rankings INNER JOIN (SELECT MAX(ranking) ranking, _driver FROM rankings 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 +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 - overall top peak ratings - -SELECT drivers.driver, rankings.ranking, rankings.rank_date FROM rankings INNER JOIN (SELECT MAX(rank_date) rank_date, _driver FROM rankings GROUP BY _driver) r ON r.rank_date=rankings.rank_date AND r._driver=rankings._driver JOIN drivers ON rankings._driver = drivers.id ORDER BY rankings.ranking DESC + +SELECT drivers.driver, + rankings.ranking, + rankings.rank_date +FROM rankings +INNER JOIN ( + SELECT MAX(ranking) ranking, _driver FROM rankings + 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 - highest exit ratings -SELECT MAX(ranking), AVG(ranking), YEAR(rank_date) FROM rankings GROUP BY YEAR(rank_date) ORDER BY YEAR(rank_date) ASC +SELECT drivers.driver, + rankings.ranking, + rankings.rank_date +FROM rankings +INNER JOIN ( + SELECT MAX(rank_date) rank_date, _driver FROM rankings GROUP BY _driver +) 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 - year-by-year rating inflation + +SELECT YEAR(rank_date), + MAX(ranking), + MIN(ranking), + AVG(ranking), + AVG(ranking)+STDDEV(ranking), + AVG(ranking)-STDDEV(ranking) +FROM rankings +GROUP BY YEAR(rank_date) +ORDER BY YEAR(rank_date) ASC |