{"id":1072,"date":"2024-12-19T09:42:28","date_gmt":"2024-12-19T02:42:28","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1072"},"modified":"2024-12-25T08:41:33","modified_gmt":"2024-12-25T01:41:33","slug":"postgresql-window-functions","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/","title":{"rendered":"PostgreSQL Window Functions"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about PostgreSQL window functions and how to use them effectively to construct powerful queries.<\/p>\n\n\n\n

Getting Started with PostgreSQL Window Functions #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a window function allows you to calculate across a set of rows related to the current row.<\/p>\n\n\n\n

If it sounds confusing to you, let’s take an example to make the concept more clear.<\/p>\n\n\n\n

Suppose you want to retrieve the product name, price, and highest price from the products<\/code> table:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you use the MAX<\/code> aggregate function, you’ll get a single row:<\/p>\n\n\n

SELECT<\/span>\n  MAX(price)\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   max\n---------<\/span>\n 2999.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the product and price for each row together with the max price, you can use the MAX<\/code> window function:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  price,\n  MAX(price) OVER<\/span> ()\nFROM<\/span>\n  products\nORDER<\/span> BY<\/span>\n  price DESC<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        product_name        |  price  |   max\n----------------------------+---------+---------<\/span>\n Samsung QN900C Neo QLED    | 2999.99<\/span> | 2999.99<\/span>\n LG G3 OLED                 | 2499.99<\/span> | 2999.99<\/span>\n Sony Bravia XR A95K        | 2499.99<\/span> | 2999.99<\/span>\n LG OLED TV C3              | 1999.99<\/span> | 2999.99<\/span>\n Samsung Galaxy Z Fold 5<\/span>    | 1799.99<\/span> | 2999.99<\/span>\n Lenovo ThinkPad X1 Carbon  | 1599.99<\/span> | 2999.99<\/span>\n...<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the OVER<\/code> clause makes the MAX<\/code> function a window function.<\/p>\n\n\n\n
The MAX<\/code> window function returns the maximum price for each row in the products table.<\/p>\n\n\n\n
In practice, you’ll find the window functions helpful for queries like calculating running total, ranking, and moving averages.<\/p>\n\n\n\n
Note that analytic function<\/em> is another term often used to refer to window function.<\/p>\n\n\n\n
PostgreSQL Window Function Syntax #<\/a><\/h2>\n\n\n\n
Here’s the basic syntax for a window function:<\/p>\n\n\n
function_name (expression) OVER<\/span> (\n    [PARTITION<\/span> BY<\/span> expression_list]\n    [ORDER<\/span> BY<\/span> expression_list]\n    [frame_clause]\n)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
Unlike the MAX<\/a><\/code> aggregate function, the MAX<\/code> window function does not group rows but retains individual rows.<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n