Analytische Funktionen (Teil 3): Gruppen mit zufälliger Auswahl der Datenbasis
Von Tobias Arnhold →
10.07.2015
Das folgende Beispiel bezieht sich auf die Datenbasis meines ersten Post zu "Analytischen Funktionen".
Es geht darum, dass für das Jahr 2014 drei Gruppen (mit einem Hash-Wert je Gruppe) gebildet werden müssen.
Gruppe 1:
- 3 zufällig ausgewählte Bundesländer die mit B anfangen
Gruppe 2:
- 3 zufällig ausgewählte Bundesländer die mit S anfangen
Gruppe 3:
- 3 zufällig ausgewählte Bundesländer die NICHT mit B und S anfangen
Hier das dafür notwendige SQL:
Im Endeffekt wird mit Hilfe der ROW_NUMBER() Funktion und DBMS_RANDOM.VALUE Funktion eine zufällige Verteilung geschaffen.
Es geht darum, dass für das Jahr 2014 drei Gruppen (mit einem Hash-Wert je Gruppe) gebildet werden müssen.
Gruppe 1:
- 3 zufällig ausgewählte Bundesländer die mit B anfangen Gruppe 2:
- 3 zufällig ausgewählte Bundesländer die mit S anfangen
Gruppe 3:
- 3 zufällig ausgewählte Bundesländer die NICHT mit B und S anfangen
Hier das dafür notwendige SQL:
SELECT
BUNDESLAND,
EINWOHNER,
GRUPPE,
/* Hash Generierung */
DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW (GRUPPE||'XYZ'), 1) AS HASH_WERT
FROM (
SELECT
JAHR,
BUNDESLAND,
EINWOHNER,
/* Gruppe definieren */
CASE
WHEN BUNDESLAND LIKE 'B%'
THEN 1
WHEN BUNDESLAND LIKE 'S%'
THEN 2
ELSE 3
END AS GRUPPE,
/* Zufällige Sortierung innerhalb der Gruppe */
ROW_NUMBER() OVER (
PARTITION BY
CASE
WHEN BUNDESLAND LIKE 'B%'
THEN 1
WHEN BUNDESLAND LIKE 'S%'
THEN 2
ELSE 3
END
ORDER BY DBMS_RANDOM.VALUE(1,10)
) AS RN
FROM AS_EINWO_BUNDESL_JAHR
WHERE JAHR = 2014
)
WHERE RN <= 3
ORDER BY GRUPPE, BUNDESLAND
/*
Nicht vergessen bei der Verwendung von DBMS_CRYPTO.HASH:
grant execute on sys.dbms_crypto to APEX_SCHEMA;
*/
Ergebnis:
| BUNDESLAND | EINWOHNER | GRUPPE | HASH_WERT |
|---|---|---|---|
| Baden-Württemberg | 10666000 | 1 | C5B1DF1A52DCC34C648FC79804F49A0A |
| Bayern | 12643000 | 1 | C5B1DF1A52DCC34C648FC79804F49A0A |
| Bremen | 659000 | 1 | C5B1DF1A52DCC34C648FC79804F49A0A |
| Sachsen | 4045000 | 2 | 38FAEEE9CD02869F273DE5A44CF75218 |
| Sachsen-Anhalt | 2238000 | 2 | 38FAEEE9CD02869F273DE5A44CF75218 |
| Schleswig-Holstein | 2819000 | 2 | 38FAEEE9CD02869F273DE5A44CF75218 |
| Hamburg | 1762000 | 3 | 7DA2D9ED3366BAEA4B1FA0D6269F7F9F |
| Mecklenburg-Vorpommern | 1594000 | 3 | 7DA2D9ED3366BAEA4B1FA0D6269F7F9F |
| Rheinland-Pfalz | 3996000 | 3 | 7DA2D9ED3366BAEA4B1FA0D6269F7F9F |
Im Endeffekt wird mit Hilfe der ROW_NUMBER() Funktion und DBMS_RANDOM.VALUE Funktion eine zufällige Verteilung geschaffen.
