Time Series in SQL: Gaps, Windows, Resampling
Two missing days made SQLite report 156.6 in sales a day for a week that averaged 111.9 once the gaps were filled.
Video #134 ยท SQL and analytics
Steps in the video
- 0:00 Two missing days turned 111.9 a day into 156.6
- 0:33 The idea
- 0:56 Setup
- 1:20 Fourteen days, eleven rows
- 1:48 Finding the gaps
- 2:07 The run
- 2:29 A calendar to fill the holes
- 3:07 Two moving averages
- 3:50 Resampling to weeks
- 4:38 What this does not show
- 5:00 Mistakes to avoid
- 5:26 My take and next steps
- 5:55 Recap
Files
- run.py (20 lines)
- sales.py (17 lines)
- series.py (35 lines)
Tested with
Python 3.12.15
What this does not show
Fourteen made-up days checked only in SQLite, with no times of day or time zones, where a missing day is treated as zero sales by choice.
Sources
Learning IT by Example.