Why is Saturday always busy?
A made-up cafe writes down its sales every day for two weeks. Saturday is always the best day. Sunday is always the worst. And each week is a bit better than the last.
Three different patterns are mixed in one list of numbers. Time series analysis pulls them apart.
Key words
| Word | What it means | Example |
|---|---|---|
| SQL | A language for asking questions of data held in tables. | select avg(sales) from daily |
| Time series | Numbers recorded in time order, such as sales each day. | Daily cafe sales |
| Trend | The long-term direction: up, down or flat. | Each week is better |
| Seasonality | A pattern that repeats at a fixed length of time. | A busy Saturday every week |
| Noise | Small random ups and downs with no pattern. | A sunny hour brings extra customers |
| Stationary | A series whose average and spread stay about the same over time. | Sales that go up and down around 50 |
| Moving average | The average of the last few values, moved along one step at a time. | The last 7 days |
| Window function | An SQL tool that looks at nearby rows while it keeps every row. | avg over the last 7 rows |
| Lag | The value from an earlier row. | Sales on the same day last week |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can tell trend, seasonality and noise apart. I can say what stationary means. I can use a moving average and a lag in SQL to see daily and weekly patterns.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Three parts of a time series
Every time series can be split into parts.
- Trend. Where the numbers are heading over weeks or months.
- Seasonality. A pattern that repeats on a fixed cycle. A week is a cycle. So is a year.
- Noise. What is left over: small random changes.
Worked example: the cafe, part by part
| Part | What we see | How to find it |
|---|---|---|
| Trend | Week 2 is 8 higher than week 1 on every day | Compare with the same day last week |
| Seasonality | Friday and Saturday are high, Sunday is low | Average each weekday |
| Noise | Small changes no pattern explains | Smooth with a moving average |
The cells students fill in are left blank in this preview.
Try it: a seven day moving average
select day, weekday, sales,
round(avg(sales) over (order by day rows between 6 preceding and current row), 1) as week_average
from daily
order by day;
-- From day 7, the average covers a whole week. The weekday pattern cancels out and the trend shows.Try it: the weekly pattern
select weekday, avg(sales) as average_sales
from daily
group by weekday
order by min(day);
-- Which weekdays are above 60? That is the seasonality.Stationary series
A series is stationary when its average and its spread stay about the same over time. Sales that bounce around 50 for ever are stationary.
The cafe is not stationary, because its average climbs. Many methods work best on a stationary series. One fix is to look at the change from the same day last week. If the trend is steady, those changes are all about the same.