Revision history [back]

To use a window function in Postgres to obtain a value from another column, you can use the syntax:

SELECT column1, column2, window_function(column3) OVER (PARTITION BY column4 ORDER BY column5)
FROM table_name;

In this example, column1, column2, and column3 are columns in table_name. The window_function is the function you want to use, such as SUM, AVG, or MAX. The OVER clause is used to define the window over which the function will be applied. PARTITION BY is used to divide the result set into partitions, and ORDER BY is used to sort the rows within each partition.

You can replace column3 with the name of the column that contains the value you want to use in the window function. For example, if you want to calculate the running total of sales, you can use the following query:

SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total
FROM sales_table;

In this query, the SUM window function is applied over the sales column and is partitioned by the date column. The OVER (ORDER BY date) clause is used to calculate the running total in date order.