What are the window functions in MariaDB and how to use them for data analysis?

Window Functions in MariaDB are powerful analytical tools that allow complex calculations on query result sets while preserving detailed information about original rows. MariaDB has supported window functions since version 10.2.

1. Window Function Syntax

sql
window_function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC]]
    [FRAME_CLAUSE]
)

2. Common Window Functions

Ranking Functions

sql
-- ROW_NUMBER: Assigns a unique sequence number to each row
SELECT 
    employee_id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK: Rows with same values get same rank, possible gaps
SELECT 
    employee_id,
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- DENSE_RANK: Rows with same values get same rank, no gaps
SELECT 
    employee_id,
    name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Aggregate Functions

sql
-- SUM: Calculate running total
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- AVG: Calculate moving average
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM orders;

-- COUNT: Calculate cumulative count
SELECT 
    order_date,
    amount,
    COUNT(*) OVER (ORDER BY order_date) AS cumulative_count
FROM orders;

Offset Functions

sql
-- LAG: Access value from previous row
SELECT 
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS change
FROM orders;

-- LEAD: Access value from next row
SELECT 
    order_date,
    amount,
    LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount
FROM orders;

-- FIRST_VALUE: Get first value of partition
SELECT 
    employee_id,
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS highest_salary
FROM employees;

-- LAST_VALUE: Get last value of partition
SELECT 
    employee_id,
    name,
    department,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_salary
FROM employees;

3. Window Frame

sql
-- ROWS: Based on physical rows
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS sum_4_rows
FROM orders;

-- RANGE: Based on logical value range
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
    ) AS sum_7_days
FROM orders;

-- GROUPS: Based on groups
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS sum_2_groups
FROM orders;

4. Practical Use Cases

Calculate Year-over-Year Growth Rate

sql
SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(amount) AS monthly_sales,
    LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) AS same_month_last_year,
    (SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date))) / 
    LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) * 100 AS yoy_growth
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

Calculate Moving Average

sql
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_days
FROM orders;

Find Top N

sql
SELECT * FROM (
    SELECT 
        employee_id,
        name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank <= 3;

Window functions provide powerful data analysis capabilities, simplifying complex SQL queries and improving code readability and maintainability.