SQL - Statistical Functions

SQL: Aggregate and Window Functions

Modern SQL provides powerful functions for performing statistical calculations and data analysis. These are broadly categorized into aggregate functions, which operate on a group of rows to return a single summary value, and window functions, which perform calculations across a set of rows related to the current row.

Note: The functions described here are part of the SQL standard and are available in most modern database systems like PostgreSQL, SQL Server, Oracle, and recent versions of MySQL.

1. Standard Aggregate Functions

Aggregate functions are typically used with the GROUP BY clause to summarize data. Let’s consider a sample Sales table:

CREATE TABLE Sales (
    product_id INT,
    category VARCHAR(50),
    units_sold INT,
    sale_price DECIMAL(10, 2)
);

INSERT INTO Sales VALUES
(101, 'Electronics', 5, 1200.00),
(102, 'Electronics', 2, 800.00),
(201, 'Books', 10, 15.00),
(202, 'Books', 20, 12.50);

Function	Description	Example Query
COUNT()	Counts the number of rows. COUNT(*) counts all rows, while COUNT(column) counts non-NULL values in that column.	SELECT category, COUNT(*) FROM Sales GROUP BY category;
SUM()	Calculates the sum of a numeric column.	SELECT category, SUM(units_sold) FROM Sales GROUP BY category;
AVG()	Calculates the average value of a numeric column.	SELECT category, AVG(sale_price) FROM Sales GROUP BY category;
MIN()	Finds the minimum value in a column.	SELECT MIN(sale_price) FROM Sales;
MAX()	Finds the maximum value in a column.	SELECT MAX(sale_price) FROM Sales;

2. Introduction to Window Functions

Window functions are a revolutionary feature for data analysis in SQL. Unlike aggregate functions that collapse rows, window functions perform a calculation over a specific set of rows (a ‘window’ or ‘partition’) and return a value for each row.

They are defined using the OVER() clause, which specifies how to partition and order the data for the calculation.

Syntax

FUNCTION_NAME() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
)

• PARTITION BY: Divides the rows into groups (partitions). The function is applied independently to each partition.
• ORDER BY: Orders rows within each partition. This is required for ranking and offset functions.

Common Window Functions

Here are a few powerful examples:

Function	Description
ROW_NUMBER()	Assigns a unique sequential integer to each row within a partition.
RANK()	Assigns a rank to each row. Skips ranks after ties (e.g., 1, 2, 2, 4).
DENSE_RANK()	Assigns a rank without gaps. Does not skip ranks after ties (e.g., 1, 2, 2, 3).
LEAD()	Accesses data from a subsequent row in the same result set without a self-join.
LAG()	Accesses data from a previous row in the same result set without a self-join.

Example: Ranking Products by Sales within Each Category

Let’s find the top-selling product (by price) in each category from our Sales table.

SELECT
    product_id,
    category,
    sale_price,
    RANK() OVER (PARTITION BY category ORDER BY sale_price DESC) as price_rank
FROM
    Sales;

Output

The RANK() is calculated separately for ‘Electronics’ and ‘Books’.

product_id	category	sale_price	price_rank
201	Books	15.00	1
202	Books	12.50	2
101	Electronics	1200.00	1
102	Electronics	800.00	2