Vlad Mihalcea

Einführung

In diesem Artikel werden wir sehen, wie wir eine SQL-Abfrageergebnismenge mithilfe einer ORDER BY-Klausel sortieren können, die eine Zufallsfunktion verwendet, die von einer datenbankspezifischen Funktion bereitgestellt wird.

Dies ist ein sehr praktischer Trick, insbesondere wenn Sie eine bestimmte Ergebnismenge mischen möchten.

Beachten Sie, dass das Sortieren einer großen Ergebnismenge mit einer Zufallsfunktion sehr langsam sein kann.

Wenn Sie eine große Ergebnismenge mischen und danach einschränken müssen, ist es besser, etwas wie das Oracle SAMPLE(N) oder das TABLESAMPLE in SQL Server oder PostgreSQL anstelle einer Zufallsfunktion in der ORDER BY-Klausel zu verwenden.

Datenbanktabelle

Nehmen wir an, wir entwickeln eine Musik-Player-Desktop-Anwendung und haben die folgende song Tabelle in unserer Datenbank.

Die song Tabelle wird wie folgt gefüllt:

id	artist	title
1	Miyagi & Эндшпиль ft. Рем Дигга	I Got Love
2	HAIM	Don’t Save Me (Cyril Hahn Remix)
3	2Pac ft. DMX	Aufstieg eines Champions (UHD Remix)
4	Ed Sheeran & Passagier	Keine Diggity (Kygo Remix)
5	JP Cooper ft. Mali-Koa	All This Love

Mit einer Zufallsfunktion in der SQL ORDER BY-Klausel

möchten wir die Songliste so mischen, dass sich jede neue Wiedergabeliste von einer zuvor generierten unterscheidet. Wann immer wir eine bestimmte SQL-Abfrageergebnismenge sortieren müssen, müssen wir die ORDER BY-Klausel verwenden.

Zur Randomisierung der zurückgegebenen Zeilen benötigen wir jedoch die ORDER BY-Klausel, um eine Funktion oder ein Datenbankobjekt zu verwenden, das für jede Zeile in der SQL-Ergebnismenge einen Zufallswert zurückgibt.

Wie bereits erwähnt, ist die Verwendung einer datenbankspezifischen Funktion, die Zufallswerte zurückgibt, nur für kleine Ergebnismengen geeignet, da sonst die SQL-Abfrageleistung beeinträchtigt wird. Glücklicherweise stellt in unserem Fall die Tabelle song die aktuelle Wiedergabeliste einer Desktop-Anwendung dar, daher ist die Größe der Ergebnismenge angemessen.

Oracle

Unter Oracle müssen Sie die Funktion DBMS_RANDOM.VALUE verwenden, wie im folgenden Beispiel veranschaulicht:

SELECT artist||' - '||title AS songFROM songORDER BY DBMS_RANDOM.VALUE

Wenn Sie die obige SQL-Abfrage unter Oracle ausführen, wird die folgende Ergebnismenge erhalten:

Lied

HAIM – Rette mich nicht (Cyril Hahn Remix)

Ed Sheeran & Passagier – Keine Diggity (Kygo Remix)

Miyagi & эндшпиль ft. Рем Дигга – Ich habe Liebe

JP Cooper ft. Mali-Koa – All diese Liebe

2Pac ft. DMX – Rise Of A Champion (UHD Remix)

Beachten Sie, dass die Songs in zufälliger Reihenfolge aufgelistet werden, dank des DBMS_RANDOM.VALUE Funktionsaufrufs, der von der ORDER BY-Klausel verwendet wird.

Die Funktion VALUE im Paket DBMS_RANDOM gibt einen numerischen Wert im Intervall [0, 1) mit einer Genauigkeit von 38 Bruchstellen zurück.

SQL Server

Auf SQL Server müssen Sie die Funktion NEWID verwenden, wie im folgenden Beispiel veranschaulicht:

SELECT CONCAT(CONCAT(artist, ' - '), title) AS songFROM songORDER BY NEWID()

Wenn Sie die obige SQL-Abfrage auf SQL Server ausführen, wird die folgende Ergebnismenge erhalten:

Lied

Miyagi & эндшпиль ft. Рем Дигга – Ich habe Liebe

HAIM – Rette mich nicht (Cyril Hahn Remix)

2Pac ft. DMX – Aufstieg eines Champions (FHD Remix)

