Working with BigQuery Analytic Functions
Tags: bigquery, bigdata, database, sql
Published: 2019-10-21 08:05:10

---
title: Working with BigQuery Analytic Functions
published: true
description: How to use next level SQL to perform more complex computations
tags: BigQuery, Big Data, bigdata, databases, SQL
cover_image: https://thepracticaldev.s3.amazonaws.com/i/wad017phjcgz62wwngtr.png
---

<strong>1. Introduction</strong></br><p>Most of the day-to-day database users can make do with the standard database functionality. Want to get all the friends of a certain user? Just join the users table with the friends table and there you have it (btw — it’s never that simple). If you’re a more advanced user, you might use aggregations and ordering.</p><p>However, sometimes you can’t get the insights you want with just joins and aggregations. When I faced such a problem, my first instinct was to load all the data into a simple python/javascript/ruby/(favorite dynamic programming language) and calculate it there. But it meant loading a big amount of data into the script, while losing the parallel computation power BigQuery has to offer. Also, it meant more moving parts in the system and more deployment details.</p><p>That’s when a simple google search (I think it was “BigQuery calculate row based on other rows”) led me to BigQuery Analytic Functions (the concept is not specific to BigQuery).</p><p>In this post, I will describe what these functions are, what was the problem I was trying to solve, how I solved it with Analytic functions and my conclusions from the process.</p><p>Ok then…</p><p><strong>2. The problem at hand (simplified)</strong></p><p>The dataset I was working with is local rain data. It basically has three columns: date, location and amount. Each row represents the amount of rain (in mm) that poured on each date in a certain location.</p><p>What I wanted to extract was an aggregation by “rain events” in a location. A “rain event” is defined as one or more consecutive days of rain. Once there’s a rain-free day, the event is over. That means I would like to aggregate together consecutive rainy days and get the average and total rain for each “rain event”.</p><p>Let’s take a look at a quick example. The dataset:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YYpAvRiujTcco5N5q0G91g.png" /><figcaption>Original rain days table</figcaption></figure><p>We can see three different rain events (consecutive days of rain):</p><ul><li>2019–01–01 to 2019–01–03 in New York</li><li>2019–03–31 to 2019–04–01 in New York</li><li>2019–05–25 to 2019–05–27 in LA</li></ul><p>And the resulting table I wished to get from the input dataset should look something like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kaYEpqPyJcb9AjPlp4w5Bg.png" /><figcaption>Required results table</figcaption></figure><p>How can this be calculated with an SQL query? Grouping by month or week won’t work. As far as I saw, the best choice was to use Analytic Functions.</p><p><strong>3. Analytic functions: description and syntax</strong></p><p>Analytic functions perform operations on groups of rows, same as aggregations (GROUP BY and the likes). There are two major differences:</p><p>a. Analytic functions return a single value for each of the input rows.</p><p>b. The group of rows for the operation is defined using a window.</p><p>Analytic functions are added as part of the SELECT clause, and with them, one has to specify three parameters:</p><p>a. The partition (breaks input table into different groups).</p><p>b. The order (orders the rows in each partition); Order influences the operation’s result, as we’ll see later.</p><p>c. The window — each row is operated on with a group of other rows, which are defined by the window parameter.</p><p>Let’s have a look at a simple example and explain the syntax:</p><blockquote>SELECT<br/>SUM(amount) OVER (PARTITION BY location ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)<br/>FROM rain_data</blockquote><p>The partition and order parts are pretty clear, just mention the column names like ordinary GROUP BY and ORDER BY inputs.</p><p>The window part needs some explaining: it states that for each row, the operation should look at a window which includes the row itself, one row before it (<em>1 PRECEDING</em>), and one row after it (<em>1 FOLLOWING</em>). So the operation calculates the sum of three rows, but it does that for every row in the input table and gives the fitting result for each row.</p><p>The flow (leaving out the order part) is depicted in the following chart:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*CNVBLvu4Jn-jsh46" /></figure><p>More information on the syntax can be found in the BigQuery documentation (it’s not that bad): <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts">https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts</a></p><p>The example query did not solve my problem. What did solve my problem? On to the next section.</p><p><strong>4. Solving the problem</strong></p><p>So how can Analytic functions help? In my case, I wanted to partition by location, order by date, and use a window of two rows.</p><p>Why two rows? For each row we would calculate the date difference between the current row and the previous one. If the difference is larger than a single day, then it belongs to a different rain event.</p><p>Lucky for me, BigQuery has a built-in Analytic function for working with the previous row: the LAG function (for some reason it is called a navigation function). The LAG function references the previous row, according to a provided Partition and Order (a window is not necessary!).</p><p>So Let’s see how the initial query looks like:</p><blockquote><em>SELECT * FROM (<br/>SELECT *,<br/>DATE_DIFF(date, LAG(date) OVER(PARTITION BY location ORDER BY date), day) AS days_from_last_rain<br/>FROM `rain_example`<br/>) AS t<br/>WHERE<br/>days_from_last_rain &gt; 1<br/>OR days_from_last_rain IS NULL</em></blockquote><p>The result of this query (on the example table above):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/746/0*4Uhh6rHss9D6_sXQ" /></figure><p>This is the set of rainy days that were the first in a rain event. We got it by selecting the diff between the date of the previous row and the date of the current row. We then filtered all those with a diff of 1 day, as those have preceding rainy days.</p><p>Now we can connect each rainy day from the original dataset to the correct rain event, and aggregate each group to get the average and sum</p><p><strong>5. Conclusions</strong></p><p>There’s a bunch of ‘hidden’ functionality when you dive into the BigQuery documentation (or any other database for that matter). So next time before you load data into a script and start hacking, give the documentation a look (or just google it).</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ad1d9f0cba" width="1" height="1">