Window Functions

Overview

Window Functions are a powerful analytical feature in Singdata Lakehouse SQL that allow calculations to be performed across a set of related rows, rather than just on individual rows. By using the WINDOW clause, you can define one or more named windows and then reference those windows in window functions, avoiding repetitive window specifications and making queries more concise and maintainable.

Syntax

WINDOW <window_name> AS (<window_specification>, ...)

Parameter Description

• window_name: The name of the window, which must be a valid identifier and must not conflict with table or column names
• window_specification: The window specification, defining the scope and ordering of the window, consisting of:
  • PARTITION BY: Specifies how data is grouped; each group is a partition
  • ORDER BY: Specifies how data within each partition is ordered
  • frame_clause: Specifies the window frame, limiting the computation range of the window function

Window Frame Types

1. ROWS Frame: Window frame based on row count
2. RANGE Frame: Window frame based on value range

Test Data Setup

The following examples use a custom sales table (monthly sales data) to demonstrate various window function features. The PARTITION BY example in the Best Practices section uses the doc_test.employees table.

CREATE TABLE IF NOT EXISTS sales ( month INT, sales INT, profit DOUBLE ); INSERT INTO sales (month, sales, profit) VALUES (1, 100, 0.1), (2, 120, 0.15), (3, 80, 0.05), (4, 150, 0.2), (5, 90, 0.1), (6, 110, 0.12);

Usage Examples

Example 1: Moving Window Aggregation — Moving Sum and Moving Average

Business Scenario: Calculate the sum and average of sales for the current month and the two preceding months

SELECT month, sales, SUM(sales) OVER w AS sum_sales, AVG(sales) OVER w AS avg_sales FROM sales WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

Execution Result:

+-------+-------+-----------+--------------------+ | month | sales | sum_sales | avg_sales | +-------+-------+-----------+--------------------+ | 1 | 100 | 100 | 100.0 | | 2 | 120 | 220 | 110.0 | | 3 | 80 | 300 | 100.0 | | 4 | 150 | 350 | 116.67 | | 5 | 90 | 320 | 106.67 | | 6 | 110 | 350 | 116.67 | +-------+-------+-----------+--------------------+

Explanation: Window w defines a sliding window containing the current row and the 2 preceding rows, so each row computes the sum and average of the most recent 3 months of sales.

---

Example 2: Multi-Window Ranking — Quarterly and Annual Ranking

Business Scenario: Simultaneously compute ranking within each quarter and across the full year

SELECT month, sales, RANK() OVER w1 AS rank_quarter, ROW_NUMBER() OVER w2 AS rank_year FROM sales WINDOW w1 AS (PARTITION BY CEIL(month / 3) ORDER BY sales DESC), w2 AS (ORDER BY sales DESC);

Execution Result:

+-------+-------+--------------+-----------+ | month | sales | rank_quarter | rank_year | +-------+-------+--------------+-----------+ | 4 | 150 | 1 | 1 | | 2 | 120 | 1 | 2 | | 6 | 110 | 2 | 3 | | 1 | 100 | 2 | 4 | | 5 | 90 | 3 | 5 | | 3 | 80 | 3 | 6 | +-------+-------+--------------+-----------+

Explanation:

• The w1 window partitions by quarter (every 3 months), computing sales ranking within each quarter
• The w2 window has no partition, computing sales ranking across the full year
• CEIL(month/3) divides months into Q1 (months 1–3), Q2 (months 4–6), etc.

---

Example 3: Cumulative Aggregation — Year-to-Date Statistics

Business Scenario: Calculate cumulative sales, average profit margin, etc. from the beginning of the year to the current month

SELECT month, sales, profit, SUM(sales) OVER w AS cumulative_sales, AVG(profit) OVER w AS avg_profit, MAX(sales) OVER w AS max_sales, MIN(sales) OVER w AS min_sales FROM sales WINDOW w AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Execution Result:

