docs/sql-reference/functions/window.mdx
Window functions perform calculations across a set of rows that are related to the current row. Unlike aggregate functions with GROUP BY, window functions do not collapse rows into a single output row. Every input row produces a corresponding output row, with the window function result appended.
<Info> Turso supports aggregate functions used as window functions with the default frame definition. Custom frame specifications (ROWS, RANGE, or GROUPS with explicit bounds) and dedicated window functions (row_number, rank, dense_rank, ntile, lag, lead, first_value, last_value, nth_value) are not yet supported. </Info>aggregate_function(expression) OVER (
[PARTITION BY expression [, ...]]
[ORDER BY expression [ASC | DESC] [, ...]]
)
| Clause | Description |
|---|---|
aggregate_function | Any supported aggregate function: count, sum, avg, min, max, total, group_concat |
OVER (...) | Defines the window over which the function operates |
PARTITION BY | Divides the result set into partitions. The function is applied independently within each partition. If omitted, the entire result set is one partition |
ORDER BY | Defines the order of rows within each partition. This determines which rows are included in the frame for each calculation |
When ORDER BY is specified, the default frame is:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This means the function considers all rows from the start of the partition up to and including the current row (and any rows with equal ORDER BY values, since the frame mode is RANGE).
When ORDER BY is omitted, the default frame covers the entire partition.
Any aggregate function can be used as a window function by adding an OVER clause.
| Function | Description |
|---|---|
count(*) | Number of rows in the frame |
count(expression) | Number of non-NULL values in the frame |
sum(expression) | Sum of non-NULL values in the frame |
avg(expression) | Average of non-NULL values in the frame |
min(expression) | Minimum value in the frame |
max(expression) | Maximum value in the frame |
total(expression) | Sum as REAL (returns 0.0 for empty frames instead of NULL) |
group_concat(expression, separator) | Concatenation of values in the frame |
PARTITION BY divides the rows into groups. The window function resets and recalculates independently for each partition.
SELECT
department,
name,
salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
| department | name | salary | dept_total |
|---|---|---|---|
| Engineering | Alice | 90000 | 250000 |
| Engineering | Bob | 85000 | 250000 |
| Engineering | Carol | 75000 | 250000 |
| Marketing | Dave | 70000 | 130000 |
| Marketing | Eve | 60000 | 130000 |
Without PARTITION BY, the function treats the entire result set as one partition:
SELECT
name,
salary,
SUM(salary) OVER () AS company_total
FROM employees;
ORDER BY within the OVER clause determines row ordering within each partition. Combined with the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), this produces running calculations.
SELECT
name,
salary,
SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;
| name | salary | running_total |
|---|---|---|
| Eve | 60000 | 60000 |
| Dave | 70000 | 130000 |
| Carol | 75000 | 205000 |
| Bob | 85000 | 290000 |
| Alice | 90000 | 380000 |
Use both clauses together for running calculations within groups:
SELECT
department,
name,
salary,
SUM(salary) OVER (
PARTITION BY department
ORDER BY salary
) AS dept_running_total
FROM employees;
| department | name | salary | dept_running_total |
|---|---|---|---|
| Engineering | Carol | 75000 | 75000 |
| Engineering | Bob | 85000 | 160000 |
| Engineering | Alice | 90000 | 250000 |
| Marketing | Eve | 60000 | 60000 |
| Marketing | Dave | 70000 | 130000 |
The WINDOW clause defines a reusable window specification that can be referenced by multiple window functions in the same query. This avoids repeating the same OVER definition.
SELECT
department,
name,
salary,
SUM(salary) OVER w AS running_total,
AVG(salary) OVER w AS running_avg,
COUNT(*) OVER w AS running_count
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary)
ORDER BY department, salary;
Multiple named windows can be defined:
SELECT
department,
name,
salary,
SUM(salary) OVER dept AS dept_total,
SUM(salary) OVER company AS company_total
FROM employees
WINDOW
dept AS (PARTITION BY department),
company AS ()
ORDER BY department, name;
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Show each employee alongside their department headcount
SELECT
name,
department,
COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees
ORDER BY department, name;
SELECT
date,
temperature,
AVG(temperature) OVER (ORDER BY date) AS running_avg_temp
FROM weather_readings
ORDER BY date;
SELECT
product_name,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM products
ORDER BY revenue DESC;
SELECT
department,
name,
salary,
MIN(salary) OVER (PARTITION BY department) AS dept_min,
MAX(salary) OVER (PARTITION BY department) AS dept_max,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;
SELECT
department,
name,
GROUP_CONCAT(name, ', ') OVER (PARTITION BY department ORDER BY name) AS names_so_far
FROM employees;
The following window function features are not yet supported in Turso:
| Feature | Status |
|---|---|
row_number() | Not supported |
rank() | Not supported |
dense_rank() | Not supported |
ntile(N) | Not supported |
lag(expr, offset, default) | Not supported |
lead(expr, offset, default) | Not supported |
first_value(expr) | Not supported |
last_value(expr) | Not supported |
nth_value(expr, N) | Not supported |
cume_dist() | Not supported |
percent_rank() | Not supported |
Custom frame: ROWS BETWEEN ... | Not supported |
Custom frame: RANGE BETWEEN ... AND ... | Not supported |
Custom frame: GROUPS BETWEEN ... | Not supported |
EXCLUDE clause | Not supported |
FILTER (WHERE ...) on window functions | Not supported |