Calculated field
RUNNING_SUM(SUM([Revenue])) / WINDOW_SUM(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((sum("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / nullif(sum("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),0)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Runs the example above.
SELECT CAST((sum("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / nullif(sum("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),0)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Written for this dialect; not run here.
SELECT CAST((sum(`Revenue`) OVER (PARTITION BY `Region` ORDER BY `Period` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / nullif(sum(`Revenue`) OVER (PARTITION BY `Region` ORDER BY `Period` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),0)) 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 | 0.166667 |
| Harbor | 2 | 180 | 100 | 0.416667 |
| Harbor | 3 | 180 | 120 | 0.666667 |
| Harbor | 4 | 240 | 140 | 1 |
| Upland | 1 | 90 | 50 | 0.157895 |
| Upland | 2 | 150 | 110 | 0.421053 |
| Upland | 3 | null | 90 | 0.421053 |
| Upland | 4 | 330 | 190 | 1 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- An unpartitioned SQL window mixes independent regional calculation sequences.
Naive SQL
SELECT CAST((sum("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / nullif(sum("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),0)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 0.0930233 Harbor 2 180 100 0.302326 Harbor 3 180 120 0.55814 Harbor 4 240 140 0.744186 Upland 1 90 50 0.162791 Upland 2 150 110 0.418605 Upland 3 null 90 0.55814 Upland 4 330 190 1 Matches the calculation
SELECT CAST((sum("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / nullif(sum("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),0)) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 0.166667 Harbor 2 180 100 0.416667 Harbor 3 180 120 0.666667 Harbor 4 240 140 1 Upland 1 90 50 0.157895 Upland 2 150 110 0.421053 Upland 3 null 90 0.421053 Upland 4 330 190 1
Signature
RUNNING_SUM(SUM([Revenue])) / WINDOW_SUM(SUM([Revenue]))