+-------+-------+--------+------------------+------------+-----------+-----------+ | month | sales | profit | cumulative_sales | avg_profit | max_sales | min_sales | +-------+-------+--------+------------------+------------+-----------+-----------+ | 1 | 100 | 0.1 | 100 | 0.1 | 100 | 100 | | 2 | 120 | 0.15 | 220 | 0.125 | 120 | 100 | | 3 | 80 | 0.05 | 300 | 0.1 | 120 | 80 | | 4 | 150 | 0.2 | 450 | 0.125 | 150 | 80 | | 5 | 90 | 0.1 | 540 | 0.12 | 150 | 80 | | 6 | 110 | 0.12 | 650 | 0.12 | 150 | 80 | +-------+-------+--------+------------------+------------+-----------+-----------+

Explanation: UNBOUNDED PRECEDING starts from the first row of the partition, and CURRENT ROW ends at the current row, achieving cumulative computation.

---

Example 4: Time Series Analysis — Period-over-Period Comparison

Business Scenario: View each month's sales compared to the previous and next month, and the period-over-period change

SELECT month, sales, LAG(sales, 1) OVER w AS prev_sales, LEAD(sales, 1) OVER w AS next_sales, sales - LAG(sales, 1) OVER w AS sales_change FROM sales WINDOW w AS (ORDER BY month);

Execution Result:

+-------+-------+------------+------------+--------------+ | month | sales | prev_sales | next_sales | sales_change | +-------+-------+------------+------------+--------------+ | 1 | 100 | NULL | 120 | NULL | | 2 | 120 | 100 | 80 | 20 | | 3 | 80 | 120 | 150 | -40 | | 4 | 150 | 80 | 90 | 70 | | 5 | 90 | 150 | 110 | -60 | | 6 | 110 | 90 | NULL | 20 | +-------+-------+------------+------------+--------------+

Explanation:

• LAG(sales, 1): Gets the sales value from the previous row
• LEAD(sales, 1): Gets the sales value from the next row
• prev_sales and next_sales are NULL for the first and last rows respectively

---

Example 5: Comparison of Different Ranking Functions

Business Scenario: Understand the differences between various ranking functions

SELECT month, sales, DENSE_RANK() OVER w AS dense_rank, RANK() OVER w AS rank, ROW_NUMBER() OVER w AS row_num, PERCENT_RANK() OVER w AS percent_rank FROM sales WINDOW w AS (ORDER BY sales DESC);

Execution Result:

+-------+-------+------------+------+---------+--------------+ | month | sales | dense_rank | rank | row_num | percent_rank | +-------+-------+------------+------+---------+--------------+ | 4 | 150 | 1 | 1 | 1 | 0.0 | | 2 | 120 | 2 | 2 | 2 | 0.2 | | 6 | 110 | 3 | 3 | 3 | 0.4 | | 1 | 100 | 4 | 4 | 4 | 0.6 | | 5 | 90 | 5 | 5 | 5 | 0.8 | | 3 | 80 | 6 | 6 | 6 | 1.0 | +-------+-------+------------+------+---------+--------------+

Ranking Function Descriptions:

• ROW_NUMBER(): Unique sequential row numbers; rows with the same value do not tie
• RANK(): Same value gets the same rank; the next rank skips
• DENSE_RANK(): Same value gets the same rank; the next rank is consecutive
• PERCENT_RANK(): Returns a percentile rank between 0 and 1

---

Example 6: Position Functions — Accessing Values at Specific Positions

Business Scenario: Get the first, last, and nth values within the window

SELECT month, sales, profit, FIRST_VALUE(sales) OVER w AS first_sales, LAST_VALUE(sales) OVER w AS last_sales, NTH_VALUE(sales, 2) OVER w AS second_sales FROM sales WINDOW w AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Execution Result:

