PDA

Vollständige Version anzeigen : Top & Rangfolgen (SQL Server 2000 und höher)


strausto
19.07.2006, 18:34
Beschreibung:

Im Grunde bietet die Top Klausel die Möglichkeit die ersten n order n % Datensätze aus einem Resultset zurückzuliefern.

Syntax:

[
TOP (expression) [PERCENT]
[ WITH TIES ]
]


Anhand des folgenden Beispiels wird schnell klar wie die Top Klausel arbeitet:


use tempdb
go
create table tbl_Top
(
id int identity(1,1) not null,
[col1] int not null,
[col2] varchar(10) not null
)
go
insert tbl_Top values (1,'a')
insert tbl_Top values (3,'c')
insert tbl_Top values (2,'b')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')
insert tbl_Top values (5,'f')
go
select top 2
*
from
tbl_Top
go
drop table tbl_Top
go

Resultset:
id col1 col2
1 1 a
2 3 c


Wie zu erwarten war beschränkt die Top Klausel die Abfrage auf die ersten zwei Datensätze.
Wichtig in diesem Zusammenhang zu wissen ist, dass die Auswahl der Zeilen ( in diesem Beispiel 2 ) dem Zufall unterliegt! Insofern man die Frage der Rangfolge beantworten will muss man der Abfrage die Sortierreihenfolge durch ein Order By mitteilen.


TOP mit Order By


select top 3
*
from
tbl_Top
order by
col1 desc,
col2 asc


Resultset:

id col1 col2
6 5 f
4 4 d
5 4 e

Es ist durchaus erlaubt in der Order by Klausel mehrere Spalten einzubeziehen und eine Top Klausel auf eine nicht numerische Spalte anzuwenden. Ebenfalls ist ein Prädikat in Form einer Where Klausel denkbar.

TOP With Ties

Beschränken wir die Betrachtung auf folgende Abfrage:


select top 4
col1
from
tbl_Top
order by
col1 asc


Resultset:
col1
1
2
3
4


Das Ergebnis beinhaltet genau vier Datensätze. Das ist soweit richtig - versucht man jedoch die Frage nach den ersten vier Werten unter Berücksichtigung der Rangfolge in der Spalte col1 zu beantworten, ist das Ergebnis unvollständig! Genau genommen sollte der Datensatz mit der ID 5 ebenfalls in dem Resultset auftauchen. Um dieser Anforderung Rechnung zu tragen, wurde die Top Klausel um „with ties“ erweitert


select top 4 with ties
col1
from
tbl_Top
order by
col1 asc


Resultset:

col1
1
2
3
4
4

Der Unterschied zwischen einer Top und einer Top with ties Anweisung, liegt also in der Behandlung redundanter Werte nach denen sortiert werden soll. Am besten lässt sich das mit einer Sportdisziplin vergleichen, in der es bei einem Gleichstand von Punkten doppelte Belegungen von Plätzen geben kann. In dem konkreten Fall sind die Werte 4 redundant und daher gleichwertig. Es resultieren also 2 vierte Plätze innerhalb der vereinbarten Rangfolge.

Bevor man sich also Fragen hinsichtlich der Rangfolge beantwortet, sollte man zunächst Gedanken über mögliche Gleichwertigkeiten anstellen.


TOP Percent

Sind wir an den ersten n Prozent einer Population interessiert, bietet uns der SQL Server die Erweiterung der Top Klausel um „Percent“.

Percent gibt n Percent Datensätze zurück, wobei immer die nächst größere Ganzzahl die tatsächliche Anzahl der Datensätze bestimmt.

Konkret bedeutet das in unserem Beispiel, dass Top 33 2 und Top 34 schon 3 Datensätze listet.

Berechnung:

Percent / 100 * Gesamtdatensätze aus der originären Tabelle = Anzahl Datensätze Resultset

33 / 100 * 6 = 1,98 entspricht 2 Datensätze
34 / 100 * 6 = 2,04 entspricht 3 Datensätze



select top 60 percent
col1
from
tbl_Top
order by
col1 asc


Resultset:

col1
1
2
3
4


TOP Percent With Ties

Die Verwendung von Percent in Verbindung mit With Ties ist ebenfalls möglich:


