július 18, 2020

SQLShack (Magyar)

Ebben a cikkben tárgyalni fogunk az SQL ROW_NUMBER függvény. Ez az SQL alapvető sorozatának folytatása. Ebben az útmutatóban elmagyarázom, hogy miről is szól egy ablakfunkció, és példákat fog látni az SQL ROW_NUMBER függvény mögött álló fogalmak megértéséhez.

Bevezetés

A legtöbb Az SQL Server általában használt függvénye az SQL ROW_NUMBER függvény. Az SQL ROW_NUMBER függvény az SQL Server 2005 és újabb verzióiból érhető el.

A ROW_NUMBER egyedi növekményes számot ad az eredményrácshoz. A sorszámok alkalmazásának sorrendjét a ORDER BY kifejezés határozza meg. Legtöbbször egy vagy több oszlop van megadva az ORDER BY kifejezésben, de lehetséges összetettebb kifejezések vagy akár részlekérdezés is. Tehát folyamatosan növekvő integrálértéket hoz létre, és mindig 1-től indul, és a következő sorok a következő magasabb értéket kapják.

Használhatja a PARTITION BY záradékkal is. De amikor átlép egy partíciós határt vagy határt, akkor visszaállítja a számlálót és 1-től indul. Tehát a partíciónak lehetnek 1, 2, 3 és így tovább értékei, és a második partíciók újra elindítják a számlálót 1, 2, 3-ból … és így tovább, és így tovább.

Alapok:

  1. Az SQL ROW_NUMBER függvény az ideiglenes értékek sorozatának nem állandó generálása, és dinamikusan kerül kiszámításra a lekérdezés végrehajtásakor.
  2. Nincs garancia arra, hogy az SQL ROW_NUMBER függvényt használó SQL lekérdezés által visszaadott sorok minden végrehajtáskor pontosan ugyanazok lesznek rendezve.
  3. A ROW_NUMBER és a RANK függvények hasonlóak. A ROW_NUMBER kimenete olyan értékek sorozata, amely 1-től kezdődik, 1-es lépésekkel, de míg a RANK funkció, az értékeket is növeljük 1-vel, de az értékek megismétlődnek a kapcsolatoknál.
  4. Ha az Oracle tapasztalata, akkor a ROWNUM ismerősebb számodra. Ez egy áloszlop. 1-gyel indul, és eggyel növekszik, egészen az asztal végéig.
  5. Az SQL ROW_NUMBER függvény dinamikus jellegű, és engedélyezhetjük az értékek alaphelyzetbe állítását a PARTITION BY záradék használatával.
  6. A lekérdezés ORDER BY záradékát és az OVER ORDER BY záradékát. záradéknak semmi köze nincs egymáshoz.

Szintaxis

1
2

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

ROW_NUMBER

ROW_NUMBER, majd OVER függvény majd a zárójelben használja az ORDER BY záradékot. Az ORDER BY záradék használatára van szükség annak érdekében, hogy fajta sorrendet vezessen be az eredményhalmazra.

OVER

Az OVER záradék határozza meg azt az ablakot vagy sorkészletet, amelyet az ablak funkció működik, ezért nagyon fontos, hogy megértse. Az OVER záradék lehetséges összetevői: ORDER BY és PARTITION BY.

Az OVER záradék ORDER BY kifejezése akkor támogatott, ha a függvény működéséhez a sorokat bizonyos módon fel kell sorolni.

1
PARTITION BY value_expression1

PARTITION BY

A Partition By záradék nem kötelező. Az érték megadásakor felosztja a FROM záradék által létrehozott eredménykészletet olyan partíciókra, amelyekre az SQL ROW_NUMBER függvényt alkalmazzák. A PARTITION záradékban megadott értékek határozzák meg az eredményhalmaz határait. Ha a PARTITION BY záradék nincs megadva, akkor az OVER záradék az eredményhalmaz minden során egyetlen adatkészletként működik. Ez a záradék egy vagy több oszlopból, egy összetettebb kifejezésből vagy akár egy al-lekérdezésből állhat.

order_by_clause

A Order by záradék kötelező záradék. Meghatározza az ideiglenes érték sorrendjét és társítását egy megadott partíció soraihoz. Az ORDER BY záradék az OVER klauzula kifejezője, és meghatározza, hogy a sorokat hogyan kell egy bizonyos módon felsorolni a függvény számára.

Demo

Ebben a szakaszban Megnézem az SQL ROW_NUMBER függvényt. A teljes bemutató során az AdventureWorks2016 adatbázist használtam.

A ROW_NUMBER használata SQL lekérdezésben

A következő példákban láthatjuk az OVER záradék használatát.

