Calculated field
RUNNING_AVG(SUM([Revenue]))
Compute using: addressing Period; partitioning Region
Sample data
| Region | Period | Revenue | Cost |
|---|---|---|---|
| Harbor | 1 | 120 | 70 |
| Harbor | 2 | 180 | 100 |
| Harbor | 3 | 180 | 120 |
| Harbor | 4 | 240 | 140 |
| Upland | 1 | 90 | 50 |
| Upland | 2 | 150 | 110 |
| Upland | 3 | null | 90 |
| Upland | 4 | 330 | 190 |
The same calculation as SQL
SELECT CAST((avg("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Runs the example above.
SELECT CAST((avg("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Written for this dialect; not run here.
SELECT CAST((avg(`Revenue`) OVER (PARTITION BY `Region` ORDER BY `Period` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS FLOAT64) AS result FROM input ORDER BY `Region`, `Period`
Written for this dialect; not run here.
Result
| Region | Period | Revenue | Cost | result |
|---|---|---|---|---|
| Harbor | 1 | 120 | 70 | 120 |
| Harbor | 2 | 180 | 100 | 150 |
| Harbor | 3 | 180 | 120 | 160 |
| Harbor | 4 | 240 | 140 | 180 |
| Upland | 1 | 90 | 50 | 90 |
| Upland | 2 | 150 | 110 | 120 |
| Upland | 3 | null | 90 | 120 |
| Upland | 4 | 330 | 190 | 190 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Omitting the region partition allows a calculation to consume marks from other regions.
Naive SQL
SELECT CAST((avg("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 120 Harbor 2 180 100 130 Harbor 3 180 120 144 Harbor 4 240 140 160 Upland 1 90 50 105 Upland 2 150 110 135 Upland 3 null 90 144 Upland 4 330 190 184.286 Matches the calculation
SELECT CAST((avg("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 120 Harbor 2 180 100 150 Harbor 3 180 120 160 Harbor 4 240 140 180 Upland 1 90 50 90 Upland 2 150 110 120 Upland 3 null 90 120 Upland 4 330 190 190
Signature
RUNNING_AVG(expression)