iulie 18, 2020

SQLShack (Română)

În acest articol, vom discuta funcția SQL ROW_NUMBER. Aceasta este o continuare a seriei esențiale SQL. În acest ghid, voi explica despre ce este vorba despre o funcție de fereastră și veți vedea exemple de exemple pentru a înțelege conceptele din spatele funcției SQL ROW_NUMBER.

Introducere

funcția frecvent utilizată în SQL Server este funcția SQL ROW_NUMBER. Funcția SQL ROW_NUMBER este disponibilă din SQL Server 2005 și versiunile ulterioare.

ROW_NUMBER adaugă un număr unic de incrementare în grila de rezultate. Ordinea, în care se aplică numerele rândurilor, este determinată de expresia ORDER BY. De cele mai multe ori, una sau mai multe coloane sunt specificate în expresia ORDER BY, dar este posibil să se utilizeze expresii mai complexe sau chiar o interogare secundară. Deci, creează o valoare integrală în continuă creștere și începe întotdeauna la 1 și rândurile următoare obțin următoarea valoare mai mare.

Îl puteți utiliza și cu o clauză PARTITION BY. Dar când traversează o limită sau o limită de partiție, resetează contorul și pornește de la 1. Deci, partiția poate avea valori 1, 2, 3 și așa mai departe și a doua partiții pornește din nou contorul de la 1, 2, 3 … și așa mai departe, și așa mai departe.

Noțiuni de bază:

  1. Funcția SQL ROW_NUMBER este o generație nepersistentă a unei secvențe de valori temporare și este calculată dinamic atunci când se execută interogarea.
  2. Nu există nicio garanție că rândurile returnate de o interogare SQL utilizând funcția SQL ROW_NUMBER vor fi ordonate exact la fel cu fiecare execuție.
  3. Funcțiile ROW_NUMBER și RANK sunt similare. Ieșirea ROW_NUMBER este o secvență de valori începe de la 1 cu un increment de 1, dar în timp ce funcția RANK, valorile sunt, de asemenea, incrementate cu 1, dar valorile se vor repeta pentru legături.
  4. Dacă ați o experiență cu Oracle, atunci ROWNUM vă este mai familiar. Este o pseudo-coloană. Începe cu 1 și merge până la capăt crescând cu unul până la capătul mesei.
  5. Funcția SQL ROW_NUMBER are o natură dinamică și ni se permite să resetăm valorile folosind clauza PARTITION BY
  6. Clauza ORDER BY a interogării și clauza ORDER BY a OVER clauza nu au nicio legătură una cu cealaltă.

Sintaxă

1
2

ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)

ROW_NUMBER

ROW_NUMBER urmat de funcția OVER și apoi între paranteze folosiți o clauză ORDER BY. Este necesară utilizarea clauzei ORDER BY pentru a impune un fel de ordine pentru setul de rezultate.

OVER

Clauza OVER definește fereastra sau setul de rânduri pe care le are fereastra funcția funcționează, deci este foarte important pentru dvs. să înțelegeți. Componentele posibile ale Clauzei OVER sunt ORDINĂ ȘI PARTIȚIE DE.

Expresia ORDER BY a clauzei OVER este acceptată atunci când rândurile trebuie aliniate într-un anumit mod pentru ca funcția să funcționeze.

1
PARTITION BY value_expression1

PARTITION BY

Clauza Partition By este opțională. La specificarea valorii, împarte setul de rezultate produs de clauza FROM în partiții cărora li se aplică funcția SQL ROW_NUMBER. Valorile specificate în clauza PARTITION definesc limitele setului de rezultate. Dacă clauza PARTITION BY nu este specificată, atunci clauza OVER funcționează pe toate rândurile setului de rezultate ca un singur set de date. Această clauză poate consta din una sau mai multe coloane, o expresie mai complexă sau chiar o interogare secundară.

order_by_clause

Clauza Order by este o clauză obligatorie. Determină secvența și asocierea valorii temporare la rândurile unei partiții specificate. Clauza ORDER BY este o expresie a clauzei OVER și determină modul în care rândurile trebuie aliniate într-un anumit mod pentru funcție.

Demo

În această secțiune, vom Vom arunca o privire la funcția SQL ROW_NUMBER. Pentru întreaga demonstrație, am folosit baza de date AdventureWorks2016.

Cum se utilizează ROW_NUMBER în interogarea SQL

Următoarele exemple, vom vedea utilizarea clauzei OVER.

