Sub-SELECT nelle query

Le espressioni sub-SELECT sono espressioni SELECT nidificate utilizzate per limitare lo spazio del cubo dal quale viene valutata l'istruzione SELECT esterna. Le sub-SELECT consentono di definire un nuovo spazio sul quale tutti i calcoli vengono valutati.

Esempio di sub-SELECT

Si inizia con un esempio di come le sub-SELECT possono consentire di produrre i risultati che si desidera mostrare. Si supponga che sia stato richiesto di produrre una tabella che mostri il comportamento delle vendite, durante gli anni, per i primi 10 prodotti.

Il risultato sarà simile a quello contenuto nella tabella seguente:

Per eseguire un'operazione simile alla precedente, si potrebbe scrivere l'espressione MDX seguente:

SELECT [Date].[Calendar Year].MEMBERS on 0
     , TOPCOUNT( [Product].[Product].MEMBERS
               , 10
               , [Measures].[Sales Amount]
               ) ON 1
  FROM [Adventure Works]

Vengono restituiti i risultati seguenti:

Questo risultato è molto vicino a quello che si sta cercando, ma la query ha restituito 9 e non 10 prodotti e il totale di Tutti i prodotti riflette la somma di tutti i prodotti e non la somma dei primi 9 restituiti (in questo caso). Un altro tentativo di risolvere il problema viene presentato nella query MDX seguente:

SELECT [Date].[Calendar Year].MEMBERS on 0
     , TOPCOUNT( [Product].[Product].CHILDREN, 10, [Measures].[Sales Amount]) ON 1
  FROM [Adventure Works]

Vengono restituiti i risultati seguenti:

Questo risultato è molto vicino a quello desiderato perché manca solo la somma dei prodotti. In questa fase è possibile iniziare modificando l'espressione MDX precedente per aggiungere la riga mancante. Tuttavia, questa attività potrebbe rivelarsi complicata.

Un altro approccio per risolvere il problema sarebbe quello di ridefinire lo spazio del cubo sul quale l'espressione MDX viene risolta. Si supponga che il "nuovo" cubo contenga solo i dati dei primi 10 prodotti. In questo caso il membro Totale contenuto nel cubo verrà modificato in modo da includere solo i primi 10 prodotti e ora una semplice query soddisferebbe le nostre esigenze.

Nell'espressione MDX seguente viene utilizzata un'istruzione sub-SELECT per ridefinire lo spazio del cubo in base ai primi 10 prodotti e produrre i risultati desiderati:

SELECT [Date].[Calendar Year].MEMBERS on 0
     , [Product].[Product].MEMBERS on 1
  FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN
                       , 10
                       , [Measures].[Sales Amount]
                       ) ON 0
          FROM [Adventure Works]
        )
 WHERE [Measures].[Sales Amount]

Tramite l'espressione sopra indicata vengono restituiti i risultati seguenti:

I risultati precedenti sono esattamente quelli desiderati.

Di seguito vengono esaminate le operazioni eseguite dall'istruzione sub-SELECT. La sub-SELECT ha restituito un nuovo cubo che contiene tutte le altre dimensioni del prodotto così come sono. Nella dimensione del prodotto sono stati tuttavia filtrati tutti i membri in modo da farli corrispondere ai primi 10 prodotti a cui si è interessati. È come se fossero stati rimossi tutti i dati che non hanno soddisfatto i Primi 10 criteri e avevano ricostruito il cubo. L'altro concetto importante da capire in questo esempio è il fatto che i primi 10 prodotti sono stati calcolati sul membro Totale di tutte le altre dimensioni del cubo. L'affermazione precedente è vera perché non è stata imposta nessun'altra restrizione di filtro nella sub-SELECT.

Le sub-SELECT possono avere il livello di complessità desiderato. Nell'esempio seguente viene illustrato come produrre una tabella simile a quella menzionata sopra, ma filtrata sulla Francia per la dimensione Sales Territory (territorio di vendita) e su Internet per la dimensione Sales Channel (canale di vendita).

SELECT [Date].[Calendar Year].MEMBERS on 0
     , [Product].[Product].MEMBERS on 1
  FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN
                       , 10
                       , [Measures].[Sales Amount]
                       ) ON 0
             , [Sales Territory].[Sales Territory].[Region].[France] on 1
             ,  [Sales Channel].[Sales Channel].[Internet] on 2
          FROM [Adventure Works]
        )
 WHERE [Measures].[Sales Amount]

