Luglio 18, 2020

SQLShack (Italiano)

In questo articolo, discuteremo la funzione SQL ROW_NUMBER. Questa è una continuazione della serie essenziale di SQL. In questa guida, spiegherò in cosa consiste una funzione finestra e vedrai esempi di esempio per comprendere i concetti alla base della funzione SQL ROW_NUMBER.

Introduzione

La maggior parte la funzione comunemente utilizzata in SQL Server è la funzione SQL ROW_NUMBER. La funzione SQL ROW_NUMBER è disponibile da SQL Server 2005 e versioni successive.

ROW_NUMBER aggiunge un numero incrementale univoco alla griglia dei risultati. Lordine in cui vengono applicati i numeri di riga è determinato dallespressione ORDER BY. La maggior parte delle volte, una o più colonne sono specificate nellespressione ORDER BY, ma è possibile utilizzare espressioni più complesse o anche una sottoquery. Quindi, crea un valore integrale sempre crescente e inizia sempre da 1 e le righe successive ottengono il valore successivo più alto.

Puoi anche usarlo con una clausola PARTITION BY. Ma quando attraversa un limite o un confine di partizione, azzera il contatore e inizia da 1. Quindi, la partizione può avere valori 1, 2, 3 e così via e le seconde partizioni riavviano il contatore da 1, 2, 3 … E così via e così via.

Nozioni di base:

  1. La funzione SQL ROW_NUMBER è una generazione non persistente di una sequenza di valori temporanei e viene calcolata dinamicamente quando viene eseguita la query.
  2. Non vi è alcuna garanzia che le righe restituite da una query SQL utilizzando la funzione SQL ROW_NUMBER verranno ordinate esattamente nello stesso modo ad ogni esecuzione.
  3. Le funzioni ROW_NUMBER e RANK sono simili. Loutput di ROW_NUMBER è una sequenza di valori che inizia da 1 con un incremento di 1 ma mentre la funzione RANK, i valori vengono anche incrementati di 1 ma i valori si ripeteranno per i legami.
  4. Se hai unesperienza con Oracle allora il ROWNUM ti è più familiare. È una pseudo-colonna. Inizia con 1 e scende fino in fondo aumentando di uno, fino alla fine del tavolo.
  5. La funzione SQL ROW_NUMBER è di natura dinamica e ci è consentito reimpostare i valori utilizzando la clausola PARTITION BY
  6. La clausola ORDER BY della query e la clausola ORDER BY della OVER clausole non hanno nulla a che fare luna con laltra.

Sintassi

1
2

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

ROW_NUMBER

ROW_NUMBER seguito dalla funzione OVER e quindi tra parentesi utilizzare una clausola ORDER BY. È necessario utilizzare la clausola ORDER BY per imporre una sorta di ordine per il set di risultati.

OVER

La clausola OVER definisce la finestra o linsieme di righe che la finestra funziona, quindi è molto importante per te capire. I possibili componenti della clausola OVER sono ORDER BY e PARTITION BY.

Lespressione ORDER BY della clausola OVER è supportata quando le righe devono essere allineate in un certo modo affinché la funzione funzioni.

1
PARTIZIONE PER espressione_valore1

PARTITION BY

La clausola Partition By è facoltativa. Quando si specifica il valore, divide il set di risultati prodotto dalla clausola FROM in partizioni a cui viene applicata la funzione SQL ROW_NUMBER. I valori specificati nella clausola PARTITION definiscono i limiti del set di risultati. Se la clausola PARTITION BY non è specificata, la clausola OVER opera su tutte le righe del set di risultati come un singolo set di dati. Questa clausola può essere costituita da una o più colonne, unespressione più complessa o anche una sottoquery.

order_by_clause

La clausola Order by è una clausola obbligatoria. Determina la sequenza e lassociazione del valore temporaneo alle righe di una partizione specificata. La clausola ORDER BY è unespressione della clausola OVER e determina come le righe devono essere allineate in un certo modo per la funzione.

Demo

In questa sezione, noi daremo uno sguardo alla funzione SQL ROW_NUMBER. Per lintera demo, ho utilizzato il database AdventureWorks2016.

Come utilizzare ROW_NUMBER nella query SQL

