Aggregate Functions

Introduction

• Aggregate Functions are functions that perform a calculation on a set of values (multiple rows) and return a single value.
• They are often used with the GROUP BY clause to group data, or combined with OVER to become Window Functions to calculate without losing detailed rows.

Postgres natively supports Aggregate Functions which can be divided into the following groups:

Basic Aggregate Functions

• SUM(column): Calculates the sum of all numeric values in the column (ignores NULL).
• AVG(column): Calculates the average value of the column (ignores NULL).
• COUNT: Counts the number of rows
  • COUNT(*) counts all rows, including rows with NULL values
  • COUNT(column) only counts rows with non-NULL values.
• MAX(column): Finds the maximum value in the column, works with numbers, strings and dates.
• MIN(column): Finds the minimum value in the column.

NULL Handling Functions

• COALESCE(val1, val2, ..., valN): Returns the first non-NULL value in the input list. This function is often paired with aggregate functions to avoid returning NULL.
• Example: COALESCE(SUM(salary), 0) means if the department has no employees and the total salary is NULL, it automatically returns 0.

String & Array Functions

• STRING_AGG(column, separator): Concatenates strings from multiple rows into a single string, separated by a specified character.
  • For example, combining product names in the same category into a single string like "iPhone, Laptop, Headphone".
• ARRAY_AGG(column): Aggregates all values across rows into a PostgreSQL Array.

Ranking Functions

• ROW_NUMBER(): Assigns a sequential integer (1, 2, 3...) to rows within each partition. Two rows will never have the same number within the same group.
• RANK(): Ranks rows
  • If 2 rows tie, they receive the same rank. However, the next rank skips numbers.
  • Example: If two people tie for rank 2, the next rank will be 4 (skipping rank 3).
• DENSE_RANK(): Similar to RANK(), but does not skip numbers. If two people tie for rank 2, the next person receives rank 3.
• LEAD(column, offset): Fetches the value of a subsequent row (below) the current row. Very useful for calculating gaps between 2 consecutive rows.
• LAG(column, offset): Opposite of LEAD, fetches the value of a preceding row (above).
• FIRST_VALUE(column) / LAST_VALUE(column): Fetches the first or last value in an ordered set of data.

Advanced Statistical Functions

Measuring Volatility

Used to calculate standard deviation and variance in statistics.

• VARIANCE(column): Calculates population variance
  • Used to measure the mean squared deviation of each value from the mean. The greater the spread of data, the larger the variance.
  • If you simply subtract the mean from each value and sum them up, negative and positive numbers cancel each other out, making the total deviation always equal to 0.
  • To solve this issue, squaring is used to turn all deviations (whether negative or positive) into positive numbers, amplifying values far from the mean (helping detect outliers better).
• STDDEV(column): Calculates standard deviation
  • This is the square root of Variance. It brings the unit back to real-world context (For example, if data has unit A, the standard deviation also has unit A).
  • When squared in the variance step, the measurement unit of the data is transformed, making squared units hard to interpret and impossible to compare directly with the mean. Therefore, taking the square root of variance cancels out the squaring, returning the measurement unit to its original unit
  • A smaller standard deviation indicates more uniform data (closer to the mean). A larger standard deviation indicates greater spread (some row values are too high, others too low).
  • Example of product prices in 2 categories:
    • Category A has product prices of 18, 19, 22
      • AVG(price) ~ 19.6
      • STDDEV(price) ~ 1.7
    • Category B has product prices of 5, 15, 40
      • AVG(price) ~ 19.6
      • STDDEV(price) ~ 14.8
    • Based on the results, Category B exhibits strong price dispersion even though its average price is equal to Category A

Additionally, there are other Functions such as VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP

Determining Percentile Position

Used to calculate percentiles and cumulative distribution of a value within a group, provided that the column used must be sorted (ASC or DESC).

• PERCENT_RANK(): Relative percentage rank
  • Formula: (Rank_of_row - 1) / (Total_rows - 1)
  • Feature: The first row is always 0 (0%) and the last row is always 1 (100%).
  • This function answers the question: "What percentage of the entire table does the distance from the current row to the last row represent?"
  • Example:
    • If the returned value is 50%, it means this row value lies in the middle of the list
    • If the returned value is 75%, it means this row value ranks higher than 75% of the group range
• CUME_DIST(): Cumulative Distribution
  • Formula: Number_of_rows_with_value <= current_row / Total_rows
  • This function answers the question: "What percentage of rows in the group have a value less than or equal to the current row?"
  • Commonly used to solve calculations like salaries or revenues falling into top X%
  • Example:
    • If there is only 1 lowest row value, its value is 1/(total rows)
    • If there is only 1 highest row value, its value is (total rows)/(total rows) = 100%, since this row's value is the largest, all other row values are <= this row

