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 in-line 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 in-line 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 in-line 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 dept_id, emp_lname, start_date, salary,
SUM(salary) OVER (PARTITION BY dept_id ORDER BY
start_date rows between unbounded preceding and
current row) AS sum_salary
FROM employee
WHERE state IN ('MA') AND dept_id IN (100, 200)
ORDER BY dept_id;
The following result set is partitioned by department.
dept_id emp_lname start_date salary sum_salary -------- ----------- ------------- ---------- ----------- 100 Whitney 1984-08-28 45700.000 45700.000 100 Cobb 1985-01-01 62000.000 107700.000 100 Breault 1985-06-17 57490.000 165190.000 100 Shishov 1986-06-07 72995.000 238185.000 100 Driscoll 1986-07-01 48023.690 286208.690 100 Guevara 1986-10-14 42998.000 329206.690 100 Gowda 1986-11-30 59840.000 389046.690 100 Melkisetian 1986-12-06 48500.000 437546.690 100 Pastor 1987-04-26 74500.000 512046.690 100 Lull 1987-06-15 87900.000 599946.690 100 Rabkin 1987-06-15 64500.000 664446.690 100 Siperstein 1987-07-23 39875.500 704322.190 100 Scott 1987-08-04 96300.000 800622.190 100 Sullivan 1988-02-03 67890.000 868512.190 100 Samuels 1988-03-23 37400.000 905912.190 100 Wang 1988-09-29 68400.000 974312.190 100 Marshall 1989-04-20 42500.000 1016812.190 100 Soo 1990-07-31 39075.000 1055887.190 100 Diaz 1990-08-19 54900.000 1110787.190 200 Dill 1985-12-06 54800.000 54800.000 200 Powell 1988-10-14 54600.000 109400.000 200 Poitras 1988-11-28 46200.000 155600.000 200 Singer 1989-06-01 34892.000 190492.000 200 Kelly 1989-10-01 87500.000 277992.000 200 Martel 1989-10-16 55700.000 333692.000 200 Sterling 1990-04-29 64900.000 398592.000 200 Chao 1990-05-13 33890.000 432482.000 200 Preston 1990-07-11 37803.000 470285.000 200 Goggin 1990-08-05 37900.000 508185.000 200 Pickett 1993-08-12 47653.000 555838.000
