tldr tsql over()
- User
tldr tsql over()
Assistant
OVER() Clause in T-SQL
The
OVER()clause in T-SQL is used to specify a window over which a function is applied. It allows you to perform calculations across a set of rows that are related to the current row, such as aggregating values or ranking rows.Basic Syntax
SELECT column1, column2, function_name() OVER (window_specification) AS result FROM table_name;Window Specification
The
window_specificationcan include:PARTITION BY: divides the result set into partitions to which the function is appliedORDER BY: specifies the order of the rows within each partitionROWSorRANGE: specifies the window frame (e.g.,ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
Common Use Cases
- Ranking rows (e.g.,
ROW_NUMBER(),RANK(),DENSE_RANK()) - Aggregating values (e.g.,
SUM(),AVG(),MAX()) - Accessing previous or next row values (e.g.,
LAG(),LEAD())
Example
SELECT order_id, order_date, SUM(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total FROM orders;This query calculates the running total of order totals for each customer, ordered by order date.
- User
example of 'ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING'
Assistant
Example of
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGSuppose we have a table
saleswith columnssale_id,sale_date, andsale_amount. We want to calculate the average sale amount for each row, considering the current row, the previous row, and the next row.CREATE TABLE sales ( sale_id INT, sale_date DATE, sale_amount DECIMAL(10, 2) ); INSERT INTO sales (sale_id, sale_date, sale_amount) VALUES (1, '2022-01-01', 100.00), (2, '2022-01-02', 120.00), (3, '2022-01-03', 110.00), (4, '2022-01-04', 130.00), (5, '2022-01-05', 140.00);We can use the
AVG()function with theOVER()clause and theROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGwindow specification to achieve this:SELECT sale_id, sale_date, sale_amount, AVG(sale_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_sale_amount FROM sales;Result
sale_id sale_date sale_amount avg_sale_amount 1 2022-01-01 100.00 110.00 2 2022-01-02 120.00 110.00 3 2022-01-03 110.00 120.00 4 2022-01-04 130.00 120.00 5 2022-01-05 140.00 135.00 Explanation
- For the first row (sale_id = 1), the average is calculated using the current row (100.00) and the next row (120.00), resulting in an average of (100.00 + 120.00) / 2 = 110.00.
- For the second row (sale_id = 2), the average is calculated using the previous row (100.00), the current row (120.00), and the next row (110.00), resulting in an average of (100.00 + 120.00 + 110.00) / 3 = 110.00.
- For the third row (sale_id = 3), the average is calculated using the previous row (120.00), the current row (110.00), and the next row (130.00), resulting in an average of (120.00 + 110.00 + 130.00) / 3 = 120.00.
- For the fourth row (sale_id = 4), the average is calculated using the previous row (110.00), the current row (130.00), and the next row (140.00), resulting in an average of (110.00 + 130.00 + 140.00) / 3 = 120.00.
- For the fifth row (sale_id = 5), the average is calculated using the previous row (130.00) and the current row (140.00), resulting in an average of (130.00 + 140.00) / 2 = 135.00.
- User
Examples of 'ROWS or RANGE'
Assistant
Examples of
ROWSandRANGEThe
ROWSandRANGEclauses are used to specify the window frame in theOVERclause. Here are some examples:ROWS
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: includes the current row, the previous row, and the next row.ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING: includes the current row, the two previous rows, and the two next rows.ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING: includes all rows from the beginning of the result set to the current row and the next row.ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING: includes the previous row, the current row, and all rows to the end of the result set.
RANGE
RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING: includes all rows with values within 10 units of the current row's value.RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING: includes all rows with values from the beginning of the result set to 10 units above the current row's value.RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING: includes all rows with values from 10 units below the current row's value to the end of the result set.
Example
Suppose we have a table
saleswith columnssale_id,sale_date, andsale_amount. We want to calculate the average sale amount for each row, considering different window frames.CREATE TABLE sales ( sale_id INT, sale_date DATE, sale_amount DECIMAL(10, 2) ); INSERT INTO sales (sale_id, sale_date, sale_amount) VALUES (1, '2022-01-01', 100.00), (2, '2022-01-02', 120.00), (3, '2022-01-03', 110.00), (4, '2022-01-04', 130.00), (5, '2022-01-05', 140.00);We can use the
AVG()function with theOVER()clause and different window frames to achieve this:SELECT sale_id, sale_date, sale_amount, -- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING AVG(sale_amount) OVER ( ORDER BY sale_amount ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_sale_amount_rows, -- RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING AVG(sale_amount) OVER ( ORDER BY sale_amount RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING ) AS avg_sale_amount_range, -- ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING AVG(sale_amount) OVER ( ORDER BY sale_amount ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) AS avg_sale_amount_rows_unbounded, -- RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING AVG(sale_amount) OVER ( ORDER BY sale_amount RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING ) AS avg_sale_amount_range_unbounded FROM sales;Result
sale_id sale_date sale_amount avg_sale_amount_rows avg_sale_amount_range avg_sale_amount_rows_unbounded avg_sale_amount_range_unbounded 1 2022-01-01 100.00 110.00 110.00 100.00 110.00 2 2022-01-02 120.00 115.00 115.00 110.00 115.00 3 2022-01-03 110.00 120.00 120.00 110.00 120.00 4 2022-01-04 130.00 125.00 125.00 120.00 125.00 5 2022-01-05 140.00 135.00 135.00 130.00 135.00 Note that the results may vary depending on the specific data and window frames used.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.