Rolling windows and asof joins
Price a year of daily sales with an asof join, smooth it with a time-based rolling mean, and summarize it by month or quarter.
Uses
phpolars
Inputs
RunningThe data frame holds a year of daily unit sales and a table of list price changes. Three steps turn it into a report: joinAsof() gives each day the price in force on that date, rollingMeanBy() smooths the units over a time window, and groupByDynamic() sums them by calendar period.
use function Phpolars\col;
$priced = $daily
->joinAsof($prices, leftOn: 'date', rightOn: 'from') // the latest price on or before each date
->withColumns(
col('units')->mul(col('list_price'))->alias('revenue'),
col('units')->rollingMeanBy('date', '28d')->round(1)->alias('rolling_units'),
);
$summary = $priced
->groupByDynamic('date', every: '1mo')
->agg(
col('units')->sum()->alias('units'),
col('revenue')->sum()->round(2)->alias('revenue'),
col('list_price')->last()->alias('price_at_end'),
);Daily units and the 28d rolling mean
By month
| date | units | revenue | price_at_end |
|---|---|---|---|
| 2024-01-01 | 5,101 | 22,954.50 | 4.50 |
| 2024-02-01 | 5,154 | 23,193.00 | 4.50 |
| 2024-03-01 | 6,045 | 28,464.30 | 4.95 |
| 2024-04-01 | 6,542 | 32,382.90 | 4.95 |
| 2024-05-01 | 7,222 | 35,748.90 | 4.95 |
| 2024-06-01 | 7,111 | 35,199.45 | 4.95 |
| 2024-07-01 | 7,376 | 38,724.00 | 5.25 |
| 2024-08-01 | 6,881 | 36,125.25 | 5.25 |
| 2024-09-01 | 6,083 | 31,935.75 | 5.25 |
| 2024-10-01 | 5,685 | 28,235.75 | 4.75 |
| 2024-11-01 | 5,028 | 23,883.00 | 4.75 |
| 2024-12-01 | 4,964 | 23,579.00 | 4.75 |
A window such as 28d covers calendar time, so it stays correct when days are missing. rollingMean(28) would count rows instead. groupByDynamic() labels each period by its start date.
Script: site/vignettes/time-series.php in phpstats/phpolars@8487062.