Ключевое слово Oracle 'Partition By' и 'Row_Number'

У меня есть 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);

Я видел несколько примеров в Интернете, они слишком глубоки.

Спасибо заранее!

Ответ 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

В принципе, вы должны разбить на неповторимые столбцы. ORDER BY на OVER требовал, чтобы 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 (вы используете newid() для SQL Server). Таким образом, тот же результат, производимый предыдущим разработчиком, к сожалению, некоторая база данных не позволяет PARTITION без ORDER BY

Хотя на самом деле это ускользает от меня, и я не могу найти вескую причину, чтобы поместить число в те же комбинации (B-W, B-W в примере выше). Это создает впечатление, что база данных имеет избыточные данные. Как-то напомнил мне об этом: Как получить одну уникальную запись из того же списка записей из таблицы? Нет уникального ограничения в таблице

Это действительно выглядит тайным, видя PARTITION BY с той же комбинацией столбцов с ORDER BY, не может легко сделать вывод о намерении кода.

Live test: 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');

Затем вы разделяете привет, хо; и тогда вы ЗАКАЗЫВАЕТ привет, хо. Нет смысла нумерации похожих данных:-) 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

Этот набор данных имеет больше смысла

Live test: 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

См? нет смысла?

Live test: 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

Ответ 2

Это выбирает номер строки для кода страны, учетной записи и валюты. Таким образом, строки с кодом страны "США", счет "XYZ" и валютой "$ USD" будут каждый получить номер строки, присвоенный с 1-n; то же самое относится к любой другой комбинации этих столбцов в результирующем наборе.

Этот запрос довольно забавный, потому что предложение order by ничего не делает. Все строки в каждом разделе имеют один и тот же код страны, учетную запись и валюту, поэтому нет точного упорядочения по этим столбцам. Поэтому конечные номера строк, назначенные в этом конкретном запросе, будут непредсказуемыми.

Надеюсь, что это поможет...

Ответ 3

Я часто использую row_number() как быстрый способ сбросить дубликаты записей из моих операторов select. Просто добавьте предложение where. Что-то вроде...

select a,b,rn 
  from (select a, b, row_number() over (partition by a,b order by a,b) as rn           
          from table) 
 where rn=1;

Ответ 4

Я знаю, что это старый поток, но PARTITION - это equiv GROUP BY, а не ORDER BY. ORDER BY в этой функции., СОРТИРОВАТЬ ПО. Это просто способ создать уникальность из избыточности, добавив порядковый номер. Или вы можете исключить другие избыточные записи по предложению WHERE, ссылаясь на столбцы с псевдонимом для функции. Однако DISTINCT в выражении SELECT, вероятно, выполнит то же самое в этом отношении.