Oracle 23ai adds a meaningful set of new aggregate functions to SQL. These fill gaps in analytical and statistical capabilities that previously required PL/SQL, external tools, or verbose workarounds.
CHECKSUM
Returns a deterministic checksum value for a set of rows. Extremely useful for data validation and change detection.
SELECT CHECKSUM(*) AS row_fingerprint
FROM employees
WHERE department_id = 10;If any row in the result set changes, the checksum changes — making it ideal for comparing snapshots.
BIT_AND_AGG / BIT_OR_AGG / BIT_XOR_AGG
Bitwise aggregate functions — apply bitwise AND, OR, or XOR across all values in a group.
-- Which permissions are held by ALL members of a group?
SELECT role_id, BIT_AND_AGG(permission_flags) AS common_permissions
FROM role_members
GROUP BY role_id;
-- Which permissions are held by AT LEAST ONE member?
SELECT role_id, BIT_OR_AGG(permission_flags) AS any_permission
FROM role_members
GROUP BY role_id;SKEWNESS_POP / SKEWNESS_SAMP
Measures statistical skewness of a distribution — how asymmetric the data is around the mean.
SELECT SKEWNESS_POP(salary) AS salary_skew
FROM employees;A positive value means the distribution tails right (a few very high salaries); negative means it tails left.
KURTOSIS_POP / KURTOSIS_SAMP
Measures the “tailedness” of a distribution — whether outliers are common or rare.
SELECT KURTOSIS_SAMP(response_time_ms) AS response_time_kurtosis
FROM api_logs;Why these matter
Before 23ai, these calculations required either loading data into R/Python, implementing them manually as complex SQL expressions, or using the DBMS_STAT_FUNCS package. Now they’re first-class aggregate functions available anywhere SQL is valid — in reports, views, materialized views, and analytics queries.
