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:

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:

SELECT max_sum, type, date, point
FROM (  
SELECT max(inc) over() AS max_sum, *  
FROM (  
  SELECT inc, 'inc' type, date, point FROM Income   
  UNION ALL   
  SELECT inc, 'inc' type, date, point FROM Income_o   
  UNION ALL   
  SELECT out, 'out' type, date, point FROM Outcome_o   
  UNION ALL   
  SELECT out, 'out' type, date, point FROM Outcome   
) X   
) Y  
WHERE inc = max_sum;

Consider another example.

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:

SELECT *, price - AVG(price) OVER(PARTITION BY speed) AS dprice   
FROM PC;

Other solution to this problem can be obtained by means of a correlated subqueries.

SELECT *, price - (SELECT AVG(price) FROM PC AS PC1 WHERE PC1.speed = PC.speed) AS dprice   
FROM PC;

Suggested exercises: 109, 134