Working with BigQuery Math Functions: Made Easy 101

Google BigQuery has quickly become one of the most popular Serverless Data Warehouses for companies around the globe. Owned by Google Cloud, BigQuery is offered as a Platform as a Service (PaaS) to companies that want a proper space for analyzing their data.

BigQuery supports queries using ANSI SQL, which is one of the main reasons why it’s so popular. If your company deals in large amounts of data, you might already be using BigQuery. With the machine learning capabilities that BigQuery offers, users can easily perform a comprehensive analysis of their data. 

More importantly, BigQuery also gives you a whole host of mathematical functions that you can use. BigQuery Math functions are particularly useful when you are dealing with large chunks of data and need to quickly and efficiently calculate important figures from large datasets. This article will list some of the commonly used BigQuery Math functions. So, read along and gain insights into BigQuery Math functions.

Prerequisites

Before you start using BigQuery Math functions, it’s important for you to know two important things:

• If any input parameters are NULL, then the return is also NULL. 
• Secondly, you will get NaN if any of the arguments is also NaN. 

Understanding Rules of Function Calls

Before we go into detail about Google BigQuery Math functions, there are a few important things that you should know about functions, operators, and expressions. 

Unless explicitly specified, all functions follow the below-mentioned rules:

• The default time zone for all functions is UTC, specifically for time zone-sensitive functions.
• More importantly, if one operand is a floating point and the other is of a numeric type, both operands are then converted automatically to FLOAT64 before evaluation. 
• Apart from the IS operator, any NULL operand will automatically return the NULL result. 

To read more about function call rules, refer to BigQuery Documentation.

Commonly Used BigQuery Math Functions

Now that you know the basics, let’s talk about some of the most popular BigQuery Math functions. A few are listed below:

• ABS (X)
• SIGN (X)
• IS_INF
• IS_NAN (X)
• GREATEST
• LEAST
• DIV
• SAFE_DIVIDE
• LOG10
• SAFE_MULTIPLY
• SAFE_ADD
• SAFE_SUBSTRACT
• MOD
• TRUNC
• CEIL (X)
• COS (X)
• SIN (X)
• TAN (X)

1) ABS (X)

This function is designed to compute the absolute value of a dataset. For instance, if the argument being analyzed is an integer, and the output value can’t be represented as an integer too, this function will return an error. Keep in mind that this only happens for the highest negative input value. 

So, for instance, if X is 25, then ABS (X) will also be 25. However, if X is -25, then ABS (X) will still be 25.

2) SIGN (X)

Another popular math function used in BigQuery is the SIGN (X) function. This one returns a -1, 0, or a +1 for negative, positive, or any zero arguments. In the case of a floating-point argument, the function doesn’t discriminate between a negative or a positive zero. 
So, for instance, if X = 12, then SIGN (X) will be a +1. However, if X = -12, then SIGN (X) will be +1. But, if X = 0, then SIGN (X) will still be 0. 

3) IS_INF

This is another popular math function, IS_INF used in BigQuery, which simply returns TRUE if the value is either negative infinity or positive. So, for instance, if X is +inf, then IS_INF (X) will be TRUE. The same is returned if the value is -inf. However, if the value is 10, for example, then it’s going to return a FALSE.

4) IS_NAN (X)

If the value provided is a NaN value, then IS_NAN (X) will return TRUE. Thus, if X= NaN, it’ll return TRUE. On the other hand, if the value is 15, for example, IS_NAN (X) will be false.

5) GREATEST

This is a simple function that returns the largest value between X1, X2, …, XN, based on the conventional < comparison. However, if any of the values of X is NULL, the value returns are also NULL.So, if you run the function GREATEST (2, 6, 1), it’s going to give you 6 as the answer.

6) LEAST

Conversely, this function returns the smallest value of X based on the values available. Similarly, if any value is NULL, then the return value is also NULL. 
For example, if you run LEAST (2, 6, 3), the value it’s going to return is 2. 

7) DIV