Vengono prodotti i risultati seguenti:

I risultati sopra riportati sono i primi 10 prodotti venduti in Francia tramite il canale Internet.

Istruzione sub-SELECT

La notazione BNF per la sub-SELECT è:

[WITH [<calc-clause> ...]]
SELECT [<axis-spec> [, <axis-spec> ...]]
FROM [<identifier> | (< sub-select-statement >)]
[WHERE <slicer>]
[[CELL] PROPERTIES <cellprop> [, <cellprop> ...]]

< sub-select-statement > :=
   SELECT [<axis-spec> [, <axis-spec> ...]]
   FROM [<identifier> | (< sub-select-statement >)]
   [WHERE <slicer>]

La sub-SELECT è un'altra istruzione SELECT in cui le specifiche dell'asse e la specifica del sezionamento filtrano lo spazio del cubo sul quale viene valutata l'istruzione SELECT esterna.

Quando un membro viene specificato in una delle clausole dell'asse o di sezionamento, tale membro e i relativi predecessori e discendenti vengono inclusi nello spazio del sottocubo per la sub-SELECT. Tutti i membri di pari livello non menzionati nella clausola dell'asse o di sezionamento e i relativi discendenti vengono filtrati dal sottospazio. In tal modo lo spazio dell'istruzione SELECT esterna è stato limitato ai membri esistenti nella clausola dell'asse o nella clausola di sezionamento, con i relativi predecessori e discendenti come menzionato in precedenza.

Poiché il membro Totale di tutte le dimensioni non menzionate nelle clausole dell'asse o di sezionamento appartiene allo spazio dell'istruzione SELECT, anche tutti i discendenti del membro Totale su tali dimensioni fanno parte dello spazio della sub-SELECT.

Il membro Totale, in tutte le dimensioni, nello spazio del sottocubo viene rivalutato in modo da riflettere l'impatto dei vincoli del nuovo spazio.

Nell'esempio seguente viene mostrato quanto detto sopra. La prima espressione MDX consente di visualizzare i valori non filtrati nel cubo e nella seconda viene illustrato l'effetto del filtro nella clausola sub-SELECT:

SELECT { [Customer].[Customer Geography].[All Customers]
       , [Customer].[Customer Geography].[Country].&[United States]
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]
       } ON 1
     ,  {[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} ON 0
  FROM [Adventure Works]

Restituisce i valori seguenti:

Nell'esempio precedente Seattle è un figlio di Washington, Portland è un figlio di Oregon, Oregon e Washington sono figli di Stati Uniti e Stati Uniti è un figlio di [Customer Geography].[All.Customers]. Tutti i membri mostrati in questo esempio dispongono di altri elementi di pari livello che contribuiscono al valore di aggregazione padre; cioè Spokane, Tacoma e Everett sono città di pari livello di Seattle e tutto contribuire a Importo vendite Internet di Washington. Il valore di Importo vendite venditore è indipendente dall'attributo Customer Geography (Dati geografici cliente), pertanto il valore Totale viene visualizzato nei risultati. L'espressione MDX successiva illustra l'effetto del filtro della clausola sub-SELECT:

SELECT { [Customer].[Customer Geography].[All Customers]
       , [Customer].[Customer Geography].[Country].&[United States]
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]
       } ON 1
     ,  {[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} ON 0
  FROM ( SELECT [Customer].[State-Province].&[WA]&[US] ON 0
           FROM [Adventure Works]
        )

Restituisce i valori seguenti:

I risultati precedenti mostrano che solo i predecessori e i discendenti dello Stato di Washington fanno parte del sottospazio in cui è stata valutata l'istruzione SELECT esterna. Oregon e Portland sono stati rimossi dal sottocubo perché Oregon e tutti gli altri stati di pari livello non sono stati menzionati nella sub-SELECT quando è stato menzionato Washington.

Il membro Totale è stato modificato in modo da riflettere l'applicazione del filtro su Washington. È stato modificato non solo nella dimensione [Customer Geography], ma in tutte le altre dimensioni incrociate con [Customer Geography]. Tutte le dimensioni che non si incrociano con [Customer Geography] restano inalterate nel sottocubo.

Le due istruzioni MDX seguenti illustrano come il membro Totale nelle altre dimensioni viene modificato per soddisfare l'effetto del filtro della sub-SELECT. La prima query visualizza i risultati inalterati e la seconda mostra l'impatto dell'applicazione del filtro:

SELECT { [Customer].[Customer Geography].[All Customers]
       , [Customer].[Customer Geography].[Country].&[United States]
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]
       } ON 1
     ,   [Product].[Product Line].MEMBERS ON 0
  FROM [Adventure Works]
 WHERE [Measures].[Internet Sales Amount]

