How to Work with BigQuery Mathematical Functions

Alyona Samovar, Senior Digital Analyst @ OWOX

Oct 25, 2024

Data analysis is a game of precision, where even the smallest misstep can lead to skewed results and misguided decisions.

Google BigQuery’s mathematical functions are the unsung heroes in this process, transforming and turning raw numbers into actionable business insights with accuracy and speed.

Whether you're optimizing workflows, calculating complex metrics, or simply ensuring your reports are error-free, mastering these functions is crucial.

This guide will walk you through the most powerful maths tools BigQuery has to offer, equipping you with the knowledge to elevate your data analysis to new heights. Ready to sharpen your skills and deliver data that drives decisions? Let’s get started.

Introduction to Mathematical Functions in BigQuery

Google BigQuery's powerful data processing capabilities have emerged as a major serverless data warehouse that is frequently used by businesses. BigQuery is a top option for companies managing big volumes of data because it supports ANSI SQL and is available as a Platform as a Service (PaaS) via Google Cloud.

With its Machine Learning capabilities, BigQuery not only makes data analysis easier, but it also offers several mathematical operations necessary for effectively handling big datasets. In addition to providing insights into how these often-used BigQuery Math functions can improve your data research, this post will showcase some of the more useful ones.

Prerequisites of Using Mathematical Functions in BigQuery

Before diving into BigQuery Math functions, it's crucial to understand two key prerequisites:

NULL Handling: If any input parameter in your function is NULL, the result will also be NULL.
NaN Values: If any argument in the function is NaN (Not a number), the output will be NaN as well.

Different Categories of Math Functions in BigQuery (with Examples)

BigQuery offers a wide range of mathematical functions, each designed to enhance your data processing capabilities by working seamlessly with different data structures. These functions are categorized based on their purpose and use cases, allowing you to apply them effectively within various data frameworks.

Trigonometric Functions: Used for calculations involving angles, such as sine, cosine, and tangent.
Exponential and Logarithmic Functions: Essential for operations involving growth rates, decay processes, and logarithmic scaling.
Rounding and Truncation Functions: Help control the precision of numerical results, crucial for financial data and reporting.
Power and Root Functions: Support advanced mathematical operations, including raising numbers to power or extracting roots.
Sign and Comparison Functions: Useful for evaluating the sign of values and comparing numbers to determine the greatest or least values.

Below, we'll explore these categories with examples.

Get BigQuery Reports in Seconds

Seamlessly generate and update reports in Google Sheets—no complex setup needed

Start Reporting Now

Trigonometric Functions in BigQuery

Trigonometric functions, related to angles and geometric measurements, are widely used in fields like engineering, physics, and data analysis involving spatial data. BigQuery provides a variety of trigonometric functions, allowing you to compute values such as sine, cosine, tangent, and their inverses.

ACOS

The ACOS function computes the principal value of the inverse cosine (arccosine) of a given number X. The result is a value in the range [0, π]. This function is useful for determining the angle whose cosine is the given number. However, ACOS will generate an error if X is outside the valid range of [-1, 1].

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X < -1 → Result: Error
For X > 1 → Result: Error

Syntax:

ACOS(X)

Example:

The ACOS function can be used to determine the angle between vectors like sales growth and marketing spend.

Here’s how it works:

SELECT ACOS(0.5) AS angle;

Here, ACOS(0.5): Computes the arccosine of 0.5, yielding approximately 1.047 radians (60 degrees), useful for analyzing the alignment of two growth metrics.

ACOSH

The ACOSH function computes the inverse hyperbolic cosine of X, typically used in calculations involving hyperbolic angles. It is important to note that ACOSH will generate an error if X is less than 1, as the hyperbolic cosine is only defined for values X ≥ 1.

Behavior:

For X = +inf → Result: +inf
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X < 1 → Result: Error

Syntax:

ACOSH(X)

Example:

ACOSH is useful for modeling the growth of a financial portfolio that follows a hyperbolic pattern.

Here’s how it’s applied:

SELECT ACOSH(1.5) AS growth_rate;

Here, ACOSH(1.5): Computes the inverse hyperbolic cosine of 1.5, yielding approximately 0.962, useful for modeling rapid early growth that stabilizes over time.

ASIN

The ASIN function computes the principal value of the inverse sine (arcsine) of a given number X. The result is a value in the range [-π/2, π/2]. This function is used to determine the angle whose sine is the given number. The ASIN function will generate an error if X is outside the range [-1, 1].

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X < -1 → Result: Error
For X > 1 → Result: Error

Syntax:

ASIN(X)

Example:

You can use ASIN to find the angle that represents a wave-like pattern in sales over time.

Here's how it works:

SELECT ASIN(0.5) AS angle;

Here, ASIN(0.5) computes the arcsine of 0.5, resulting in approximately 0.524 radians (30 degrees), useful for understanding the amplitude of cyclical sales trends.

ASINH

The ASINH function computes the inverse hyperbolic sine of a given number X. Unlike other functions, ASINH does not fail regardless of the input. It is commonly used in mathematical and scientific calculations involving hyperbolic functions.

Behavior:

For X = +inf → Result: +inf
For X = -inf → Result: -inf
For X = NaN → Result: NaN

Syntax:

ASINH(X)

Example:

In business scenario, the ASINH function can be used in modeling customer adoption rates that accelerate rapidly.

SELECT ASINH(1) AS adoption_rate;

Here, ASINH(1): Computes the inverse hyperbolic sine of 1, yielding approximately 0.881, useful for modeling initial rapid growth in customer adoption.

ATAN

The ATAN function computes the principal value of the inverse tangent (arctangent) of a given number X. The result is in the range [-π/2, π/2]. This function is useful for finding the angle whose tangent is the given number, and it does not fail regardless of the input.

Behavior:

For X = +inf → Result: π/2
For X = -inf → Result: -π/2
For X = NaN → Result: NaN

Syntax:

ATAN(X)

Example:

ATAN is ideal for determining the slope in customer spend analysis, showing trends over time.

SELECT ATAN(1) AS slope;

Here:

ATAN(1): Computes the inverse tangent of 1.
Result: The output is approximately 0.7854 radians, or π/4.

ATAN2

The ATAN2 function calculates the principal value of the inverse tangent of X/Y, taking into account the signs of both arguments, to determine the correct quadrant. The result is in the range [-π, π]. This function is particularly useful for determining angles in different quadrants based on the given X and Y values.

Behavior:

For X = NaN or Y = NaN → Result: NaN
For X = 0.0 and Y = 0.0 → Result: 0.0
For X > 0 and Y = -inf → Result: π
For X < 0 and Y = -inf → Result: -π
For X = finite value and Y = +inf → Result: 0.0
For X = +inf and Y = finite value → Result: π/2
For X = -inf and Y = finite value → Result: -π/2
For X = +inf and Y = -inf → Result: ¾π
For X = -inf and Y = -inf → Result: -¾π
For X = +inf and Y = +inf → Result: π/4
For X = -inf and Y = +inf → Result: -π/4

Syntax:

ATAN2(X, Y)

Example:

ATAN2 is used to calculate the angle of return on investment (ROI) based on x and y financial components.

SELECT ATAN2(10, 10) AS angle;

Here, ATAN2(10, 10) Computes the arctangent of y/x, yielding approximately 0.785 radians (45 degrees), useful for understanding the effectiveness of marketing spend.

ATANH

The ATANH function computes the inverse hyperbolic tangent of a given number X. It is important to note that this function will generate an error if X is outside the range (-1, 1).

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X < -1 → Result: Error
For X > 1 → Result: Error

