Understanding BigQuery Statistical Functions Made Easy

In data science, functions provide a set of instructions for performing a specific task. They are an integral part of software programs forming the bedrock of most applications that are out there today. The BigQuery data warehouse solution has many functions and capabilities that can be harnessed to make your work easier when building, creating code to gain access to your data, or when trying to store your data.

There are many functions available for use on BigQuery but this write-up’s focus will be on BigQuery statistical functions and will state the available aggregate functions and give their syntaxes as well.

Table of Content

1. What is BigQuery?
2. Introduction to BigQuery Functions
3. Introduction to BigQuery Statistical Functions
4. Conclusion

What is BigQuery?

Google BigQuery is a prominent data warehouse solution that is used to manage and analyze data. It is fully managed and has in-built features such as machine learning, geospatial analysis, and business intelligence to help in the analysis of your data. 

BigQuery enables super-fast SQL queries using the processing power of Google’s infrastructure, its distributed analysis engine helps you to query terabytes or petabytes of data in little or no time, making it highly scalable. 

BigQuery is one of those data warehouses that separates its compute and storage, therefore allowing you to analyze your data to answer your organization’s biggest questions and store your data with ease in the cloud.

Introduction to BigQuery Functions

A function can be termed as a set of SQL statements that perform a specific task. Functions are very useful as they can be used repeatedly. When you have to write a large SQL script to perform the same task, you can simply create a function that performs this and can be used at any time to handle such tasks. This is done by using the call function to retrieve the code for use instead of having to rewrite it every time.

A function also accepts inputs in the form of parameters and returns a value as result. BigQuery has several functions for use, its statistical functions will be discussed in the next section.

Introduction to BigQuery Statistical Functions

BigQuery data warehouse has a variety of functions, one of which is the BigQuery Statistical Functions.

BigQuery Statistical Functions include CORR, COVAR_POP, COVAR_SAMP, STDDEV_POP, STDDEV_SAMP, STDDEV, VAR_POP, VAR_SAMP, VARIANCE.

CORR

This function is used to return the Pearson coefficient of correlation of a set of numbers in pairs. The Pearson coefficient can be described as the measure of linear correlation between two sets of data. For each number pair, the first is the dependent variable and the second number is the independent variable. It returns a data type of FLOAT64.

Usually, the return result is between -1 and 1, and a result of 0 means that there is no correlation between the data, it supports all numeric types but ignores any input pair that contains one or more than one NULL value. Hence, if there are fewer than two input pairs without NULL values, this function returns NULL. 

CORR(
  X1, X2
)
[OVER (...)]

Optional Clauses

• OVER: This specifies a window that defines a group of rows around the rows being evaluated in a table we use an analytic function on.

COVAR_POP

This BigQuery Statistical Function returns the population covariance of a set of number pairs. Covariance is defined as a measure of the joint variability of two random variables where the 1st number is the dependent variable and the second number is the independent variable. Its return results are between -Inf and +Inf. Its returned data type is FLOAT64.

COVAR_POP supports all numeric types and also ignores any input pairs that contain one or more than one NULL values. 

But, if there is no input pair without NULL values, this function returns NULL and if only one input pair without NULL values, this function returns 0.

COVAR_POP(
  X1, X2
)
[OVER (...)]

Optional Clauses

• OVER: This specifies a window that defines a group of rows around the rows being evaluated in a table and where to use an analytic function.

COVAR_SAMP

This BigQuery Statistical Function returns the sample covariance of a set of number pairs where the 1st number is the dependent variable and the 2nd number is the independent variable. Its return data type is FLOAT64.

The return result is between -Inf and +Inf, it supports all numeric types and ignores any input pairs that contain one or more than one NULL values. If fewer than two input pairs results without NULL values, then this function returns NULL.

COVAR_SAMP(
  X1, X2
)
[OVER (...)]

Optional Clauses

