SQL 试图理解 over() 和 partition by
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9315070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trying to understand over() and partition by
提问by Luke101
I am trying to get the over and partition by functionality wrapped around my head. Here is an example that I just do not understand.
我试图通过围绕在我头上的功能来解决和分区。这是一个我只是不明白的例子。
Here is the data I have:
这是我拥有的数据：
SALESORDERID ORDERDATE
43894 08/01/2001
43664 07/01/2001
43911 08/01/2001
43867 08/01/2001
43877 08/01/2001
44285 10/01/2001
44501 11/01/2001
43866 08/01/2001
43895 08/01/2001
43860 08/01/2001
When I run this query:
当我运行此查询时：
select Row_Number() over(partition by orderdate order by orderdate asc)
as Rownumber, salesorderid, orderdate
from test2
order by rownumber
Here are the results I get:
以下是我得到的结果：
ROWNUMBER SALESORDERID ORDERDATE
1 43664 07/01/2001
1 43911 08/01/2001
1 44109 09/01/2001
1 44483 11/01/2001
1 44285 10/01/2001
2 43867 08/01/2001
2 44501 11/01/2001
3 43895 08/01/2001
4 43894 08/01/2001
5 43877 08/01/2001
Can someone explain this query to me. I am not new to SQL but windowing I have been struggling with and can't get my head wrapped around this.
有人可以向我解释这个查询。我对 SQL 并不陌生，但我一直在努力解决窗口问题，无法解决这个问题。
回答by Chi
Try ordering by order date, you'll see the results more easily
尝试按订单日期订购，您会更容易看到结果
select Row_Number() over(partition by orderdate order by orderdate asc)
as Rownumber, salesorderid, orderdate
from test2
order by orderdate;
should give (i've added blank lines for clarity)
应该给（为了清楚起见，我添加了空行）
ROWNUMBER SALESORDERID ORDERDATE
1 43664 07/01/2001
1 43911 08/01/2001
2 43867 08/01/2001
3 43895 08/01/2001
4 43894 08/01/2001
5 43877 08/01/2001
1 44109 09/01/2001
1 44285 10/01/2001
1 44483 11/01/2001
2 44501 11/01/2001
You'll notice that the result is divided into 'partitions', each partition being the set of rows with identical orderdates. That is what 'partition by orderdate' means.
您会注意到结果被划分为“分区”，每个分区是一组具有相同订单日期的行。这就是“按订单日期分区”的意思。
Within a partition, the rows are ordered by orderdate, as per the second clause of '(partition by orderdate order by orderdate asc)'. That isn't very useful, as all rows within a partition are going to have the same orderdate. Because of that, the ordering of the rows within a partition is random. Try ordering by salesorderid within the partition by clause to have a more reproducable result.
在分区内，行按 orderdate 排序，如“(partition by orderdate order by orderdate asc)”的第二个子句。这不是很有用，因为分区中的所有行都将具有相同的 orderdate。因此，分区内行的顺序是随机的。尝试在 partition by 子句中按 salesorderid 排序以获得更可重现的结果。
row_number() just returns the row's ordering within each partition
row_number() 只返回每个分区中行的顺序
回答by ruakh
The partition by orderdatemeans that you're only comparing records to other records with the same orderdate. For example, of the five records with orderdate = '08/01/2001', one will have row_number() = 1, one will have row_number() = 2, and so on.
这partition by orderdate意味着您只是将记录与具有相同orderdate. 例如，在带有 的五个记录中orderdate = '08/01/2001'，一个将有row_number() = 1，一个将有row_number() = 2，等等。
The order by orderdate ascmeans that, within a partition, row-numbers are to be assigned in order of orderdate. In your example that has no effect, because you're already partitioning by orderdate, so all records within a partition will have the same orderdate. (It would be like writing SELECT ... FROM t WHERE c = 6 ORDER BY c: all selected records have the same value of c, so the ORDER BY cdoes nothing.) So, within a partition, the assignment of row_number()is arbitrary: each row will have a different number, but there are no guarantees about which row will have which number.
这order by orderdate asc意味着，在一个分区内，要按 的顺序分配行号orderdate。在您的示例中没有任何影响，因为您已经按 进行了分区orderdate，因此分区内的所有记录都将具有相同的orderdate. （这就像写SELECT ... FROM t WHERE c = 6 ORDER BY c：所有选定的记录都有相同的值c，所以ORDER BY c什么都不做。）所以，在一个分区内， 的分配row_number()是任意的：每一行都有不同的数字，但不能保证哪一行会有哪个号码。
