Oracle 'Partition By' 和 'Row_Number' 关键字

(SELECT cdt.*,ROW_NUMBER ()OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currencyORDER BY cdt.country_code, cdt.account, cdt.currency)序列号从 CUSTOMER_DETAILS cdt);

解决方案

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，那么您对这个表达式有什么期望?

选择row_number() over(partition by newid() order by ho)来自 tbl;

…对了，所有的分区(没有分区，每一行都分区在自己的行中)行的row_numbers都设置为1

基本上，您应该对非唯一列进行分区.OVER 上的 ORDER BY 需要 PARTITION BY 有一个非唯一的组合，否则所有的 row_numbers 都会变成 1

例如，这是您的数据:

create table tbl(hi varchar, ho varchar);插入 tbl 值('斧头')，('A','Y'),('A','Z'),('B','W'),('B','W'),('C','L'),('C','L');

那么这类似于您的查询:

选择row_number() over(按 ho 分区，按 ho 排序)来自 tbl;

它的输出是什么?

HI HO COLUMN_2一个 X 1A Y 11黑白 1黑白 2CL 1CL 2

你看到HI HO的组合了吗?前三行有唯一的组合，因此它们被设置为1，B行具有相同的W，因此不同的ROW_NUMBERS，对于HI C行也是如此.

现在，为什么需要 ORDER BY 呢?如果之前的开发者只是想在相似的数据上放一个row_number(例如HI B，所有数据都是B-W，B-W)，他可以这样做:

选择row_number() over(按 ho 分区)来自 tbl;

但是，遗憾的是，Oracle(和 Sql Server 也是如此)不允许没有 ORDER BY 的分区；而在 Postgresql 中，PARTITION 上的 ORDER BY 是可选的:http:///www.sqlfiddle.com/#!1/27821/1

选择row_number() over(按 ho 分区)来自 tbl;

你分区上的ORDER BY看起来有点多余，不是因为前任开发者的错，有些数据库就是不允许PARTITION没有ORDERBY，他可能找不到合适的候选列进行排序.如果 PARTITION BY 列和 ORDER BY 列相同，只需删除 ORDER BY，但由于某些数据库不允许这样做，您可以这样做:

SELECT cdt.*,ROW_NUMBER ()OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency按 newid() 排序)序列号来自 CUSTOMER_DETAILS cdt

您找不到用于对相似数据进行排序的好列?您也可以随机排序，无论如何，分区数据具有相同的值.例如，您可以使用 GUID(对于 SQL Server，您使用 newid()).因此，具有与以前的开发人员相同的输出，不幸的是，某些数据库不允许 PARTITION 没有 ORDER BY

尽管如此，它让我难以捉摸，而且我找不到将数字放在相同组合上的充分理由(在上面的示例中为 B-W、B-W).它给人的印象是数据库具有冗余数据.不知怎的让我想起了这一点:如何从表的同一记录列表中获取唯一记录?表中没有唯一约束

看到 PARTITION BY 与 ORDER BY 的列组合相同，这真的看起来很神秘，无法轻易推断出代码的意图.

现场测试:http://www.sqlfiddle.com/#!3/27821/6

---

但正如 dbaseman 也注意到的那样，在同一列上进行分区和排序是没有用的.

你有一组这样的数据:

create table tbl(hi varchar, ho varchar);插入 tbl 值('斧头')，('斧头')，('斧头')，('经过')，('经过')，('C','Z'),('C','Z');

然后你 PARTITION BY ho;然后你通过 ho 订购.对类似数据进行编号是没有意义的