试图了解over()和分区 [英] Trying to understand over() and partition by
问题描述
我试图通过包裹在我头上的功能来解决问题.这是一个我不明白的例子.
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.
这是我的数据:
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
当我运行此查询时:
select Row_Number() over(partition by orderdate order by orderdate asc)
as Rownumber, salesorderid, orderdate
from test2
order by rownumber
这是我得到的结果:
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
有人可以向我解释此查询.我对SQL并不陌生,但是我一直在苦苦挣扎的窗口工作,无法解决这个问题.
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.
推荐答案
尝试按订购日期订购,您会更轻松地看到结果
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.
在分区中,行按照orderdate排序,如'((按orderdate排序,按orderdate asc)排序.这不是很有用,因为分区中的所有行都将具有相同的orderdate.因此,分区中行的顺序是随机的.尝试在partition by子句中按salesorderid进行排序,以获得更可重复的结果.
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.
row_number()仅返回行在每个分区内的顺序
row_number() just returns the row's ordering within each partition
这篇关于试图了解over()和分区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!