Google BigQuery AVG Function 101: Syntax, Usage & Examples Simplified

BigQuery is a Cloud Data Warehouse solution provided by Google. It provides its users with a platform where they can store their data online. Google’s BigQuery users enjoy a number of benefits. The platform is exceptionally elastic, scaling up and down to meet the changing Storage and Compute needs of its users. Google BigQuery users can also access their data anytime from different geographic locations. Google BigQuery uses various mechanisms to ensure that the data is secure. 

One of such functions is the Google BigQuery AVG Function, which can help you to get the average value for a field. Google BigQuery organizes data into tables, which are simply intersections of rows and columns. The good news is that Google BigQuery provides its users with a number of functions that they can use to get summaries from their data. They only have to invoke the function of choice and it will return the summary.

This makes it easy for Google BigQuery users to understand their data as they’d often want to get summaries, especially when dealing with huge volumes of data. In this article, we will be discussing the Google BigQuery AVG Function in detail. 

Table of Contents

• Prerequisites
• What is Google BigQuery?
• What is Google BigQuery AVG Function?
  • Syntax: How to use the Google BigQuery AVG Function?
• Example Queries for Google BigQuery AVG Function
  • Using AS Clause with AVG Function
  • Using the DISTINCT Clause with AVG Function
  • Using the OVER Clause with AVG Function
• Conclusion

Prerequisites

• A Google Account.

What is Google BigQuery?

Google BigQuery is a highly scalable, Serverless Data Warehouse with a built-in Query Engine. It was developed by Google, hence, it uses the processing power of Google’s Infrastructure. The Query Engine can run SQL Queries on terabytes of data within seconds, and petabytes within minutes. Google BigQuery gives you this performance without the need to maintain the infrastructure or rebuild or create indexes. 

Google BigQuery’s Speed and Scalability make it suitable for use in processing huge datasets. It also comes with built-in Machine Learning capabilities that can help you to understand your data better. 

With Google BigQuery, you can achieve the following:

• Democratize Insights with a scalable and secure platform that comes with machine learning features. 
• Improve business decision-making from data using a Multi-Cloud and Flexible Analytics solution. 
• Adapt to data of any size, from bytes to petabytes, with no operational overhead. 
• Run large-scale analytics.  

Google BigQuery also allows you to create dashboards and reports that you can use to analyze your data and gain meaningful insights from it. It is also a powerful tool for real-time data analytics. 

What is Google BigQuery AVG Function?

The Google BigQuery AVG Function returns the average or the mean of all the non-null values provided in the input, or NaN if the input has a NaN. The function supports numeric types such as INT64. If the input has Floating-point Values, the output from the function will be non-deterministic, meaning that you may get different results for the same input values when you run the function repeatedly. 

Syntax: How to use the Google Bigquery AVG Function?

The Google BigQuery AVG Function takes the following syntax:

AVG
[DISTINCT]
expression)
[OVER (...)]

The function takes two optional clauses in the following order:

• OVER: It helps you to specify a window. It is incompatible with all other clauses of the AVG function. 
• DISTINCT: When this clause is used in the Google BigQuery AVG Function, every distinct value in the expression will be aggregated only once. 

Example Queries for Google BigQuery AVG Function

• Using AS Clause with AVG Function
• Using the DISTINCT Clause with AVG Function
• Using the OVER Clause with AVG Function

1) Using AS Clause with AVG Function

SELECT AVG(x) as Average
FROM UNNEST([2, 4, 8, 12, 1]) as x;

In the above query, we have invoked the AVG Function to calculate the average for a set of input values. The input values are integers passed inside an array. The UNNEST operator helps us to convert the array into rows. The AVG Function obtained the sum of the input values and divided the sum with their total number (5) to get the average, which is 5.4 as shown below:

The AS keyword helped us to give a name to the result column. 

SELECT AVG(x) as Average
FROM UNNEST([1, 2, 4, 3, 5]) as x;

The query will return the following result:

Note that we have a set of integers as the input values, the result is a floating-point value. We also used the UNNEST function to convert the array into rows. 

2) Using the DISTINCT Clause with AVG Function

The Google BigQuery AVG Function can also be used together with the DISTINCT Clause. In that case, any value that occurs more than once in the input will only be considered once in the calculation of the average. Consider the example given below:

SELECT AVG(DISTINCT x) AS Average
FROM UNNEST([0, 3, 7, 3, 12]) AS x;

The query will return the following output:

In the above query, there are two 3s in the input, but only one was considered in the calculation of the average. The reason is that we have used the DISTINCT Clause in the query. 

Here is another example:

SELECT AVG(DISTINCT x) AS Average
FROM UNNEST([0,  3,  7,  3, 12, 7]) AS x;

The query will return the following result:

Both 3 and 7 occur more than once in the input values, but each was only considered once in the calculation of the average. 

Consider the next example given below:

SELECT AVG(DISTINCT x) AS Average
FROM UNNEST([0, 3, NULL, 7, 3, 12]) AS x;

The query will return the following result:

In the above query, we have NULL as one of the input values. However, this hasn’t changed the result. This means that the Google BigQuery AVG function ignored the NULL. 

3) Using the OVER Clause with AVG Function

SELECT
x,
AVG(x) 
OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Average
FROM UNNEST([0, 4, NULL, 2, 4, 6]) AS x;

The query should return the following output:

In the above query, we have a number of input values passed into an array. The UNNEST Function helped us to convert the array into a set of rows. We have then used the OVER Clause to specify the window for the Google BigQuery AVG Function.

This has helped us to calculate the average value between the value of x in the current row and its previous row. That is how the Google BigQuery AVG function works. 

Conclusion

In this article, you learned the fundamentals of Google BigQuery. You also learned about Google BigQuery AVG Function and its key features, as well as the various operations that can be performed on Google BigQuery AVG Function.

Extraction of complex data from a diverse set of data sources such as Databases, CRMs, Project Management Tools, Streaming Services, and Marketing Platforms to Google BigQuery can be difficult. This is where a simpler solution, such as Hevo Data, can come in handy!

VISIT OUR WEBSITE TO EXPLORE HEVO

Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources including 40+ Free Sources, into your Data Warehouse such as Google BigQuery to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin? SIGN UP for a 14-day Free Trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about the Google BigQuery AVG Function in the comments section below!