BigQuery/Google Studio Question: Reasonably Complicated Math In A BigQuery query

Question

I'm working on a covid dashboard for my community: Chestertown Responds My challenge is to compute the 7-day moving average and displaying that information on the Google Studios page.

I've managed to do the calculation via Bigquery (though it might not be as efficient as if could be -- apologies) with the following code:

--Declare some variables. declare ConfirmedCases numeric; declare DateOfAverage datetime; Declare mvgAvg numeric; declare caseDiff numeric; declare multiplier numeric;

--get/set confirmed cases set ConfirmedCases = (select covid.confirmed_cases FROM
bigquery-public-data.covid19_usafacts.summaryAS covid WHERE
county_fips_code = '24029' ORDER BY date DESC limit 1);

--get/set the latest date. set DateOfAverage = (select Date FROM bigquery-public-data.covid19_usafacts.summaryAS covid WHERE
county_fips_code = '24029' ORDER BY date DESC limit 1); --get/set 7 day average set mvgAVG = (select avg(Covid.confirmed_cases) over(order by Covid.confirmed_cases asc ROWS 7 PRECEDING) FROM
bigquery-public-data.covid19_usafacts.summaryAS covid WHERE
county_fips_code = '24029' ORDER BY date DESC limit 1);
--calculate the difference between actual and average. set caseDiff = (select avg(Covid.confirmed_cases) over(order by Covid.confirmed_cases asc ROWS 7 PRECEDING) - covid.Confirmed_cases from bigquery-public-data.covid19_usafacts.summaryAS covid WHERE
county_fips_code = '24029' ORDER BY date DESC limit 1); -- set the multiplier set multiplier = 100;

--Do the final math. select caseDiff / ConfirmedCases * multiplier as result;

MY QUESTION: Recognizing that I have a lot to learn and some definitely cleanup in this query, but result I get in Bigquery is correct, but I'm getting:

View Result
View Result
View Result
View Result

Where the final 'view result' is the answer I want. How can I get this code to just yield the answer (similar to simply running a select ((Answer)) from ((Table)).

Daniel Zagales Daniel Zagales · Accepted Answer · 2020-11-23T17:08:14

The way you're defining the OVER criteria is wrong. The following should help you align it a little more correctly. The partition basically is how do you want the data to be separated to determine the Average, in your case its the county. And you want to sort (ORDER BY) something that aligns to your use case (the last seven days). So we sort desc to get an order list so the query can pull the last seven rows that will then correspond to the last 7 days.

On another note I think there is a data set that has the daily incremental so you don't have do to what you're doing in this query.

select *
, AVG(confirmed_cases) OVER (
        PARTITION BY county_fips_code 
        ORDER BY date desc ROWS 7 PRECEDING
    ) as svn_day_del_avg
from `bigquery-public-data.covid19_usafacts.summary`
where county_fips_code = '24029'
order by date desc

BigQuery/Google Studio Question: Reasonably Complicated Math In A BigQuery query

1 Answers