Permiteți-ne să obținem lista tuturor clienților proiectând coloane precum SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue și RowNum. Funcția Row_Number se aplică odată cu ordinea coloanei CustomerID.Valoarea temporară începe de la 1 atribuit pe baza comenzii CustomerID, iar valorile sunt continuate până la ultimele rânduri ale tabelului. Ordinea CustomerID nu este garantată deoarece nu specificăm clauza ORDER BY în interogare.

1
2
3
4
5
6
7
8
9
10
11

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Cum se utilizează clauza Order by

Următorul exemplu folosește clauza ORDER BY în interogare. Clauza ORDER BY din interogarea aplicată pe coloana SalesOrderID. Putem vedea că rândurile din ieșire sunt încă ordonate și returnate. Row_Number este încă aplicat la CustomerID. Ieșirea indică faptul că ORDER BY a interogării și ORDER BY a clauzei OVER sunt independente de ieșire.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

Cum se utilizează mai multe coloane cu clauza OVER

Următorul exemplu puteți vedea că am enumerat ID client și OrderDate în clauza ORDER BY. Aceasta oferă clienților detalii cu cele mai recente detalii ale comenzii, împreună cu succesiunea numerelor atribuite întregului set de rezultate.

1
2
3
4
5
6
7
8
9
10

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader

Cum se utilizează funcția SQL ROW_NUMBER cu PARTITION

Următorul exemplu folosește clauza PARTITION BY în câmpurile CustomerID și OrderDate. În rezultat, puteți vedea că clientul 11019 are trei comenzi pentru luna 2014-iunie. În acest caz, partiția se face pe mai multe coloane.

Partiția este o combinație între OrderDate și CustomerID. Row_Number va începe de la capăt pentru fiecare combinație unică de OrderDate și CustomerID. În acest fel, este ușor să găsiți clientul care a plasat mai multe comenzi în aceeași zi.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (LUN, DATEDIFF (LUN, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Cum se returnează un subset de rânduri folosind CTE și ROW_NUMBER

Următorul exemplu vom analiza SalesOrderHeader pentru a afișa primele cinci cele mai mari comenzi plasate de fiecare client la fiecare lună. Folosind funcția Lună, coloanele orderDate sunt manipulate pentru a prelua partea lunară.În acest fel, se partiționează vânzările corespunzătoare lunii specifice (OrderDate) împreună cu clientul (CustomerID).

Pentru a enumera cele mai mari cinci comenzi din fiecare lună pentru fiecare client, se utilizează un CTE. Se creează o fereastră pentru datele partiției și este atribuită cu valorile și apoi CTE este apelat pentru a prelua cele mai mari comenzi.

1
2
3
4
5
6
7
8
9
10
11
12

WITH cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY CLIENT ID, LUN (OrderDate) ORDER BY SubTotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
LUNA (OrderDate) Luna,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Rezumat

Până acum, am analizat în detaliu funcția SQL ROW_NUMBER. Am discutat câteva exemple de la simplu la complex. De asemenea, am discutat despre modul de utilizare a funcției SQL ROW_NUMBER cu CTE (Common Table Expressions). În majoritatea cazurilor, veți vedea întotdeauna o clauză peste cu fiecare funcție de fereastră.

Clauza over definește fereastra pe care o vede fiecare rând. În cadrul clauzei over, există o partiție by, din nou este susținută de fiecare funcție de fereastră, urmată de clauza order by. Asta e tot deocamdată … Sper că ți-a plăcut să citești articolul.

  • Autor
  • Postări recente
Sunt tehnolog în baze de date, având peste 11 ani de experiență bogată și practică în tehnologiile bazelor de date. Sunt profesionist certificat Microsoft și am obținut o diplomă în master în aplicații informatice.
Specialitatea mea constă în proiectarea & implementând soluții de înaltă disponibilitate și migrarea bazelor de date pe mai multe platforme. Tehnologiile care lucrează în prezent sunt SQL Server, PowerShell, Oracle și MongoDB.
Vezi toate mesajele lui Prashanth Jayaram

Ultimele mesaje ale lui Prashanth Jayaram (vezi toate)
  • O prezentare rapidă a auditului bazei de date în SQL – 28 ianuarie 2021
  • Cum se configurează Sincronizarea datelor Azure între bazele de date SQL Azure și SQL Server local – 20 ianuarie 2021
  • Cum se efectuează operațiunile de import / export Azure SQL bazate pe PowerShell – 14 ianuarie , 2021

admin
0

Lasă un răspuns

Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate cu *

Arhive

Articole recente