T-SQL学习中--窗口函数

http://www.minxue.net/94/n-46794.html 这里有本比较详细的书。

几道习题和问答：
1.What are the clauses that the different types of window function support?
不同种类的窗口函数支持哪些类型的子句？
Answer: Partitioning, ordering, and framing clauses.
分区子句、排序子句以及取帧范围子句。
2.What do the delimiters UNBOUNDED PRECEDING and UNBOUNEDE FOLLOWING represent?
定界符UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING代表什么？
The beginning and end of the partition, respectively.
分别代表分区的开头和结尾。

3 What is the default frame window functions use when a window order clause is specified but an explicit window frame clause isn't(Choose all that apply)
如果指定了一个窗口排序子句，但是没有指定定明确的窗口帧范围子句，窗口函数所用的默认帧范围是什么？
A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
B. ROWS UNBOUNDED PRECEDING.
C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
D. RANGE UNBOUNEDE PRECEDING.
答案：C，D
A. Incorrect: The default frame is based on the RANGE unit
默认帧是基于RANGE单位的
B. Incorrect: The default frame is based on the RANGE unit.
C. Correct: This the default frame.
D. Correct: This an abbreviated form of the default frame, having the same meaning.
2.What do the RANK and DENSE_RANK functions compute?
RANK函数和DENSE_RANK函数分别计算什么？
A. The RANK function returns the number of rows that have a lower ordering value( assuming ascending ordering) than the current; the DENSE_RANK function returns the number of distinct ordering values that are lower than the current.
RANK函数返回排序上比当前行更小的排序值的行的数目。而DENSE_RANK函数返回当前行更小的不重复排序值的行的数目。
B. The RANK function returns one more than the number of rows that have a lower ordering value that the current; the DENSE_RANK function returns one more that the number of distinct ordering values that are lower than the current.
RANK函数返回排序上比当前行更小的排序值的行的数目增一。而DENSE_RANK函数返回当前行更小的不重复排序值的行的数目增一。
C. The RANK function return one less than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one less than the number of distinct ordering values that are lower than the current.
RANK函数返回排序上比当前行更小的排序值的行的数目减一。而DENSE_RANK函数返回当前行更小的不重复排序值的行的数目减一。
D. The two functions return the same result unless that ordering is unique.
除非排序是唯一的，否则它们返回同样的结果。
答案：
A. Incorrect: The definitions are one less than the correct ones;
这个定义比正确值少了1
B. Correct: These are the correct definitions.
这是正确的定义
C. Incorrect: The definitions are two less than the correct ones;
这个定义比正确值少了2
D. Incorrect: The opposite is true-- the two function return the same result when the ordering is unique
反过来才是对的：当排序是唯一的时候，这两个函数返回同样的结果。
3.Why are window functions allowed only in the SELECT and ORDER BY clauses of a query?
为什么窗口函数只允许用在查询的SELECT以及ORDER BY子句中？
A. Because they are supposed to operate on the underlying query's result, which is achieved when logical query pressing gets to the SELECT phase.
因为他们应该操作底层查询的结果,这是在逻辑查询进行到SELECT阶段时实现。
B. Because Microsoft didn't have time to implement them in other clauses.
因为微软没有时间把它们实现到别的子句
C. Because you never need to filter or group data based on the result of window functions.
因为你不需要基于窗口函数筛选或编组数据
D. Because in the other clause, the functions are considered door functions (also known as backdoor functions).
因为在别的子句中，函数被视为门函数（又称后门函数）
答案：
A. Correct: Windows function are supposed to operatoe on the underlying query's result set. In terms of logical query processing, this result set is reached in the SELECT phase.
在逻辑查询进行时，结果集在SELECT阶段遇到。
B. Incorrect: Standard SQL defined this restriction, so it has nothing to do with Microsoft's time constraints.
标准的SQL定义这一限制。所以它和微软的时间无关。
C. Incorrect: There are practical reasons to want to filter or group data based on the results of window functions.
基于窗口函数的结果进行筛选或编组操作有其实际应用理由。
D. Incorrect: There are neither door function nor backdoor function in SQL.
SQL中既没有门函数也没有后门函数。
You are interviewed for a position as a T-SQL developer. Respond to the following questions presented to you by your interview.
你在面试一个T-SQL开发员的职位，请回答下面几个面试管给你的问题
1. Describe the difference between ROW_NUMBER and RANK.
描述ROW_NUMBER和RANK的区别。
Thw ROW_NUMBER function isn't sensitive to ties in the window order values. Therefore, the computation is deterministic only when the window ordering is unique. When the window ordering isn't unique, the function isn't deterministic. The RANK function is sensitive to ties and produces the same rank value to all rows with the same ordering value. Therefore, it is deterministic even when the window ordering isn't unique.
ROW_NUMBER函数对窗口排序值的约束不敏感。然而，当窗口排序值是唯一时候，这个计算值是确定的。
当窗口排序值不是唯一值时，这个函数值也是不确定的。RANK函数对约束第三，而且对多行里有相同排序值，rank函数会对每行制造出同样的级别值。因此，就算窗口排序不是唯一的，它的结果也是确定的。
2.Describe the difference between the ROWS and RANAGE window frame units.
描述ROWS和RANGE两个窗口取帧范围单位的区别。
The difference between RoWS and RANGE is actually similar to the difference between ROW_NUMBER and RANK, respectively. When the window ordering isn't unique, ROWS doesn't include peers, and therefore it isn't deterministic, whereas RANGE includes peers, and therefore it is deterministic. Also, the ROWS option can be optimized with an efficient in-memory spool; RANGE is optimized with an on-disk spool and therefore is usually slower.
ROWS和RANGE函数的区别各自近似于ROW_NUMBER和RANK函数。当窗口排序不唯一时，ROW不包含平辈，而且它也不是确定的。然而RANGE包括平辈，而且它确定的。而且，ROWS选项可以内存池中优化效率。RANGE可以在一个磁盘池中被优化，而且它经常很慢
3.Why can you not refer to a window function in the WHERE clause of a query and what is the workaround for that?
为什么你不能在一个查询的WHERE子句中引用窗口函数？要实现它该如何变通？
Window function are allowed only in the SELECT and ORDER BY clauses because the initial window they are supposed to work with is the underlying query's result set. If you need to filter rows based on a window function, you need to use a table expression like CTE or derived table. You specify the window function in the inner query's SELECT clause and assign the target column an alias. You can then filter the rows by referring to that column alias in the outer query's WHERE clause.
窗口函数只在SELECT和ORDER BY子句中允许使用。因为初始化窗口就是潜在的查询结果集。如果你必须基于窗口函数筛选行，你必须使用一个表表达式，比如说CTE或者派生表。 你可以在行内查询的SELECT子句中指定窗口函数，并给目标列分配一个别名。你可以在外查询的WHERE子句中通过引用这个列别名来筛选它。