How to use BigQuery Lag() and Lead() Functions?

Google BigQuery is a serverless Data Warehouse that allows you to analyze voluminous data. As your company grows and starts generating terabytes of complex data, and you have data stored in different sources, you must roll up your sleeves and incorporate a data warehouse like BigQuery into your data architecture. Sieving through terabytes of data on sheets is quite a monotonous endeavor and places a ceiling on what is achievable regarding data analysis. At this juncture, incorporating a data warehouse like BigQuery is necessary. 

This blog talks in detail about BigQuery LAG and LEAD functions. This can help you understand how to improve efficiency by leveraging the BigQuery LAG and LEAD functions. It also gives a brief overview of Google BigQuery before diving into the finer details of these two functions.

What is Google BigQuery?

Google BigQuery is a fully managed and serverless enterprise cloud data warehouse. It uses Dremel technology, which transforms SQL queries into tree structures. BigQuery provides an outstanding query performance owing to its column-based storage system.

Key Features:

1. Serverless Architecture: BigQuery manages servers and storage in the background, so a user does not need to.
2. High Scalability: It scales seamlessly to handle petabytes of data.
3. SQL Compatibility: It supports ANSI SQL, which is useful for people who already know SQL and want to write and run queries. This also allows a user to combine various BI tools for data visualization.
4. Machine Learning: BigQuery ML allows users to train and run machine learning models in BigQuery using only SQL syntax.

What is a Navigation Function?

BigQuery offers many features that make data analysis and computations easier for the end-users. These features are primarily enabled by the built-in functions that can be called upon per the requirements. Navigation functions are the primary ones among the lot; they are considered a subset of the Analytical function in BigQuery. As arranging and computing data in rows is one of the pre-requisites for performing effective data analysis, it is of utmost importance to compute the relevant values for individual or group of rows; an Analytic function can calculate the requisite values over a set of rows and return a single output value against each row. 

Please note that the Analytic function differs from an aggregate function, which computes a single value for any row set.

The navigation function calculates the value_expression for a different row in the display window frame from the reference or current row. OVER clause forms the basis of the usage of the Navigation function, and the relevant pre-conditions are given as follows:

• PARTITION BY: Optional
• ORDER BY:
  • The clause isn’t allowed for PERCENTILE_CONT and PERCENTILE_DISC functions.
  • The clause is an essential requirement for FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD, and LAG functions.
• Window_Frame_Clause:
  • The clause isn’t allowed for PERCENTILE_CONT, PERCENTILE_DISC, LEAD, and LAG functions.
  • The clause is an essential requirement for FIRST_VALUE, LAST_VALUE, and NTH_VALUE functions.

Please note that for every and any navigation function, the output or resultant value would always be of the same type, i.e., value_expression. 

BigQuery LAG() Function

Here are three essential components of the BigQuery LAG Function:

• BigQuery LAG Function Description
• BigQuery LAG Function Syntax
• BigQuery LAG Function Example

BigQuery LAG Function Description

This navigation function yields the output of the value_expression on the preceding row of the dataset.

BigQuery LAG Function Syntax

LAG (value_expression[, offset [, default_expression]])

• User Input Dependency: The output of the offset function depends on the user-defined value, with a default offset value of 1, which refers to the next row in the result set.
• Error Handling: If the user inputs a negative or null value for the offset, an error will occur, indicating that such values are not acceptable.
• Value Expression Compatibility: The value_expression can include any data type that is computable or displayable from a given expression, and there must be seamless compatibility between the default_expression and the type of value_expression used.
• Data Type Considerations: Ensuring that the types of expressions match is crucial for the successful execution of queries involving offset functions, enhancing the reliability of the results.

Supported Argument Types

• value_expression can be any data type that can be returned from an expression.
• Offset must be a non-negative integer literal or parameter.
• default_expression must be compatible with the value expression type.

BigQuery LAG Function Example

The following example illustrates the basic usage of the BigQuery LAG function:

WITH finishers AS (SELECT 'Sophia Liu' as name,  TIMESTAMP '2016-10-18 2:51:45' as finish_time,  'F30-34' as division  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,  finish_time,  division,  LAG(name)    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner FROM finishers;

BigQuery LEAD() Function

Here are three essential components of the BigQuery LEAD Function:

• BigQuery LEAD Function Description
• BigQuery LEAD Function Syntax
• BigQuery LEAD Function Example

BigQuery LEAD Function Description

This navigation function yields the output of the value_expression on the following/subsequent row. 

BigQuery LEAD Function Syntax

LEAD (value_expression[, offset [, default_expression]])

The output primarily depends on the value input by the user for the offset function; the default scenario for the offset value is 1, which certainly designates the next row in the window display frame. By the same logic, an error will appear if the input value for the offset function is negative or null.  

Value_expression might necessarily entail any data type that can be computed or displayed from a given expression. More so, there must exist seamless compatibility amongst the default_expression and the type of value expression.

BigQuery LEAD Function Example

The basic usage of the LEAD function can be illustrated by the following example:

WITH finishers AS
(SELECT 'Sophia Liu' as name,   TIMESTAMP '2016-10-18 2:51:45' as finish_time,'F30-34' as division UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,  finish_time,  division,  LEAD(name)    OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by FROM finishers;

Conclusion

• Growing Reliance on Data: Enterprises increasingly depend on data and analytics to derive meaningful insights, making tools like BigQuery essential for effective data management.
• Enhanced Flexibility: Navigation functions in BigQuery enable users to work with data more flexibly, enhancing visualization and allowing for cross-linkages among multiple rows, which aids in comparing different data sets.
• LEAD and LAG Functions: These built-in functions allow users to easily access preceding and following rows within their datasets, facilitating the analysis of trends and changes over time.
• Support for Decision-Making: By complementing the information extraction process, navigation functions help users derive critical insights that contribute to effective managerial decisions .

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like Google BigQuery into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Give Hevo a try by signing up for a 14-day free trial and see how Hevo will suit your organization’s needs. Check out the pricing details to find the right plan for you.

Frequently Asked Questions