Подзапросы выборки в запросах

Выражения подзапросов выборки являются вложенными выражениями SELECT, которые используются для ограничения пространства куба, из которого вычисляется внешнее выражение SELECT. Подзапросы выборки позволяют определять новое пространство, в котором будут выполняться все вычисления.

Подзапросы выборки в примерах

Начнем с примера того, как подзапросы выборки могут помочь сформировать результаты, которые нам требуется отобразить. Предположим, требуется сформировать таблицу, отражающую динамику продаж за несколько лет по 10 самым популярным продуктам.

Полученный результат должен иметь следующий вид.

Для получения подобного результата можно было бы написать следующее многомерное выражение:

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

Оно возвращает следующие результаты.

Это очень близко к тому, что нам нужно, за исключением того, что запрос возвратил 9 продуктов, а не 10 и что итог «Все продукты» отражает сумму по всем продуктам, а не сумму по возвращенным 9 (в данном случае) самым популярным. Еще одна попытка решить проблему представлена в следующем многомерном запросе:

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

Оно возвращает следующие результаты.

Это было очень близко к желаемому результату, поскольку отсутствует только сумма по продуктам. На данном этапе можно было бы начать корректировать приведенное выше многомерное выражение таким образом, чтобы недостающая строка возвращалась, однако решение этой задачи может оказаться довольно громоздким.

Еще один подход к данной проблеме — попробовать переопределить пространство куба, в котором вычисляется многомерное выражение. Что если «новый» куб содержал бы только данные по 10 самым популярным продуктам? В таком кубе все элементы соответствовали бы только этим 10 популярным продуктам, и задачу тогда решил бы простой запрос.

В приведенном ниже многомерном выражении используется инструкция подзапроса выборки, переопределяющая пространство куба под 10 продуктов и формирующая следующий результат.

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]

Приведенное выше выражение возвратит следующие результаты.

Приведенные выше результаты в точности соответствуют требованию.

Рассмотрим, что именно сделал подзапрос выборки для получения этого результата. Он возвратил новый куб, содержащий все другие измерения для продукта «как есть», но в измерении самого продукта он отфильтровал все элементы таким образом, чтобы остались только 10 самых популярных продуктов, которые нас интересовали. Практически мы убрали все данные, которые не соответствовали критерию «10 самых популярных» и перестроили куб. В данном примере важно отметить еще один момент: 10 самых популярных продуктов вычислялись по всем элементам и всем измерениям в кубе. Это верно, поскольку в подзапросе выборки не было никаких иных ограничений фильтра.

Подзапросы выборки могут быть сложными настолько, насколько это необходимо. Следующий пример иллюстрирует формирование таблицы, аналогичной приведенной выше, но с фильтром «France» в измерении «Sales Territory» и «Internet» в измерении «Sales Channel».

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]

Выданы следующие результаты.

Приведенные выше результаты — это 10 самых популярных продуктов, проданных во Франции через Интернет.

Инструкция подзапроса выборки

Имя участника-службы для подзапроса выборки:

[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>]

Подзапрос выборки представляет собой еще одну инструкцию Select, где определения оси и среза фильтруют пространство куба, для которого вычисляется внешнее выражение 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]

Возвращает следующие значения:

В приведенном выше примере «Seattle» является дочерним для «Washington», «Portland» — для «Орегона», «Oregon» и «Washington» — дочерние для «United States», а «United States» — для [Customer Geography].[All Customers]. У всех отображаемых в этом примере элементов имеются другие одноуровневые элементы, влияющие на значение статистического выражения родительского элемента, например Spokane, Tacoma и Everett — это одноуровневые элементы для Seattle и все они влияют на сумму продаж через Интернет для Washington. Значение Reseller Sales Amount не зависит от атрибута «Customer Geography», поэтому в результатах отображается значение «Все». Следующее многомерное выражение иллюстрирует влияние фильтра на предложение подзапроса выборки.

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]
        )

Возвращает следующие значения:

Приведенные выше результаты показывают, что только предки и потомки штата Вашингтон входят в подпространство, по которому вычислялась внешняя инструкция SELECT. Орегон и Портленд были удалены из вложенного куба, поскольку Орегон и все другие штаты, имеющие общего родителя, не были упомянуты в подзапросе выборки, тогда как Вашингтон был.

Элемент «Все» отразил фильтрацию по штату Вашингтон, причем не только в измерении [Customer Geography], но также и в других измерениях, пересекающихся с [Customer Geography]. Все измерения, не пересекающиеся с [Customer Geography], остаются во вложенном кубе без изменений.

Приведенные ниже многомерные выражения иллюстрируют, каким образом элемент «Все» в других измерениях отражает фильтрацию в подзапросе выборки. Первый запрос отображает результаты без изменений, а второй показывает действие фильтра:

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]

В приведенном выше результате видим, что значения All Products скорректированы и отражают только данные по штату Вашингтон, как и предполагалось.

Подзапросы выборки могут быть вложенными, глубина вложенности может быть любой и ограничена только доступной памятью. Самый внутренний подзапрос выборки определяет начальное подпространство, к которому применяется фильтр, передаваемый затем соседней внешней операции выбора. Здесь важно отметить тот факт, что вложение не является коммутативной операцией, поэтому порядок вложенности может влиять на результаты. В приведенном ниже примере показаны различия в зависимости от порядка вложенности.

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]

Возвращены следующие результаты.

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]

Возвращены следующие результаты.

Очевидно, между двумя наборами результатов есть различия. Первый запрос ответил на вопрос, каковы пять наиболее популярных продуктов в пяти наиболее успешных регионах, а второй запрос — где лучше всего продаются пять наиболее популярных продуктов.

Замечания

Для подзапросов выборки действуют следующие ограничения.

• Предложение WHERE не фильтрует подпространство.

• Предложение WHERE изменяет элемент по умолчанию только во вложенном кубе.

• Предложение NON EMPTY нельзя использовать в предложении оси, вместо него следует использовать выражение функции NonEmpty (многомерные выражения).

• Предложение HAVING нельзя использовать в предложении оси, вместо него следует использовать выражение функции Filter (многомерные выражения).

• По умолчанию в подзапросах выборки запрещены вычисляемые элементы, однако это ограничение можно изменить по сеансам путем присвоения значения свойству строки подключения SubQueries в свойстве ConnectionString или DBPROP_MSMD_SUBQUERIES в Поддерживаемые свойства XML для аналитики (XMLA). Дополнительные сведения о поведении вычисляемых элементов в зависимости от значений свойства SubQueries или DBPROP_MSMD_SUBQUERIES см. в разделе Вычисляемые элементы в подзапросах выборки и вложенных кубах.