• OVER: This specifies a window that defines a group of rows around the rows being evaluated in a table and where to use an analytic function.

STDDEV_POP

This BigQuery Statistical Function returns the population (biased) standard deviation of the values; the return result is between 0 and +Inf. It supports all numeric types but ignores any NULL inputs. It returns a data type of FLOAT64.

If inputs are ignored, the function returns NULL and if it receives a single non_NULL input, it returns 0.

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

Optional Clauses

• OVER: This references a window that defines a group of rows around the rows being evaluated in a table upon which to use an analytic function. The clause is currently incompatible with all other clauses within STDDEV_POP ( ).
• DISTINCT: Each distinct value of the expression is aggregated only once into the result.

STDDEV_SAMP

This BigQuery Statistical Function returns the sample (unbiased) standard deviation of the values and its return result is between 0 and +Inf. It supports all numeric types but also ignores any NULL inputs.

If there are fewer than two non-NULL inputs, the STDDEV_SAMP function returns NULL. The STDDEV_SAMP return data type is FLOAT64.

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

Optional Clauses

• OVER: This references a window that defines a group of rows around the rows being evaluated in a table upon which to use an analytic function. The clause is currently incompatible with all other clauses within STDDEV_SAMP ( ).
• DISTINCT: Each distinct value of the expression is aggregated only once into the result.

STDDEV

This BigQuery Statistical Function is an alternative to the STDDEV_SAMP function, therefore, it carries the same attributes.

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

VAR_POP

VAR_POP is a BigQuery Statistical Function that returns the population (biased) variance of the values between 0 and +Inf. It supports all numeric types and ignores any NULL inputs. 

If all inputs are ignored, this BigQuery Statistical Function returns NULL and if it receives a single non-NULL input, it returns 0. Its return data type is FLOAT64.

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

Optional Clauses

• OVER: This references a window that defines a group of rows around the rows being evaluated in a table upon which to use an analytic function. The clause is currently incompatible with all other clauses within VAR_POP ( ).
• DISTINCT: Each distinct value of the expression is aggregated only once into the result.

VAR_SAMP

This BigQuery Statistical Function returns the sample (unbiased) variance of the values and returns the result between 0 and +Inf. It supports all numeric types but ignores any NULL inputs. 

If there are fewer than two non-NULL inputs, the VAR_SAMP function returns NULL. The VAR_SAMP return data type is FLOAT64.

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

Optional Clauses

• OVER: This references a window that defines a group of rows around the rows being evaluated in a table upon which to use an analytic function. The clause is currently incompatible with all other clauses within VAR_SAMP ( ).
• DISTINCT: Each distinct value of the expression is aggregated only once into the result.

VARIANCE

This is an alternative to VAR_SAMP, therefore, it has the same features.

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

Conclusion

In this article, we introduced you to Google BigQuery and its statistical aggregate functions. It explained and defined what each of these statistical functions is used for and also provided the syntax for clarity. 

With this guide for BigQuery Statistical Functions, it is believed that you should be able to apply them where necessary using the syntax and insights from this article. 

Having said that, learning BigQuery Statistical Functions can be a hectic task; therefore, you may decide to use a platform that can cater to your data warehousing needs without needing to write any line of code. This is where Hevo data comes in.

Visit our Website to Explore Hevo

Hevo Data will effectively transfer your data, allowing you to focus on important aspects of your business like Analytics, Customer Management, etc. This platform allows you to seamlessly transfer data from a vast sea of sources to a Data Warehouse like Google BigQuery or a destination of your choice to be visualized in a BI Tool. It is a reliable, secure, and fully automated service that doesn’t require you to write any code!

If you are using Google Big Query as a Data Warehousing and Analysis platform for your business and looking for a No-fuss alternative to Manual Data Integration, then Hevo can efficiently automate this for you. Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan meets all your business needs.

Also, let us know in the comments section below your experience of performing the BigQuery Statistical Functions.