+-------+-------+--------+-------------+------------+--------------+ | month | sales | profit | first_sales | last_sales | second_sales | +-------+-------+--------+-------------+------------+--------------+ | 1 | 100 | 0.1 | 100 | 110 | 120 | | 2 | 120 | 0.15 | 100 | 110 | 120 | | 3 | 80 | 0.05 | 100 | 110 | 120 | | 4 | 150 | 0.2 | 100 | 110 | 120 | | 5 | 90 | 0.1 | 100 | 110 | 120 | | 6 | 110 | 0.12 | 100 | 110 | 120 | +-------+-------+--------+-------------+------------+--------------+

Explanation:

• FIRST_VALUE(): Returns the value of the first row in the window
• LAST_VALUE(): Returns the value of the last row in the window (requires UNBOUNDED FOLLOWING to see the actual last row)
• NTH_VALUE(expr, n): Returns the value of the nth row in the window

---

Example 6.1: IGNORE NULLS — Skip NULL Values When Accessing

Business Scenario: Skip NULLs when accessing values, for example to forward-fill gaps with the most recent non-null value

The value access functions FIRST_VALUE, LAST_VALUE, and NTH_VALUE support appending IGNORE NULLS after the function call, which skips NULL values during computation. The default behavior is RESPECT NULLS (NULLs participate in computation).

-- Prepare data containing NULLs CREATE TABLE w_nulls (id INT, v INT); INSERT INTO w_nulls VALUES (1, 10), (2, NULL), (3, 30), (4, NULL), (5, 50); SELECT id, v, LAST_VALUE(v) IGNORE NULLS OVER (ORDER BY id) AS fill_down, LAST_VALUE(v) OVER (ORDER BY id) AS keep_null FROM w_nulls ORDER BY id;

Execution Result:

+----+------+-----------+-----------+ | id | v | fill_down | keep_null | +----+------+-----------+-----------+ | 1 | 10 | 10 | 10 | | 2 | NULL | 10 | NULL | | 3 | 30 | 30 | 30 | | 4 | NULL | 30 | NULL | | 5 | 50 | 50 | 50 | +----+------+-----------+-----------+

Explanation:

• IGNORE NULLS: Skips NULLs; the fill_down column fills gaps with the most recent non-null value
• RESPECT NULLS (default): NULLs pass through as-is
• Functions that support IGNORE NULLS / RESPECT NULLS: FIRST_VALUE, LAST_VALUE, NTH_VALUE
• LAG and LEAD do not currently support IGNORE NULLS; using it will raise an error

---

Business Scenario: Find all records within a sales value range of ±10 and compute statistics

SELECT month, sales, SUM(sales) OVER w AS range_sum, COUNT(*) OVER w AS range_count FROM sales WINDOW w AS (ORDER BY sales RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING);

Execution Result:

+-------+-------+-----------+-------------+ | month | sales | range_sum | range_count | +-------+-------+-----------+-------------+ | 3 | 80 | 170 | 2 | | 5 | 90 | 270 | 3 | | 1 | 100 | 300 | 3 | | 6 | 110 | 330 | 3 | | 2 | 120 | 230 | 2 | | 4 | 150 | 150 | 1 | +-------+-------+-----------+-------------+

Explanation:

• RANGE windows are based on value ranges rather than row counts
• For the row with sales=100, the window includes all rows with sales values in the range [90, 110]
• For the row with sales=150, only itself falls within the range [140, 160]

---

Example 8: Comprehensive Analysis — Multi-Dimensional Business Report (Advanced)

Business Scenario: Generate a comprehensive analytical report including ranking, cumulative values, period-over-period comparison, and proportions

SELECT month, sales, -- Ranking related ROW_NUMBER() OVER w1 AS row_num, RANK() OVER w1 AS sales_rank, -- Cumulative statistics SUM(sales) OVER w2 AS ytd_sales, AVG(sales) OVER w2 AS ytd_avg, -- Period-over-period comparison LAG(sales) OVER w1 AS prev_month_sales, sales - LAG(sales) OVER w1 AS mom_change, -- Proportion analysis ROUND(sales * 100.0 / SUM(sales) OVER w3, 2) AS pct_of_total FROM sales WINDOW w1 AS (ORDER BY month), w2 AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), w3 AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Execution Result:

