Redgate Hub (Magyar)

töltöttem az elmúlt hat évben járja a MINKET mondom, adatbázis szakemberek arról, hogy a T-SQL Ablakban Függvények az SQL szombaton, illetve egyéb eseményeket. Csodálkozom, hogy kevesen hallottak ezekről a funkciókról, sőt még kevesebben használják őket. Minden előadás végén egy vagy több ember jön fel, hogy azt mondják, hogy azt akarták, hogy évekkel korábban megtudják ezeket a funkciókat, mert sok lekérdezés szempontjából hasznosak lehetnek.,

ezeket a funkciókat elősegítették, hogy javítsák a teljesítményt más, hagyományosabb módszerekkel szemben. Részben egyetértek. Sok lekérdezést könnyebb írni, néha pedig javítják a teljesítményt.

semmi köze a Windows operációs rendszerhez

ezek a funkciók az ANSI SQL 2003 szabványok részét képezik, SQL Server esetén pedig a lekérdezések írására Használt T-SQL funkciók. Semmi köze a Windows operációs rendszerhez vagy bármilyen API-híváshoz. Más adatbázis-rendszerek, mint például az Oracle, ezeket saját SQL nyelvük részeként is bevonták.,

ablak (is, ablakos vagy ablakos) funkciók elvégzi a számítást egy sor sor. Szeretem azt gondolni, hogy “az ablakon keresztül” nézem meg a visszaküldött sorokat, és még egy utolsó esélyem van a számítás elvégzésére. Az ablakot az OVER záradék határozza meg, amely meghatározza, hogy a sorok kisebb halmazokra vannak-e felosztva, illetve megrendelve vannak-e. Valójában, ha ablak funkciót használ, akkor mindig egy TÚLZÁRADÉKOT fog használni. Az over záradék is része a következő érték szintaxis szükséges a szekvencia objektum, de egyébként ez használható ablakfüggvények.,

a túlzáradék tartalmazhat partíciót opció szerint. Ez a sorokat kisebb készletekre bontja. Lehet, hogy úgy gondolja, hogy ez ugyanaz, mint a csoport által, de ez nem. Csoportosításkor egyedi csoportonként egy sor kerül visszaadásra. A PARTITION BY használatakor az összes részletsor a számításokkal együtt visszakerül. Ha otthonában van egy ablak, amely panelekre van osztva, minden ablaktábla egy ablak. Az ablakfunkciókra gondolva az eredmények teljes halmaza partíció, de partíció használata esetén minden partíció ablaknak is tekinthető., Partíció által támogatott-opcionális-az összes ablak funkció.

a túlzáradék opció szerint is tartalmazhat megrendelést. Ez független a lekérdezés záradékától. Egyes funkciók rendelést igényelnek, a többiek nem támogatják. Ha a sorok sorrendje fontos a számítás alkalmazásakor, akkor a megrendelés szükséges.

az Ablakfüggvények csak a lekérdezés záradékai szerinti kiválasztásban és sorrendben használhatók. Ezeket minden csatlakozás, szűrés vagy csoportosítás után alkalmazzák.,

rangsor függvények

a leggyakrabban használt ablakfüggvények, rangsor függvények 2005 óta állnak rendelkezésre. Ekkor mutatta be a Microsoft a ROW_NUMBER, RANK, DENSE_RANK és NTILE-t. A ROW_NUMBER-t nagyon gyakran használják, hogy egyedi sorszámokat adjon hozzá egy partícióhoz vagy a teljes eredménykészlethez. A sorszám vagy a többi rangsorolási funkció hozzáadása általában nem a cél, de ez egy lépés a megoldás felé vezető úton.

az OVER záradékban a ROW_NUMBER és a csoport többi funkciójának használata szükséges., Ez jelzi az adatbázis-motornak a számok alkalmazásának sorrendjét. Ha a sorrendben használt oszlopok vagy kifejezések értékei nem egyediek, akkor a RANK and DENSE_RANK foglalkozik a kötésekkel, míg a ROW_NUMBER nem törődik a kötésekkel. NTILE használják osztani a sorok vödrök alapján a sorrendben.

a ROW_NUMBER egyik előnye, hogy a nem egyedi sorokat egyedi sorokká alakíthatja. Ezt fel lehet használni, hogy megszüntesse ismétlődő sorok, például.

annak bemutatásához, hogy ez hogyan működik, kezdje egy ismétlődő sorokat tartalmazó temp táblával., Az első lépés az asztal létrehozása és feltöltése.

hozzáadása ROW_NUMBER és particionálás minden oszlop újraindítja a sor számok minden egyes egyedi sor. Az egyedi sorokat úgy azonosíthatja, hogy megtalálja azokat, akiknek egy sorszáma megegyezik.,

Most, annyit kell tenned, hogy törölni minden olyan sort, hogy egy sor-szám nagyobb, mint egy., A probléma az, hogy nem adhat hozzá ablakfunkciókat a WHERE záradékhoz.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Ezután törölheti a sorokat közvetlenül a CTE-ből.

siker! Az extra sorokat törölték, és egy egyedi sor maradt.

