Oracle SQL Model Clause Vertiefung

Grundlegender Aufbau

Der grundlegende Aufbau der Model Clause ist bereits in einem anderen Blog-Eintrag beschrieben, den sie HIER finden.

Weitere Steuerungsmöglichkeiten

Im ersten Blog-Artikel wurde nur das main_model beachtet und auch von diesem nur die verpflichtenden Teile. Ergänzen wir das ganze nun um die cell_reference_options.

Die Cell Reference Options definieren wie mit fehlenden und leeren Werten umgegangen wird und wie streng die Eindeutigkeit geprüft wird. Im Detail sieht das wie folgt aus:

IGNORE NAV
Wenn diese Anweisung inkludiert wird, dann werden folgende Default Werte bei NULL-Werten oder fehlenden Werten zurückgegeben

• 0 für numerische Spalten
• 01.2000 für Datumsspalten
• Ein leerer String für Textspalten
• NULL für alle anderen Datentypen

KEEP NAV (Default)
Wenn diese Anweisung inkludiert wird, wird immer NULL zurückgegeben, wenn ein Wert fehlt oder NULL ist

UNIQUE DIMENSION (Default)
Wenn diese Anweisung inkludiert ist, muss die Kombination der Spalten der PARTITION BY und der DIMENSION Spalten eine Zeile eindeutig identifizieren (die Spalten müssten also einen Unique Key definieren können)

UNIQUE SINGLE REFERENCEMit dieser Anweisung werden lediglich Referenzen auf eine einzelne Zelle auf der rechten Seite auf Uniqueness geprüft, nicht das gesamte Set

NAV Anweisung

Wenn Werte nicht gefunden werden, definiert diese Anweisung wie damit umgegangen wird. Als Beispiel auf Basis der vorhandenen Testdaten sollen die folgenden beiden Statements zeigen wie die Auswirkungen sind:

select schueler, note, schulstufe
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler, schulstufe)
 MEASURES(note) keep nav(note [ 'Anton Anger',
           5 ] = note [ schueler = 'Anton Anger',
           schulstufe = 4 ]);

In diesem Fall mit KEEP NAV werden nicht vorhandene Werte mit NULL ausgegeben. Die Zeile mit Schulstufe 5 hat also in der Notenspalte einen NULL Wert stehen.

select schueler, note, schulstufe
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler, schulstufe)
 MEASURES(note) ignore nav(note [ 'Anton Anger',
           5 ] = note [ schueler = 'Anton Anger',
           schulstufe = 4 ]);

Wenn nun IGNORE NAV verwendet wird, dann wird der Wert 0 eingetragen.

UNIQUE Anweisung

Die Unique Anweisung ist sehr simpel umzusetzen. Auf Basis der schon im ersten Blogeintrag verwendeten Testdaten würde folgendes Statement fehlschlagen:

  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler) MEASURES(note)
 unique dimension (note [ schueler = 'Anton Anger'
                 ] = round(avg(note) [ schueler = 'Anton Anger' ], 0));

Der Grund dafür ist simpel: Die Spalte SCHUELER definiert keine eindeutige Zuweisung. Um das Statement valid zu machen, müsste man das Jahr oder die Schulstufe ergänzen, das würde dann wie folgt aussehen:

select schueler, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler, schulstufe)
 MEASURES(note) unique
 dimension(note [ schueler = 'Anton Angera',
                 schulstufe = 4
                 ] = round(avg(note) [ schueler = 'Anton Angera',
                           schulstufe between 1 and 3 ],

Oder alternativ die UNIQUE Anweisung ändern, was dann so aussehen würde:

select schueler, note
  from schulnoten
 where schuelernummer = 1
   and fach = 'Deutsch' MODEL DIMENSION BY(schueler) MEASURES(note)
 unique single
 reference(note [ schueler = 'Anton Anger'
                 ] = round(avg(note) [ schueler = 'Anton Angera' ], 0));