SQL OLAP provides two ways of specifying a window in a query:
The explicit window clause lets you define a window that follows a HAVING clause. You reference windows defined with those window clauses by specifying their names when you invoke an OLAP function, such as:
SUM ( ...) OVER w2
The inline window specification lets you define a window in the SELECT list of a query expression. This capability lets you define your windows in a window clause that follows the HAVING clause and then reference them by name from your window function invocations, or to define them along with the function invocations.
If you use an inline window specification, you cannot
name the window. Two or more window function invocations in a single SELECT list
that use identical windows must either reference a named window defined
in a window clause or they must define their inline windows redundantly.
Window function example The following example shows a window function. The query returns a result set that partitions the data by department and then provides a cumulative summary of employees’ salaries, starting with the employee who has been at the company the longest. The result set includes only those employees who reside in Massachusetts. The column Sum_Salary provides the cumulative total of employees’ salaries.
SELECT DepartmentID, Surname, StartDate, Salary,SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BYstartdate rows between unbounded preceding andcurrent row) AS sum_salaryFROM EmployeesWHERE State IN ('CA') AND DepartmentID IN (100, 200)ORDER BY DepartmentID;
The following result set is partitioned by department.
DepartmentID Surname start_date salary sum_salary ------------ ----------- ------------ ---------- ----------- 200 Overbey 1987-02-19 39300.000 39300.000 200 Savarino 1989-11-07 72300.000 111600.000 200 Clark 1990-07-21 45000.000 156600.000
