loading..
Русский    English
02:11

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
Execute
  1. SELECT max_sum, type, date, point
  2. FROM (
  3. SELECT MAX(inc) over() AS max_sum, *
  4. FROM (
  5.   SELECT inc, 'inc' type, date, point FROM Income
  6.   UNION ALL
  7.   SELECT inc, 'inc' type, date, point FROM Income_o
  8.   UNION ALL
  9.   SELECT out, 'out' type, date, point FROM Outcome_o
  10.   UNION ALL
  11.   SELECT out, 'out' type, date, point FROM Outcome
  12. ) X
  13. ) Y
  14. WHERE 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
Execute
  1. SELECT *, price - AVG(price) OVER(PARTITION BY speed) AS dprice
  2. FROM PC;

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

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

Suggested exercises109, 134

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100