PostgreSQL Partition By Example

The “Partition By” clause or function of PostgreSQL belongs to the Window Functions category. The Window Functions of PostgreSQL are the ones that are capable of performing calculations that span multiple rows of a column but not all the rows. It means that unlike the aggregate functions of PostgreSQL, the Windows Functions do not necessarily produce a single value as output. Today, we wish to explore the usage of the “Partition By” clause or function of PostgreSQL in Windows 10.

PostgreSQL Partition By Examples in Windows 10:

This function displays the output in the form of partitions or categories with respect to the specified attribute. This function simply takes one of the attributes of the PostgreSQL table as an input from the user and then displays the output accordingly. However, the “Partition By” clause or function of PostgreSQL is the most suitable for large data sets and not for the ones in which you cannot identify distinct partitions or categories. You will have to go through the two examples discussed below to understand the usage of this function in a better way.

Example # 1: Extracting the Average Body Temperature from Patients’ Data:

For this particular example, our goal is to find out the average body temperature of the patients from the “patient” table. You might wonder if we can simply use the “Avg” function of PostgreSQL to do so, then why are we even using the “Partition By” clause over here. Well, our “patient” table also consists of a column named “Doc_ID” which is there to specify which doctor treated a particular patient. As far as this example is concerned, then we are interested in seeing the average body temperatures of the patients treated by each doctor.

This average will be different for each doctor since they attended to different patients having different body temperatures. That is why the use of the “Partition By” clause is mandatory in this situation. Moreover, we are going to use an already existing table for demonstrating this example. You can also create a new one if you want. You will be able to understand this example well by going through the following steps:

Step # 1: Viewing the Data that the Patient Table holds:

Since we have already stated that we are going to use an already existing table for this example, we will try to display its data first so that you can take a look at the attributes that this table has. For that, we will execute the query shown below:

# SELECT * FROM patient;

You can see from the following image that the “patient” table has four attributes, i.e., Pat_ID (refers to the patient ID), Pat_Name (holds the name of the patient), Pat_Temp (refers to the body temperature of the patient), and Doc_ID (refers to the doctor’s ID who treated a particular patient).

Step # 2: Extracting the Average Body Temperature of Patients with respect to the Doctor who attended to them:

For finding out the average body temperature of patients partitioned by the doctor who attended to them, we will execute the query stated below:

# SELECT Pat_ID, Pat_Name, Pat_Temp, Doc_ID, avg(Pat_Temp) OVER (PARTITION BY Doc_ID) FROM patient;

This query will calculate the average of the patients’ temperature concerning the doctor who attended to them and then simply displays it along with the other attributes on the console as shown in the following image:

Since we had five different doctor IDs, we managed to calculate the averages of five different partitions through this query, i.e., 99.5, 99, 101.5, 99.5, and 105.5, respectively.

Example # 2: Extracting the Average, Minimum, and Maximum Prices belonging to each Dish Type from Meal Data:

In this example, we want to find out the average, minimum, and maximum prices of each dish with respect to the dish type from the “meal” table. Again, we will use an already existing table to demonstrate this example; however, you are free to create a new table if you want. You will get a clearer idea of what we are talking about after going through the steps mentioned below:

Step # 1: Viewing the Data that the Meal Table holds:

# SELECT * FROM meal;

You can see from the following image that the “meal” table has three attributes, i.e., Dish_Name (refers to the name of the dish), Dish_Type (holds the type to which the dish belongs, i.e., Main Course, Starter, or Dessert), and Dish_Price (refers to the price of the dish).

Step # 2: Extracting the Average Dish Price of the Dish with respect to the Dish Type it belongs to:

For finding out the average dish price of the dish partitioned by the dish type to which it belongs, we will execute the query stated below:

# SELECT Dish_Name, Dish_Type, Dish_Price, avg(Dish_Price) OVER (PARTITION BY Dish_Type) FROM meal;

This query will calculate the average price of the dishes with respect to the dish type to which they belong and then simply display it along with the other attributes on the console as shown in the following image:

Since we had three different dish types, we managed to calculate the averages of three different partitions through this query, i.e., 155, 241.67, and 261.67, respectively.

Step # 3: Extracting the Minimum Dish Price of the Dish with respect to the Dish Type it belongs to:

Now, on similar grounds, we can extract the minimum dish price with respect to each dish type simply by executing the query stated below:

# SELECT Dish_Name, Dish_Type, Dish_Price, min(Dish_Price) OVER (PARTITION BY Dish_Type) FROM meal;

This query will calculate the minimum price of the dishes with respect to the dish type to which they belong and then simply display it along with the other attributes on the console as shown in the following image:

Step # 4: Extracting the Maximum Dish Price of the Dish with respect to the Dish Type it belongs to:

Finally, in the very same manner, we can extract the maximum dish price with respect to each dish type simply by executing the query stated below:

# SELECT Dish_Name, Dish_Type, Dish_Price, max(Dish_Price) OVER (PARTITION BY Dish_Type) FROM meal;

This query will calculate the maximum price of the dishes with respect to the dish type to which they belong and then simply display it along with the other attributes on the console as shown in the following image:

Conclusion:

This article was intended to give you an overview of the usage of the PostgreSQL “Partition By” function. For doing so, we first introduced you to the PostgreSQL Window Functions, followed by a brief description of the “Partition By” function. Finally, to elaborate the usage of this function in PostgreSQL in Windows 10, we presented you with two different examples with the help of which you can easily learn the usage of this PostgreSQL function in Windows 10.

PostgreSQL Partition By Example