Explain advanced window functions.

SQL window functions are powerful tools for performing calculations across a set of table rows that are related to the current row. While basic aggregations (SUM, AVG, COUNT) operate on groups, window functions operate on a "window" of rows, allowing them to return a value for each row individually without collapsing the result set. Advanced window functions extend this capability, offering sophisticated ways to analyze data trends, comparisons, and distributions.

Understanding Window Functions (Recap)

At their core, all window functions use the OVER() clause to define the window. This clause can include PARTITION BY to divide the rows into groups (similar to GROUP BY, but without aggregating the final result set) and ORDER BY to specify the order of rows within each partition. This ordering is crucial for many advanced functions.

Advanced Framing Clauses (`ROWS` and `RANGE`)

The OVER() clause can also specify a "frame" within each partition, which defines the set of rows the window function operates on relative to the current row. This is where advanced framing comes in, using ROWS BETWEEN or RANGE BETWEEN. - ROWS BETWEEN: Specifies a frame based on a fixed number of rows before or after the current row. - RANGE BETWEEN: Specifies a frame based on a range of values relative to the current row's ordering column. This is less common and often implies ORDER BY on a single column. Common frame specifications include UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, and UNBOUNDED FOLLOWING. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for ordered windows, or ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for unordered windows.

SELECT
    order_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY EXTRACT(YEAR FROM order_date)
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_day_moving_sum
FROM
    orders;

Value Window Functions: `LAG()`, `LEAD()`, `NTH_VALUE()`

These functions allow you to access data from a different row within the same window, which is crucial for comparisons, trend analysis, and gap detection. - LAG(expression, offset, default): Retrieves the value of an expression from a row that offset rows before the current row within the partition. The default value is returned if the offset goes beyond the partition start. - LEAD(expression, offset, default): Retrieves the value of an expression from a row that offset rows after the current row within the partition. The default value is returned if the offset goes beyond the partition end.

SELECT
    product_id,
    sale_date,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_sale,
    LEAD(sales_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_sale
FROM
    daily_sales;

NTH_VALUE(expression, n): Returns the n-th value in the window frame specified by the OVER() clause. This is useful for picking specific values from a ranked or ordered set. It's important to specify an appropriate frame (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to ensure the function can see all rows necessary to find the n-th value.

SELECT
    department,
    employee_name,
    salary,
    NTH_VALUE(employee_name, 2) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_earner
FROM
    employees;

`FIRST_VALUE()` and `LAST_VALUE()`

These functions retrieve the first or last value of an expression within the current window frame. They are particularly sensitive to the framing clause. - FIRST_VALUE(expression): Returns the value of expression for the first row in the window frame. - LAST_VALUE(expression): Returns the value of expression for the last row in the window frame. Due to the default frame (up to CURRENT ROW), LAST_VALUE often returns the current row's value unless a broader frame is explicitly defined.

-- Custom frame (UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) for true first/last value within partition
SELECT
    product_id,
    sale_date,
    sales_amount,
    FIRST_VALUE(sales_amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS true_first_sale_in_partition,
    LAST_VALUE(sales_amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS true_last_sale_in_partition
FROM
    product_sales
ORDER BY product_id, sale_date;

`NTILE()`

NTILE(n) divides the rows in each partition into n groups, or "buckets," and assigns a bucket number (from 1 to n) to each row. This is useful for creating percentiles, deciles, or quartiles. If the number of rows is not evenly divisible by n, NTILE distributes the remaining rows among the first buckets, ensuring that the number of rows in any two buckets differs by at most 1.

SELECT
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile,
    NTILE(10) OVER (ORDER BY salary DESC) AS salary_decile
FROM
    employees;

`CUME_DIST()` and `PERCENT_RANK()`

These ranking functions provide insight into the relative position of a row within its partition, often used for percentile analysis. - CUME_DIST(): Returns the cumulative distribution of a value within its partition. It represents the number of rows with values less than or equal to the current row's value, divided by the total number of rows in the partition. The result is a value between 0 and 1. - PERCENT_RANK(): Returns the percentile rank of a row within its partition. It calculates (rank - 1) / (total_rows - 1), where rank is the rank of the current row (e.g., using RANK()). The result ranges from 0 to 1, with the first row in the partition typically getting 0.

SELECT
    score,
    CUME_DIST() OVER (ORDER BY score) AS cumulative_distribution,
    PERCENT_RANK() OVER (ORDER BY score) AS percentile_rank
FROM
    exam_results;

`WIDTH_BUCKET()`

WIDTH_BUCKET(expression, min_value, max_value, num_buckets) is a non-standard window function (often available in Oracle, PostgreSQL, and some other databases) that assigns an item to an appropriate bucket. It allows you to distribute data into a specified number of equal-width buckets based on a given range. Values below min_value are assigned to bucket 0, and values above max_value are assigned to bucket num_buckets + 1.

-- Assuming a range of salaries from 30000 to 120000 for 5 buckets
SELECT
    employee_name,
    salary,
    WIDTH_BUCKET(salary, 30000, 120000, 5) AS salary_bucket
FROM
    employees;

Practical Use Cases for Advanced Window Functions

• Calculating running totals or moving averages over specific time windows (e.g., 7-day moving average).
• Comparing current values to previous or next values (e.g., day-over-day sales change, identifying gaps in sequences).
• Finding the first or last event in a series, or a specific N-th event within a group.
• Ranking items within groups (e.g., top N employees per department by salary).
• Analyzing cumulative distributions and percentiles for performance measurement or grading.
• Detecting data anomalies or trends by comparing values across a window.
• Segmenting data into quantiles (quartiles, deciles) for analysis and reporting.

Conclusion

Advanced SQL window functions significantly enhance SQL's analytical capabilities, moving beyond simple group-level aggregations to provide powerful row-level insights. Mastering concepts like intelligent framing (ROWS BETWEEN, RANGE BETWEEN) and specific functions like LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTILE, CUME_DIST, and PERCENT_RANK unlocks new possibilities for complex data analysis, trend identification, and business intelligence, enabling more sophisticated reporting directly within SQL.