a row_number, RANK és DENSE_RANK közötti különbség megtekintéséhez futtassa ezt a lekérdezést:

minden OVER záradék sorrendje nem egyedi OrderDate. Ez az Ügyfél két megrendelést 2013-10-24. ROW_NUMBER csak folytatta a számok hozzárendelését, és nem csinált semmi mást, annak ellenére, hogy van egy duplikált dátum., RANK rendelt 6 mindkét sorban, majd utolérte ROW_NUMBER egy 8 a következő sorban. DENSE_RANK is rendelt 6 a két sor, de rendelt 7 a következő sorban.

kettő magyarázza el a különbséget, gondoljon a ROW_NUMBER-re pozíciósként. A rang mind pozicionális, mind logikus. Ez a két sor logikusan azonos, de a következő sort a készlet pozíciója rangsorolja. DENSE_RANK sorolja őket logikusan. Rendelés 2013-11-04 a 7. egyedi dátum.

ebben a csoportban a végső függvény neve NTILE. Ez hozzárendeli vödör számok a sorok helyett sorszámok vagy soraiban., Itt van egy példa:

NTILE van egy paraméter, ebben az esetben 4, amely a vödrök számát szeretné látni az eredményeket. A megrendelés az értékesítés összegére vonatkozik. A legalacsonyabb 25% – os sorok 1-hez vannak rendelve, a legmagasabb 25% – os sorok 4-hez vannak rendelve. Végül, az eredmények NTILE szorozzuk 1000, hogy dolgozzon ki a bónusz összege. Mivel a 14-et nem lehet egyenletesen elosztani 4-gyel, egy extra sor kerül az első két vödörbe.

ablak aggregátumok

ablak aggregátumok is bevezették az SQL Server 2005., Ezek megkönnyítik néhány trükkös lekérdezés írását, de gyakran rosszabbul teljesítenek, mint a régebbi technikák. Ezek lehetővé teszik, hogy hozzá a kedvenc összesített funkció egy nem összesített lekérdezés. Mondjuk, például szeretné megjeleníteni az összes ügyfél megrendelések együtt részösszeg minden ügyfél számára., Hozzáadásával ÖSSZEG segítségével a VÉGE záradék lehet elérni ezt nagyon egyszerűen:

a partíció hozzáadásával minden ügyfél számára egy részösszeg kerül kiszámításra. Bármely aggregált függvény használható, a megrendelés pedig az OVER záradékban nem támogatott.

Window Aggregate Enhancements in 2012

2012-től kezdődően a OVER záradékban rendelést adhat az ablak aggregátumokhoz, hogy például futó totálokat és mozgó átlagokat állítson elő. Ugyanakkor a Microsoft bevezette a keretezés fogalmát. Partíció hozzáadása olyan, mintha egy ablakot panelekre osztanánk., A keretezés hozzáadása olyan, mint egy ólomüveg ablak létrehozása. Minden sornak van egy egyedi ablaka, ahol a kifejezést alkalmazzák.

ezzel a bővítéssel futóösszegeket hozhat létre a keretezés szintaxisának hozzáadása nélkül is., Here is an example that returns a running total by customer:

Az alapértelmezett keret, amelyet akkor használnak, ha a keret nincs megadva, az előző és az aktuális sor közötti hatótávolság. Sajnos ez nem fog olyan jól működni, mint ha ezt a keretet adja meg: sorok az előző és az aktuális sor között. A különbség a sorok szó. A tartomány csak részben valósul meg ebben az időben, és ez azt jelentette, hogy a munka időszakok, míg a sorok pozicionális., Az előző és az aktuális sor közötti sorokból álló keret azt jelenti, hogy az ablak a partíció első sorából és az összes sorból áll az aktuális sorig. Minden számítás különböző sorokon történik. Például a 4. sor kiszámításakor az 1-4 sorokat használják.

az 5. sor kiszámításakor a sorok 1-5. Az ablak nagyobb lesz, ahogy az egyik sorról a másikra mozog.,

a szintaxissorokat az aktuális és az N sorok között is használhatja. Ez hasznos lehet például egy három hónapos mozgóátlag kiszámításához. Az alábbi ábra az előző és az aktuális sor közötti sorokat jelöli.

amikor 5 az aktuális sor, az ablak mozog; nem változtatja meg a méretét.,

itt található azoknak a kifejezéseknek a listája, amelyeket tudnia kell a keretezési lehetőség írásakor:

beismerem, hogy ez a szintaxis kissé zavaró, de az SQL Prompt használata megkönnyíti a keretezési lehetőség írását!

Offset funkciók

