In the last section, we populated our
us_counties table and used it for some queries. Now it’s time to create some views of the data. With views we’ll be able to do more interesting things without retyping huge queries. We can use views as a foundation for more analysis.
The first view we need is the one to give us back the 7-day moving averages along with the raw data. We’ll call it
us_counties_avg7. It looks like this.
CREATE OR REPLACE VIEW us_counties_avg7 AS SELECT date, county, state, fips, cases, AVG(cases) OVER ( PARTITION BY state, county ORDER BY date ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS cases7, deaths, AVG(deaths) OVER ( PARTITION BY state, county ORDER BY date ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) AS deaths7 FROM us_counties WHERE county <> 'Unknown' AND cases >= 100 AND deaths >= 10;
We exclude cases assigned to an Unknown county; those cases eventually are reassigned to an actual county by each state’s medical statisticians. We also exclude entries with small numbers of cases or deaths; this helps speed up the queries.
Now doing casual queries is easier. We can, for example, use the new view to get the 7-day moving average data for Los Angeles:
SELECT * FROM us_counties_avg7 WHERE date >= '2020-11-01' AND county = 'Los Angeles' AND state = 'California' ORDER BY date;
Even better, we can create another view using the view we just created to give us the daily new case loads from the 7-day averages. Let’s call the new view
CREATE OR REPLACE VIEW us_counties_new AS SELECT today.date, today.state, today.county, today.fips, today.cases, today.cases - yesterday.cases newcases, today.cases7 - yesterday.cases7 newcases7, today.deaths, today.deaths - yesterday.deaths newdeaths, today.deaths7 - yesterday.deaths7 newdeaths7 FROM us_counties_avg7 today JOIN us_counties_avg7 yesterday ON today.date = yesterday.date + INTERVAL 1 DAY AND today.state = yesterday.state AND today.county = yesterday.county
That makes it easy to get the daily caseloads like this. Again, they’re horrendous.
SELECT date, DAYNAME(date) weekday, state, county, fips, newcases7, newdeaths7 FROM us_counties_new WHERE date >= '2020-11-01' AND county = 'Los Angeles' AND state = 'California' ORDER BY date;