Szerezzük meg az összes ügyfél listáját az oszlopok, például SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue és RowNum, kivetítésével. A Row_Number függvény a CustomerID oszlop sorrendjével kerül alkalmazásra.Az ideiglenes érték 1-től kezdődik, a CustomerID sorrendje alapján hozzárendelve, és az értékeket a táblázat utolsó soraiig folytatjuk. A CustomerID sorrendje nem garantált, mert a lekérdezésben nem adjuk meg az ORDER BY záradékot.

1
2
3
4
5
6
7
8
9
10
11

HASZNÁLJA az AdventureWorks2016 alkalmazást;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Hogyan használjuk a sorrendet tagmondat szerint

Az alábbi példa az ORDER BY záradékot használja a lekérdezésben. A SalesOrderID oszlopban alkalmazott lekérdezés ORDER BY záradéka. Láthatjuk, hogy a kimeneti sorok továbbra is rendezettek és visszaküldésre kerülnek. A Row_Number továbbra is alkalmazandó a CustomerID-re. A kimenet azt jelzi, hogy a lekérdezés ORDER BY és az OVER záradék ORDER BY független a kimenettől.

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

AZ AdventureWorks2016 HASZNÁLATA;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
RENDELÉS a SalesOrderID alapján;

Több oszlop használata az OVER záradékkal

A következő példa azt láthatja, hogy felsoroltuk a customerID és a OrderDate elemeket az ORDER BY záradékban. Ez megadja az ügyfél adatait a legfrissebb megrendelés részleteivel, valamint a teljes eredményhalmazhoz rendelt számok sorrendjével.

1
2
3
4
5
6
7
8
9
10

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

Az SQL ROW_NUMBER függvény használata a PARTITION

A következő példában a PARTITION BY záradékot használjuk az CustomerID és a OrderDate mezőkön. A kimenetben láthatja, hogy az 11019 ügyfélnek három megrendelése van a 2014-június hónapra. Ebben az esetben a partíció egynél több oszlopon történik.

A partíció a OrderDate és a CustomerID kombinációja. A Row_Number elölről kezdődik a OrderDate és a CustomerID minden egyes kombinációja esetében. Így könnyű megtalálni azt az ügyfelet, aki egynél több megrendelést adott le ugyanazon a napon.

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

AZ AdventureWorks2016 HASZNÁLATA;
GO
SELECT RUM_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
Részösszeg,
TotalDue
FROM Sales.SalesOrderHeader;

Hogyan adhat vissza sorok egy részhalmazát a CTE és a ROW_NUMBER használatával?

A következő példában elemezni fogjuk a SalesOrderHeader-t, hogy az egyes ügyfelek által leadott öt legnagyobb megrendelést minden hónap. A Hónap funkció használatával a orderDate oszlopokat manipulálják a hónaprész lekéréséhez.Ily módon az adott hónapnak (OrderDate) és az ügyfélnek (CustomerID) megfelelő eladások fel vannak osztva.

A havi öt legnagyobb megrendelés felsorolásához minden ügyfél számára CTE-t használnak. A partíció adataira ablakot hoznak létre, és hozzárendelik az értékekhez, majd a CTE-t hívják a legnagyobb megrendelések beolvasására.

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

CTE-vel
AS (SELECT RUM_NUMBER OVER (PARTITION BY CustomerID, MONTH (OrderDate) ORDER BY SubTotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) hónap,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Összegzés

Eddig részletesen átnéztük az SQL ROW_NUMBER függvényt. Több példát is megvitattunk az egyszerűtől a bonyolultig. Megbeszéltük az SQL ROW_NUMBER függvény CTE-kkel (Common Table Expressions) történő használatát is. A legtöbb esetben minden ablakfüggvénynél mindig látható egy túllépés.

Az over záradék határozza meg az ablakot, amelyet minden sor lát. Az over záradékon belül található egy partíció, amelyet minden ablakfüggvény támogat, majd a sorrendet záradék követi. Egyelőre ennyi … Remélem, tetszett olvasni a cikket.

  • Szerző
  • Legfrissebb bejegyzések
Adatbázis-technológus vagyok, több mint 11 év gazdag, gyakorlati tapasztalattal az adatbázis-technológiák terén. Microsoft Certified Professional vagyok, és a számítógépes alkalmazás mester fokozatával rendelkezem.
A specialitásom a & magas rendelkezésre állású megoldások és a platformokon átnyúló DB Migration megvalósításában rejlik. A jelenleg működő technológiák az SQL Server, a PowerShell, az Oracle és a MongoDB.
Prashanth Jayaram összes üzenetének megtekintése

Prashanth Jayaram legújabb bejegyzései (az összes megtekintése)
  • Az SQL audit gyors áttekintése – 2021. január 28.
  • Az Azure Data Sync beállítása az Azure SQL adatbázisok és a helyszíni SQL Server között – 2021. január 20.
  • Az Azure SQL adatbázis import / export műveletek végrehajtása a PowerShell használatával – január 14. , 2021

admin
0

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöltük

Archívum

Legutóbbi bejegyzések