Calculated field
SUM([Revenue]) * PREVIOUS_VALUE(1)
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((CASE WHEN count(*) OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) != count("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) THEN NULL ELSE product("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Runs the example above.
Result
| Region | Period | Revenue | Cost | result |
|---|---|---|---|---|
| Harbor | 1 | 120 | 70 | 120 |
| Harbor | 2 | 180 | 100 | 21600 |
| Harbor | 3 | 180 | 120 | 3888000 |
| Harbor | 4 | 240 | 140 | 933120000 |
| Upland | 1 | 90 | 50 | 90 |
| Upland | 2 | 150 | 110 | 13500 |
| Upland | 3 | null | 90 | null |
| Upland | 4 | 330 | 190 | null |
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((CASE WHEN count(*) OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) != count("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) THEN NULL ELSE product("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 120 Harbor 2 180 100 1944000 Harbor 3 180 120 52488000000 Harbor 4 240 140 null Upland 1 90 50 10800 Upland 2 150 110 291600000 Upland 3 null 90 null Upland 4 330 190 null Matches the calculation
SELECT CAST((CASE WHEN count(*) OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) != count("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) THEN NULL ELSE product("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 120 Harbor 2 180 100 21600 Harbor 3 180 120 3888000 Harbor 4 240 140 933120000 Upland 1 90 50 90 Upland 2 150 110 13500 Upland 3 null 90 null Upland 4 330 190 null
Signature
SUM([Revenue]) * PREVIOUS_VALUE(1)