+-------+-------+---------+------------+-----------+---------+------------------+------------+---------------+ | month | sales | row_num | sales_rank | ytd_sales | ytd_avg | prev_month_sales | mom_change | pct_of_total | +-------+-------+---------+------------+-----------+---------+------------------+------------+---------------+ | 1 | 100 | 1 | 1 | 100 | 100.0 | NULL | NULL | 15.38 | | 2 | 120 | 2 | 2 | 220 | 110.0 | 100 | 20 | 18.46 | | 3 | 80 | 3 | 3 | 300 | 100.0 | 120 | -40 | 12.31 | | 4 | 150 | 4 | 4 | 450 | 112.5 | 80 | 70 | 23.08 | | 5 | 90 | 5 | 5 | 540 | 108.0 | 150 | -60 | 13.85 | | 6 | 110 | 6 | 6 | 650 | 108.33 | 90 | 20 | 16.92 | +-------+-------+---------+------------+-----------+---------+------------------+------------+---------------+

Explanation:

• This example uses 3 different window definitions simultaneously
• w1: Ordered by month, used for ranking and period-over-period comparison
• w2: Cumulative window, used for YTD (Year-To-Date) calculations
• w3: Full window, used for computing the total sum and calculating proportions
• Demonstrates how to implement complex multi-dimensional analysis in a single query

---

Example 9: Data Bucketing — NTILE Function Application

Business Scenario: Divide sales data into high, medium, and low performance tiers

SELECT month, sales, NTILE(3) OVER w AS sales_tier, CASE WHEN NTILE(3) OVER w = 1 THEN 'High' WHEN NTILE(3) OVER w = 2 THEN 'Medium' ELSE 'Low' END AS tier_label FROM sales WINDOW w AS (ORDER BY sales DESC);

Execution Result:

+-------+-------+------------+------------+ | month | sales | sales_tier | tier_label | +-------+-------+------------+------------+ | 4 | 150 | 1 | High | | 2 | 120 | 1 | High | | 6 | 110 | 2 | Medium | | 1 | 100 | 2 | Medium | | 5 | 90 | 3 | Low | | 3 | 80 | 3 | Low | +-------+-------+------------+------------+

Explanation: NTILE(n) divides ordered data into n buckets as evenly as possible, commonly used for tiered analysis, ABC classification, etc.

---

Example 10: Cumulative Distribution — CUME_DIST Function Application

Business Scenario: Calculate the cumulative distribution percentile of each sales value within the dataset

SELECT month, sales, CUME_DIST() OVER w AS cumulative_dist, ROUND(CUME_DIST() OVER w * 100, 2) AS percentile FROM sales WINDOW w AS (ORDER BY sales);

Execution Result:

+-------+-------+------------------+------------+ | month | sales | cumulative_dist | percentile | +-------+-------+------------------+------------+ | 3 | 80 | 0.167 | 16.67 | | 5 | 90 | 0.333 | 33.33 | | 1 | 100 | 0.500 | 50.00 | | 6 | 110 | 0.667 | 66.67 | | 2 | 120 | 0.833 | 83.33 | | 4 | 150 | 1.000 | 100.00 | +-------+-------+------------------+------------+

Explanation: CUME_DIST() returns the ratio of rows with values less than or equal to the current row's value to the total number of rows, indicating the cumulative distribution position of the current value within the dataset.

---

Window Frame Details

ROWS vs RANGE

Frame Type	Description	Example
ROWS	Based on physical row count	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
RANGE	Based on logical value range	RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING

Boundary Keywords

