Advanced SQL for Data Science: Time Series
1h 19mAdvanced2019-04-26
Authors
Dan Sullivan
Enterprise Architect, Big Data Expert
Course details
Time series data is data gathered over time: performance metrics, user interactions, and information collected by sensors. Since different time series data have different measures and different intervals, these data present a unique challenge for data scientists. However, SQL has some features designed to help. This course teaches you how to standardize and model time series data with them. Instructor Dan Sullivan discusses windowing and the difference between sliding and tumbling window calculations. Then learn how SQL constructs such as OVER and PARTITION BY help to simplify analysis, and how denormalization can be used to augment data while avoiding joins. Plus, discover optimization techniques such as indexing. Dan also introduces time series analysis techniques such as previous time period comparisons, moving averages, exponential smoothing, and linear regression.
Learning objectives
Basics of time series data
Writing time series data
Querying time series data
Installing PostgreSQL
Evaluating query performance
Joining time series
Denormalizing time series
Indexing data
Querying a partitioned table
Functions for time series
Calculating aggregates over windows
Calculating moving averages
Forecasting with linear regression
Learning objectives
Basics of time series data
Writing time series data
Querying time series data
Installing PostgreSQL
Evaluating query performance
Joining time series
Denormalizing time series
Indexing data
Querying a partitioned table
Functions for time series
Calculating aggregates over windows
Calculating moving averages
Forecasting with linear regression
Skills covered
Data Science FoundationsSQLDatabase AdministrationAdvancedDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development
Concepts
0. Introduction
- 01 - Learn time series data analysis with SQL
- 02 - What you should know
1. Introduction to Time Series Data
- 03 - Characteristics of time series data
- 04 - Examples of time series data
- 05 - Writing time series data
- 06 - Querying time series data
2. Installing Database and Tools
- 07 - Installing PostgreSQL
- 08 - Creating schema and tables
- 09 - Timing a query
- 10 - Evaluating query performance with EXPLAIN
3. Querying Time Series Data
- 11 - Time window queries and aggregates
- 12 - Sliding windows
- 13 - Tumbling windows
- 14 - Joining two time series
- 15 - Denormalizing time series data
4. Modeling Time Series Data
- 16 - Example data set 1 - Temperature by time and location
- 17 - Indexing data set 1 - Time index only
- 18 - Indexing data set 1 - Time and location index
- 19 - Creating a partitioned table
- 20 - Querying a partitioned table
- 21 - Example data set 2 - CPU utilization and application type
- 22 - Indexing data set 2 - Time and type Indexing
5. Commonly Used Functions for Time Series
- 23 - Lead
- 24 - Lag
- 25 - Rank
- 26 - Percent rank
6. Time Series Analysis
- 27 - Common Table Expressions and recursion
- 28 - Calculating aggregates over windows
- 29 - Previous day comparison
- 30 - Moving averages
- 31 - Weighted moving averages
- 32 - Forecasting with linear regression
- 33 - Exponential moving average
Conclusion
- 34 - Next steps