Calculated field
LN([amount])
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 CASE WHEN amount>0 THEN ln(amount) ELSE NULL END AS result FROM input ORDER BY id
Runs the example above.
SELECT CASE WHEN amount>0 THEN ln(amount) ELSE NULL END AS result FROM input ORDER BY id
Written for this dialect; not run here.
SELECT CASE WHEN amount>0 THEN ln(amount) ELSE NULL END 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 | null |
| 2 | 0 | 0 | 0 | null |
| 3 | 1.5 | 3 | 0.25 | 0.405465 |
| 4 | 4 | 4 | 0.5 | 1.38629 |
| 5 | 9 | 5 | 0.8 | 2.19722 |
| 6 | 16 | 6 | 1 | 2.77259 |
| 7 | null | 7 | null | null |
| 8 | 4 | 8 | -0.5 | 1.38629 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Replacing null results with zero changes missing-value semantics.
Naive SQL
SELECT coalesce((CASE WHEN amount>0 THEN ln(amount) ELSE NULL END),0) AS result FROM input ORDER BY id
id amount units score result 1 -2.5 2 -0.8 0 2 0 0 0 0 3 1.5 3 0.25 0.405465 4 4 4 0.5 1.38629 5 9 5 0.8 2.19722 6 16 6 1 2.77259 7 null 7 null 0 8 4 8 -0.5 1.38629 Matches the calculation
SELECT CASE WHEN amount>0 THEN ln(amount) ELSE NULL END AS result FROM input ORDER BY id
id amount units score result 1 -2.5 2 -0.8 null 2 0 0 0 null 3 1.5 3 0.25 0.405465 4 4 4 0.5 1.38629 5 9 5 0.8 2.19722 6 16 6 1 2.77259 7 null 7 null null 8 4 8 -0.5 1.38629
Signature
LN(number)