az SQL Server 2012 kiadásához is tartozik négy olyan funkció, amely lehetővé teszi, hogy más sorokból származó értékeket is tartalmazzon – öncsatlakozás nélkül. A Microsoft ezeket az “analitikus funkciókat” hívja, de mindig “offset funkcióknak” hívom őket, amikor bemutatom ezt a témát., A funkciók közül kettő lehetővé teszi, hogy oszlopokat vagy kifejezéseket húzzon egy sorból az aktuális sor előtt (LAG) vagy után (ólom). A másik két funkció lehetővé teszi az értékek visszatérését a partíció első sorából (FIRST_VALUE) vagy a partíció utolsó sorából (LAST_VALUE). A FIRST_VALUE és a LAST_VALUE keretezést is igényel, ezért ezeket a funkciókat feltétlenül vegye be a keretbe. Mind a négy funkció megköveteli a megrendelést az OVER záradék lehetőségével. Ennek van értelme, mert az adatbázis-motornak ismernie kell a sorok sorrendjét, hogy kitalálja, melyik sor tartalmazza a visszatérendő értéket.,

néhány embernek kedvenc zenekara van, néhány embernek kedvenc filmje van. Van egy kedvenc funkcióm-LAG. Könnyen használható (nincs keret!) és remekül teljesít., Here is an example:

LAG and LEAD argumentumot igényel – a visszatérni kívánt oszlop vagy kifejezés. Alapértelmezés szerint a LAG az előző sorból adja vissza az értéket, a LEAD pedig a következő sorból adja vissza az értéket. Ezt úgy módosíthatja, hogy megadja az OFFSET paraméter értékét, amely alapértelmezés szerint 1. Figyeljük meg, hogy az első sorban a partíció visszatér NULL. Ha felül szeretné írni a nullákat, megadhat egy alapértelmezett értéket., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE és LAST_VALUE értéket a partíció első sorából vagy utolsó sorából lehet megtalálni. Feltétlenül adja meg a keretet, nem csak teljesítmény okokból, hanem azért is, mert az alapértelmezett keret nem működik úgy, ahogy a LAST_VALUE esetében elvárnánk. Az alapértelmezett keret, amely az előző és az aktuális sor között van, csak az aktuális sorig terjed. A partíció utolsó sorát nem tartalmazza., A várt eredmények eléréséhez a LAST_VALUE használatakor feltétlenül adjon meg sorokat az aktuális sor és a határtalan követés között., Itt egy példa segítségével FIRST_VALUE:

statisztikai funkciók

a Microsoft csoportosítja ezt a négy funkciót – SZÁZALÉK_RANK, CUME_DIST, PERCENTILE_DISC, percentile_cont – az eltolás funkciókkal együtt mind a nyolc analitikus függvényt hívja. Mivel szeretem megkülönböztetni ezeket az eltolási függvényektől, ezeket statisztikai jellegűnek hívom.

SZÁZALÉK_RANK and CUME_DIST provide a ranking for each row over a partition. Ezek kissé eltérnek. A SZÁZALÉK_RANK az aktuális sornál alacsonyabb sorokat adja vissza., “A pontszámom magasabb, mint a pontszámok 90% – a.”CUME_DIST, vagy kumulatív eloszlása, visszaadja a pontos rangot. “A pontszámom a pontszámok 90% – án van.”Itt van egy példa az átlagos magas hőmérséklet St. Louis minden hónapban. Vegye figyelembe, hogy a rangokat a Fahrenheit hőmérséklet határozta meg.

a rangokat nem a relatív értékek határozzák meg, hanem a sorok pozíciói. Figyeljük meg, hogy március és November azonos átlagos magas hőmérséklet, így voltak rangsorolva azonos.

lehet, hogy kíváncsi, hogyan kell kiszámítani SZÁZALÉK_RANK és CUME_DIST., Íme a képlet:

PERCENTILE_DISC, valamint PERCENTILE_CONT munka az ellenkező irányba. Ha százalékos rangot kap, keresse meg az értéket ebben a rangsorban., Ezek abban különböznek, hogy a PERCENTILE_DISC visszaadja a készletben létező értéket, míg a PERCENTILE_CONT kiszámítja a pontos értéket, ha a készlet egyik értéke sem esik pontosan erre a rangra. Használhatja PERCENTILE_CONT kiszámításához medián ellátásával 0.5 mint a százalékos rang. Például, Melyik hőmérséklet 50% – os St. Louis-ban?

a PERCENTILE_CONT függvény a közepéhez legközelebb eső két érték átlagát veszi át, 67 és 69, és átlagolja őket. A PERCENTILE_DISC pontos értéket ad vissza, 67., Vegye figyelembe azt is, hogy e két funkciónak van egy extra záradéka, amely nem látható a csoporton belüli többi funkcióban, amely a megrendelést tartalmazza, nem pedig a túlzáradékon belül.

összefoglaló

Ez a cikk egy nagyon gyors áttekintést T-SQL ablak funkciók. Kétféle funkciót adtak ki az SQL Server 2005, a rangsor funkciók és az ablak aggregátumok. 2012-ben a keretezéssel és az analitikus funkciókkal megnövelt ablakösszeget kapott. Az analitikus függvényeket két csoportra, az eltolás és a statisztikai függvényekre bontom., Az ablakfunkciók sok lekérdezést megkönnyítenek, és úgy gondolom, hogy ez a fő előny. Egyes esetekben a lekérdezések jobban teljesítenek, is, de ez egy vita egy másik napra.

remélem, hogy ez a cikk inspirálta Önt, hogy többet megtudjon ezekről a fantasztikus funkciókról!