Syntax:

ATANH(X)

Example:

Businesses can use ATANH to model the slowing of customer churn over a period as market matures.

SELECT ATANH(0.5) AS param_rate;

Here, ATANH(0.5) computes the inverse hyperbolic tangent of 0.5, yielding approximately 0.549, useful for modeling decelerating customer churn.

COS

The COS function computes the cosine of a given number X, where X is specified in radians. This function is commonly used in trigonometric calculations and does not fail, though it returns NaN for infinite or undefined values.

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN

Syntax:

COS(X)

Example:

In terms of business use case, COS function can analyze periodic fluctuations in sales, such as seasonal trends.

SELECT COS(3.141592653589793 /3) AS seasonal_effect;

Here, COS(3.141592653589793/3) computes the cosine of 60 degrees (π/3 radians), yielding 0.5, useful for modeling periodic trends like seasonal sales.

COSH

The COSH function computes the hyperbolic cosine of a given number X, where X is specified in radians. This function is used in hyperbolic trigonometry and generates an error if an overflow occurs.

Behavior:

For X = +inf → Result: +inf
For X = -inf → Result: +inf
For X = NaN → Result: NaN

Syntax:

COSH(X)

Example:

The COSH function can be used in business to model the rapid growth that typically occurs during the early stages of a product launch.

SELECT COSH(1) AS growth_factor;

Here, COSH(1) computes the hyperbolic cosine of 1, yielding approximately 1.543, useful for modeling the rapid growth phase in product launches.

Simplify BigQuery Reporting in Sheets

Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs

Simplify Reporting Now

COT

The COT function in BigQuery computes the cotangent of an angle X, where X is specified in radians. This function accepts any data type that can be coerced to FLOAT64. It also supports the SAFE. prefix, which prevents errors by returning NULL instead of raising an error when invalid input is provided, such as when X is zero.

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X = 0 → Result: Error (without SAFE. prefix)
For X = NULL → Result: NULL

Syntax:

COT(X)

Example:

In business scenarios, COT can help to calculate the rate of change in product demand relative to price adjustments.

SELECT COT(3.141592653589793/4) AS rate_of_change;

Here, COT(3.141592653589793/4) computes the cotangent of 45 degrees (π/4 radians), yielding 1, useful for understanding how demand changes with price adjustments.

COTH

The COTH function in BigQuery computes the hyperbolic cotangent of an angle X, where X is specified in radians. This function accepts any data type that can be coerced to FLOAT64 and supports the SAFE. prefix, which ensures that instead of generating an error (such as when X is zero), the function will return NULL.

Behavior:

For X = +inf → Result: 1
For X = -inf → Result: -1
For X = NaN → Result: NaN
For X = 0 → Result: Error (without SAFE. prefix)
For X = NULL → Result: NULL

Syntax:

COTH(X)

Example:

COTH can be useful for modeling how customer growth slows down in mature markets.

SELECT COTH(1) AS deceleration_rate;

Here, COTH(1) computes the hyperbolic cotangent of 1, yielding approximately 1.313, useful for modeling the slowing pace of customer acquisition in mature markets.

CSC

The CSC function in BigQuery computes the cosecant of an input angle X, where X is specified in radians. This function accepts any data type that can be coerced to FLOAT64 and supports the SAFE. prefix, which ensures that instead of generating an error (such as when X is zero), the function will return NULL.

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X = 0 → Result: Error (without SAFE. prefix)
For X = NULL → Result: NULL

Syntax:

CSC(X)

Example:

Businesses can use CSC to analyze the response curve of marketing campaigns with sharp initial gains.

SELECT CSC(3.141592653589793/6) AS response_curve;

Here, CSC(3.141592653589793/6) computes the cosecant of 30 degrees (π/6 radians), yielding approximately 2, useful for analyzing campaigns with steep initial engagement.

CSCH

The CSCH function in BigQuery computes the hyperbolic cosecant of an input angle X, where X is specified in radians. This function accepts any data type that can be coerced to FLOAT64 and supports the SAFE. prefix, which ensures that instead of generating an error (such as when X is zero), the function will return NULL.

This capability is part of BigQuery's extensive set of data manipulation functions, allowing for precise and safe mathematical operations within your datasets.

Behavior:

For X = +inf → Result: 0
For X = -inf → Result: 0
For X = NaN → Result: NaN
For X = 0 → Result: Error (without SAFE. prefix)
For X = NULL → Result: NULL

Syntax:

CSCH(X)

Example:

In a business scenario, CSCH is great for modeling the initial high growth rate of a viral marketing campaign.

SELECT CSCH(1) AS viral_growth;

Here CSCH(1) computes the hyperbolic cosecant of 1, yielding approximately 0.850, useful for modeling rapid early growth in viral campaigns.

SEC

The SEC function in BigQuery computes the secant of an input angle X, where X is specified in radians. This function accepts any data type that can be coerced to FLOAT64.

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN
For X = NULL → Result: NULL

Syntax:

SEC(X)

Example:

SEC can be used to measure how small changes have big effects in a responsive market in business scenario.

SELECT SEC(3.141592653589793/3) AS market_amplification;

Here, SEC(3.141592653589793/3) computes the secant of 60 degrees (π/3 radians), yielding approximately 2, useful for modeling scenarios where small market changes have large effects.

SECH

The SECH function computes the hyperbolic secant of an input angle X, where X is specified in radians. This function accepts any data type that can be coerced to FLOAT64 and never produces an error, making it a reliable function for a wide range of inputs.

Behavior:

For X = +inf → Result: 0
For X = -inf → Result: 0
For X = NaN → Result: NaN
For X = NULL → Result: NULL

Syntax:

SECH(X)

Example:

In business data analysis, SECH is useful for modeling decaying customer interest over time.

SELECT SECH(1) AS interest_decay;

Here, SECH(1) computes the hyperbolic secant of 1 with the help of BigQuery SQL, yielding approximately 0.648, useful for modeling the decline in customer interest over time.

SIN

The SIN function computes the sine of a given number X, where X is specified in radians. It is a fundamental trigonometric function used to find the sine of an angle.

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN

Syntax:

SIN(X)

Example:

SIN can be used to analyze cyclic trends in user engagement data, helping with pattern recognition.

SELECT SIN(3.141592653589793/6) AS engagement_trend;

Here, SIN(3.141592653589793/6) computes the sine of 30 degrees (π/6 radians), yielding 0.5, useful for modeling cyclic patterns in user engagement.

Unlock BigQuery Insights in Google Sheets

Report on what matters to you. Integrate corporate BigQuery data into a familiar spreadsheet interface. Get insightful, up-to-date reports with just a few clicks

Unlock Data Insights Now

SINH

The SINH function computes the hyperbolic sine of a given number X, where X is specified in radians. It is used in hyperbolic trigonometry and in case of error, it returns +inf or -inf for large positive or negative values, respectively.

Behavior:

For X = +inf → Result: +inf
For X = -inf → Result: -inf
For X = NaN → Result: NaN

Syntax:

SINH(X)

Example:

In business scenario, SINH can be used to model fast growth when a new product is first adopted.

SELECT SINH(1) AS adoption_growth;

Here, SINH(1) computes the hyperbolic sine of 1, yielding approximately 1.175, useful for modeling rapid initial growth in product adoption.

TAN

The TAN function computes the tangent of a given number X, where X is specified in radians. This function is commonly used in trigonometry to find the tangent of an angle. It returns NaN for inputs where the tangent is undefined.

