BigQuery LAG and LEAD Functions: 2 Critical Aspects

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, that’s when you have to 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 when it comes to data analysis. At this juncture incorporating a data warehouse like BigQuery becomes a necessity. 

This blog talks in detail about BigQuery LAG and LEAD functions. This can help you understand how you can bring about an improvement in 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.

Table of Contents

• What is Google BigQuery?
  • Key Features of BigQuery
• What is a Navigation Function?
• BigQuery LEAD Function
  • BigQuery LEAD Function Description
  • BigQuery LEAD Function Syntax
  • BigQuery LEAD Function Example
• BigQuery LAG Function
  • BigQuery LAG Function Description
  • BigQuery LAG Function Syntax
  • BigQuery LAG Function Example
• Conclusion

What is Google BigQuery?

More and more organizations are looking to unlock business insights from the data. But it can be difficult to ingest, store, and analyze that data as it rapidly grows in scale and scope. Google’s enterprise data warehouse, BigQuery, has been designed to make large-scale data analysis accessible to everyone.

When the size of recorded data grows to gigabytes, terabytes, or even petabytes, an enterprise surely needs a more efficient system like a data warehouse; all that data isn’t very useful unless one has a way to analyze it. BigQuery has the capability to handle massive amounts of data e.g., logs from outlets of retail chains down to the SQL level or IoT data from millions of utility meters, telecom usage, and vehicles across the globe. Powered by Google, BigQuery is certainly a Platform as a Service (PaaS) offering, with a fully managed data house in a serverless architecture; it enables the organizations to focus on analytics instead of managing the infrastructure.

Key Features of BigQuery

• Being a serverless architecture, BigQuery operates on the cloud platform, thus facilitating the scalability of analytics automatically. 
• BigQuery allows the users to capture the best of the decision-making insights by forming and implementing machine learning algorithms using SQL; it offers real-time analytics based upon high-speed streaming insertion API. The user just needs to incorporate the real-time data and BigQuery can analyze it instantaneously. 
• By design, BigQuery helps one avoid the data silo problem owing to the existence of individual teams in an organization, having their independent data marts as it offers cross-team communication concerning any of the databases.  
• Owing to the integration of the subject tool with Google Cloud’s native identity and access management frameworks, the user can take control of the permissions and relevant access criteria for specific individuals, teams, or ventures thus enabling the safety and security of classified data to keep the classified all while still empowering the cross-team communications. 
• Working with data in BigQuery involves three primary parts – storage, ingestion, and querying. Being a fully managed provision, one doesn’t need to set up or install anything and even doesn’t need the database administrator. One can simply log in to the google cloud project from a browser and get started. 
• Data in BigQuery is stored in a structured table, which means one can use standard SQL for easy querying and data analysis. It is perfect for big data because BigQuery manages all the storage and scaling operations automatically for the client. 
• Of course, storing the data alone doesn’t matter if one can’t get into BigQuery in the first place; there are a lot many ways to do that as BigQuery is integrated with many of the data analytics platforms. Once the data is in the BigQuery, one can use SQL, having worked with ANSI-compliant relational databases in the past. 
• BigQuery also supports the data transfer service via which users can get data from multiple sources on a scheduled basis e.g., Google Marketing Platform, Google Ads, YouTube, Partner SaaS applications to BigQuery, Teradata, and Amazon S3. 
• Additionally, the user is able to share access with other users, so that they can also derive insights from the relevant datasets. BigQuery provides users the flexibility to bypass the ingestion and storage steps, by analyzing BigQuery Public Datasets; these are third-party datasets that have been made public for anyone to query against. 
• Considering the essential nature of data for any organization, BigQuery offers automatic backup and restore options. It also keeps track of the performed changes on a 7-days basis so that comparison with previous versions can be done if necessary and recall any changes accordingly.

What is a Navigation Function?

BigQuery offers a host of 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 as per the requirements. Navigation functions are the most primary amongst the lot; navigation functions are considered as a subset of the Analytic 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 is different from an aggregate function which computes a single value for any given set of rows.

More specifically, 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 type of navigation function, the output or resultant value would always be of the same type i.e., value_expression. 

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;

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]])

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 LAG Function Example

The basic usage of the BigQuery LAG 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,  LAG(name)    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner FROM finishers;

Conclusion

As the reliance of enterprises is significantly growing on data and analytics, BigQuery has certainly become the go-to choice so as to extract meaningful insights from the raw data in the best possible manner. The navigation functions form the pivot of these efforts by allowing end-users to work with a lot more flexibility; not only do they enhance the applicability of visualization but also establish a cross-linkage amongst multiple rows that can help in cross tabbing different sets of information.

Amongst the built-in navigation functions in BigQuery, BigQuery LEAD and BigQuery LAG functions certainly broaden the canvas of analytics by allowing the user to display the preceding and following rows into the display frame with minimal effort. One can safely conclude that navigation functions in BigQuery further complement the process of information extraction, thus enabling the users to derive key insights which result in efficient and 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.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.