Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Window Functions

SQL Server Usage

Window functions use an OVER clause to define the window and frame for a data set to be processed. They are part of the ANSI standard and are typically compatible among various SQL dialects. However, most RDBMS don’t yet support the full ANSI specification.

Window functions are a relatively new, advanced, and efficient T-SQL programming tool. They are highly utilized by developers to solve numerous programming challenges.

SQL Server currently supports the following window functions:

Syntax

<Function()>
OVER
(
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)

Examples

The following example creates and populates an OrderItems table.

CREATE TABLE OrderItems
(
  OrderID INT NOT NULL,
  Item VARCHAR(20) NOT NULL,
  Quantity SMALLINT NOT NULL,
  PRIMARY KEY(OrderID, Item)
);

INSERT INTO OrderItems (OrderID, Item, Quantity)
VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200),
(3, 'M6 Locking Nut', 300);

The following example uses a window ranking function to rank items based on the ordered quantity.

SELECT Item,
  Quantity,
RANK() OVER(ORDER BY Quantity) AS QtyRank
FROM OrderItems;

The preceding example produces the following results.

Item            Quantity  QtyRank
M8 Bolt         100       1
M8 Nut          100       1
M8 Washer       200       3
M6 Locking Nut  300       4

The following example uses a partitioned window aggregate function to calculate the total quantity for each order. This statement doesn’t use a GROUP BY clause.

SELECT Item,
Quantity,
OrderID,
SUM(Quantity)
OVER (PARTITION BY OrderID) AS TotalOrderQty
FROM OrderItems;

The preceding example produces the following results.

Item            Quantity  QtyRank  TotalOrderQty
M8 Bolt         100       1        100
M8 Nut          100       2        100
M6 Locking Nut  300       3        500
M8 Washer       200       3        500

The following example uses an analytic LEAD function to get the next largest quantity for the order.

SELECT Item,
  Quantity,
  OrderID,
  LEAD(Quantity)
  OVER (PARTITION BY OrderID ORDER BY Quantity) AS NextQtyOrder
FROM OrderItems;

The preceding example produces the following results.

Item            Quantity  OrderID  NextQtyOrder
M8 Bolt         100       1        NULL
M8 Nut          100       2        NULL
M8 Washer       200       3        300
M6 Locking Nut  300       3        NULL

For more information, see SELECT - OVER Clause (Transact-SQL) in the SQL Server documentation.

PostgreSQL Usage

PostgreSQL refers to ANSI SQL analytical functions as window functions. They provide the same core functionality as SQL Server analytical functions. Window functions in PostgreSQL operate on a logical partition or window of the result set and return a value for rows in that window.

From a database migration perspective, you should examine PostgreSQL window functions by type and compare them with the equivalent SQL Server window functions to verify compatibility of syntax and output.

PostgreSQL provides support for two main types of window functions: aggregation functions and ranking functions.

PostgreSQL Window Functions by Type

PostgreSQL Window Functions

Examples

The following example uses he PostgreSQL rank() function.

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees WHERE department_id = 80;

DEPARTMENT_ID  LAST_NAME  SALARY    COMMISSION_PCT  Rank
80             Russell    14000.00  0.40            1
80             Partners   13500.00  0.30            2
80             Errazuriz  12000.00  0.30            3

The returned formatting for certain numeric data types is different.

The following example calculates the total salary for the department 80.

SELECT SUM(salary)
FROM employees WHERE department_id = 80;

SUM(SALARY)
39500.00

The following example creates and populates an OrderItems table.

CREATE TABLE OrderItems
(
  OrderID INT NOT NULL,
  Item VARCHAR(20) NOT NULL,
  Quantity SMALLINT NOT NULL,
  PRIMARY KEY(OrderID, Item)
);

INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES
(1, 'M8 Bolt', 100),
(2, 'M8 Nut', 100),
(3, 'M8 Washer', 200),
(3, 'M6 Locking Nut', 300);

The following example uses a window ranking function to rank items based on the ordered quantity.

SELECT Item, Quantity, RANK()
  OVER(ORDER BY Quantity) AS QtyRank
FROM OrderItems;

Item            Quantity  QtyRank
M8 Bolt         100       1
M8 Nut          100       1
M8 Washer       200       3
M6 Locking Nut  300       4

The following example uses a partitioned window aggregate function to calculate the total quantity for each order. This statement doesn’t use a GROUP BY clause.

SELECT Item, Quantity, OrderID, SUM(Quantity)
  OVER (PARTITION BY OrderID) AS TotalOrderQty
FROM OrderItems;

Item            Quantity  OrderID  TotalOrderQty
M8 Bolt         100       1        100
M8 Nut          100       2        100
M6 Locking Nut  300       3        500
M8 Washer       200       3        500

The following example uses an analytic LEAD function to get the next largest quantity for the order.

SELECT Item, Quantity, OrderID, LEAD(Quantity)
  OVER (PARTITION BY OrderID ORDER BY Quantity) AS NextQtyOrder
FROM OrderItems;

Item            Quantity  OrderID  NextQtyOrder
M8 Bolt         100       1        NULL
M8 Nut          100       2        NULL
M8 Washer       200       3        300
M6 Locking Nut  300       3        NULL

For more information, see Window Functions in the PostgreSQL documentation.