Behavior:

For X = +inf → Result: NaN
For X = -inf → Result: NaN
For X = NaN → Result: NaN

Syntax:

TAN(X)

Example:

TAN can help in calculating the slope of sales growth trends, indicating steepness for business data analysis.

SELECT TAN(3.141592653589793/4) AS growth_slope;

Here, TAN(3.141592653589793/4) computes the tangent of 45 degrees (π/4 radians), yielding 1, useful for understanding the steepness of sales growth trends.

TANH

The TANH function computes the hyperbolic tangent of a given number X, where X is specified in radians. This function is used in hyperbolic trigonometry and does not fail, even for extreme values.

Behavior:

For X = +inf → Result: 1.0
For X = -inf → Result: -1.0
For X = NaN → Result: NaN

Syntax:

TANH(X)

Example:

TANH can be used to model how growth slows down as a product matures fora business.

SELECT TANH(1) AS growth_flattening;

Here, TANH(1) computes the hyperbolic tangent of 1, yielding approximately 0.762, useful for modeling the tapering off of growth as a product matures.

Exponential and Logarithmic Functions in BigQuery

Exponential and logarithmic functions are fundamental tools in data analysis, especially when dealing with growth rates, scaling, and data transformations. The exponential function is useful for modeling exponential growth or decay, allowing you to predict how a value changes over time or in response to different variables.

Logarithmic functions, on the other hand, are crucial for scaling data, such as converting multiplicative relationships into additive ones, making it easier to interpret and compare data across different magnitudes. These functions are widely used in fields like finance, economics, engineering, and data science, where understanding the nature of change over time or across various scales is critical.

EXP

The EXP function in BigQuery computes e raised to the power of a given number X, where e is the base of the natural logarithm (approximately 2.718). If the result underflows (becomes too small to be represented), the function returns 0.0. If the result overflows (exceeds the representable range), it returns +inf.

Behavior:

For X = 0.0 → Result: 1.0
For X = +inf → Result: +inf
For X = -inf → Result: 0.0

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

EXP(X)

Example:

EXP is can be used to model exponential revenue growth in fast-growing business scenarios.

SELECT EXP(1) AS revenue_growth;

Here, EXP(1) calculates e^1, yielding approximately 2.718, useful for projecting exponential growth in revenue or user base.

LN

The LN function in BigQuery computes the natural logarithm of a given number X. It is important to note that the LN function generates an error if X is less than or equal to zero, as the natural logarithm is undefined for these values.

Behavior:

For X = 1.0 → Result: 0.0
For X = +inf → Result: +inf
For X < 0 → Result: Error

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

LN(X)

Example:

In business scenario, LN can help to calculate the time needed for an investment to double in value under continuous compounding (if the interest rate is 5%).

SELECT LN(2) / 0,05 AS doubling_time;

Here, LN(2) computes the natural logarithm of 2, useful for determining the doubling time of an investment under continuous compounding.

LOG

The LOG function in BigQuery calculates the logarithm of a given number X. If only X is provided, LOG acts as a synonym for the natural logarithm function LN(X). However, if a second parameter Y is provided, the function computes the logarithm of X to the base Y.

Behavior:

For X = 100.0 and Y = 10.0 → Result: 2.0
For X = -inf and any value of Y → Result: NaN
For any value of X and Y = +inf → Result: NaN
For X = +inf and 0.0 < Y < 1.0 → Result: -inf
For X = +inf and Y > 1.0 → Result: +inf
For X ≤ 0 → Result: Error
For any value of X and Y ≤ 0 → Result: Error
For any value of X and Y = 1.0 → Result: Error

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

LOG(X [, Y])

Example:

Businesses can use LOG to analyze the logarithmic decay of user engagement over time.

SELECT LOG(10, 100) AS engagement_decay;

Here, LOG(10, 100) computes the base-10 logarithm of 100, yielding 2, useful for analyzing decay processes such as diminishing user engagement.

LOG10

The LOG10 function in BigQuery computes the logarithm of a given number X to the base 10. It is similar to the LOG function but is specifically designed for base-10 calculations.

Behavior:

For X = 100.0 → Result: 2.0
For X = -inf → Result: NaN
For X = +inf → Result: +inf
For X ≤ 0 → Result: Error

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

LOG10(X)

Example:

LOG10 is ideal for modeling logarithmic scaling in data compression scenarios.

SELECT LOG10(1000) AS compression_scale;

Here, LOG10(1000) computes the base-10 logarithm of 1000, yielding 3, useful for understanding how data scales logarithmically in compression algorithms.

Get BigQuery Reports in Seconds

Seamlessly generate and update reports in Google Sheets—no complex setup needed

Start Reporting Now

Rounding and Truncation Functions in BigQuery

Rounding and truncation functions in BigQuery are vital for controlling numerical precision in data analysis. Rounding functions in BigQuery, are essential for adjusting numerical values to the nearest integer or a specified number of decimal places.

These functions help simplify data, making it easier to analyze and present by reducing the number of decimal digits, which is particularly useful in financial calculations and trend analysis.

Truncation, allows you to remove unwanted decimal places without rounding, preserving the significant part of a number. This is crucial when you need to maintain conservative estimates or ensure consistent formatting in reports and data processing.

CEIL

The CEIL function in BigQuery returns the smallest integer value that is not less than the given number X. Essentially, it rounds X up to the nearest whole number. This function is useful when you need to ensure that a value is rounded upwards, regardless of its fractional component.

Behavior:

For X = 2.0 → Result: 2.0
For X = 2.3 → Result: 3.0
For X = 2.8 → Result: 3.0
For X = 2.5 → Result: 3.0
For X = -2.3 → Result: -2.0
For X = -2.8 → Result: -2.0
For X = -2.5 → Result: -2.0
For X = 0 → Result: 0
For X = +inf → Result: +inf
For X = -inf → Result: -inf
For X = NaN → Result: NaN

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

CEIL(X)

Example:

CEIL can be used to round up the results of financial calculations to the nearest whole number, ensuring accuracy.

SELECT CEIL(4.2) AS rounded_value1,
CEIL(4.8) AS rounded_value2,
CEIL(-3.2) AS rounded_value3,
CEIL(-3.9) AS rounded_value4;

Here, CEIL(4.2) rounds up 4.2 to the nearest whole number, yielding 5, useful for financial calculations where rounding up is required.

CEILING

The CEILING function in BigQuery is a synonym for the CEIL function. It performs the same operation, returning the smallest integer value that is not less than the given number X.

Behavior:

Identical to the CEIL function.

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

CEILING(X)

Example:

CEILING is used to round up prices to the nearest higher integer to avoid fractional amounts.

SELECT CEILING(19.95) AS rounded_price1,
CEILING(19.05) AS rounded_price2

Here, CEILING(19.95) rounds 19.95 up to 20, useful for pricing strategies that avoid fractional amounts.

FLOOR

The FLOOR function in BigQuery returns the largest integer value that is not greater than X. Essentially, it rounds X down to the nearest whole number. This function is useful when you need to ensure that a value is rounded downwards, regardless of its fractional component.

Behavior:

For X = 2.0 → Result: 2.0
For X = 2.3 → Result: 2.0
For X = 2.8 → Result: 2.0
For X = 2.5 → Result: 2.0
For X = -2.3 → Result: -3.0
For X = -2.8 → Result: -3.0
For X = -2.5 → Result: -3.0
For X = 0 → Result: 0
For X = +inf → Result: +inf
For X = -inf → Result: -inf
For X = NaN → Result: NaN

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