Ed Sheeran & Passagier – Keine Diggity (Kygo Remix)

JP Cooper ft. Mali-Koa – All This Love

Beachten Sie, dass die Songs in zufälliger Reihenfolge aufgelistet werden, dank des NEWID() Funktionsaufrufs, der von der ORDER BY-Klausel verwendet wird.

Die NEWID Funktion gibt einen uniqueidentifier Datentyp zurück, der eine 16-Byte-GUID darstellt. Daher wird jeder neue Wert, der von der Funktion NEWID zurückgegeben wird, praktisch eindeutig sein.

PostgreSQL

Unter PostgreSQL müssen Sie die Funktion random verwenden, wie im folgenden Beispiel veranschaulicht:

SELECT artist||' - '||title AS songFROM songORDER BY random()

Wenn Sie die obige SQL-Abfrage unter PostgreSQL ausführen, wird die folgende Ergebnismenge erhalten Das ist der Song von JP Cooper feat. Mali-Koa – All diese Liebe

2Pac ft. DMX – Aufstieg eines Champions (UHD Remix)

HAIM – Rette mich nicht (Cyril Hahn Remix)

Ed Sheeran & Passagier – Keine Diggity (Kygo Remix)

Miyagi & эндшпиль ft. Рем Дигга – I Got Love

Beachten Sie, dass die Songs in zufälliger Reihenfolge aufgelistet werden, dank des random() Funktionsaufrufs, der von der ORDER BY-Klausel verwendet wird.

Die Funktion random gibt einen numerischen Wert im Intervall [0, 1) des Typs double precision zurück.

MySQL

Unter MySQL müssen Sie die Funktion RAND verwenden, wie im folgenden Beispiel veranschaulicht:

SELECT CONCAT(CONCAT(artist, ' - '), title) AS songFROM songORDER BY RAND()

Wenn Sie die obige SQL-Abfrage unter MySQL ausführen, erhalten Sie die folgende Ergebnismenge:

lied

2Pac ft. DMX – Aufstieg eines Champions (FHD Remix)

JP Cooper ft. Mali-Koa – All diese Liebe

Miyagi & эндшпиль ft. Рем Дигга – Ich habe Liebe

Ed Sheeran & Passagier – Keine Diggity (Kygo Remix)

HAIM – Rette mich nicht (Cyril Hahn Remix)

Beachten Sie, dass die Songs in zufälliger Reihenfolge aufgelistet werden, dank der RAND() Funktion aufruf, der von der ORDER BY-Klausel verwendet wird.

Die Funktion RAND gibt einen numerischen Wert im Intervall [0, 1) eines Gleitkommatyps zurück.

JPA und Hibernate

Wenn Sie JPA- und Hibernate-Entitätsabfragen verwenden, können Sie einfach die Zufallsfunktion an die ORDER BY-Klausel übergeben, wie in der folgenden JPQL-Abfrage dargestellt:

List<Song> songs = entityManager.createQuery( "SELECT s " + "FROM Song s " + "ORDER BY random()", Song.class).getResultList();

Die obige JPQL-Abfrage funktioniert einwandfrei für PostgreSQL, SQL Server und MySQL, da die random()NEWID() oder RAND() Funktionen werden vom Ruhezustand unverändert an die SQL ORDER BY übergeben.

Da die Zufälligkeit für Oracle durch den DBMS_RANDOM.VALUE Zahlengenerator gegeben ist, müssen Sie ihn als SQL-Funktion referenzieren (z. B. DBMS_RANDOM.VALUE()):

List<Song> songs = entityManager.createQuery( "SELECT s " + "FROM Song s " + "ORDER BY DBMS_RANDOM.VALUE()", Song.class).getResultList();

Das war’s!

Online-Workshops

Wenn Ihnen dieser Artikel gefallen hat, werden Sie meinen bevorstehenden 4-tägigen Online-Workshop lieben!

• High-Performance Java Persistence Online Workshop (4 Stunden x 4 Tage) zwischen dem 19. und 22.April

Fazit

Das Randomisieren der SQL-Abfrageergebnismenge ist eine häufige Anforderung, daher ist es nützlich zu wissen, welche SQL-Funktion Sie kann in der ORDER BY-Klausel abhängig vom zugrunde liegenden relationalen Datenbanktyp verwendet werden.