PARTITION BY
用于分隔数据集,这使得您能够独立地处理与之相关的行集(如ROW_NUMBER()、COUNT()、SUM()等)。在您的查询中,相关的数据集由具有相似cdt.country_code、cdt.account和cdt.currency的行组成。当您在这些列上进行分区并对它们应用ROW_NUMBER时,这些组合/集上的其他列将从ROW_NUMBER接收连续编号。
但是,如果您按一些唯一的数据进行分区,并在其上放置一个row_number,那么查询就会很有趣,因为它将只产生相同的数字。这就像您在一个保证唯一的分区上进行ORDER BY。例如,将GUID视为cdt.country_code、cdt.account、cdt.currency
的唯一组合。
newid()
生成GUID,那么您对此表达式有什么期望呢?
select
hi,ho,
row_number() over(partition by newid() order by hi,ho)
from tbl;
……所有已分区的行(没有进行分区,每行都在其自己的行中分区)的行号都被设为 1。
基本上,你应该在非唯一列上进行分区。在 OVER 子句上使用 ORDER BY 时,PARTITION BY 需要有一个非唯一组合,否则所有行号将变为 1。
举个例子,这是你的数据:
create table tbl(hi varchar, ho varchar);
insert into tbl values
('A','X'),
('A','Y'),
('A','Z'),
('B','W'),
('B','W'),
('C','L'),
('C','L');
那么这类似于您的查询:
select
hi,ho,
row_number() over(partition by hi,ho order by hi,ho)
from tbl;
那会是什么输出?
HI HO COLUMN_2
A X 1
A Y 1
A Z 1
B W 1
B W 2
C L 1
C L 2
你看到了HI HO的组合吗?前三行有独特的组合,因此它们被设置为1,B行具有相同的W,因此具有不同的ROW_NUMBERS,HI C行也是如此。
那么,为什么需要在那里使用ORDER BY
呢?如果之前的开发人员只想在类似数据上放置一行号(例如,HI B所有数据都是B-W,B-W),他可以这样做:
select
hi,ho,
row_number() over(partition by hi,ho)
from tbl;
然而,遗憾的是,Oracle(以及Sql Server)不允许没有ORDER BY
的分区;而在PostgreSQL中,PARTITION上的ORDER BY
是可选的:http://www.sqlfiddle.com/#!1/27821/1
select
hi,ho,
row_number() over(partition by hi,ho)
from tbl;
你的分区上的 ORDER BY
看起来有点多余,这不是之前开发人员的错,有些数据库不允许没有 ORDER BY
的 PARTITION
,他可能找不到一个好的候选列进行排序。如果 PARTITION BY 列和 ORDER BY 列都相同,只需移除 ORDER BY,但由于某些数据库不允许这样做,你可以这样做:
SELECT cdt.*,
ROW_NUMBER ()
OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
ORDER BY newid())
seq_no
FROM CUSTOMER_DETAILS cdt
你找不到一个好的列来对类似的数据进行排序?那么干脆乱序排列吧,划分后的数据
值相同。例如,你可以使用GUID(在SQL Server中使用
newid()
)。这样就能产生与之前开发人员生成的相同输出,遗憾的是有些数据库不允许在没有
ORDER BY
的情况下使用
PARTITION
。
但实际上,我不明白为什么要在相同的组合上加上数字(例如上面的B-W,B-W),这给数据库带来了冗余数据的印象。这让我想起了这个问题:如何从表格的相同记录列表中获取一条唯一记录?表格中没有唯一约束条件
看到PARTITION BY和ORDER BY使用相同的列时,真的很玄妙,不容易推断代码的意图。
在线测试:http://www.sqlfiddle.com/#!3/27821/6
但正如dbaseman也注意到的那样,按相同的列进行分区和排序是无用的。
你有一组像这样的数据:
create table tbl(hi varchar, ho varchar);
insert into tbl values
('A','X'),
('A','X'),
('A','X'),
('B','Y'),
('B','Y'),
('C','Z'),
('C','Z');
然后你按 hi,ho 进行分区,接着再按 hi,ho 进行排序。对于相似的数据没有标号的意义 :-) http://www.sqlfiddle.com/#!3/29ab8/3
select
hi,ho,
row_number() over(partition by hi,ho order by hi,ho) as nr
from tbl;
输出:
HI HO ROW_QUERY_A
A X 1
A X 2
A X 3
B Y 1
B Y 2
C Z 1
C Z 2
看到了吗?为什么需要在同一组合上放置行号?你将分析三个A、X,两个B、Y和两个C、Z上的什么? :-)
你只需要在非唯一列上使用“PARTITION”,然后按非唯一列的“唯一”列进行排序。例子会让它更清晰:
create table tbl(hi varchar, ho varchar);
insert into tbl values
('A','D'),
('A','E'),
('A','F'),
('B','F'),
('B','E'),
('C','E'),
('C','D');
select
hi,ho,
row_number() over(partition by hi order by ho) as nr
from tbl;
PARTITION BY hi
作用于非唯一列,在每个分区内,根据其唯一列(ho)进行排序,ORDER BY ho
输出:
HI HO NR
A D 1
A E 2
A F 3
B E 1
B F 2
C D 1
C E 2
那个数据集更有意义。
实时测试:http://www.sqlfiddle.com/#!3/d0b44/1
这与您的查询非常相似,两者在PARTITION BY和ORDER BY上使用了相同的列:
select
hi,ho,
row_number() over(partition by hi,ho order by hi,ho) as nr
from tbl;
这是输出结果:
HI HO NR
A D 1
A E 1
A F 1
B E 1
B F 1
C D 1
C E 1
看到了吗?一点感觉都没有吗?
在线测试:http://www.sqlfiddle.com/#!3/d0b44/3
最后这可能是正确的查询:
SELECT cdt.*,
ROW_NUMBER ()
OVER (PARTITION BY cdt.country_code, cdt.account
ORDER BY
cdt.currency)
seq_no
FROM CUSTOMER_DETAILS cdt