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.
Previous day comparison - SQL Tutorial
From the course: Advanced SQL for Data Science: Time Series
Previous day comparison
- [Instructor] Now let's take a look at another common query pattern we see when working with time_series, and that's comparing to a previous period. So in this example, I want to work with the daily average temperature and I want to be able compare a couple of days' temperatures. So I'm going to use a common table expression, or essentially a temporary table and I'll call it the daily average temp and we'll simply define that as we did earlier, Select. I'm going to work with just the day so we'll truncate to the day, the event time, and we'll calculate the average. Temp and Celsius. And we'll call it A-V-G temp. Oh, and actually I forgot to alias this with event date, so we'll give that an alias. I'll make a little room here. And we'll continue with the From clause and of course we'll pull this from time_series, location_temp and we're going to Group By. We're going to Group By the date so we'll do our date trunc operation again, and truncate the event_time just to the day. So this…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
-
-
(Locked)
Common Table Expressions and recursion2m 46s
-
(Locked)
Calculating aggregates over windows1m 34s
-
(Locked)
Previous day comparison3m 50s
-
(Locked)
Moving averages1m 59s
-
(Locked)
Weighted moving averages5m 18s
-
(Locked)
Forecasting with linear regression3m 39s
-
(Locked)
Exponential moving average1m 29s
-
(Locked)
-