select top 60 percent with ties
col1
from
tbl_Top
order by
col1 asc


Resultset:

col1
1
2
3
4
4


Eigentlich sollten hier nur vier Datensätze zurückgeben werden, da die Berechnung (siehe oben) 3,6 als Anzahl ergibt. Jedoch wird der letzte Datensatz ergänzt da dieser mit dem vorherigen gleichwertig ist, obwohl damit eigentlich 83,3 % ausgeben werden.


Jeder zweite Datensatz

Will man beispielsweise jeden zweiten Datensatz innerhalb eines Resultsets zurückgeben, bietet sich folgende Lösung an:


select
id,
col1,
col2
from
tbl_Top
where
(select count(*) from tbl_Top A where A.id <= tbl_Top.ID) % 2 = 1


Resultset:

id col1 col2
1 1 a
3 2 b
5 4 e

Diese Lösung funktioniert immer dann, wenn in der Unterabfrage ein eindeutiger Wert (z.B. Primärschlüssel oder Identity Col) verglichen wird. Vergleicht man nicht eindeutige Werte kommt es zu einem falschen Ergebnis:


select
id,
col1,
col2
from
tbl_Top
where
(select count(*) from tbl_Top A where A.col1 <= tbl_Top.col1) % 2 = 1


Resultset:

id col1 col2
1 1 a
2 3 c
4 4 d
5 4 e

Eigentlich sollte man mit Rücksicht auf die Regeln für die Erstellung einer Datenbank immer einen eindeutigen Schlüssel je Tabelle einrichten, jedoch beweist die Praxis anderes. Daher gibt es für diese Situation einen anderen Ansatz.

Setzen wir voraus, dass wir keine eindeutige Spalte (ID) in unsere Tabelle haben.


use tempdb
go
create table tbl_Top
(
[col1] int not null,
[col2] varchar(10) not null
)
--ID eindeutige Spalte (ID) fehlt jetzt.
go
insert tbl_Top values (1,'a')
insert tbl_Top values (3,'c')
insert tbl_Top values (2,'b')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')
insert tbl_Top values (5,'f')
go
declare @temp_tbl_top table (
id int identity(1,1) not null,
[col1] int not null,
[col2] varchar(10) not null
)


insert @temp_tbl_top
Select
col1,
col2
from
tbl_Top

Select
*
from
@temp_tbl_top
go
drop table tbl_Top
go


Damit wird eine temporäre Tabelle erzeugt, die eine Kopie der originären Tabelle darstellt, jedoch erweitert wird um eine eindeutige Spalte. Somit kann die oben aufgeführte Anfrage wieder ausgeführt werden.


Der n - te und m - te Datensatz

Um eine Rückgabe des zweiten und fünften Datensatzes zu erreichen, ist folgende Abfrage einzustellen.


use tempdb
go
create table tbl_Top
(
[id] int identity(1,1) not null,
[col1] int not null,
[col2] varchar(10) not null
)
go
insert tbl_Top values (1,'a')
insert tbl_Top values (3,'c')
insert tbl_Top values (2,'b')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')
insert tbl_Top values (5,'f')
go
Select
*
from
tbl_Top
where
(select count(*) from tbl_Top A where A.ID <= tbl_Top.ID) IN (2,5)
go
drop table tbl_Top
go


Resultset:

id col1 col2
2 3 c
5 4 e

Alternative:

Select
A.*
from
tbl_Top A
join
tbl_Top B
ON
B.ID >= A.ID
group by
A.ID,
A.Col1,
A.Col2
Having
count(*) IN (2,5)


Resultset:

id col1 col2
2 3 c
5 4 e


Wobei zu erwähnen ist, dass die Abfrage mit dem auf der Grundlage des Inner Joins bei großen Daten deutlich länger dauert als die Erstellung der temporären Tabelle mit der Identity Column.


TOP With Ties je Gruppe

Will man jeweils die ersten zwei Datensätze einer Gruppe auswerten, kann man folgende Lösung anwenden.


