From 659f73b5cac3653283c3cdc09faaf048d974f240 Mon Sep 17 00:00:00 2001 From: emkael Date: Sun, 1 Jul 2018 14:48:02 +0200 Subject: Aggregate function fix for MySQL 5.7 --- charts.py | 12 +++++------- sql/views.sql | 7 +++---- 2 files changed, 8 insertions(+), 11 deletions(-) diff --git a/charts.py b/charts.py index 6125686..d0511ab 100644 --- a/charts.py +++ b/charts.py @@ -26,7 +26,6 @@ def fetch_raw(): GROUP BY _driver ) r ON r.ranking=rankings.ranking AND r._driver=rankings._driver JOIN drivers ON rankings._driver = drivers.id - GROUP BY rankings._driver ORDER BY rankings.ranking DESC LIMIT 0,20 ''', @@ -42,7 +41,7 @@ def fetch_raw(): SELECT MAX(mr.max_ranking) FROM max_date_rankings mr WHERE mr.max_rank_date < max_date_rankings.max_rank_date ) - ''', + ''', 'top-exit-rankings': ''' SELECT drivers.driver, rankings.ranking, @@ -106,7 +105,7 @@ def fetch_raw(): for result in connection.execute(text(query)): csv.writerow(result) -def fetch_decades(): +def fetch_decades(): for decade in range(1950, 2020, 5): drivers = [] for year in range(decade, decade + 5): @@ -124,14 +123,14 @@ def fetch_decades(): LIMIT 0,3) '''), year=year): drivers.append(driver_id[0]) - rankings = connection.execute(text("SELECT rankings.id, rankings.rank_date, MAX(rankings.ranking), rankings._driver, drivers.* FROM rankings JOIN drivers ON drivers.id = rankings._driver WHERE rank_date >= :date_from AND rank_date <= :date_to AND _driver IN :drivers GROUP BY rankings._driver, rankings.rank_date"), date_from=str(decade)+'-01-01', date_to=str(decade+4)+'-12-31', drivers=drivers) + rankings = connection.execute(text("SELECT rankings.rank_date, MAX(rankings.ranking), rankings._driver, drivers.* FROM rankings JOIN drivers ON drivers.id = rankings._driver WHERE rank_date >= :date_from AND rank_date <= :date_to AND _driver IN :drivers GROUP BY rankings._driver, rankings.rank_date"), date_from=str(decade)+'-01-01', date_to=str(decade+4)+'-12-31', drivers=drivers) csv = unicodecsv.writer(open('charts/' + str(decade) + 's.csv', 'w')) tree = lambda: collections.defaultdict(tree) table = tree() dates = set() for row in rankings: - dates.add(str(row[1])) - table[row[5]][str(row[1])] = row[2] + dates.add(str(row[0])) + table[row[4]][str(row[0])] = row[1] dates = list(dates) dates.append('') dates = sorted(dates) @@ -172,4 +171,3 @@ elif sys.argv[1] == 'all': fetch_raw() fetch_decades() fetch_rolling() - diff --git a/sql/views.sql b/sql/views.sql index b9b597c..6f5222f 100644 --- a/sql/views.sql +++ b/sql/views.sql @@ -15,7 +15,8 @@ CREATE VIEW driver_seasons_count AS DROP VIEW IF EXISTS driver_yearly_rankings; CREATE VIEW driver_yearly_rankings AS - SELECT MAX(rankings.ranking) max_ranking, AVG(rankings.ranking) avg_ranking, MIN(rankings.ranking) min_ranking, YEAR(rankings.rank_date) date, COUNT(rankings.id) count, championship.position, rankings._driver + SELECT MAX(rankings.ranking) max_ranking, AVG(rankings.ranking) avg_ranking, MIN(rankings.ranking) min_ranking, + YEAR(rankings.rank_date) date, COUNT(rankings.id) count, MIN(championship.position) position, rankings._driver FROM rankings LEFT JOIN championship ON rankings._driver = championship._driver AND YEAR(rankings.rank_date) = championship.year GROUP BY YEAR(rankings.rank_date), rankings._driver; @@ -59,6 +60,4 @@ CREATE VIEW top_peak_rankings AS FROM rankings JOIN top_yearly_rankings ON YEAR(rankings.rank_date) = top_yearly_rankings.date AND rankings.ranking = top_yearly_rankings.peak JOIN drivers ON rankings._driver = drivers.id - JOIN driver_yearly_rankings ON rankings._driver = driver_yearly_rankings._driver AND top_yearly_rankings.date = driver_yearly_rankings.date - GROUP BY top_yearly_rankings.date; - + JOIN driver_yearly_rankings ON rankings._driver = driver_yearly_rankings._driver AND top_yearly_rankings.date = driver_yearly_rankings.date; -- cgit v1.2.3