Question

Explain the SQL window operator (function) with the help of examples in Oracle, Sql Server,
and My SQL databases.

Answer 1

Answer:

Explanation:

Part 9 in this series, “Having Sums, Averages, and Other Grouped Data” (Oracle Magazine, January/February 2013), introduced common SQL aggregate functions and the GROUP BY and HAVING clauses, showing how you can use them to manipulate single-row and grouped result set data to convey more-meaningful results. The discussion of aggregate functions segues logically into the subject of more-advanced SQL operations that use aggregations and other specific views of your data. This article is the first in a three-article sequence that introduces you to some commonly used analytic functions and their associated clauses. Analytic functions not only operate on multiple rows but also can perform operations such as ranking data, calculating running totals, and identifying changes between different time periods (to name a few)—all of which facilitate creation of queries that answer business questions for reporting purposes.

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Express Edition 11g Release 2. If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_101 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and SYSTEM and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_101 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)

Increasing Your Bottom Line

You can use standard SQL to answer most data questions. However, pure SQL queries that answer questions such as “What is the running total of employee salary values as they are summed row by row?” aren’t easy to write and may not perform well over time. Analytic functions add extensions to SQL that make such operations faster-running and easier to code.

The query in Listing 1 demonstrates use of the SUM analytic function. The query results list all employees alongside their respective salary values and display a cumulative total of their salaries.

Code Listing 1: Obtain a cumulative salary total, row by row, for all employees

SQL> set feedback on

SQL> set lines 32000

SQL> select last_name, first_name, salary,

 2     SUM (salary)

 3    OVER (ORDER BY last_name, first_name) running_total

 4    from employee

 5   order by last_name, first_name;

LAST_NAME  FIRST_NAME                         SALARY RUNNING_TOTAL

—————————  ——————————— ————————————————————————————— —————————————  

Dovichi    Lori

Eckhardt   Emily                              100000        100000

Friedli    Roger                               60000        160000

James      Betsy                               60000        220000

Jeffrey    Thomas                             300000        520000

Michaels   Matthew                             70000        590000

Newton     Donald                              80000        670000

Newton     Frances                             75000        745000

Wong       Theresa                             70000        815000

leblanc    mark                                65000        880000

peterson   michael                             90000        970000

11 rows selected.

This result is accomplished with the query line that reads

SUM (salary)

 OVER (ORDER BY last_name, first_name) running_total

Anatomy of an Analytic Function

Learning the syntax of an analytic function is half the battle in harnessing its power for efficient query processing. The syntax for the analytic query line in Listing 1 is

FUNCTION_NAME( column | expression,column | expression,... )  

OVER  

( Order-by-Clause )

In Listing 1, the function name is SUM. The argument to the SUM function is the SALARY column (although it could also be an expression). The OVER clause identifies this function call as an analytic function (as opposed to an aggregate function). The ORDER BY clause identifies the piece of data this analytic function will be performed “over.”

This series will discuss scalar subqueries in a later installment. Suffice it to say for this article’s purposes that using a scalar subquery is another method you could employ to achieve the result obtained in Listing 1. However, it would perform significantly more slowly and its syntax would be more difficult to write than the analytic query line in Listing 1.

Code Listing 2: Obtain a cumulative salary total, row by row, by department

SQL> select last_name, first_name, department_id, salary,

 2     SUM (salary)

 3    OVER (PARTITION BY department_id ORDER BY last_name, first_name) department_total

 4    from employee

 5  order by department_id, last_name, first_name;

.