Calculated field
PI()
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
SELECT pi() AS result FROM input ORDER BY id
Runs the example above.
SELECT pi() AS result FROM input ORDER BY id
Written for this dialect; not run here.
SELECT acos(-1) AS result FROM input ORDER BY id
Written for this dialect; not run here.
Result
| id | amount | units | score | result |
|---|---|---|---|---|
| 1 | -2.5 | 2 | -0.8 | 3.14159 |
| 2 | 0 | 0 | 0 | 3.14159 |
| 3 | 1.5 | 3 | 0.25 | 3.14159 |
| 4 | 4 | 4 | 0.5 | 3.14159 |
| 5 | 9 | 5 | 0.8 | 3.14159 |
| 6 | 16 | 6 | 1 | 3.14159 |
| 7 | null | 7 | null | 3.14159 |
| 8 | 4 | 8 | -0.5 | 3.14159 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- A rounded decimal literal loses precision.
Naive SQL
SELECT 3.14 AS result FROM input ORDER BY id
id amount units score result 1 -2.5 2 -0.8 3.14 2 0 0 0 3.14 3 1.5 3 0.25 3.14 4 4 4 0.5 3.14 5 9 5 0.8 3.14 6 16 6 1 3.14 7 null 7 null 3.14 8 4 8 -0.5 3.14 Matches the calculation
SELECT pi() AS result FROM input ORDER BY id
id amount units score result 1 -2.5 2 -0.8 3.14159 2 0 0 0 3.14159 3 1.5 3 0.25 3.14159 4 4 4 0.5 3.14159 5 9 5 0.8 3.14159 6 16 6 1 3.14159 7 null 7 null 3.14159 8 4 8 -0.5 3.14159
Signature
PI()