Calculated field
RANK_PERCENTILE(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((CASE WHEN "Revenue" IS NULL THEN NULL ELSE CASE WHEN count("Revenue") OVER (PARTITION BY "Region")=1 THEN 0 ELSE (rank() OVER (PARTITION BY "Region" ORDER BY "Revenue" ASC NULLS LAST) + count("Revenue") OVER (PARTITION BY "Region", "Revenue") - 2)::DOUBLE / (count("Revenue") OVER (PARTITION BY "Region")-1) END END) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Runs the example above.
SELECT CAST((CASE WHEN "Revenue" IS NULL THEN NULL ELSE CASE WHEN count("Revenue") OVER (PARTITION BY "Region")=1 THEN 0 ELSE (rank() OVER (PARTITION BY "Region" ORDER BY "Revenue" ASC NULLS LAST) + count("Revenue") OVER (PARTITION BY "Region", "Revenue") - 2) / (count("Revenue") OVER (PARTITION BY "Region")-1) END END) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"Written for this dialect; not run here.
SELECT CAST((CASE WHEN `Revenue` IS NULL THEN NULL ELSE CASE WHEN count(`Revenue`) OVER (PARTITION BY `Region`)=1 THEN 0 ELSE (rank() OVER (PARTITION BY `Region` ORDER BY `Revenue` ASC NULLS LAST) + count(`Revenue`) OVER (PARTITION BY `Region`, `Revenue`) - 2) / (count(`Revenue`) OVER (PARTITION BY `Region`)-1) END END) 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.666667 |
| Harbor | 3 | 180 | 120 | 0.666667 |
| Harbor | 4 | 240 | 140 | 1 |
| Upland | 1 | 90 | 50 | 0 |
| Upland | 2 | 150 | 110 | 0.5 |
| 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
- Omitting the region partition allows a calculation to consume marks from other regions.
Naive SQL
SELECT CAST((CASE WHEN "Revenue" IS NULL THEN NULL ELSE CASE WHEN count("Revenue") OVER ()=1 THEN 0 ELSE (rank() OVER ( ORDER BY "Revenue" ASC NULLS LAST) + count("Revenue") OVER (PARTITION BY "Revenue") - 2)::DOUBLE / (count("Revenue") OVER ()-1) END END) 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.666667 Harbor 3 180 120 0.666667 Harbor 4 240 140 0.833333 Upland 1 90 50 0 Upland 2 150 110 0.333333 Upland 3 null 90 null Upland 4 330 190 1 Matches the calculation
SELECT CAST((CASE WHEN "Revenue" IS NULL THEN NULL ELSE CASE WHEN count("Revenue") OVER (PARTITION BY "Region")=1 THEN 0 ELSE (rank() OVER (PARTITION BY "Region" ORDER BY "Revenue" ASC NULLS LAST) + count("Revenue") OVER (PARTITION BY "Region", "Revenue") - 2)::DOUBLE / (count("Revenue") OVER (PARTITION BY "Region")-1) END END) 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.666667 Harbor 3 180 120 0.666667 Harbor 4 240 140 1 Upland 1 90 50 0 Upland 2 150 110 0.5 Upland 3 null 90 null Upland 4 330 190 1
Signature
RANK_PERCENTILE(expression, [order])