20:16

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

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

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;

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

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

Execute

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

Suggested exercises: 109, 134

Previous [Paging records ]

[LAG and LEAD functions] Next

Last added:

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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

https://exchangesumo.com/obmen/ALPCNY-WEXRUB/ . ремонт ноутбука

SQL Problems and solutions

S. I. Moiseenko

New sql features

Window functions

Other examples of using window functions