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

У меня есть SQL-запрос, написанный кем-то другим, и я пытаюсь понять, что он делает. Может кто-нибудь объяснить, что здесь Row_Number ключевые слова 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); 

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

Заранее спасибо!

РАЗДЕЛЕНИЕ Сегрегированными наборами, это позволяет вам работать независимо (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 AX 1 AY 1 AZ 1 BW 1 BW 2 CL 1 CL 2 

Вы видите комбинацию HI HO? Первые три строки имеют уникальную комбинацию, поэтому они установлены в 1, строки B имеют одинаковые W, а следовательно, разные ROW_NUMBERS, аналогично строкам HI C.

Теперь, зачем нужен ORDER BY ? Если предыдущий разработчик просто хочет поставить row_number на подобные данные (например, HI B, все данные BW, BW), он может просто сделать это:

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

Но, увы, Oracle (и Sql Server тоже) не разрешает раздел без ORDER BY ; тогда как в Postgresql ORDER BY on 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

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

Это действительно выглядит тайным, видя 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'); 

Тогда вы разделяете привет, хо; и тогда вы ЗАКАЗЫВАЕТ привет, хо. Нет смысла нумерации подобных данных 🙂 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 AX 1 AX 2 AX 3 BY 1 BY 2 CZ 1 CZ 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 AD 1 AE 2 AF 3 BE 1 BF 2 CD 1 CE 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 AD 1 AE 1 AF 1 BE 1 BF 1 CD 1 CE 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 

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

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

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

Я часто использую 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; 

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