   18:20

# 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:  Console
`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 ) YWHERE inc = max_sum;`

max_sum type date point
18000.00 inc 2001-10-02 00:00:00.000 3

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:  Console
`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.  Console
`SELECT *, price - (SELECT AVG(price) FROM PC AS PC1 WHERE PC1.speed = PC.speed) AS dprice FROM PC;`

Suggested exercises109, 134  