Скачать или смотреть How to Sample Time Series Data with BigQuery SQL

Learn how to effectively sample time series data using BigQuery SQL to achieve evenly spaced intervals for analysis and visualization.
---
This video is based on the question https://stackoverflow.com/q/67689342/ asked by the user 'Hippocrates' ( https://stackoverflow.com/u/384117/ ) and on the answer https://stackoverflow.com/a/67690380/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Sampling time series data with BigQuery SQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Sampling Time Series Data with BigQuery SQL

Time series analysis is essential for any data-driven decision-making process, especially when it comes to tracking changes over time, understanding trends, and making forecasts. However, you may run into a common problem: how to efficiently sample time series data to obtain consistent intervals, especially when your data isn't recorded at regular intervals. This guide will delve into how to solve this problem using BigQuery SQL, specifically to achieve a sampling rate of four samples per hour at approximately every 15 minutes.

The Challenge

Imagine you have the following time series data in BigQuery:

timestampprice2018-08-20 04:01:0052018-08-20 04:04:0062018-08-20 04:05:0052018-08-20 04:06:0052018-08-20 04:10:0052018-08-20 04:22:0062018-08-20 04:30:0062018-08-20 04:59:007In this instance, the goal is to obtain samples roughly every 15 minutes (0, 15, 30, and 45 minutes) while considering that your actual data might not conform perfectly to this distribution. You may also face challenges such as duplicate measurements, missing time slots, and uneven distribution.

The Solution

Fortunately, selecting the required data points can be done effectively with BigQuery's SQL capabilities. Below, we outline two methods to achieve this:

Method 1: Using Row Number with Sub-query

This method allows you to select the first data point in every 15-minute interval:

[[See Video to Reveal this Text or Code Snippet]]

How It Works:

PARTITION BY: The FLOOR function groups timestamps into 15-minute intervals by converting them into seconds and dividing by 900 (the number of seconds in 15 minutes).

ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition of a result set.

EXCEPT: This clause is used to exclude the seqnum from the final results since it’s not needed for further analysis.

Method 2: Using the QUALIFY Clause

Another, more streamlined way to achieve the same result is by utilizing the QUALIFY clause:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

The QUALIFY clause works similarly to filtering in the WHERE clause, but it's applied after the window functions have been calculated. This allows you to return only the first entry in each 15-minute interval directly.

Conclusion

By using either of the above SQL queries, you can effectively sample your time series data in BigQuery at 15-minute intervals. Utilizing ROW_NUMBER() in conjunction with partitioning ensures that your analysis remains reliable and easy to manage, particularly when confronted with inconsistent data sampling.

Remember, understanding the structure of your data and employing appropriate SQL functions are key to achieving your desired outcomes in data analytics. Happy querying!