Detail

Create the following tables and Indexes:

CREATE TABLE categories (
    category_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT REFERENCES categories(category_id),
    price NUMERIC(12, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0
);

CREATE TABLE order_items (
    item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL,
    price_per_unit NUMERIC(12, 2) NOT NULL, 
    cost_per_unit NUMERIC(12, 2), 
    discount_amount NUMERIC(12, 2),        
    created_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_products_category_price ON products(category_id, price);

CREATE INDEX idx_order_items_net_discount ON order_items ((COALESCE(discount_amount, 0)));

CREATE INDEX idx_order_items_analytic ON order_items(product_id, created_at DESC);

• Table order_items
  • price_per_unit: Purchase price, may differ from original product price
  • cost_per_unit: Cost price used for profit calculation
  • discount_amount: Can be NULL if no discount code is applied
• idx_order_items_net_discount is an Expression Index handling NULL values of discount_amount
• idx_order_items_analytic is a Composite Index supporting Time-Series analysis, optimizing performance without needing to re-sort data in RAM

Postgres also supports creating custom AGGREGATE functions as needed:

CREATE OR REPLACE FUNCTION trans_margin_pct(
    state NUMERIC[],    
    price NUMERIC,     
    cost NUMERIC       
)
RETURNS NUMERIC[] AS $$
BEGIN
    IF price IS NOT NULL AND cost IS NOT NULL THEN
        state[1] := state[1] + price;                  
        state[2] := state[2] + (price - cost);         
    END IF;
    RETURN state;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION final_margin_pct(state NUMERIC[]) 
RETURNS NUMERIC AS $$
BEGIN
    IF state[1] = 0 THEN
        RETURN 0; 
    END IF;
    RETURN ROUND((state[2] / state[1]) * 100, 2); 
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE AGGREGATE margin_pct(NUMERIC, NUMERIC) (
    SFUNC = trans_margin_pct,
    STYPE = NUMERIC[],
    FINALFUNC = final_margin_pct,
    INITCOND = '{0,0}'
);

• trans_margin_pct is a Transition Function used to calculate total revenue and total profit
• final_margin_pct is a Final Function to calculate the margin percentage of total revenue and profit
• margin_pct is an AGGREGATE created from trans_margin_pct and final_margin_pct

CREATE OR REPLACE FUNCTION trans_median(state NUMERIC[], val NUMERIC)
RETURNS NUMERIC[] AS $$
BEGIN
    IF val IS NOT NULL THEN
        state := array_append(state, val);
    END IF;
    RETURN state;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION final_median(state NUMERIC[])
RETURNS NUMERIC AS $$
DECLARE
    len INT;
    sorted_state NUMERIC[];
BEGIN
    len := array_length(state, 1);
    IF len IS NULL OR len = 0 THEN 
        RETURN NULL; 
    END IF;

    SELECT array_agg(x ORDER BY x) INTO sorted_state FROM unnest(state) x;

    IF len % 2 = 1 THEN
        RETURN sorted_state[(len / 2) + 1];
    ELSE
        RETURN (sorted_state[len / 2] + sorted_state[(len / 2) + 1]) / 2.0;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE AGGREGATE median(NUMERIC) (
    SFUNC = trans_median,
    STYPE = NUMERIC[],
    FINALFUNC = final_median,
    INITCOND = '{}'
);

• trans_median is a Transition Function used to accumulate elements into an array
• final_median is a Final Function used to sort the array and pick the middle element, following these steps:
  • Sort the numerical array in ascending order
  • If the number of elements is odd, pick the middle element. If even, take the average of the two middle elements.
• median is an AGGREGATE created from trans_median and final_median

-- Query 1
SELECT 
    c.category_name,
    COUNT(p.product_id) as total_skus,                  
    SUM(p.stock_quantity) as total_stock,               
    MIN(p.price) as min_price,                          
    MAX(p.price) as max_price,                          
    AVG(p.price)::NUMERIC(12,2) as avg_price,            
    COALESCE(SUM(oi.quantity * oi.price_per_unit), 0) as total_revenue,
    SUM(COALESCE(oi.discount_amount, 0)) as total_discount_given,
    STRING_AGG(p.product_name, ', ' ORDER BY p.price DESC) as product_list_string,
    ARRAY_AGG(p.product_id) as product_id_array
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_id, c.category_name;

-- Query 2
WITH product_revenue AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.category_id,
        SUM(oi.quantity * oi.price_per_unit) as revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name, p.category_id
)
SELECT 
    c.category_name,
    pr.product_name,
    pr.revenue,
    ROW_NUMBER() OVER (PARTITION BY pr.category_id ORDER BY pr.revenue DESC) as row_num_in_category,
    RANK() OVER (PARTITION BY pr.category_id ORDER BY pr.revenue DESC) as rank_in_category,
    DENSE_RANK() OVER (PARTITION BY pr.category_id ORDER BY pr.revenue DESC) as dense_rank_in_category,
    (PERCENT_RANK() OVER (ORDER BY pr.revenue DESC) * 100)::NUMERIC(5,2) || '%' as relative_percent_rank,
    ((1 - CUME_DIST() OVER (ORDER BY pr.revenue)) * 100)::NUMERIC(5,2) || '%' as top_revenue_tier
FROM product_revenue pr
JOIN categories c ON pr.category_id = c.category_id;

-- Query 3
SELECT 
    item_id,
    order_id,
    created_at,
    price_per_unit as current_purchase_price,
    LAG(price_per_unit, 1) OVER (PARTITION BY product_id ORDER BY created_at) as previous_order_price,
    price_per_unit - LAG(price_per_unit, 1) OVER (PARTITION BY product_id ORDER BY created_at) as price_diff_from_previous,
    LEAD(price_per_unit, 1) OVER (PARTITION BY product_id ORDER BY created_at) as next_order_price,
    FIRST_VALUE(price_per_unit) OVER (PARTITION BY product_id ORDER BY created_at ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as launch_price,
    LAST_VALUE(price_per_unit) OVER (PARTITION BY product_id ORDER BY created_at ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as latest_price,
    VARIANCE(price_per_unit) OVER (PARTITION BY product_id)::NUMERIC(15,2) as price_variance,
    STDDEV(price_per_unit) OVER (PARTITION BY product_id)::NUMERIC(12,2) as price_standard_deviation
FROM order_items
WHERE product_id = 1 
ORDER BY created_at;

-- Query 4
SELECT 
    c.category_name,
    COUNT(DISTINCT oi.order_id) as total_orders,        
    SUM(oi.quantity * oi.price_per_unit) as total_sales, 
    margin_pct(
        (oi.quantity * oi.price_per_unit) - COALESCE(oi.discount_amount, 0), 
        oi.quantity * oi.cost_per_unit
    ) || '%' as category_margin_percent,
    median((oi.quantity * oi.price_per_unit) - COALESCE(oi.discount_amount, 0)) as median_order_value
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_id, c.category_name;

• Query 1: Overview Report of Products by Category
• Uses SUM, AVG, COUNT, MAX, MIN, COALESCE, STRING_AGG, ARRAY_AGG
• Used to generate a dashboard aggregating inventory information and sales performance for each category including total units sold, revenue, min/max product values, product name list as string and as array.

• Query 2: Ranking and Percentile Position of Product Revenue
• Uses WINDOW OVER PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST
• Used to identify top-selling products in each category, rank them and determine which top percentage tier of overall company revenue they belong to for promotion planning.

• Query 3: Shopping Behavior Analysis and Transaction Price Volatility Range
• Uses LEAD, LAG, FIRST_VALUE, LAST_VALUE, STDDEV, VARIANCE
• Tracks product transaction history to analyze price trends, including:
• Price difference between current and previous orders
• Launch price vs latest price
• Price variance and standard deviation across sales seasons

• Query 4: Gross Margin Percentage and Median Order Value
  • Gross Margin Percentage (category_margin_percent) represents profit percentage per revenue dollar after subtracting cost of goods sold and discount amounts.
    • Answers the question: "For every 100 currency units paid by customers, how much net profit does the company earn?"
    • Example: If category_margin_percent for Accessories is 30%, selling 1000 yields a gross profit of 300.
    • Standard AVG() on unit margins leads to skewing caused by small orders. Using margin_pct sums total profit across the entire category before dividing by total revenue, reflecting true financial health.
  • Median Order Value (median_order_value) represents the middle order value when sorted from lowest to highest.
    • Represents typical customer spend, preventing AVG distortion caused by extreme values (outliers).
    • Example with 5 orders:
      • 4 small orders: 2, 3, 4, 5
      • 1 large order: 100
    • Average (AVG): (2+3+4+5+100) / 5 = 22.8 (skewed by outlier).
    • Median (MEDIAN): Sorted: 2, 3, 4, 5, 100. Middle value is 4, accurately representing majority spend.

Happy coding!

See more articles here.