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

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Common Table Expressions and recursion

Common Table Expressions and recursion - SQL Tutorial

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

Start my 1-month free trial

Common Table Expressions and recursion

“

- [Lecturer] When we're working with time series data, we often need to reference the table in multiple times. Now this can lead to a series of subqueries and they can get complicated and hard to read. One of the options in newer versions of SQL is called Common Table Expressions. And that's basically a way of creating a temporary table that's used just with a single select statement. Let's take a look at how that's done. We start with a with statement and we specify the name of our temporary table. Now let's say I'd like to get a daily average temperature. So let's create daily average temp. And we'll create that as, and we'll specify a select statement that will define this temporary table. And I'm going to select, let's see, well, of course I want to work with the event time, since we're working with dates. Now I don't actually want to use the event time, I'm more interested in the date. So I'm going to extract that by using the date trunc function. And I'm going to tell PostgreS…

Contents