• UNBOUNDED PRECEDING: The first row of the partition
• UNBOUNDED FOLLOWING: The last row of the partition
• CURRENT ROW: The current row
• n PRECEDING: The nth row before the current row (ROWS) or rows with value − n (RANGE)
• n FOLLOWING: The nth row after the current row (ROWS) or rows with value + n (RANGE)

---

Common Window Function Categories

Aggregate Functions

• SUM(), AVG(), COUNT(), MAX(), MIN()

Ranking Functions

• ROW_NUMBER(): Unique sequential row numbers
• RANK(): Same value gets same rank, with gaps
• DENSE_RANK(): Same value gets same rank, without gaps
• PERCENT_RANK(): Percentile ranking
• NTILE(n): Divides data into n buckets
• CUME_DIST(): Cumulative distribution value

Value Access Functions

• LAG(): Access preceding rows
• LEAD(): Access following rows
• FIRST_VALUE(): First value in the window (supports IGNORE NULLS)
• LAST_VALUE(): Last value in the window (supports IGNORE NULLS)
• NTH_VALUE(): The nth value in the window (supports IGNORE NULLS)

---

Best Practices

1. Use the WINDOW Clause to Improve Readability

Not recommended (repeated window definitions):

SELECT month, SUM(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM sales;

Recommended (using the WINDOW clause):

SELECT month, SUM(sales) OVER w, AVG(sales) OVER w FROM sales WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

2. Use PARTITION BY Appropriately

For grouped analysis, use PARTITION BY to perform intra-group calculations without breaking the data structure:

-- Example: Calculate salary ranking by department SELECT dept, name, salary, RANK() OVER w AS rank_in_dept FROM doc_test.employees WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Execution Result:

+-------------+-------+----------+--------------+ | dept | name | salary | rank_in_dept | +-------------+-------+----------+--------------+ | Engineering | Alice | 12000.00 | 1 | | Engineering | Bob | 9500.00 | 2 | | HR | Eve | 6000.00 | 1 | | Marketing | Carol | 8500.00 | 1 | | Marketing | Dave | 6500.00 | 2 | +-------------+-------+----------+--------------+

Explanation: Salary rankings are computed independently within each department without affecting each other.

3. Pay Attention to LAST_VALUE Window Scope

To get the actual last value, you must use UNBOUNDED FOLLOWING:

WINDOW w AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

4. Specific Considerations

In Lakehouse, it is recommended to add a LIMIT clause at the end of the query to improve performance:

SELECT ... FROM ... WINDOW ... LIMIT 50;

---

Performance Optimization Suggestions

1. Index Optimization: Create appropriate indexes on columns used in ORDER BY and PARTITION BY
2. Window Scope: Use smaller window scopes whenever possible to reduce computation
3. Reuse Window Definitions: Use the WINDOW clause to avoid redefining identical windows
4. Partitioning Strategy: Use PARTITION BY appropriately to reduce the data volume within each window

---

Frequently Asked Questions

Q1: Where must the WINDOW clause be placed?

A: The WINDOW clause must be placed after the FROM and WHERE clauses, and before the ORDER BY clause.

Q2: Can multiple windows be defined in the same query?

A: Yes, use commas to separate multiple window definitions.

Q3: What is the main difference between ROWS and RANGE?

A: ROWS is based on physical row count, while RANGE is based on logical value range. When there are duplicate values, RANGE will include all rows with the same value.

Q4: Why doesn't LAST_VALUE return the expected last value?

A: You need to explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to include the entire partition.

---

Related Guides

• Ranking and Percentile Analysis: Complete usage scenarios for ROW_NUMBER, RANK, DENSE_RANK, NTILE, and PERCENTILE
• Cumulative Calculation and Running Total: Usage patterns for SUM/AVG OVER, moving averages, YTD cumulative values, FIRST_VALUE/LAST_VALUE
• Time Series Analysis: Implementing rolling windows and period-over-period comparisons using window functions