Calculated field
HEXBINY([amount], [other])
Sample data
| id | amount | units | score | other |
|---|---|---|---|---|
| 1 | -2.5 | 2 | -0.8 | 3 |
| 2 | 0 | 0 | 0 | 4 |
| 3 | 1.5 | 3 | 0.25 | 2 |
| 4 | 4 | 4 | 0.5 | 8 |
| 5 | 9 | 5 | 0.8 | 11 |
| 6 | 16 | 6 | 1 | 14 |
| 7 | null | 7 | null | 9 |
| 8 | 4 | 8 | -0.5 | null |
The same calculation as SQL
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN other IS NULL THEN NULL WHEN pow(ux-x,2)+3*pow(uy-y,2)<=pow(sx-x,2)+3*pow(sy-y,2) THEN uy*sqrt(3) ELSE sy*sqrt(3) END AS result FROM centers ORDER BY id
Runs the example above.
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN other IS NULL THEN NULL WHEN power(ux-x,2)+3*power(uy-y,2)<=power(sx-x,2)+3*power(sy-y,2) THEN uy*sqrt(3) ELSE sy*sqrt(3) END AS result FROM centers ORDER BY id
Written for this dialect; not run here.
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN other IS NULL THEN NULL WHEN power(ux-x,2)+3*power(uy-y,2)<=power(sx-x,2)+3*power(sy-y,2) THEN uy*sqrt(3) ELSE sy*sqrt(3) END AS result FROM centers ORDER BY id
Written for this dialect; not run here.
Result
| id | amount | units | score | result |
|---|---|---|---|---|
| 1 | -2.5 | 2 | -0.8 | 3.4641 |
| 2 | 0 | 0 | 0 | 3.4641 |
| 3 | 1.5 | 3 | 0.25 | 2.59808 |
| 4 | 4 | 4 | 0.5 | 7.79423 |
| 5 | 9 | 5 | 0.8 | 10.3923 |
| 6 | 16 | 6 | 1 | 14.7224 |
| 7 | null | 7 | null | 8.66025 |
| 8 | 4 | 8 | -0.5 | null |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Rounding an axis independently does not select the nearest hexagonal center.
Naive SQL
SELECT round(other) AS result FROM input ORDER BY id
id amount units score result 1 -2.5 2 -0.8 3 2 0 0 0 4 3 1.5 3 0.25 2 4 4 4 0.5 8 5 9 5 0.8 11 6 16 6 1 14 7 null 7 null 9 8 4 8 -0.5 null Matches the calculation
WITH coords AS (SELECT *,coalesce(amount,0) AS x,coalesce(other,0)/sqrt(3) AS y FROM input), centers AS (SELECT *,3*floor(x/3+0.5) AS ux,floor(y+0.5) AS uy,3*floor((x-1.5)/3+0.5)+1.5 AS sx,floor(y) +0.5 AS sy FROM coords) SELECT CASE WHEN other IS NULL THEN NULL WHEN pow(ux-x,2)+3*pow(uy-y,2)<=pow(sx-x,2)+3*pow(sy-y,2) THEN uy*sqrt(3) ELSE sy*sqrt(3) END AS result FROM centers ORDER BY id
id amount units score result 1 -2.5 2 -0.8 3.4641 2 0 0 0 3.4641 3 1.5 3 0.25 2.59808 4 4 4 0.5 7.79423 5 9 5 0.8 10.3923 6 16 6 1 14.7224 7 null 7 null 8.66025 8 4 8 -0.5 null
Signature
HEXBINY(number, number)