Practical SQL by Anthony DeBarros

Author:Anthony DeBarros
Language: eng
Format: epub, pdf
Publisher: Penguin Random House LLC (Publisher Services)
Published: 2018-04-24T04:00:00+00:00

SELECT

category,

store,

unit_sales,

➊ rank() OVER (PARTITION BY category ORDER BY unit_sales DESC)

FROM store_sales;

Listing 10-7: Applying rank() within groups using PARTITION BY

In the table, each row includes a store’s product category and sales for that category. The final SELECT statement creates a result set showing how each store’s sales ranks within each category. The new element is the addition of PARTITION BY in the OVER clause ➊. In effect, the clause tells the program to create rankings one category at a time, using the store’s unit sales in descending order. Here’s the output:

category store unit_sales rank

--------- ------- ---------- ----

Beer Wallace 988 1

Beer Broders 641 2

Beer Cramers 640 3

Cereal Broders 1104 1

Cereal Cramers 1003 2

Cereal Wallace 980 3

Ice Cream Broders 2517 1

Ice Cream Cramers 2112 2

Ice Cream Wallace 1863 3

Notice that category names are ordered and grouped in the category column as a result of PARTITION BY in the OVER clause. Rows for each category are ordered by category unit sales with the rank column displaying the ranking.

Using this table, we can see at a glance how each store ranks in a food category. For instance, Broders tops sales for cereal and ice cream, but Wallace wins in the beer category. You can apply this concept to many other scenarios: for example, for each auto manufacturer, finding the vehicle with the most consumer complaints; figuring out which month had the most rainfall in each of the last 20 years; finding the team with the most wins against left-handed pitchers; and so on.

SQL offers additional window functions. Check the official PostgreSQL documentation at https://www.postgresql.org/docs/current/static/tutorial-window.html for an overview of window functions, and check https://www.postgresql.org/docs/current/static/functions-window.html for a listing of window functions.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.