Calculated field
LOOKUP(SUM([Revenue]), 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((lead("Revenue",1) OVER (PARTITION BY "Region" ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Runs the example above.
SELECT CAST((lead("Revenue",1) OVER (PARTITION BY "Region" ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Written for this dialect; not run here.
SELECT CAST((lead(`Revenue`,1) OVER (PARTITION BY `Region` ORDER BY `Period`)) 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 | 180 |
| Harbor | 2 | 180 | 100 | 180 |
| Harbor | 3 | 180 | 120 | 240 |
| Harbor | 4 | 240 | 140 | null |
| Upland | 1 | 90 | 50 | 150 |
| Upland | 2 | 150 | 110 | null |
| Upland | 3 | null | 90 | 330 |
| 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((lead("Revenue",1) OVER ( ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 90 Harbor 2 180 100 150 Harbor 3 180 120 null Harbor 4 240 140 330 Upland 1 90 50 180 Upland 2 150 110 180 Upland 3 null 90 240 Upland 4 330 190 null Matches the calculation
SELECT CAST((lead("Revenue",1) OVER (PARTITION BY "Region" ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Region Period Revenue Cost result Harbor 1 120 70 180 Harbor 2 180 100 180 Harbor 3 180 120 240 Harbor 4 240 140 null Upland 1 90 50 150 Upland 2 150 110 null Upland 3 null 90 330 Upland 4 330 190 null
Signature
LOOKUP(SUM([Revenue]), 1)