Подзапросы выборки в запросах
Применимо к:
SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium
Выражения подзапросов выборки являются вложенными выражениями SELECT, которые используются для ограничения пространства куба, из которого вычисляется внешнее выражение SELECT. Подзапросы выборки позволяют определять новое пространство, в котором будут выполняться все вычисления.
Подзапросы выборки в примерах
Начнем с примера того, как подзапросы выборки могут помочь сформировать результаты, которые нам требуется отобразить. Предположим, требуется сформировать таблицу, отражающую динамику продаж за несколько лет по 10 самым популярным продуктам.
Полученный результат должен иметь следующий вид.
| Sum of Years | Year 1 | ... | |
|---|---|---|---|
| Sum of Top 10 Products | |||
| Product A | |||
| ... |
Для получения подобного результата можно было бы написать следующее многомерное выражение:
SELECT [Date].[Calendar Year].MEMBERS on 0
, TOPCOUNT( [Product].[Product].MEMBERS
, 10
, [Measures].[Sales Amount]
) ON 1
FROM [Adventure Works]
Оно возвращает следующие результаты.
| All Periods | CY 2005 | CY 2006 | CY 2007 | CY 2008 | |
|---|---|---|---|---|---|
| Все продукты | $ 80 450 596,98 | 8 065 435,31 $ | 24 144 429,65 $ | 32 202 669,43 $ | 16 038 062,60 $ |
| Mountain-200 Black, 38 | 1 634 647,94 $ | (null) | (null) | 894 207,97 $ | 740 439,97 $ |
| Mountain-200 Black, 42 | 1 285 524,65 $ | (null) | (null) | 722 137,65 $ | 563 387,00 $ |
| Mountain-200 Silver, 38 | 1 181 945,82 $ | (null) | (null) | 634 600,78 $ | 547 345,03 $ |
| Mountain-200 Black, 46 | 995 927,43 $ | (null) | (null) | 514 995,76 $ | 480 931,68 $ |
| Mountain-200 Silver, 42 | 1 005 111,77 $ | (null) | (null) | 529 543,29 $ | 475 568,49 $ |
| Mountain-200 Silver, 46 | 975 932,56 $ | (null) | (null) | 526 759,30 $ | 449 173,26 $ |
| Road-150 Red, 56 | 792 228,98 $ | 382 159,24 $ | 410 069,74 $ | (null) | (null) |
| Mountain-200 Black, 38 | 1 471 078,72 $ | (null) | 789 958,49 $ | 681 120,23 $ | (null) |
| Road-350-W Yellow, 48 | 1 380 253,88 $ | (null) | (null) | 744 988,37 $ | 635 265,50 $ |
Это очень близко к тому, что нам нужно, за исключением того, что запрос возвратил 9 продуктов, а не 10 и что итог «Все продукты» отражает сумму по всем продуктам, а не сумму по возвращенным 9 (в данном случае) самым популярным. Еще одна попытка решить проблему представлена в следующем многомерном запросе:
SELECT [Date].[Calendar Year].MEMBERS on 0
, TOPCOUNT( [Product].[Product].CHILDREN, 10, [Measures].[Sales Amount]) ON 1
FROM [Adventure Works]
Оно возвращает следующие результаты.
| All Periods | CY 2005 | CY 2006 | CY 2007 | CY 2008 | |
|---|---|---|---|---|---|
| Mountain-200 Black, 38 | 1 634 647,94 $ | (null) | (null) | 894 207,97 $ | 740 439,97 $ |
| Mountain-200 Black, 42 | 1 285 524,65 $ | (null) | (null) | 722 137,65 $ | 563 387,00 $ |
| Mountain-200 Silver, 38 | 1 181 945,82 $ | (null) | (null) | 634 600,78 $ | 547 345,03 $ |
| Mountain-200 Black, 46 | 995 927,43 $ | (null) | (null) | 514 995,76 $ | 480 931,68 $ |
| Mountain-200 Silver, 42 | 1 005 111,77 $ | (null) | (null) | 529 543,29 $ | 475 568,49 $ |
| Mountain-200 Silver, 46 | 975 932,56 $ | (null) | (null) | 526 759,30 $ | 449 173,26 $ |
| Road-150 Red, 56 | 792 228,98 $ | 382 159,24 $ | 410 069,74 $ | (null) | (null) |
| Mountain-200 Black, 38 | 1 471 078,72 $ | (null) | 789 958,49 $ | 681 120,23 $ | (null) |
| Road-350-W Yellow, 48 | 1 380 253,88 $ | (null) | (null) | 744 988,37 $ | 635 265,50 $ |
| Road-150 Red, 62 | 566 797,97 $ | 234 018,86 $ | 332 779,11 $ | (null) | (null) |
Это было очень близко к желаемому результату, поскольку отсутствует только сумма по продуктам. На данном этапе можно было бы начать корректировать приведенное выше многомерное выражение таким образом, чтобы недостающая строка возвращалась, однако решение этой задачи может оказаться довольно громоздким.
Еще один подход к данной проблеме — попробовать переопределить пространство куба, в котором вычисляется многомерное выражение. Что если «новый» куб содержал бы только данные по 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]
Приведенное выше выражение возвратит следующие результаты.
| All Periods | CY 2005 | CY 2006 | CY 2007 | CY 2008 | |
|---|---|---|---|---|---|
| Все продукты | 19 997 183,30 $ | 1 696 815,63 $ | 2 816 611,28 $ | 7 930 797,72 $ | 7 552 958,66 $ |
| Mountain-200 Silver, 38 | 2 160 981,60 $ | (null) | (null) | 1 024 359,10 $ | 1 136 622,49 $ |
| Mountain-200 Silver, 42 | 1 914 547,85 $ | (null) | (null) | 903 061,68 $ | 1 011 486,18 $ |
| Mountain-200 Silver, 46 | 1 906 248,55 $ | (null) | (null) | 877 077,79 $ | 1 029 170,76 $ |
| Mountain-200 Black, 38 | 1 811 229,02 $ | (null) | 896 511,60 $ | 914 717,43 $ | (null) |
| Mountain-200 Black, 38 | 2 589 363,78 $ | (null) | (null) | 1 261 406,37 $ | 1 327 957,41 $ |
| Mountain-200 Black, 42 | 2 265 485,38 $ | (null) | (null) | 1 126 055,89 $ | 1 139 429,49 $ |
| Mountain-200 Black, 46 | 1 957 528,24 $ | (null) | (null) | 946 453,88 $ | 1 011 074,37 $ |
| Road-150 Red, 62 | 1 769 096,69 $ | 828 011,68 $ | 941 085,01 $ | (null) | (null) |
| Road-150 Red, 56 | 1 847 818,63 $ | 868 803,96 $ | 979 014,67 $ | (null) | (null) |
| Road-350-W Yellow, 48 | 1 774 883,56 $ | (null) | (null) | 877 665,59 $ | 897 217,96 $ |
Приведенные выше результаты в точности соответствуют требованию.
Рассмотрим, что именно сделал подзапрос выборки для получения этого результата. Он возвратил новый куб, содержащий все другие измерения для продукта «как есть», но в измерении самого продукта он отфильтровал все элементы таким образом, чтобы остались только 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]
Выданы следующие результаты.
| All Periods | CY 2005 | CY 2006 | CY 2007 | CY 2008 | |
|---|---|---|---|---|---|
| Все продукты | 748 682,49 $ | 32 204,43 $ | 73 125,18 $ | 269 506,56 $ | 373 846,32 $ |
| Mountain-200 Silver, 38 | 90 479,61 $ | (null) | (null) | 41 759,82 $ | 48 719,79 $ |
| Mountain-200 Silver, 42 | 97 439,58 $ | (null) | (null) | 39 439,83 $ | 57 999,75 $ |
| Mountain-200 Silver, 46 | 102 079,56 $ | (null) | (null) | 27 839,88 $ | 74 239,68 $ |
| Mountain-200 Black, 38 | 26 638,28 $ | (null) | 12 294,59 $ | 14 343,69 $ | (null) |
| Mountain-200 Black, 38 | 96 389,58 $ | (null) | (null) | 41 309,82 $ | 55 079,76 $ |
| Mountain-200 Black, 42 | 80 324,65 $ | (null) | (null) | 43 604,81 $ | 36 719,84 $ |
| Mountain-200 Black, 46 | 107 864,53 $ | (null) | (null) | 45 899,80 $ | 61 964,73 $ |
| Road-150 Red, 62 | 46 517,51 $ | 14 313,08 $ | 32 204,43 $ | (null) | (null) |
| Road-150 Red, 56 | 46 517,51 $ | 17 891,35 $ | 28 626,16 $ | (null) | (null) |
| Road-350-W Yellow, 48 | 54 431,68 $ | (null) | (null) | 15 308,91 $ | 39 122,77 $ |
Приведенные выше результаты — это 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]
Возвращает следующие значения:
| Internet Sales Amount | Reseller Sales Amount | |
|---|---|---|
| Все клиенты | 29 358 677,22 $ | $ 80 450 596,98 |
| США | 9 389 789,51 $ | $ 80 450 596,98 |
| Орегон | 1 170 991,54 $ | $ 80 450 596,98 |
| Портленд | 110 649,54 $ | $ 80 450 596,98 |
| Вашингтон | 2 467 248,34 $ | $ 80 450 596,98 |
| Сиэтл; | 75 164,86 $ | $ 80 450 596,98 |
В приведенном выше примере «Seattle» является дочерним для «Washington», «Portland» — для «Орегона», «Oregon» и «Washington» — дочерние для «США», а «США» — для [Customer Geography].[All Customers]. Все элементы, показанные в данном примере, имеют другие элементы с общим родителем, влияющие на статистическое значение родителя, т. е. города Спокане, Такома и Эверетт имеют общего родителя с Сиэтлом и все они будут влиять на сумму для продаж через Интернет в штате Вашингтон. Значение 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]
)
Возвращает следующие значения:
| Internet Sales Amount | Reseller Sales Amount | |
|---|---|---|
| Все клиенты | 2 467 248,34 $ | $ 80 450 596,98 |
| США | 2 467 248,34 $ | $ 80 450 596,98 |
| Вашингтон | 2 467 248,34 $ | $ 80 450 596,98 |
| Сиэтл; | 75 164,86 $ | $ 80 450 596,98 |
Приведенные выше результаты показывают, что только предки и потомки штата Вашингтон входят в подпространство, по которому вычислялась внешняя инструкция 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]
| Все продукты | Accessory | Компоненты | Mountain | Дорожная | Touring | |
|---|---|---|---|---|---|---|
| Все клиенты | 29 358 677,22 $ | 604 053,30 $ | (null) | 10 251 183,52 $ | 14 624 108,58 $ | 3 879 331,82 $ |
| США | 9 389 789,51 $ | 217 168,79 $ | (null) | 3 547 956,78 $ | 4 322 438,41 $ | 1 302 225,54 $ |
| Орегон | 1 170 991,54 $ | 30 513,17 $ | (null) | 443 607,98 $ | 565 372,10 $ | 131 498,29 $ |
| Портленд | 110 649,54 $ | 2 834,17 $ | (null) | 47 099,91 $ | 53 917,17 $ | 6 798,29 $ |
| Вашингтон | 2 467 248,34 $ | 62 662,92 $ | (null) | 945 219,38 $ | 1 155 880,07 $ | 303 485,97 $ |
| Сиэтл; | 75 164,86 $ | 2 695,74 $ | (null) | 19 914,53 $ | 44 820,06 $ | 7 734,54 $ |
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]
| Все продукты | Accessory | Компоненты | Mountain | Дорожная | Touring | |
|---|---|---|---|---|---|---|
| Все клиенты | 2 467 248,34 $ | 62 662,92 $ | (null) | 945 219,38 $ | 1 155 880,07 $ | 303 485,97 $ |
| США | 2 467 248,34 $ | 62 662,92 $ | (null) | 945 219,38 $ | 1 155 880,07 $ | 303 485,97 $ |
| Вашингтон | 2 467 248,34 $ | 62 662,92 $ | (null) | 945 219,38 $ | 1 155 880,07 $ | 303 485,97 $ |
| Сиэтл; | 75 164,86 $ | 2 695,74 $ | (null) | 19 914,53 $ | 44 820,06 $ | 7 734,54 $ |
В приведенном выше результате видим, что значения 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]
Возвращены следующие результаты.
| All Sales Territories | Австралия | Канада | Central | Northwest | Southwest | |
|---|---|---|---|---|---|---|
| Все продукты | 7 591 495,49 $ | 1 281 059,99 $ | 1 547 298,12 $ | 600 205,79 $ | 1 924 763,50 $ | 2 238 168,08 $ |
| Mountain-200 Silver, 38 | 1 449 576,15 $ | 248 702,93 $ | 275 052,45 $ | 141 103,65 $ | 349 487,01 $ | 435 230,12 $ |
| Mountain-200 Black, 38 | 1 722 896,50 $ | 218 024,05 $ | 418 726,43 $ | 123 929,46 $ | 486 694,63 $ | 475 521,93 $ |
| Mountain-200 Black, 42 | 1 573 655,14 $ | 239 137,96 $ | 319 921,61 $ | 130 102,75 $ | 420 445,84 $ | 464 046,98 $ |
| Mountain-200 Black, 46 | 1 420 500,58 $ | 192 320,16 $ | 230 875,99 $ | 117 044,49 $ | 424 813,66 $ | 455 446,27 $ |
| Road-150 Red, 56 | 1 424 867,11 $ | 382 874,89 $ | 302 721,64 $ | 88 025,44 $ | 243 322,36 $ | 407 922,78 $ |
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]
Возвращены следующие результаты.
| All Sales Territories | Австралия | Канада | Northwest | Southwest | Соединенное Королевство | |
|---|---|---|---|---|---|---|
| Все продукты | 7 938 218,56 $ | 1 096 312,24 $ | 1 474 255,49 $ | 2 042 674,72 $ | 2 238 099,55 $ | 1 086 876,56 $ |
| Mountain-200 Silver, 38 | 1 520 958,53 $ | 248 702,93 $ | 275 052,45 $ | 349 487,01 $ | 435 230,12 $ | 212 486,03 $ |
| Mountain-200 Silver, 42 | 1 392 237,14 $ | 198 127,15 $ | 229 679,01 $ | 361 233,58 $ | 407 854,24 $ | 195 343,16 $ |
| Mountain-200 Black, 38 | 1 861 703,23 $ | 218 024,05 $ | 418 726,43 $ | 486 694,63 $ | 475 521,93 $ | 262 736,19 $ |
| Mountain-200 Black, 42 | 1 702 427,25 $ | 239 137,96 $ | 319 921,61 $ | 420 445,84 $ | 464 046,98 $ | 258 874,87 $ |
| Mountain-200 Black, 46 | 1 460 892,41 $ | 192 320,16 $ | 230 875,99 $ | 424 813,66 $ | 455 446,27 $ | 157 436,31 $ |
Очевидно, между двумя наборами результатов есть различия. Первый запрос ответил на вопрос, каковы пять наиболее популярных продуктов в пяти наиболее успешных регионах, а второй запрос — где лучше всего продаются пять наиболее популярных продуктов.
Комментарии
Для подзапросов выборки действуют следующие ограничения.
Предложение WHERE не фильтрует подпространство.
Предложение WHERE изменяет элемент по умолчанию только во вложенном кубе.
Предложение NON EMPTY не допускается в предложении оси; вместо этого используйте выражение функции NonEmpty (многомерные выражения).
Предложение HAVING не допускается в предложении оси; вместо этого используйте выражение функции Filter (многомерные выражения).
По умолчанию вычисляемые элементы не допускаются во вложенных выборах; Однако это ограничение можно изменить для каждого сеанса, назначив значение свойству SubQueries строка подключения в ConnectionString свойстве или DBPROP_MSMD_SUBQUERIES в разделе Поддерживаемые свойства XMLA (XMLA). Дополнительные сведения о поведении вычисляемых элементов в зависимости от значений свойства SubQueries или DBPROP_MSMD_SUBQUERIES см. в разделе Вычисляемые элементы в подзапросах выборки и вложенных кубах.