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

Question

我有一个由其他人编写的 SQL 查询，我正在尝试弄清楚它的作用。有人可以解释一下 Partition By和 Row_Number关键字在这里并给出一个简单的例子来说明它的实际作用，以及为什么要使用它？

分区示例:

(SELECT cdt.*,
        ROW_NUMBER ()
        OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
              ORDER BY cdt.country_code, cdt.account, cdt.currency)
           seq_no
   FROM CUSTOMER_DETAILS cdt);

我在网上看过一些例子，它们有点太深入了。

提前致谢!

Answer 1

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;

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

基本上，您应该对非唯一列进行分区。 OVER 上的 ORDER BY 需要 PARTITION BY 具有非唯一组合，否则所有 row_numbers 将变为 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那里需要？如果之前的开发者只是想在相似的数据上放一个row_number(例如HI B，所有数据都是B-W，B-W)，他可以这样做:

select
   hi,ho,
   row_number() over(partition by hi,ho)
from tbl;

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

select
   hi,ho,
   row_number() over(partition by hi,ho)
from tbl;

您的 ORDER BY在你的分区上看起来有点多余，不是因为以前的开发者的错，有些数据库就是不允许 PARTITION没有 ORDER BY ，他可能找不到合适的候选列进行排序。如果 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())。因此，与以前的开发人员所做的输出相同，不幸的是某些数据库不允许 PARTITION没有 ORDER BY
虽然真的，它躲避我，我找不到一个很好的理由在相同的组合上放一个数字(上面例子中的 B-W，B-W)。它给人的印象是数据库具有冗余数据。不知怎的让我想起了这个: How to get one unique record from the same list of records from table? No Unique constraint in the table

看到 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');

然后你 PARTITION BY 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 -- removed: cdt.currency
           ORDER BY 
               -- removed: cdt.country_code, cdt.account, 
               cdt.currency) -- keep
        seq_no
FROM CUSTOMER_DETAILS cdt