Oracle? Oracle!! Tabelle aufteilen…

Manchmal kommt man in die Verlegenheit eine große Tabelle in gleichmäßige Gruppen zu unterteilen.

Da Oracle von Haus aus z.B. die TOP PERCENT nicht an Board hat über die man recht simpel in einer Schleife die z.B. 10 mal die ersten 10% einer Tabelle updaten könnte, habe ich gerade über einer möglichst schönen Lösung gegrübelt. Das Grübeln hat allerdings erheblich weniger Zeit benötigt, als meine bereits fertige Access VBA-Routine mit der Erledigung über den programmatischen Weg und nachdem ich fertig war, war diese gerade mal halb fertig. Statt 3 Stunden arbeitet der Rechner jetzt gerade mal 15 Sekunden an der Aufgabe!

Vorgabe
Trage in eine Tabelle in einer Hilfsspalte einen Wert so ein, dass sich daraus eine gleichmäßige Aufteilung der Tabelle in 4 Teile ergibt.

Lösung
UPDATE GROSSETABELLE SET TEILER_ID = CEIL(4*ROWNUM/(SELECT COUNT(*) FROM GROSSETABELLE));

Wobei die 4 in der Tabelle die Anzahl der gleichmäßigen Gruppen darstellt.

Das geht natürlich auch anhand eines Kriteriums in der Tabelle. Sagen wir, es gibt in der Tabelle die Spalte Hausnummer.

Erweiterte Vorgabe
Teile alle Datensätze für die Hausnummer 21 in 4 gleich große Bereiche auf.

UPDATE GROSSETABELLE X SET TEILER_ID = CEIL(4 * ROWNUM/(SELECT COUNT(*) FROM GROSSETABELLE Y WHERE Y.HAUSNUMMER=X.HAUSNUMMER )) WHERE HAUSNUMMER='21';

Die nächste Lösung ist dann natürlich die Krönung der Automatisierung, da sie nicht nur für eine konkrete Hausnummer funktioniert, sondern für alle vorkommenden Hausnummern die Unterteilung vornimmt:

BEGIN
FOR CUR IN
(
SELECT DISTINCT HAUSNUMMER FROM GROSSETABELLE
)
LOOP
EXECUTE IMMEDIATE 'UPDATE GROSSETABELLE X SET TEILER_ID = CEIL(4*ROWNUM/(SELECT COUNT(*) FROM GROSSETABELLE Y WHERE Y.HAUSNUMMER=X.HAUSNUMMER )) WHERE HAUSNUMMER='''||CUR.HAUSNUMMER||'''';
END LOOP;
END;
/

Oracle? Oracle!

Aufgabe:

Zeige mir nur die fünf neuesten Datensätze des Tages an – und zwar aufsteigend sortiert (und mach es etwas hübscher).

Die Lösung:

SELECT ZEIT, AKTION, DETAILS FROM
(
SELECT SUBSTR(TO_CHAR(TIMEDATE, 'DD-MON-YYYY HH24:MI:SS'),1,24) ZEIT, SUBSTR(ACTION,1,10) AKTION, SUBSTR(DESCRIPTION,1,66) DETAILS, TIMEDATE FROM STATUS WHERE TIMEDATE > TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY')) ORDER BY TIMEDATE DESC
)
WHERE ROWNUM <= 5 ORDER BY TIMEDATE /

Datum einschränken

TIMEDATE > TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY'))
Hier wird das Systemdatum durch zweifache Konvertierung seiner Uhrzeit beraubt und im Vergleich wird alles, was auf dem gleichen Datum liegt, aber später als 0:00 Uhr ist ausgegeben.

Benutzung von ROWNUM

In der inneren Abfrage werden die Datensätze absteigend nach TIMEDATE (eine Spalte mit Datum inkl. Uhrzeit) sortiert. In der äußeren Abfrage erhalten sie (siehe AskTom) eine neue ROWNUM (die ROWNUM der inneren Abfrage interessiert hier nicht), werden durch die Klausel

WHERE ROWNUM <= 5 ORDER BY TIMEDATE

auf 5 Datensätze eingeschränkt und danach aufsteigend sortiert.

Nicht verstanden? Macht nichts, wer sagt, dass Oracle einfach ist?

Veröffentlicht unter Oracle