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

on Data Analytics, Data Warehouse, Google BigQuery, SQL • January 18th, 2022 • Write for Hevo

Google BigQuery AVG - Featured Image

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

  • A Google Account.

What is Google BigQuery?

BigQuery AVG - BigQuery logo
Image Source

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. 

Simplify BigQuery ETL with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources including 40+ Free Sources. It is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. 

Hevo loads the data onto the desired Data Warehouse such as Google BigQuery in real-time and enriches the data and transforms it into an analysis-ready form without having to write a single line of code. Its completely automated pipeline, fault-tolerant, and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

GET STARTED WITH HEVO FOR FREE

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Simplify your BigQuery ETL with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Example Queries for Google BigQuery 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:

BigQuery AVG - Average and AS Cluase
Image Source

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:

BigQuery AVG - Average and AS Cluase
Image Source

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:

BigQuery AVG - Average and Distinct Cluase
Image Source

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:

BigQuery AVG - Average and Distinct Cluase
Image Source

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:

BigQuery AVG - Average and Distinct Cluase
Image Source

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:

BigQuery AVG - Average
Image Source

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!

No-code Data Pipeline for Google BigQuery