From the course: PostgreSQL: Advanced Queries

Unlock the full course today

Join today to access over 24,800 courses taught by industry experts.

Streamline partition queries with a WINDOW clause

Streamline partition queries with a WINDOW clause - PostgreSQL Tutorial

From the course: PostgreSQL: Advanced Queries

Start my 1-month free trial Buy for my team

Streamline partition queries with a WINDOW clause

“

- If you write a query that includes multiple window functions, it's not uncommon to use the same partition over and over again. For instance, you may want to review the average, minimum and maximum price for each product within the same size classification. Using the OVER clause and a window frame partition, it might look like this. We'll pull the sku, product name, category ID, size and price columns from inventory.products, and we'll calculate the average min and max price over a partition by the product size. When I execute the query, the results give you these statistical details about the size group that each product is a member of. But there's a lot of repetition with the same over clause written out several times. If you wanted to alter the query, to look at a different partition, you'd have to update all three lines. This process can be simplified by specifying the window a single time within…

Contents