FLOOR(X)

Example:

FLOOR can be used to round down sales totals to the nearest whole number for more conservative reporting.

SELECT FLOOR(4.8) AS rounded_value;

Here, FLOOR(4.8) rounds 4.8 down to the nearest whole number, yielding 4, useful for conservative financial reporting.

ROUND

The ROUND function in BigQuery rounds a given number X to the nearest integer by default. If an optional parameter N is provided, X is rounded to N decimal places. When N is negative, the function rounds off digits to the left of the decimal point.

The function handles halfway cases by rounding them away from zero. If rounding_mode is specified and X is not of type NUMERIC or BIGNUMERIC, an error is generated.

Behavior:

ROUND(2.0) → Result: 2.0
ROUND(2.3) → Result: 2.0
ROUND(2.8) → Result: 3.0
ROUND(2.5) → Result: 3.0
ROUND(-2.3) → Result: -2.0
ROUND(-2.8) → Result: -3.0
ROUND(-2.5) → Result: -3.0
ROUND(0) → Result: 0
ROUND(+inf) → Result: +inf
ROUND(-inf) → Result: -inf
ROUND(NaN) → Result: NaN
ROUND(123.7, -1) → Result: 120.0
ROUND(1.235, 2) → Result: 1.24
ROUND(NUMERIC "2.25", 1, "ROUND_HALF_EVEN") → Result: 2.2
ROUND(NUMERIC "2.35", 1, "ROUND_HALF_EVEN") → Result: 2.4
ROUND(NUMERIC "2.251", 1, "ROUND_HALF_EVEN") → Result: 2.3
ROUND(NUMERIC "-2.5", 0, "ROUND_HALF_EVEN") → Result: -2
ROUND(NUMERIC "2.5", 0, "ROUND_HALF_AWAY_FROM_ZERO") → Result: 3
ROUND(NUMERIC "-2.5", 0, "ROUND_HALF_AWAY_FROM_ZERO") → Result: -3

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

ROUND(X [, N [, rounding_mode]])

Example:

ROUND can be used to round sales revenue to the nearest dollar for simplified financial reporting.

SELECT ROUND(4.567, 2) AS rounded_value;

Here, ROUND(4.567, 2) rounds 4.567 to two decimal places, yielding 4.57, useful for precise financial reporting.

TRUNC

The TRUNC function in BigQuery truncates a given number X to the nearest integer whose absolute value is not greater than the absolute value of X. If an optional parameter N is provided, TRUNC behaves similarly to ROUND(X, N) but always rounds towards zero and never overflows. This function is useful when you need to remove decimal places without rounding up or down.

Behavior:

For X = 2.0 → Result: 2.0
For X = 2.3 → Result: 2.0
For X = 2.8 → Result: 2.0
For X = 2.5 → Result: 2.0
For X = -2.3 → Result: -2.0
For X = -2.8 → Result: -2.0
For X = -2.5 → Result: -2.0
For X = 0 → Result: 0
For X = +inf → Result: +inf
For X = -inf → Result: -inf
For X = NaN → Result: NaN

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

TRUNC(X [, N])

Example:

TRUNC can be used to truncate extra decimal places in financial forecasts for precision.

SELECT TRUNC(4.5678, 2) AS truncated_value1,
TRUNC(4.5678, 1) AS truncated_value2;

Here, TRUNC(4.5678, 2) truncates 4.5678 to two decimal places, yielding 4.56, useful for financial reporting where truncation is preferred over rounding.

Simplify BigQuery Reporting in Sheets

Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs

Simplify Reporting Now

Power and Root Functions in BigQuery

Power and root functions in BigQuery are vital for performing advanced mathematical operations, such as calculating exponents or extracting square and cube roots.

These functions, including POW, SQRT, and CBRT, are commonly used in fields like finance, engineering, and data analysis to model trends, calculate growth rates, and solve equations. Mastering these functions enhances your ability to perform complex calculations directly within SQL queries.

CBRT

The CBRT function in BigQuery computes the cube root of a given number X. This function can handle any data type that coerces to FLOAT64 and is also available with the SAFE. prefix to prevent errors by returning NULL instead. The cube root is particularly useful for solving equations or analyzing data where you need to find a number that, when cubed, returns the original value.

Behavior:

For X = +inf → Result: inf
For X = -inf → Result: -inf
For X = NaN → Result: NaN
For X = 0 → Result: 0
For X = NULL → Result: NULL

Syntax:

CBRT(X)

Example:

CBRT can be used to model the cubic root of sales growth over multiple periods to understand growth patterns.

SELECT CBRT(27) AS growth_rate;

Here, CBRT(27) calculates the cubic root of 27, yielding 3, useful for understanding growth rates over multiple periods.

POW

The POW function in BigQuery calculates the value of X raised to the power of Y. It is used for exponential calculations where X is the base and Y is the exponent. If the result underflows (becomes too small to be represented), the function returns 0.0.

Behavior:

For X = 2.0 and Y = 3.0 → Result: 8.0
For X = 1.0 and any value of Y (including NaN) → Result: 1.0
For any value of X (including NaN) and Y = 0 → Result: 1.0
For X = -1.0 and Y = +inf → Result: 1.0
For X = -1.0 and Y = -inf → Result: 1.0
For ABS(X) < 1 and Y = -inf → Result: +inf
For ABS(X) > 1 and Y = -inf → Result: 0.0
For ABS(X) < 1 and Y = +inf → Result: 0.0
For ABS(X) > 1 and Y = +inf → Result: +inf
For X = -inf and Y < 0 → Result: 0.0
For X = -inf and Y > 0 → Result: -inf if Y is an odd integer, +inf otherwise
For X = +inf and Y < 0 → Result: 0
For X = +inf and Y > 0 → Result: +inf
For a finite value X < 0 and non-integer Y → Result: Error
For X = 0 and finite value Y < 0 → Result: Error

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

POW(X, Y)

Example:

POW is useful for calculating compounded interest in various financial scenarios.

SELECT POW(1.05, 10) AS compounded_value;

Here, POW(1.05, 10) computes 1.05 raised to the power of 10, yielding approximately 1.628, useful for calculating compounded interest.

POWER

The POWER function is a synonym for POW in BigQuery, performing the same operation of raising X to the power of Y.

Behavior:

Identical to the POW function.

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

POWER(X, Y)

Example:

Businesses can use POWER to project the growth of an investment over a set period of time.

SELECT POWER(2, 3) AS growth_projection;

Here, POWER(2, 3) computes 2 raised to the power of 3, yielding 8, useful for projecting the growth of an investment over time.

SQRT

The SQRT function in BigQuery computes the square root of a given number X. This function is useful in various mathematical and statistical calculations where determining the value that, when squared, equals X is necessary. If X is less than 0, the function generates an error, as square roots of negative numbers are undefined in the context of real numbers.

Behavior:

For X = 25.0 → Result: 5.0
For X = +inf → Result: +inf
For X < 0 → Result: Error

Return Data Type:

Input	INT64	NUMERIC	BIGNUMERIC	FLOAT64
Output	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

SQRT(X)

Example:

SQRT can help to calculate the standard deviation in risk analysis, providing insight into volatility.

SELECT SQRT(16) AS risk_factor;

Here, SQRT(16) computes the square root of 16, yielding 4, useful for calculating standard deviations in risk assessments.

Sign Functions in BigQuery

