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