| T-SQL Ranking Funktionen unter SQL Server 2005 und 2008 |
 |
 |
 |
| SQL Server - SQL 2005 Administration | ||||||
| Geschrieben von: Fumus | ||||||
| Sonntag, 08. Februar 2009 um 14:42 | ||||||
Seite 1 von 2
Zu den T-SQL Erweiterungen des SQL Server 2005 gehören unter anderen die sogenannten Rangfolgefunktionen. Darunter befinden sich Rank(), Row_number(), Ntile() und Dense_Rank(). Was diese Funktionen leisten können Sie im folgenden Artikel nachlesen.
Zu den T-SQL Erweiterungen des SQL Server 2005 gehören unter anderen die sogenannten Rangfolgefunktionen. Darunter befinden sich Rank(), Row_number(), Ntile() und Dense_Rank(). Was diese Funktionen leisten können Sie im folgenden Artikel nachlesen.
Die sogenannten Rangfolgefunktionen erlauben es innerhalb der auszugebenden Daten Rangfolgenummern auszugeben. Zu diesen - eigtl OLAP Erweiterungen - Funktionen gehören:
Anhand eines kleinen überschaubaren Beispiels, möchte ich den den Einsatz der Funktionen näher zu bringen. Folgenden Tabelle soll als Ausgangspunkt für unsere Beispiele dienen.
/*------------------------
select * from Kunden ------------------------*/ KDID Firma Umsatz Ort ----------- ------------------------ --------------------- ----------- 1 Micodat 12000,00 84489 2 Orical 132222,00 76632 3 Logitak 3222,00 23444 4 ucdesign 12342,00 84489 5 ppedv 23456,00 84489 6 edv consulting 234543,00 76632 7 Traulsen Gmbh 33221,00 65542 8 Müller Brezn AG 12,00 54542 9 Wappis BHs 88765,00 50200 10 Preiselbär AG 12,00 54542 Die Reihenfolgenfunktionen haben im Prinzip immer den gleichen Aufbau.
Zuerst wird die Reihenfolgefunktion augerufen. Anschliessen muß per OVER (order by ...) die Spalte gewählt werden, über welche die Reihenfolgefunktion angewendet werden soll. Die partition by ... kann innerhalb der Sortierungsangabe eine Unterteilung vornehmen. Näheres dazu sehen wir in den folgenden Beispielen. ROW_NUMBER()Nehmen wir uns mal zunächst die Row_number() Funktion vor. Wir haben zwar in unserer Tabelle bereits eine fortlaufende ID (KD identity). Allerdings möchten wir unter Umständen die Zeilennummer auch mal anderweitig - bsp. Umsatz- vergeben. /*------------------------ select firma,ort, umsatz, row_number() over (order by Umsatz desc ) as ZNr from kunden ------------------------*/ firma ort umsatz ZNr -------------------------------------------------- ----------- --------------------- --- edv consulting 76632 234543,00 1 Orical 76632 132222,00 2 Wappis BHs 50200 88765,00 3 Traulsen Gmbh 65542 33221,00 4 ppedv 84489 23456,00 5 ucdesign 84489 12342,00 6 Micodat 84489 12000,00 7 Logitak 23444 3222,00 8 Müller Brezn AG 54542 12,00 9 Preiselbär AG 54542 12,00 10 Wir sehen, dass nun die Daten nach Umsatz sortiert wurden (..(order by Umsatz)..) und die Zeilennummer in dieser Reihenfolge vergeben worden sind. Natürlich können wir die Sortierung aufsteigen oder absteigend per desc bestimmen. Nun haben wir aber im Beispiel mehere Firmen die den gleichen Umsatz aufweisen. daher ist also sportlichen Denken angesagt. Gleicher Rang für gleiche Werte. RANK()Die Rank() Funktion verteilt, wie bei Sportereignissen den gleichen Rang für gleiche Ergebnisse. In unserem Fall durch das order by bestimmt. Wie erwartet sind die Umsätze mit 12,00 beide auf Rang 9 gelandet.
firma ort umsatz ZNr Rang -------------------------------------------------- ----------- --------------------- ----- ----- edv consulting 76632 234543,00 1 1 Orical 76632 132222,00 2 2 Wappis BHs 50200 88765,00 3 3 Traulsen Gmbh 65542 33221,00 4 4 ppedv 84489 23456,00 5 5 ucdesign 84489 12342,00 6 6 Micodat 84489 12000,00 7 7 Logitak 23444 3222,00 8 8 Müller Brezn AG 54542 12,00 9 9 Preiselbär AG 54542 12,00 10 9 DENSE_RANK()Die Rank() Funktion wird Ränge überspringen. In unserem Beispiel würde der Rang 10 nicht mehr vergeben werden. Im Sport nicht unähnlich. Nun möchte der eine oder der andere, dass durchaus alle Ränge vergeben werden. Dazu wird die Dense_Rank() Funktion zur Verfügung gestellt. /*------------------------ select firma,ort, umsatz, rank() over (order by ort desc ) as ZNr, dense_rank() over (order by ort desc ) as Rang from kunden ------------------------*/ firma ort umsatz ZNr Rang -------------------------------------------------- ----------- --------------------- ----- ----- ucdesign 84489 12342,00 1 1 ppedv 84489 23456,00 1 1 Micodat 84489 12000,00 1 1 Orical 76632 132222,00 4 2 edv consulting 76632 234543,00 4 2 Traulsen Gmbh 65542 33221,00 6 3 Müller Brezn AG 54542 12,00 7 4 Preiselbär AG 54542 12,00 7 4 Wappis BHs 50200 88765,00 9 5 Logitak 23444 3222,00 10 6 Hier sehen wir nun den direkten Vergleich zwischen Rank() und Dense_Rank(). Die Abfrage wurde ein wenig umgeschrieben -es wurde nach Ort sortiert - damit wir die verschiedenen Ergebnisse besser vergleichen können. NTILE()Als letztes nehmen wir die NTILE() Funktion zur Brust. Mit folgender Abfrage bekommen wir als Ergebnis: /*------------------------ select firma,ort, umsatz, ntile(3) over (order by Umsatz) as ABC from kunden ------------------------*/ firma ort umsatz ABC -------------------------------------------------- ----------- --------------------- ---- Müller Brezn AG 54542 12,00 1 Preiselbär AG 54542 12,00 1 Logitak 23444 3222,00 1 Micodat 84489 12000,00 1 ucdesign 84489 12342,00 2 ppedv 84489 23456,00 2 Traulsen Gmbh 65542 33221,00 2 Wappis BHs 50200 88765,00 3 Orical 76632 132222,00 3 edv consulting 76632 234543,00 3 Wie man sieht unterteilt NTILE() die Datensätze in gleiche Teile. Das heißt Ntile() versucht durch Angabe eines ganzzahligen Wertes die Datensätze in gleiche Teile zu partitionieren. Die einzelnen Partitionen müssen nicht immer gleich groß sein. Das hängt lediglich davon ab, ob die Teilung gleiche Bereiche zuläßt. So läßt sich gerade unsere Demotabelle mit 10 Zeilen und einem NTILE() von 3 nicht in gleiche Teile partitionieren. Daher weist der erste Bereich mehr Datensätze, als der andere auf. PARTITION_BYWas macht nun die optionale Klausel partition_by? Durch die zusätzlich Angabe von partition_by wird die angegebene Spalte (partition by Umsatz) aufgrund gleicher Werte in Partitionen unterteilt. Erst danach setzt die Rankingfunktion ein und vergibt ihre Skalierung auf die jeweilige Partition. /*------------------------ select firma, umsatz,ort, row_number() over (partition by ort order by ort) from kunden ------------------------*/ firma umsatz ort Partitions_RANG -------------------------------------------------- --------------------- ----------- -- Logitak 3222,00 23444 1 Wappis BHs 88765,00 50200 1 Preiselbär AG 12,00 54542 1 Müller Brezn AG 12,00 54542 2 Traulsen Gmbh 33221,00 65542 1 edv consulting 234543,00 76632 1 Orical 132222,00 76632 2 Micodat 12000,00 84489 1 ucdesign 12342,00 84489 2 ppedv 23456,00 84489 3 FAZITDie Rankingfunktionen sind im SQL Server 2005 nicht besonders schwierig im Umgang und die Einsatzmöglichkeiten lassen ein weites Feld zu. Nicht vergessen sollte man hierbei auch die TOP Klausel, sowie die CONTAINSTABLE Abfragen, die einen Relevanzfolgenwert ausgeben können.
|