Sign functions in BigQuery are essential for determining the sign of a numeric value, indicating whether it is positive, negative, or zero. These functions play a crucial role in categorizing data based on its sign, which is particularly useful in tasks like data filtering, conditional logic, and trend analysis.

By using sign functions, you can easily classify and segment data within your queries, enabling more nuanced insights and decision-making.

ABS

The ABS function in BigQuery computes the absolute value of a given number X. The absolute value is the non-negative value of X, regardless of whether X is positive or negative. If the argument is an integer and the result cannot be represented as the same type due to the limitations of the data type (e.g., the largest negative integer), the function returns an error.

Behavior:

For positive X (e.g., 25) → Result: 25
For negative X (e.g., -25) → Result: 25
For X = +inf → Result: +inf
For X = -inf → Result: +inf

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
OUTPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

ABS(X)

Example:

ABS can be used to analyze the absolute value of financial losses or gains, regardless of direction.

SELECT ABS(-10) AS absolute_value;

Here, ABS(-10) computes the absolute value of -10, yielding 10, useful for reporting financial metrics where only magnitude matters.

SIGN

The SIGN function in BigQuery returns -1, 0, or +1 depending on whether the input value X is negative, zero, or positive, respectively. For floating-point arguments, SIGN does not distinguish between positive and negative zero, treating both as 0.

Behavior:

For X > 0 (e.g., 25) → Result: +1
For X = 0 → Result: 0
For X < 0 (e.g., -25) → Result: -1
For X = NaN → Result: NaN

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
OUTPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

SIGN(X)

Example:

SIGN can help to determine the direction (positive or negative) of financial trends, helping with decision-making.

SELECT SIGN(-10) AS trend_direction;

Here, SIGN(-10) computes the sign of -10, yielding -1, useful for identifying the direction of financial trends.

Unlock BigQuery Insights in Google Sheets

Report on what matters to you. Integrate corporate BigQuery data into a familiar spreadsheet interface. Get insightful, up-to-date reports with just a few clicks

Unlock Data Insights Now

Distance Functions in BigQuery

Distance functions in BigQuery, such as COSINE_DISTANCE and EUCLIDEAN_DISTANCE, are designed to calculate the distance or similarity between two data points. These functions are vital for operations like clustering, nearest-neighbor searches, and classification tasks, where understanding the relationship between data points is key.

Whether you're analyzing customer behavior, detecting anomalies, or organizing data into groups, distance functions provide the mathematical foundation needed to accurately measure and compare data. These functions are essential tools for data scientists and analysts looking to uncover patterns, draw insights, and make informed decisions based on the relationships between data points.

COSINE_DISTANCE

COSINE_DISTANCE measures the difference between two vectors based on the angle between them. It reflects how dissimilar the vectors are in direction, with values closer to 0 indicating more similarity and values closer to 1 indicating greater dissimilarity.

Things to Remember:

ARRAY<T> represents a vector, with each index corresponding to a dimension and each value representing a magnitude.
ARRAY<STRUCT<dimension,magnitude>> represents a sparse vector, including only non-zero magnitudes.
Sparse vectors are more efficient, as they only include dimension-magnitude pairs for non-zero magnitudes.
In a sparse vector, the dimension can be a STRING or INT64 value, and the magnitude is a FLOAT64 value.
Empty dimension-magnitude pairs are not included in sparse vectors.
Dimension-magnitude pairs in sparse vectors do not need to be in any specific order.
Both non-sparse vectors in a comparison must share the same dimensions; otherwise, an error occurs.
A vector cannot be a zero vector (no dimensions or all magnitudes are 0); encountering a zero vector results in an error.
An error occurs if any magnitude in a vector is NULL.
If a vector is NULL, the function returns NULL.

Syntax:

COSINE_DISTANCE(vector1, vector2)

Here:

vector1, vector2: Vectors that can be represented either by an ARRAY<T> or a sparse vector represented by a ARRAY<STRUCT<dimension,magnitude>>.

Details:

The ARRAY<T> format represents a vector where each element corresponds to a dimension's magnitude, with the index indicating the dimension.

T refers to the data type of the array elements, which must be the same for both vectors. The only supported data type for T in this function is FLOAT64.

Example:

COSINE_DISTANCE can be used to measure similarity between two customer behavior patterns, aiding in segmentation.

WITH
  vectors AS (
  SELECT
    ARRAY>[
    (1, 1.0),
    (2, 2.0),
    (3, 3.0) ] AS x,
    ARRAY>[
    (1, 4.0),
    (2, 5.0),
    (3, 6.0) ] AS y )
SELECT
  COSINE_DISTANCE(x, y) AS similarity_score
FROM
  vectors;

Here, COSINE_DISTANCE(x, y) computes the cosine distance between two vectors, useful for measuring similarity in customer behavior patterns.

Tips to Avoid Common Errors When Using COSINE_DISTANCE:

Ensure Non-Sparse Vectors Have Matching Dimensions: Both vectors must have the same number of dimensions. Mismatched dimensions will result in an error.
Avoid Using Zero Vectors: A vector cannot be a zero vector, meaning all dimensions must have non-zero magnitudes. Using a zero vector will cause an error.
Ensure No Repeated Dimensions in Sparse Vectors: When using sparse vectors, make sure each dimension is unique within the vector. Repeating a dimension will trigger an error.
Avoid NULL Magnitudes: Ensure that no magnitudes in the vector are NULL. If a magnitude is NULL, the function will return an error.
Order of Elements Does Not Matter: While the order of dimensions and magnitudes in the vector does not affect the result, ensure the correct magnitudes are associated with the correct dimensions.

EUCLIDEAN_DISTANCE

The EUCLIDEAN_DISTANCE function calculates the straight-line distance between two vectors in multi-dimensional space. It measures how far apart the two points represented by the vectors are, often used in clustering and spatial analysis.

Things to Remember:

Vectors can be represented by ARRAY<T>, where each index corresponds to a dimension and each value represents a magnitude.
ARRAY<STRUCT<dimension,magnitude>> is used for sparse vectors, including only non-zero magnitudes.
Sparse vectors are more efficient, as they only include dimension-magnitude pairs for non-zero magnitudes.
In sparse vectors, the dimension can be a STRING or INT64, and the magnitude is a FLOAT64.
Empty dimension-magnitude pairs are not included in sparse vectors.
Dimension-magnitude pairs in sparse vectors do not need to be ordered.
Both non-sparse vectors must have matching dimensions; otherwise, an error occurs.
A vector can be a zero vector if it has no dimensions or all dimensions have a magnitude of 0.
An error occurs if any magnitude in a vector is NULL.
If a vector is NULL, the function returns NULL.

Syntax:

EUCLIDEAN_DISTANCE(vector1, vector2)

Here:

vector1, vector2: These are vectors that can be represented either by an ARRAY<T> or as sparse vectors using ARRAY<STRUCT<dimension,magnitude>>.

Details:

Vectors can be defined using ARRAY<T>, where each index corresponds to a dimension, and each element represents the magnitude.
The data type T for the elements must be consistent across both vectors and is limited to FLOAT64.

Example:

EUCLIDEAN_DISTANCE can be used to calculate the straight-line distance between two customer segments, useful for analysis.

WITH
  vectors AS (
  SELECT
    [3.0, 5.0, 2.0] AS x,
    [1.0, 4.0, 6.0] AS y )
SELECT
  EUCLIDEAN_DISTANCE(x, y) AS distance
FROM
  vectors;

