From the course: Advanced SQL for Data Science: Time Series

Time window queries and aggregates - SQL Tutorial

From the course: Advanced SQL for Data Science: Time Series

Start my 1-month free trial

Time window queries and aggregates

“

- [Instructor] We're going to make a lot of use of time window queries and aggregates when working with time series data. A time window, or simply a window, is a set of contiguous rows. Now the size of a window is simply the number of rows that we consider within that window. Here's an example of a time series data table. This table is called utilization, and it's time stamp is called event time. And then we have some other IDs like server ID, so that tells us what, in this case what server we are measuring, and then we have three metrics that we measure. CPU utilization, free memory, and session count. Now here's an example query on that utilization table. We're simply selecting the event time, the server, and the CPU utilization, free memory, and session count at a particular period in time. In this case, we're looking at a span from one minute after midnight to 30 minutes after midnight. So roughly a half hour period. And we're simply listing out all of the data. Now when we're looking at the broad data like that, sometimes we might want to order it in a particular order. So for example, we've added an order by clause, and this orders the data by event time first, and then within a particular period of time, by server ID. Now oftentimes, we don't want to look at just the raw data or the very low level data. We want to look at aggregates, so in this example we apply the average function to CPU utilization and session count. Now average is a pretty basic aggregate function. Some others that are commonly used are min and max, and also standard deviation. That helps us understand the distribution of data.

Contents