Negli esempi seguenti, vedremo luso della clausola OVER.

Otteniamo lelenco di tutti i clienti proiettando le colonne come SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue e RowNum. La funzione Row_Number viene applicata con lordine della colonna CustomerID.Il valore temporaneo inizia da 1 assegnato in base allordine del CustomerID, ei valori continuano fino alle ultime righe della tabella. Lordine di CustomerID non è garantito perché non specifichiamo la clausola ORDER BY nella query.

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;

Come utilizzare la clausola Order by

Lesempio seguente utilizza la clausola ORDER BY nella query. La clausola ORDER BY nella query applicata alla colonna SalesOrderID. Possiamo vedere che le righe in output sono ancora ordinate e restituite. Il Row_Number è ancora applicato al CustomerID. Loutput indica che ORDER BY della query e ORDER BY della clausola OVER sono indipendenti dalloutput.

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;

Come utilizzare più colonne con la clausola OVER

Nellesempio seguente puoi vedere che abbiamo elencato customerID e OrderDate nella clausola ORDER BY. Questo fornisce i dettagli del cliente con i dettagli dellordine più recente insieme alla sequenza di numeri assegnati allintero set di risultati.

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

Come utilizzare la funzione SQL ROW_NUMBER con PARTITION

Lesempio seguente utilizza la clausola PARTITION BY sui campi CustomerID e OrderDate. Nelloutput, puoi vedere che il cliente 11019 ha tre ordini per il mese 2014-giugno. In questo caso, la partizione viene eseguita su più di una colonna.

La partizione è una combinazione di OrderDate e CustomerID. Il Row_Number ricomincerà per ogni combinazione univoca di OrderDate e CustomerID. In questo modo, è facile trovare il cliente che ha effettuato più di un ordine nello stesso giorno.

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

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

Come restituire un sottoinsieme di righe utilizzando CTE e ROW_NUMBER

Nel seguente esempio analizzeremo SalesOrderHeader per visualizzare i primi cinque ordini più grandi effettuati da ciascun cliente ogni mese. Utilizzando la funzione Month, le colonne orderDate vengono manipolate per recuperare la parte del mese.In questo modo, le vendite corrispondenti al mese specifico (OrderDate) insieme al cliente (CustomerID) vengono suddivise.

Per elencare i cinque ordini più grandi in ogni mese per ogni cliente, viene utilizzato un CTE. Viene creata una finestra sui dati della partizione a cui vengono assegnati i valori, quindi viene richiamata la CTE per recuperare gli ordini più grandi.

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

WITH cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY customerID, MONTH (OrderDate) ORDER BY Subtotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) Month,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Riepilogo

Finora, abbiamo esaminato in dettaglio la funzione SQL ROW_NUMBER. Abbiamo discusso diversi esempi da semplici a complessi. Inoltre, abbiamo discusso come utilizzare la funzione SQL ROW_NUMBER con CTE (espressioni di tabella comuni). Nella maggior parte dei casi, vedrai sempre una clausola over con ogni funzione di finestra.

La clausola over definisce la finestra visualizzata da ogni riga. Allinterno della clausola over, cè una partizione per, ancora una volta è supportata da ogni funzione della finestra, seguita dalla clausola order by. Per ora è tutto … Spero ti sia piaciuto leggere larticolo.

  • Autore
  • Post recenti
Sono un tecnologo di database con oltre 11 anni di ricca esperienza pratica sulle tecnologie di database. Sono Microsoft Certified Professional e ho conseguito una laurea in Master of Computer Application.
La mia specialità consiste nel progettare & implementando soluzioni ad alta disponibilità e migrazione di database multipiattaforma. Le tecnologie attualmente in uso sono SQL Server, PowerShell, Oracle e MongoDB.
Visualizza tutti i post di Prashanth Jayaram

Ultimi post di Prashanth Jayaram (vedi tutti)
  • Una rapida panoramica dellaudit del database in SQL – 28 gennaio 2021
  • Come configurare Azure Data Sync tra database SQL di Azure e SQL Server locale – 20 gennaio 2021
  • Come eseguire operazioni di importazione / esportazione di database SQL di Azure utilizzando PowerShell – 14 gennaio , 2021

admin
0

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Archivi

Articoli recenti