logo

TechCodeview

Функція SQL Server Row_Number із PARTITION BY

Функція нумерації рядків — це одна з віконних функцій, яка використовується для призначення номерів рядків у кожному з розділів у тому порядку, у якому вони були відсортовані в розділі за допомогою ПЕРЕГОРОДА пункт, РОЗДІЛ лише ЗАМОВИТИ речення можна використовувати всередині речення OVER, у такому разі вся таблиця вважатиметься одним розділом. Але пропозиція ORDER BY є обов’язковою для використання ROW_NUMBER() оскільки вона впорядковує рядки в розділах у такому логічному порядку, а пізніше функція ROW_NUMBER() може призначити номер рядка. У кожному розділі номер рядка починається з 1.

Синтаксис:

ROW_NUMBER ( )



НАД ( [ РОЗДІЛ ЗА col_1, col_2… ] ORDER BY col_3, col_4.. ASC або DESC) AS column_name

Компоненти функції ROW_NUMBER().

  • РОЗДІЛ ЗА: Це основний підпункт, який розділяє рядки на вікна, і для кожного рядка значення функції вікна застосована буде розрахована.
  • СОРТУВАТИ ЗА: Це використовується для впорядкування рядків у розділі, за замовчуванням це порядок зростання. Без пропозиції ORDER BY функція ROW_NUMBER() не працює.
  • Тип повернення: Тип повернення функції ROW_NUMBER() – BIGINT.

Використання функції ROW_NUMBER().

Крок 1: Створіть базу даних Geeksforgeeks за допомогою такого SQL-запиту:

Запит 

CREATE DATABASE Geeksforgeeks>

Використовуйте базу даних GFG.

Запит 

USE techcodeview.com>

крок 3: Створіть таблицю зі студентами різних секцій із їхніми загальними балами з 500.

Запит 

CREATE TABLE studentsSectionWise( studentId INT, studentName VARCHAR(100), sectionName VARCHAR(50), studentMarks INT  );>

крок 4: Вставте в таблицю рядки:

Запит 

INSERT INTO studentsSectionWise VALUES (1, 'Geek1','A',461), (1, 'Geek2','B',401), (1, 'Geek3','C',340), (2, 'Geek4','A',446), (2, 'Geek5','B',361), (2, 'Geek6','C',495), (3, 'Geek7','A',436), (3, 'Geek8','B',367), (3, 'Geek9','C',498), (4, 'Geek10','A',206), (4, 'Geek11','B',365), (4, 'Geek12','C',485), (5, 'Geek13','A',446), (5, 'Geek14','B',368),  (5, 'Geek15','C',295),  (6, 'Geek16','C',495);>

крок 5: Перевірте таблицю

SELECT * FROM studentsSectionWise>

Вихід:

студентиSectionWise-Table

таблиця студентівSectionWise

вибрати sql з кількох таблиць

Використання речення ORDER_BY із функцією ROW_NUMBER().

Використання простого речення ORDER BY із зростанням або спаданням розглядає всю таблицю лише як одну секцію.

Давайте перевіримо rankNumber студентів за допомогою функції ROW_NUMBER() із пропозицією ORDER BY, включаючи всі розділи: 

SELECT * , ROW_NUMBER() OVER ( ORDER BY studentMarks DESC) AS rankNumber FROM studentsSectionWise>

Пояснення: У запиті ми використали речення ORDER BY із StudentMarks, яке впорядковано в порядку DESC.

Вихід:

вихід

Вихід

Пояснення: У результаті ми бачимо, що вся таблиця розглядається як одна секція, а число rankNumber у порядку зростання, а оцінки студентів — у порядку спадання.

Використання PARTITION_BY із функцією ROW_NUMBER().

За допомогою простого речення PARTITION BY розділяється та таблиця на різні розділи на основі назви стовпця, а потім речення ORDER BY із зростанням або спаданням розглядає всю таблицю лише як одну секцію. Потім ROW_NUMBER() функціонує з кожним унікальним числом, починаючи з 1, у кожному розділі.