Here, EUCLIDEAN_DISTANCE(x, y) computes the straight-line distance between two points, useful for segmenting customers based on similar traits.

Tips to Avoid Common Errors When Using EUCLIDEAN_DISTANCE:

Ensure Non-Sparse Vectors Have Matching Dimensions: Both vectors must have the same number of dimensions; otherwise, the function will return an error.
Avoid Using Zero Vectors: While zero vectors (vectors with all zero magnitudes) are allowed, ensure they are used intentionally, as they may lead to misleading results.
Avoid Repeating Dimensions in Sparse Vectors: When using sparse vectors, each dimension must be unique within the vector. Repeating a dimension will result in an error.
Order of Magnitudes Doesn't Matter: The order of magnitudes in vectors does not affect the result, so ensure correct associations between dimensions and magnitudes rather than focusing on their order.
Handle NULL Values Carefully: Ensure that no magnitude in a vector is NULL. If a magnitude is NULL, the function will either return NULL or cause an error.

Get BigQuery Reports in Seconds

Seamlessly generate and update reports in Google Sheets—no complex setup needed

Start Reporting Now

Comparison Functions in BigQuery

Comparison functions in BigQuery, including GREATEST and LEAST, allow you to evaluate multiple values and return either the maximum or minimum. These functions are incredibly useful for ranking, sorting, and making decisions within your data queries.

For example, they can help you quickly identify the highest or lowest values in a dataset, which is critical for generating reports, optimizing processes, and performing conditional analyses where the relative value of data points is important.

GREATEST

The GREATEST function in BigQuery returns the largest value among the provided arguments X1, ..., XN. This function is useful when you need to determine the maximum value from a list of numbers or expressions.

If any of the arguments is NULL, the function returns NULL. For floating-point arguments, if any argument is NaN, the function returns NaN. The arguments must be coercible to a common supertype that supports ordering.

Behavior:

For inputs 3, 5, 1 → Result: 5
If any argument is NULL → Result: NULL
If any floating-point argument is NaN → Result: NaN

Syntax:

GREATEST(X1, ..., XN)

Example:

GREATEST can be used to identify the highest sales figure across multiple regions for comparative analysis.

SELECT GREATEST(sales1, sales2, sales3) AS highest_sales;

Here, GREATEST(sales1, sales2, sales3) returns the highest value among the sales figures, useful for regional sales analysis.

LEAST

The LEAST function in BigQuery returns the smallest value among the provided arguments X1, ..., XN. This function is useful for determining the minimum value from a set of numbers or expressions.

Behavior:

For inputs 3, 5, 1 → Result: 1
If any argument is NULL → Result: NULL
If any floating-point argument is NaN → Result: NaN

Syntax:

LEAST(X1, ..., XN)

Example:

LEAST can be used to identify the lowest cost option across multiple suppliers for cost optimization.

SELECT LEAST(cost1, cost2, cost3) AS lowest_cost;

Here, LEAST(cost1, cost2, cost3) returns the lowest value among the cost figures, useful for cost optimization across suppliers.

Random Number Generator Function in BigQuery

The Random Number Generator function in BigQuery, such as RAND, is used to produce pseudo-random numbers within a specified range. This function is essential for a variety of applications, including simulations, randomized sampling, and testing scenarios where unpredictability is required.

Random number generation is particularly valuable in creating data models, testing hypotheses, and conducting experiments that rely on randomized data inputs, ensuring that your analyses are robust and unbiased.

RAND

The RAND function in BigQuery generates a pseudo-random number of type FLOAT64 within the range [0, 1). This means the function returns a value that is greater than or equal to 0 and less than 1.

Behavior:

Returns a random floating-point number in the range [0, 1).
The value is inclusive of 0 and exclusive of 1.
Each call to RAND generates a different random number.

Syntax:

RAND()

Example:

RAND can be used to generate random discount percentages for promotions, adding variety to marketing strategies.

SELECT RAND() AS discount_percentage;

Here, RAND() generates a random number between 0 and 1, useful for applying random discount percentages in promotions.

Arithmetic and Error Handling Functions in BigQuery

Arithmetic and error handling functions in BigQuery, like SAFE_ADD, SAFE_DIVIDE, and others, provide a safe and reliable way to perform mathematical operations. These functions prevent common errors, such as overflow or division by zero, by returning NULL instead of causing your queries to fail.

This error handling capability is crucial for maintaining data integrity and ensuring that your calculations are robust, especially when working with large datasets or complex mathematical expressions. Additionally, BigQuery’s numeric functions are designed to handle precise calculations, ensuring accuracy across various mathematical operations.

DIV

The DIV function in BigQuery performs integer division of X by Y, returning the quotient as an integer. Note that division by zero returns an error, and division by -1 may cause an overflow depending on the values involved.

Behavior:

For X = 20 and Y = 4 → Result: 5
For X = 12 and Y = -7 → Result: -1
For X = 20 and Y = 3 → Result: 6
For X = 20 and Y = 0 → Result: Error

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC
INT64	INT64	NUMERIC	BIGNUMERIC
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC

Syntax:

DIV(X, Y)

Example:

DIV can be used to calculate the integer quotient in sales unit calculations, useful for inventory management.

SELECT DIV(10, 3) AS quotient;

Here, DIV(10, 3) divides 10 by 3, yielding the quotient 3, useful for determining how many full units can be made or sold.

IEEE_DIVIDE

The IEEE_DIVIDE function in BigQuery performs division of X by Y and guarantees a FLOAT64 result without failing. Unlike the standard division operator (/), it doesn't produce errors for division by zero or overflow.

Instead of generating errors, the function returns special IEEE 754 floating-point values such as +inf, -inf, or NaN, depending on the inputs. This makes it particularly useful for avoiding runtime errors in your queries.

Behavior:

For X = 20.0 and Y = 4.0 → Result: 5.0
For X = 0.0 and Y = 25.0 → Result: 0.0
For X = 25.0 and Y = 0.0 → Result: +inf
For X = -25.0 and Y = 0.0 → Result: -inf
For X = 0.0 and Y = 0.0 → Result: NaN
For any operation involving NaN → Result: NaN

Syntax:

IEEE_DIVIDE(X, Y)

Example:

IEEE_DIVIDE can be used to handle division by zero errors safely in financial calculations, avoiding disruptions.

SELECT IEEE_DIVIDE(10, 0) AS result;

Here, IEEE_DIVIDE(10, 0) divides 10 by 0, yielding infinity instead of causing an error, useful for safe handling in financial computations.

IS_INF

The IS_INF function in BigQuery checks if a given value X is either positive infinity (+inf) or negative infinity (-inf). If X is either of these infinite values, the function returns TRUE; otherwise, it returns FALSE.

Behavior:

For X = +inf → Result: TRUE
For X = -inf → Result: TRUE
For any finite number (e.g., X = 25) → Result: FALSE

Syntax:

IS_INF(X)

Example:

IS_INF can be use to check for infinite values in financial projections, ensuring data accuracy.

SELECT IS_INF(IEEE_DIVIDE(1/0)) AS infinity_check;

Here, IS_INF(1/0) checks if a value is infinite, yielding TRUE for 1/0, useful for validating financial projections that might lead to infinity.

IS_NAN

The IS_NAN function in BigQuery checks if a given value X is a "Not a Number" (NaN) value. If X is NaN, the function returns TRUE; otherwise, it returns FALSE.

Behavior:

For X = NaN → Result: TRUE
For any finite number (e.g., X = 25) → Result: FALSE

Syntax:

IS_NAN(X)

