Other examples of using window functions
The use of window functions, as well as CTE, can reduce the amount of code. Return to exercise, on which we have demonstrated the benefits of CTE:
Find the maximum amount of income/outcome among all 4 tables in the database "Recycled materials company”, as well as the type of operation, date and point of reception, when and where it was recorded.
Use the following algorithm. To query results, which unites all the operations of 4 database tables "Recycled Materials Company", add a column that is using the window function MAX to determine the maximum amount. Then we will select those rows in which the amount of the operation coincides with the maximum value:
Consider another example.
For each PC from the PC table to find difference between its price and average price for a model with the same value of CPU speed.
Here, unlike in previous exercises, is required to split the computers into groups with the same speed, which we implement by using the PARTITION BY clause. It is the speed of the current row of the table will determine the group to calculate the average value. Decision:
Other solution to this problem can be obtained by means of a correlated subqueries.