Calculated field
(SUM([Revenue]) - WINDOW_MIN(SUM([Revenue]))) / (WINDOW_MAX(SUM([Revenue])) - WINDOW_MIN(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((("Revenue" - min("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) / nullif(max("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)-min("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((("Revenue" - min("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) / nullif(max("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)-min("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(((`Revenue` - min(`Revenue`) OVER (PARTITION BY `Region` ORDER BY `Period` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) / nullif(max(`Revenue`) OVER (PARTITION BY `Region` ORDER BY `Period` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)-min(`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 |
| Harbor | 2 | 180 | 100 | 0.5 |
| Harbor | 3 | 180 | 120 | 0.5 |
| Harbor | 4 | 240 | 140 | 1 |
| Upland | 1 | 90 | 50 | 0 |
| Upland | 2 | 150 | 110 | 0.25 |
| Upland | 3 | null | 90 | null |
| 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((("Revenue" - min("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) / nullif(max("Revenue") OVER ( ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)-min("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.125 Harbor 2 180 100 0.375 Harbor 3 180 120 0.375 Harbor 4 240 140 0.625 Upland 1 90 50 0 Upland 2 150 110 0.25 Upland 3 null 90 null Upland 4 330 190 1 Matches the calculation
SELECT CAST((("Revenue" - min("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) / nullif(max("Revenue") OVER (PARTITION BY "Region" ORDER BY "Period" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)-min("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 Harbor 2 180 100 0.5 Harbor 3 180 120 0.5 Harbor 4 240 140 1 Upland 1 90 50 0 Upland 2 150 110 0.25 Upland 3 null 90 null Upland 4 330 190 1
Signature
(SUM([Revenue]) - WINDOW_MIN(SUM([Revenue]))) / (WINDOW_MAX(SUM([Revenue])) - WINDOW_MIN(SUM([Revenue])))