9 Focus nineth Chapter open window function and table rotation
Summary: Because the starting point of the window function is the result set of the underlying query, and the result set of the underlying query is only generated when the Select stage is available, the window function can only be used for the SELECT and ORDER BY clauses of the query. 1. Window functions include: Ranking window function (row_number (), rank (), etc.), aggregate window functions such as (avg,sum, etc.). In SQL Server 2012, 4 offset window functions are also available: LAG, lead, First_value and Last_value In addition, 2012 starts with support for sorting and framing of aggregations, simplifying the calculation of running aggregations. Benefits: Look at the two formulations are the same result, and the window function generated by the execution plan efficiency than grouping then associated high
2012 start to sort with frames
Table Rotation: PIVOT
Attach the test code:
IF object_id (' dbo. Students ', ' U ') is not nulldrop TABLE dbo. Studentsgo
CREATE TABLE dbo. Students (ClassID int, studentname nvarchar (), achievement Numeric (5,2))
INSERT into dbo. Studentsvalues (1, ' Grace ', 99.00), (1, ' Andrew ', 99.00), (1, ' Janet ', 75.00), (1, ' Margaret ', 89.00), (2, ' Stev En ', 86.00), (2, ' Michael ', 72.00), (2, ' Robert ', 91.00), (3, ' Laura ', 75.00), (3, ' Ann ', 94.00), (3, ' Ina ') , 80.00), (3, ' Ken ', 92.00)
SELECT ClassID, Studentname, achievement, AVG (achievement) over (PARTITION by ClassID ORDER by Achi Evement DESC ROWS between unbounded preceding and current ROW-this framework clause is the limit to calculate the average time is greater than The meaning of the beginning of the partition to the current line is equal to the score of our people to statistics, is a dynamic statistic. ) as Avg_class from dbo. Students
SELECT stu.*,temp. Avg_achievement from dbo. Students Stu Join (SELECT classid,avg (achievement) as avg_achievement from dbo. Students Group by ClassID) temp on Stu. ClassID = temp. ClassID
All in all, the open window function makes it possible to aggregate multiple partitions, that is, one statement multiple partition grouping aggregation SQL SERVER provides aggregate functions including avg,checksum_agg,count,count_big,grouping, Grouping_id,max /min/sum/stdev/stdevp/var/varp other than GROUPING and grouping_id can be followed by the aggregate calculation for the window after the over clause. Original link This article by Bean John Blog Backup expert remote One click release
9 Focus nineth Chapter open window function and table rotation (reprint)