SELECT { [Customer].[Customer Geography].[All Customers]
       , [Customer].[Customer Geography].[Country].&[United States]
       , [Customer].[Customer Geography].[State-Province].&[OR]&[US]
       , [Customer].[Customer Geography].[City].&[Portland]&[OR]
       , [Customer].[Customer Geography].[State-Province].&[WA]&[US]
       , [Customer].[Customer Geography].[City].&[Seattle]&[WA]
       } ON 1
     ,   [Product].[Product Line].MEMBERS ON 0
  FROM ( SELECT [Customer].[State-Province].&[WA]&[US] ON 0
           FROM [Adventure Works]
        )
 WHERE [Measures].[Internet Sales Amount]

I risultati precedenti mostrano che i valori di Tutti i prodotti sono stati adattati solo ai valori dello Stato di Washington, come previsto.

Le sub-SELECT possono essere annidate senza limiti di profondità, compatibilmente con la memoria disponibile. La sub-SELECT più interna definisce il sottospazio iniziale sul quale il filtro viene applicato e passato alla SELECT esterna successiva. È importante notare il fatto che l'annidamento non è un'operazione commutativa, quindi l'ordine nel quale viene impostato potrebbe produrre risultati diversi. Negli esempi seguenti viene mostrata la differenza quando si sceglie un ordine di nidificazione.

SELECT [Sales Territory].[Sales Territory Region].MEMBERS on 0
     , [Product].[Product].MEMBERS on 1
  FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN, 5, [Measures].[Sales Amount]) ON 0
          FROM (SELECT TOPCOUNT( [Sales Territory].[Sales Territory Region].CHILDREN, 5, [Measures].[Sales Amount]) on 0
                  FROM [Adventure Works]
               )
        )
 WHERE [Measures].[Sales Amount]

Vengono restituiti i risultati seguenti.

SELECT [Sales Territory].[Sales Territory Region].MEMBERS on 0
     , [Product].[Product].MEMBERS on 1
  FROM (SELECT TOPCOUNT( [Sales Territory].[Sales Territory Region].CHILDREN, 5, [Measures].[Sales Amount]) ON 0
          FROM (SELECT TOPCOUNT( [Product].[Product].CHILDREN, 5, [Measures].[Sales Amount]) on 0
                  FROM [Adventure Works]
               )
        )
 WHERE [Measures].[Sales Amount]

Vengono restituiti i risultati seguenti.

Come è possibile notare, ci sono differenze nei risultati in entrambi i set. La prima query ha risposto alla domanda sui prodotti più venduti nelle 5 aree principali, la seconda query ha risposto alla domanda sulle aree in cui si è verificato il maggior numero di vendite dei 5 prodotti più venduti.

Osservazioni

Le sub-SELECT sono sottoposte alle restrizioni e limitazioni seguenti:

• La clausola WHERE non filtra il sottospazio.

• La clausola WHERE modifica solo il membro predefinito nel sottocubo.

• La clausola NON EMPTY non è consentita in una clausola dell'asse; utilizzare invece un'espressione della funzione NonEmpty (MDX).

• La clausola HAVING non è consentita in una clausola dell'asse; utilizzare invece un'espressione della funzione Filter (MDX).

• Per impostazione predefinita i membri calcolati non sono consentiti nelle sub-SELECT. È tuttavia possibile modificare questa restrizione, per ogni singola sessione, assegnando un valore alla proprietà della stringa di connessione SubQueries in ConnectionString o alla proprietà DBPROP_MSMD_SUBQUERIES in Proprietà XMLA supportate (XMLA). Vedere Membri calcolati in sub-SELECT e sottocubi per una spiegazione dettagliata del comportamento dei membri calcolati a seconda dei valori di SubQueries o DBPROP_MSMD_SUBQUERIES.