summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--charts.py12
-rw-r--r--sql/views.sql7
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;