Давайте перевіримо розділ ранжирування за допомогою функції ROW_NUMBER() з умовами PARTITION BY і ORDER BY. 

SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber FROM studentsSectionWise;>

Пояснення: У запиті, який ми використали Речення PARTITION BY щоб розділити таблицю на розділ Name і речення ORDER BY із студентами Marks, які впорядковані в DESC.

Вихід:

вихід

Вихід

Пояснення: У результаті ми бачимо, що ми маємо розділи, розділені на назву розділу, а потім у кожному розділі студенти впорядковуються на основі оцінок студентів у порядку DESC, функція ROW_NUMBER() призначає номер рядка, починаючи з кожного розділу, із збільшенням .

Розуміння ROW_NUMBER() без PARTITION BY

Функція ROW_NUMBER() SQL Server — це гнучкий інструмент, який дозволяє надати кожному рядку в наборі результатів унікальний номер рядка. Він однаково ефективний, коли використовується без речення PARTITION BY, навіть незважаючи на те, що його часто використовують у поєднанні з ним для групування та ранжування в секціях. Можливості та використання ROW_NUMBER() без пропозиції PARTITION BY обговорюватимуться в цій статті.

Синтаксис:

ВИБРАТИ

ROW_NUMBER() ПОВЕРШЕНО ( СОРТУВАТИ ЗА YourOrderByColumn) AS RowNum,

Інші стовпці

ВІД

YourTableName;

Давайте перевіримо розділ ранжирування за допомогою функції ROW_NUMBER() без умов PARTITION BY і ORDER BY. 

SELECT * , ROW_NUMBER() OVER (ORDER BY studentMarks DESC) AS rankNumber FROM studentsSectionWise>

Вихід:

вихід

Вихід

Використання ROW_NUMBER() для отримання підмножини рядків

Функцію ROW_NUMBER() можна використовувати, щоб отримати підмножину рядків із таблиці за допомогою CTE, що може бути корисним у разі розбивки даних на сторінки.

Давайте перевіримо 2 найвищих рейтингу кожного класу за допомогою CTE (вираз загальної таблиці) з функцією ROW_NUMBER(): 

WITH topTwoRankers AS  (   SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber  FROM studentsSectionWise  )  SELECT *  FROM topTwoRankers  WHERE rankNumber <=2;>

Пояснення: Тут у запиті ми використали вираз CTE, щоб отримати тимчасову таблицю із запиту, використаного вище, і ми використали зовнішній запит SELCT, щоб отримати студентів, чий rankNumber менше або дорівнює 2.

Вихід:

вихід

Вихід

Пояснення: У результаті ми бачимо, що ми маємо 2 найкращі рейтинги з кожного розділу, які є підмножиною таблиці, отриманої за запитом, показаним у попередньому прикладі.

ПеревагиROW_NUMBER()>З PARTITION BY

  1. Дозволяє розширене ранжирування розділів.
  2. Вмикає унікальну нумерацію рядків у певних групах.
  3. Дозволяє більшу гнучкість в обставинах, що передбачають широкий аналіз.

ПеревагиROW_NUMBER()>Без PARTITION BY

  1. Прямий і простий.
  2. спрощує ранжування елементів відповідно до певного стовпця.
  3. Може запропонувати кращу продуктивність, особливо для менших наборів результатів.

Висновок

Це дуже корисно, коли ми хочемо мати послідовність на основі розділу та виконувати необхідні операції. Використання ORDER BY пункт є обов’язковим під час використання функції ROW_NUMBER(). Також за допомогою CTE ми можемо отримати підмножини таблиці з упорядкованих секцій. Основним варіантом використання ROW_NUMBER() є ранжування рядків на основі різних секцій, що дуже корисно для аналізу даних.