Numeric Functions
Overview
Numeric functions execute mathematical, rounding, and exponential calculations on integer or decimal columns.
Learning Objectives
- Use
ROUND(),CEIL(), andFLOOR()to adjust floating point values. - Calculate absolute differences with
ABS()and remainders usingMOD(). - Perform advanced mathematical exponential calculations with
POWER()andSQRT().
Detailed Concept Explanation
ROUND(numeric, decimals): Rounds a value to the specified number of decimal places.CEIL(numeric)(orCEILING): Rounds upward to the nearest integer.FLOOR(numeric): Rounds downward to the nearest integer.ABS(numeric): Returns the absolute positive value of a number.MOD(dividend, divisor)(or%): Returns the remainder of a division.POWER(base, exponent): Raises a number to the power of another.
Code Examples
SQL
-- Round average item rating and calculate floor/ceil limits
SELECT
price,
ROUND(price, 1) AS rounded_price,
CEIL(price) AS ceiling_price,
FLOOR(price) AS floor_price
FROM products;
-- Identify odd-numbered transactions using modulo
SELECT transaction_id, amount
FROM transactions
WHERE MOD(transaction_id, 2) != 0;
Best Practices
- Numeric Precision: When dividing integers, cast columns explicitly to decimal types (e.g.,
CAST(col AS DECIMAL)) to avoid automatic integer truncation (where1 / 2yields0).
Interview Perspective
Warning
Common Pitfall: Integer Division
In SQL, 5 / 2 often evaluates to 2 instead of 2.5 because the operands are integers. To get the decimal value, multiply by 1.0 or cast: 5 * 1.0 / 2.
Interactive Challenges
Summary
Numeric functions handle mathematical rounding, absolute values, and division operations to control numerical precision.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.