A popular function used by data analysts using BigQuery is the DIV function. The syntax is generally DIV (X, Y), and it’s used to get the result of dividing the integer of X with Y. As basic math goes, if you try to divide by zero, it’ll return an error. Similarly, if you divide by a negative value, such as -1, it’ll probably overflow. 
If X is 24 and Y is assigned a value of 4, then DIV (X, Y) will return a value of 6. Similarly, if X is given a value of 0 and Y is given any other value, such as 12, the result will be 0. On the other hand, if X is assigned a value of 26 but Y is given a value of 0, then DIV (X, Y) will return an Error.

8) SAFE_DIVIDE

Building on the previous function, you can also use SAFE_DIVIDE. This is similar to the DIV (X, Y) function, but in case an error occurs, the result will simply be NULL. This is ideal for spotting issues, especially when making larger calculations. 
For instance, if you run SAFE_DIVIDE (25, 0), then the result is going to be NULL. However, if you run a conventional division, such as SAFE_DIVIDE (25, 5), then it’ll just give you 5 as a result.

9) LOG10

For computing logarithms with a base 10, you will use the LOG10 function. If you use LOG10 (100), then it’ll give you 2.0 as an answer. On the other hand, if X is less than or equal to 0, the function will simply give an error in return.

10) SAFE_MULTIPLY

Another commonly used function is SAFE_MULTIPLY (X, Y). This function is similar to the multiplication operator, which is denoted by a “*” and returns a NULL if there’s an overflow. So, if you run SAFE_MULTIPLY (10, 2), the result will be 20.

11) SAFE_ADD

SAFE_ADD is similar to the operator for adding numbers, which is “+” and as the previous function, will return NULL if there’s an overflow. This is ideal for making simple additions. For instance, if you run SAFE_ADD (2, 2), the result will be 4.

12) SAFE_SUBSTRACT

Just like other functions with the SAFE prefix, this one also returns the value of Y when it’s subtracted from X. It’s similar to the subtraction operator but will return a NULL value if there’s an overflow. 
So, if you run SAFE_SUBSTRACT (8, 4), it’ll give you 4 as an answer.

13) MOD

One of the most popular BigQuery math functions is the Modulo function. It simply gives you the remainder from the division of X by Y. The value that it returns carries the same sign as X. However, if Y is 0, then it’ll return 0 as an answer. 
For instance, if you use MOD (25, 12), the return will be 1. However, if you replace Y with a 0, the MOD function will return an Error.

14) TRUNC

The TRUNC function is used to round X to the closest integer whose absolute value isn’t greater than the absolute value assigned to X. If however, N is also defined, such as TRUNC ( X [ , N]), then TRUNC simply acts like the ROUND function (defined below). 
However, unlike the ROUND function, TRUNC will always round down to zero, and will never overflow. 
So, for instance, if you use TRUNC (2.3), it’ll give you 2.0 as an answer. Similarly, if you run TRUNC (-4.4), it’ll round it down to -4.4.

15) CEIL (X)

The ceiling function is used to get the smallest integral value that’s equal to or higher than X. For instance, if you run CEIL (2.5), it’ll give you 3.0 as an answer. 
Similarly, if you run CEIL (-2.5), it’ll return -2.0 as an answer. Another function that does the same job is CEILING (X).

16) COS (X)

To calculate the cosine value of X, specifically, if X is given in radians, you should run the COS (X). If X is NaN, then COS (X) will also be NaN.

17) SIN (X)

Another function used in trigonometry is the SIN (X) function. If X is specified in radians, you can easily calculate the sine of X using this function.

18) TAN (X)

Similarly, if X is provided in radians, you can use TAN (X) to compute the tangent of X. If there’s an overflow, this function returns an error.

Conclusion

In this article, you understood and explored the commonly used BigQuery Math functions. You also learned their syntax and arguments. BigQuery is great for running calculations on large sums of data. If you have to run computations on large amounts of data, these are some of the many BigQuery Math functions that you can use. Obviously, this list is not exhaustive, and people who are familiar with SQL queries can run all other functions that they want. However, if you want to pull your data from multiple sources – using a no-code data pipeline such as Hevo is an excellent idea.

Frequently Asked Questions