Native analytical and statistical functions in Fabric Warehouse

Use cases

Imagine you need to calculate medians, standard deviations, or approximate percentiles directly on your lakehouse tables without complex subqueries. Built-in analytical functions bring native statistical capabilities to the SQL engine.

• Calculate descriptive statistics such as average, median, variance, and standard deviation.
• Perform approximate counts and percentiles using memory-efficient algorithms.
• Concatenate text values from multiple rows into a single string.

How to use

Run aggregate functions directly within your SQL queries in your preferred editor.

SELECT
    department,
    AVG(salary) AS avg_salary,
    APPROX_MEDIAN(salary) AS median_salary,
    STDEV(salary) AS salary_stdev
FROM
    my_warehouse.dbo.employees
GROUP BY
    department;

What happens in this code:

1. The query groups employee records by department.
2. The AVG function calculates the arithmetic mean of salaries.
3. The APPROX_MEDIAN function efficiently estimates the salary median.
4. The STDEV function computes the sample standard deviation of salaries per group.

Limitations and requirements

• Available in Public Preview.
• Supported in Microsoft Fabric Warehouse and the SQL analytics endpoint.
• Except for COUNT(*), aggregate functions ignore NULL values.
• Functions such as APPROX_MEDIAN and APPROX_QUANTILE are nondeterministic.