Example:

IS_NAN can be used to check for invalid values in datasets, preventing errors in analysis.

SELECT IS_NAN(CAST('NaN' AS FLOAT64)) AS nan_check;

Here, IS_NAN(SQRT(-1)) checks if a value is not a number (NaN), yielding TRUE for SQRT(-1), useful for identifying errors in datasets.

MOD

The MOD function in BigQuery computes the remainder of the division of X by Y. The result, also known as the modulus, has the same sign as X. This function is useful for operations where you need to determine the leftover part of a division. However, if Y is 0, the function generates an error, as division by zero is undefined.

Behavior:

For X = 25 and Y = 12 → Result: 1
For X = 25 and Y = 0 → Result: Error

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC
INT64	INT64	NUMERIC	BIGNUMERIC
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC

Syntax:

MOD(X, Y)

Example:

MOD can help to determine the remainder in sales unit calculations, which is essential for stock management.

SELECT MOD(10, 3) AS remainder;

Here, MOD(10, 3) computes the remainder of 10 divided by 3, yielding 1, useful for inventory or sales calculations where remainders are important.

SAFE_ADD

The SAFE_ADD function in BigQuery performs addition like the standard + operator but returns NULL if an overflow occurs. This function ensures that your calculations are safe from errors due to exceeding the limits of the data type.

Behavior:

If the addition of X and Y results in a value that exceeds the data type limit → Result: NULL
If no overflow occurs → Returns the sum of X and Y

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

SAFE_ADD(X, Y)

Example:

SAFE_ADD can be used to add values safely without risking overflow in large-scale financial reports.

SELECT SAFE_ADD(9223372036854775807, 1) AS safe_sum;

Here, SAFE_ADD(9223372036854775807, 1) safely adds values without overflow, yielding NULL if overflow would occur, useful for large-scale financial computations.

SAFE_DIVIDE

The SAFE_DIVIDE function in BigQuery performs division like the standard/ operator, but returns NULL if an error occurs, such as division by zero. This function ensures that division operations do not cause runtime errors.

Behavior:

If Y is 0 → Result: NULL
If no division errors occur, → Returns the result of X divided by Y

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

SAFE_DIVIDE(X, Y)

Example:

For business data analysis, SAFE_DIVIDE can be used to divide values, avoiding division by zero errors in financial computations.

SELECT SAFE_DIVIDE(10, 0) AS safe_division;

Here, SAFE_DIVIDE(10, 0) safely divides values, yielding NULL instead of causing a division by zero error, useful for error-proof financial analysis.

SAFE_MULTIPLY

The SAFE_MULTIPLY function in BigQuery performs multiplication like the standard * operator, but returns NULL if an overflow occurs. This function prevents errors due to exceeding the data type limits during multiplication.

Behavior:

If the multiplication of X and Y results in a value that exceeds the data type limit → Result: NULL
If no overflow occurs, → Returns the product of X and Y

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

SAFE_MULTIPLY(X, Y)

Example:

SAFE_MULTIPLY to accurately multiply large financial figures without the risk of overflow, ensuring reliable results.

SELECT SAFE_MULTIPLY(9223372036854775807, 2) AS safe_product;

Here, the SAFE_MULTIPLY(9223372036854775807, 2) function safely multiplies values and returns NULL if an overflow occurs, making it ideal for handling large-scale financial data.

SAFE_NEGATE

The SAFE_NEGATE function in BigQuery performs negation like the unary minus (-) operator, but returns NULL if an overflow occurs. This function ensures safe negation, especially with large values that might cause an overflow.

Behavior:

If negating X results in a value that exceeds the data type limit → Result: NULL
If no overflow occurs → Returns the negated value of X

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
OUTPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64

Syntax:

SAFE_NEGATE(X)

Example:

SAFE_NEGATE can be used to safely negate values to avoid overflow in debt calculations.

SELECT SAFE_NEGATE(-9223372036854775808) AS safe_negation;

Here, SAFE_NEGATE(-9223372036854775807) negates values, yielding NULL if overflow would occur, useful for handling large negative values like debts.

SAFE_SUBTRACT

The SAFE_SUBTRACT function in BigQuery subtracts Y from X like the standard - operator, but returns NULL if an overflow occurs. This function helps prevent errors during subtraction, especially with large numbers.

Behavior:

If subtracting Y from X results in a value that exceeds the data type limit → Result: NULL
If no overflow occurs, → Returns the result of X - Y

Return Data Type:

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

Syntax:

SAFE_SUBTRACT(X, Y)

Example:

SAFE_SUBTRACT can be used to subtract values safely without risking underflow in balance sheets.

SELECT SAFE_SUBTRACT(-9223372036854775808, 1) AS safe_difference;

Here, SAFE_SUBTRACT(-9223372036854775808, 1) safely subtracts values, yielding NULL if underflow would occur, useful for maintaining accurate balance sheets.

Unlock BigQuery Insights in Google Sheets

Report on what matters to you. Integrate corporate BigQuery data into a familiar spreadsheet interface. Get insightful, up-to-date reports with just a few clicks

Unlock Data Insights Now

Bucket Function in BigQuery

The Bucket function in BigQuery, exemplified by RANGE_BUCKET, is designed to segment data into predefined intervals or "buckets." This function is useful for tasks like creating histograms, grouping data by ranges, and analyzing the distribution of data points across specified categories.

By organizing data into buckets, you can gain clearer insights into the spread and frequency of values within your dataset, making it easier to identify patterns, trends, and outliers.

RANGE_BUCKET

The RANGE_BUCKET function in BigQuery scans through a sorted array and returns the 0-based position of the point's upper bound. This function is useful for tasks like creating partitions, histograms, or applying business-defined rules where you need to determine the position of a value within a set of boundaries.

Things to Remember:

If the point exists in the array, the function returns the index of the next larger value.
If the point does not exist in the array but falls between two values, it returns the index of the larger value.
If the point is smaller than the first value in the array, it returns 0.
If the point is greater than or equal to the last value in the array, it returns the length of the array.
If the array is empty, it returns 0.
If the point is NULL or NaN, it returns NULL.

Execution Failure Occurs When:

The array contains a NaN or NULL value.
The array is not sorted in ascending order.

Return Data Type:

INT64

Syntax:

RANGE_BUCKET(point, boundaries_array)

Example:

RANGE_BUCKET can be used to categorize customers into segments based on their spend levels, aiding in targeted marketing.

SELECT
  customer_id,
  spend,
  RANGE_BUCKET(spend, [0, 100, 200, 300]) AS segment
FROM
  `owox-analytics.dataset.customers_data`

Here, RANGE_BUCKET(spend, [0, 100, 200, 300]) categorizes spend into segments, useful for customer segmentation in marketing analysis.

Best Practices to Follow while Using Math Functions in BigQuery

When using math functions in BigQuery, it's essential to optimize both performance and accuracy. Always choose the appropriate data types, such as FLOAT64 or NUMERIC, to ensure precision in calculations. Be mindful of handling NULL values, as these can lead to unexpected results or errors; use safe functions like SAFE_DIVIDE to prevent issues like division by zero.

Additionally, understand how rounding and truncation functions work to avoid inaccuracies, especially in financial calculations. Consider the impact of large datasets on performance - functions like MOD and POW can be computationally expensive, so use them efficiently.

Optimize Performance with Appropriate Functions

