Advanced SQL for Data Science: Time Series

Learn how to model time series data and apply advanced analysis techniques using SQL.

Introduction

• Learn time series data analysis with SQL
• What you should know

1. Introduction to Time Series Data

• Characteristics of time series data
• Examples of time series data
• Writing time series data
• Querying time series data

2. Installing Database and Tools

• Installing PostgreSQL
• Creating schema and tables
• Timing a query
• Evaluating query performance with EXPLAIN

3. Querying Time Series Data

• Time window queries and aggregates
• Sliding windows
• Tumbling windows
• Joining two time series
• Denormalizing time series data

4. Modeling Time Series Data

• Example data set 1: Temperature by time and location
• Indexing data set 1: Time index only
• Indexing data set 1: Time and location index
• Creating a partitioned table
• Querying a partitioned table
• Example data set 2: CPU utilization and application type
• Indexing data set 2: Time and type Indexing

5. Commonly Used Functions for Time Series

• Lead
• Lag
• Rank
• Percent rank

6. Time Series Analysis

• Common Table Expressions and recursion
• Calculating aggregates over windows
• Previous day comparison
• Moving averages
• Weighted moving averages
• Forecasting with linear regression
• Exponential moving average

Conclusion

• Next steps