use tempdb
go
create table tbl_Top
(
[id] int identity(1,1) not null,
[col1] int not null,
[col2] varchar(10) not null
)
go
insert tbl_Top values (1,'a')
insert tbl_Top values (2,'b')
insert tbl_Top values (3,'c')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')
insert tbl_Top values (5,'f')
insert tbl_Top values (0,'a')
insert tbl_Top values (5,'b')
insert tbl_Top values (-1,'c')
insert tbl_Top values (5,'d')
insert tbl_Top values (0,'e')
insert tbl_Top values (-9,'f')
insert tbl_Top values (5,'a')
insert tbl_Top values (2,'b')
insert tbl_Top values (-3,'c')
insert tbl_Top values (5,'d')
insert tbl_Top values (4,'e')
insert tbl_Top values (1,'f')
insert tbl_Top values (1,'a')
insert tbl_Top values (-1,'b')
insert tbl_Top values (2,'c')
insert tbl_Top values (5,'d')
insert tbl_Top values (4,'e')
insert tbl_Top values (5,'f')
go
Select
A.id,
A.col1,
A.col2,
col3 = (
Select
count(*)
from
tbl_Top B
where
A.col2 = B.col2
AND A.col1 > B.col1
) + 1
from
tbl_Top A
where
( Select
count(*)
from
tbl_Top B
where
A.col2 = B.col2
AND A.col1 > B.col1

) < 2
order by
col2,
col1
go
drop table tbl_Top
go


Resultset:

id col1 col2 col3
7 0 a 1
1 1 a 2
19 1 a 2
20 -1 b 1
14 2 b 2
2 2 b 2
15 -3 c 1
9 -1 c 2
4 4 d 1
10 5 d 2
16 5 d 2
22 5 d 2
11 0 e 1
5 4 e 2
23 4 e 2
17 4 e 2
12 -9 f 1
18 1 f 2

Diese Art der Abfrage verhält sich wie eine Top With Ties Anweisung. Gleichwertige Werte in Col1 werden berücksichtigt. Das order by in der Abfrage und die abgeleitete Spalte col3 dienen lediglich dem besseren Verständnis respektive Visualisierung und sind daher optional.

TOP je Gruppe

Will man auf doppelwertige Nennungen innerhalb einer Gruppe verzichten und so eine klassische Top Anweidung nachstellen, ist das mit einem weiteren Prädikat möglich:


Select
A.id,
A.col1,
A.col2,
col3 = (
Select
count(*)
from
tbl_Top B
where
A.col2 = B.col2
AND A.col1 > B.col1
) + 1,
col4 = (
Select
count(*)
from
tbl_Top B
where
A.col2 = B.col2
AND A.col1 = B.col1
AND A.ID > B.ID
)
from
tbl_Top A
where
( Select
count(*)
from
tbl_Top B
where
A.col2 = B.col2
AND A.col1 > B.col1

) < 2

AND
(
Select
count(*)
from
tbl_Top B
where
A.col2 = B.col2
AND A.col1 = B.col1
AND A.ID > B.ID
) = 0
order by
col2,
col1


Resultset:

id col1 col2 col3 col4
7 0 a 1 0
1 1 a 2 0
20 -1 b 1 0
2 2 b 2 0
15 -3 c 1 0
9 -1 c 2 0
4 4 d 1 0
10 5 d 2 0
11 0 e 1 0
5 4 e 2 0
12 -9 f 1 0
18 1 f 2 0

Auch hier sind die abgeleiteten Spalten und das order by überflüssig.


Bemerkungen

Die Top Klausel steht erst ab SQL Server 2000 zur Verfügung. Auf dem SQL Server 7 kann man auf SET ROWCOUNT n zurückgreifen. Diese Funktionalität steht weiterhin auf dem SQL Server 2000 und höher aus Gründen der Abwärtskompatibilität zur Verfügung. Man sollte jedoch die Top Klausel präferieren, da der Abfrageoptimierer mit derTop Klausel besser umgehen kann, als mit der SET ROWCOUNT Funktion.
Ein Index auf der zu sortierenden Spalte innerhalb einer Top Anweisung kann die Ausführung deutlich beschleunigen. Verwendet man stetig eine Top Anweisung für eine bestimmte Tabelle empfiehlt es sich, den Index entsprechend zu setzten.