To ensure optimal performance in BigQuery, it's important to choose the right functions based on your needs. For instance, use SAFE_DIVIDE() to safely handle division by zero without causing errors. Reviewing function summaries can help you identify the most suitable options for your queries, ensuring both efficiency and accuracy. Always match the function to your specific use case to avoid unnecessary computational overhead and ensure smooth query execution.

Use Safe Functions to Handle Null Values

To prevent overflow errors in BigQuery, use safe functions like SAFE_ADD(), SAFE_MULTIPLY(), and SAFE_SUBTRACT(). These functions help manage operations safely by returning NULL instead of causing errors when an overflow occurs. Incorporating these safe functions into your queries ensures that your calculations remain reliable and error-free, even when dealing with large or unpredictable data sets.

Performance Considerations

To enhance query performance in BigQuery when using mathematical functions, avoid overly complex expressions - simplify them using functions like ROUND() or CEIL() instead of multiple nested calculations. Additionally, leverage array functions such as ARRAY_SUM() or ARRAY_PRODUCT() for operations involving multiple values, as these are optimized for efficiency and can improve query performance.

Discover the Potential of BigQuery Functions

BigQuery offers a powerful suite of functions that streamline data analysis, allowing you to handle complex queries and extract valuable insights with ease. Whether you're working with conditional logic, manipulating text, converting data types, or performing advanced calculations, these functions enhance your ability to manage and interpret data effectively.

Conditional Expressions: These expressions, like IF, CASE, and COALESCE, allow you to execute different actions or return values based on specified conditions. They're essential for handling complex logic and ensuring your queries adapt to varying criteria.
String Functions: Functions such as CONCAT, SUBSTR, and REPLACE enable manipulation and analysis of text data. They're crucial for formatting, extracting, and transforming strings, making them indispensable for working with text-based data.
Conversion Functions: Tools like CAST and SAFE_CAST let you convert data from one type to another. This is vital when changing data types to ensure compatibility or perform specific operations within queries.
Navigation Functions: Functions like LEAD, LAG, FIRST_VALUE, and LAST_VALUE provide access to data from different rows relative to the current one. They are essential for advanced analyses, such as trend analysis and time-series exploration.
Statistical Aggregate Functions: These include COUNT, SUM, AVG, MIN, and MAX, which summarize and analyze data across multiple rows. They are fundamental for generating insights and performing statistical analysis on large datasets.
Date Functions: Tools such as DATE, FORMAT_DATE, TIMESTAMP, and DATE_DIFF enable the manipulation and calculation of dates and times. They are crucial for operations like date formatting, calculating time differences, and extracting components from dates.
Window Functions: Functions like ROW_NUMBER, RANK, and NTILE allow for calculations across related rows within a dataset. These are essential for ranking, cumulative sums, and calculating moving averages within data partitions.

By mastering these BigQuery functions, you can optimize your data processing workflows, ensure accuracy in your analyses, and unlock deeper insights from your datasets, making BigQuery an indispensable tool in your analytics arsenal.

Gain Advanced Insights with the OWOX Reports Extension for Google Sheets

To truly maximize the power of BigQuery, leveraging tools like the OWOX Reports Extension for Google Sheets can significantly enhance your reporting capabilities. This extension helps you optimize the integration of BigQuery data into your business processes, making it easier to derive actionable insights from your datasets.

Simplify BigQuery Reporting in Sheets

Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs

Simplify Reporting Now

By incorporating the OWOX Reports, you ensure that your data-driven decisions are backed by precise calculations and robust analysis. Whether you're managing complex data pipelines or simply looking to improve your reporting efficiency, the OWOX Reports Extension for Google Sheets is an indispensable tool that helps you make the most of BigQuery's advanced functionalities.

FAQ

Expand all Close all

What are the common trigonometric functions available in BigQuery?

BigQuery provides a range of trigonometric functions such as SIN, COS, TAN, and their inverse functions like ASIN, ACOS, and ATAN. These functions are used to perform calculations based on angles, expressed in radians, and are essential for applications involving geometry, physics, and data modeling.
How can I perform exponential and logarithmic calculations in BigQuery?

BigQuery supports exponential functions like EXP for calculating e to the power of a given number, and logarithmic functions such as LN for natural logarithms and LOG for logarithms with a specified base. These functions are crucial for tasks involving growth rates, scaling, and data transformations.
What rounding and truncation functions does BigQuery offer?

BigQuery offers functions like ROUND, CEIL, and FLOOR for rounding numbers to the nearest integer, up to the next integer, or down to the previous integer, respectively. Additionally, the TRUNC function allows you to truncate numbers to a specified decimal place, always rounding towards zero.
How do I handle null values safely in BigQuery mathematical functions?

To safely handle null values in BigQuery, you can use functions like SAFE_ADD, SAFE_DIVIDE, SAFE_MULTIPLY, and SAFE_SUBTRACT. These functions perform mathematical operations while returning NULL instead of causing errors when encountering issues like division by zero or overflow.
Which functions in BigQuery are used for comparison and distance calculations?

For comparison, BigQuery provides functions like GREATEST and LEAST to determine the maximum or minimum values among a set of inputs. For distance calculations, functions like COSINE_DISTANCE and EUCLIDEAN_DISTANCE are used to measure similarity or distance between data points, which is useful in clustering and classification tasks.
What are the best practices for optimizing performance with mathematical functions in BigQuery?

To optimize performance, it's important to choose the appropriate data types and functions that match your calculation needs. Avoid unnecessary calculations by pre-aggregating data when possible, and use safe functions like SAFE_DIVIDE to handle potential errors without disrupting query execution. Additionally, consider partitioning and clustering your data to improve query efficiency.

How to Work with BigQuery Mathematical Functions

Introduction to Mathematical Functions in BigQuery

Prerequisites of Using Mathematical Functions in BigQuery

Different Categories of Math Functions in BigQuery (with Examples)

Trigonometric Functions in BigQuery

ACOS

ACOSH

ASIN

ASINH

ATAN

ATAN2

ATANH

COS

COSH

COT

COTH

CSC

CSCH

SEC

SECH

SIN

SINH

TAN

TANH

Exponential and Logarithmic Functions in BigQuery

EXP

LN

LOG

LOG10

Rounding and Truncation Functions in BigQuery

CEIL

CEILING

FLOOR

ROUND

TRUNC

Power and Root Functions in BigQuery

CBRT

POW

POWER

SQRT

Sign Functions in BigQuery

ABS

SIGN

Distance Functions in BigQuery

COSINE_DISTANCE

EUCLIDEAN_DISTANCE

Comparison Functions in BigQuery

GREATEST

LEAST

Random Number Generator Function in BigQuery

RAND

Arithmetic and Error Handling Functions in BigQuery

DIV

IEEE_DIVIDE

IS_INF

IS_NAN

MOD

SAFE_ADD

SAFE_DIVIDE

SAFE_MULTIPLY

SAFE_NEGATE

SAFE_SUBTRACT

Bucket Function in BigQuery

RANGE_BUCKET

Best Practices to Follow while Using Math Functions in BigQuery

Optimize Performance with Appropriate Functions

Use Safe Functions to Handle Null Values

Performance Considerations

Discover the Potential of BigQuery Functions

Gain Advanced Insights with the OWOX Reports Extension for Google Sheets

What are the common trigonometric functions available in BigQuery?

How can I perform exponential and logarithmic calculations in BigQuery?

What rounding and truncation functions does BigQuery offer?

How do I handle null values safely in BigQuery mathematical functions?

Which functions in BigQuery are used for comparison and distance calculations?

What are the best practices for optimizing performance with mathematical functions in BigQuery?