Podzapytania w usłudze Azure Cosmos DB for NoSQL
DOTYCZY: NoSQL
Podquery to zapytanie zagnieżdżone w ramach innego zapytania w usłudze Azure Cosmos DB for NoSQL. Podquery jest również nazywane zapytaniem wewnętrznym lub wewnętrznym SELECT
. Instrukcja zawierająca podquerię jest zwykle nazywana zapytaniem zewnętrznym.
Typy podzapytania
Istnieją dwa główne typy podquerii:
- Skorelowane: podqueria odwołująca się do wartości z zapytania zewnętrznego. Podzapytywanie jest oceniane raz dla każdego wiersza, który przetwarza zapytanie zewnętrzne.
- Nie skorelowane: podqueria niezależna od zapytania zewnętrznego. Można go uruchomić samodzielnie bez polegania na zapytaniu zewnętrznym.
Uwaga
Usługa Azure Cosmos DB obsługuje tylko skorelowane podquery.
Podquery można dalej klasyfikować na podstawie liczby zwracanych wierszy i kolumn. Istnieją trzy typy:
- Tabela: zwraca wiele wierszy i wiele kolumn.
- Wiele wartości: zwraca wiele wierszy i jedną kolumnę.
- Skalarny: zwraca pojedynczy wiersz i jedną kolumnę.
Zapytania w usłudze Azure Cosmos DB for NoSQL zawsze zwracają jedną kolumnę (prostą wartość lub złożony element). W związku z tym mają zastosowanie tylko podquerie wielowarte i skalarne. Podquery wielowartej można używać tylko w klauzuli FROM
jako wyrażenie relacyjne. Subquery skalarnej można użyć jako wyrażenia skalarnego w SELECT
klauzuli or WHERE
lub jako wyrażenia relacyjnego w klauzuli FROM
.
Podquery z wieloma wartościami
Podquery wielowartościowe zwracają zestaw elementów i są zawsze używane w klauzuli FROM
. Są one używane do:
JOIN
Optymalizowanie wyrażeń (samosprzężenia).- Ocenianie kosztownych wyrażeń raz i odwoływanie się do wielu razy.
Optymalizowanie wyrażeń sprzężenia własnego
Podquerie wielowarte mogą optymalizować JOIN
wyrażenia, wypychając predykaty po każdym wyrażeniu select-many , a nie po wszystkich sprzężeniach krzyżowych w klauzuli WHERE
.
Rozpatrzmy następujące zapytanie:
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
t in p.tags
JOIN
q in p.onHandQuantities
JOIN
s in p.warehouseStock
WHERE
t.name IN ("winter", "fall") AND
(q.quantity BETWEEN 0 AND 10) AND
NOT s.backstock
W przypadku tego zapytania indeks pasuje do każdego elementu, który ma tag o name
wartości "zima" lub "fall", co najmniej jeden z przedziału od zera do dziesięciu, a co najmniej jeden quantity
magazyn, w którym backstock
znajduje się false
. Wyrażenie JOIN
w tym miejscu wykonuje krzyżowy produkt wszystkich elementów tags
, onHandQuantities
i warehouseStock
tablic dla każdego pasującego elementu przed zastosowaniem dowolnego filtru.
Klauzula WHERE
stosuje następnie predykat filtru dla każdej <c, t, n, s>
krotki. Na przykład jeśli pasujący element miał dziesięć elementów w każdej z trzech tablic, rozszerza się na 1 x 10 x 10 x 10
(czyli 1000) krotek. Użycie podquerii w tym miejscu może pomóc w odfiltrowaniu sprzężonych elementów tablicy przed dołączeniem do następnego wyrażenia.
To zapytanie jest równoważne poprzedniemu, ale używa podquerii:
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
(SELECT VALUE t FROM t IN p.tags WHERE t.name IN ("winter", "fall"))
JOIN
(SELECT VALUE q FROM q IN p.onHandQuantities WHERE q.quantity BETWEEN 0 AND 10)
JOIN
(SELECT VALUE s FROM s IN p.warehouseStock WHERE NOT s.backstock)
Załóżmy, że tylko jeden element w tablicy tagów jest zgodny z filtrem i istnieje pięć elementów zarówno składników odżywczych, jak i tablic obsługujących. Następnie JOIN
wyrażenia są rozszerzane do 1 x 1 x 5 x 5
(25) elementów, a nie do 1000 elementów w pierwszym zapytaniu.
Ocena raz i odwołanie wiele razy
Podquery mogą pomóc w optymalizacji zapytań przy użyciu kosztownych wyrażeń, takich jak funkcje zdefiniowane przez użytkownika ,złożone ciągi lub wyrażenia arytmetyczne. Możesz użyć podquery wraz z wyrażeniem JOIN
, aby ocenić wyrażenie raz, ale odwoływać się do niego wiele razy.
Załóżmy, że masz zdefiniowaną następującą funkcję UDF (getTotalWithTax
).
function getTotalWithTax(subTotal){
return subTotal * 1.25;
}
Następujące zapytanie uruchamia funkcję UDF getTotalWithTax
wiele razy:
SELECT VALUE {
subtotal: p.price,
total: udf.getTotalWithTax(p.price)
}
FROM
products p
WHERE
udf.getTotalWithTax(p.price) < 22.25
Oto równoważne zapytanie, które uruchamia funkcję UDF tylko raz:
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
totalPrice < 22.25
Porada
Należy pamiętać o zachowaniu JOIN
wyrażeń między produktami. Jeśli wyrażenie UDF może ocenić wartość undefined
, należy upewnić się, że JOIN
wyrażenie zawsze tworzy pojedynczy wiersz, zwracając obiekt z podquery, a nie bezpośrednio wartość.
Naśladownictwo sprzężenia z zewnętrznymi danymi referencyjnymi
Często może być konieczne odwołowanie się do danych statycznych, które rzadko się zmieniają, na przykład jednostki miary. Idealnie nadaje się do nieduplikowania danych statycznych dla każdego elementu w zapytaniu. Unikanie duplikowania pozwala zaoszczędzić na magazynie i zwiększyć wydajność zapisu, zachowując mniejszy rozmiar pojedynczego elementu. Za pomocą podquerii można naśladować semantykę sprzężenia wewnętrznego z kolekcją statycznych danych referencyjnych.
Rozważmy na przykład ten zestaw pomiarów:
Nazwa | Mnożnik | Jednostka podstawowa | |
---|---|---|---|
ng |
Nanogram | 1.00E-09 |
Gram |
µg |
Mikrogramów | 1.00E-06 |
Gram |
mg |
Miligram | 1.00E-03 |
Gram |
g |
Gram | 1.00E+00 |
Gram |
kg |
Kilogram | 1.00E+03 |
Gram |
Mg |
Megagram | 1.00E+06 |
Gram |
Gg |
Gigagram | 1.00E+09 |
Gram |
Następujące zapytanie naśladuje dołączenie do tych danych, aby dodać nazwę jednostki do danych wyjściowych:
SELECT
s.id,
(s.weight.quantity * m.multiplier) AS calculatedWeight,
m.unit AS unitOfWeight
FROM
shipments s
JOIN m IN (
SELECT VALUE [
{unit: 'ng', name: 'nanogram', multiplier: 0.000000001, baseUnit: 'gram'},
{unit: 'µg', name: 'microgram', multiplier: 0.000001, baseUnit: 'gram'},
{unit: 'mg', name: 'milligram', multiplier: 0.001, baseUnit: 'gram'},
{unit: 'g', name: 'gram', multiplier: 1, baseUnit: 'gram'},
{unit: 'kg', name: 'kilogram', multiplier: 1000, baseUnit: 'gram'},
{unit: 'Mg', name: 'megagram', multiplier: 1000000, baseUnit: 'gram'},
{unit: 'Gg', name: 'gigagram', multiplier: 1000000000, baseUnit: 'gram'}
]
)
WHERE
s.weight.units = m.unit
Podzapytania skalarne
Wyrażenie podquery skalarnej jest podquerią, która oblicza pojedynczą wartość. Wartość wyrażenia podrzędnego skalarnego jest wartością projekcji (SELECT
klauzuli) podquery. W wielu miejscach można użyć wyrażenia podrzędnego skalarnego, w którym wyrażenie skalarne jest prawidłowe. Można na przykład użyć podquerii skalarnej w dowolnym wyrażeniu SELECT
w klauzulach i WHERE
.
Użycie podquerii skalarnej nie zawsze pomaga zoptymalizować zapytanie. Na przykład przekazanie podquerii skalarnej jako argumentu do funkcji systemowych lub zdefiniowanych przez użytkownika nie zapewnia korzyści w zmniejszeniu zużycia jednostek zasobów (RU) ani opóźnienia.
Podquery skalarne można dalej klasyfikować jako:
- Podquery skalarne wyrażeń prostych
- Agregowanie podquerii skalarnych
Podquery skalarne wyrażeń prostych
Podqueria skalarna prostego wyrażenia to skorelowana podqueria, która zawiera klauzulę SELECT
, która nie zawiera żadnych wyrażeń agregujących. Te podquery nie zapewniają żadnych korzyści optymalizacji, ponieważ kompilator konwertuje je na jedno większe proste wyrażenie. Nie ma skorelowanego kontekstu między zapytaniami wewnętrznymi i zewnętrznymi.
Jako pierwszy przykład rozważ to proste zapytanie.
SELECT
1 AS a,
2 AS b
Możesz ponownie napisać to zapytanie, używając podquerii skalarnej prostego wyrażenia.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
Oba zapytania generują te same dane wyjściowe.
[
{
"a": 1,
"b": 2
}
]
To następne przykładowe zapytanie łączy unikatowy identyfikator z prefiksem jako podquerią skalarną wyrażeń prostych.
SELECT
(SELECT VALUE Concat('ID-', p.id)) AS internalId
FROM
products p
W tym przykładzie użyto podquerii skalarnej prostego wyrażenia, aby zwrócić tylko odpowiednie pola dla każdego elementu. Zapytanie zwraca coś dla każdego elementu, ale zawiera tylko pole przewidywane, jeśli spełnia filtr w podkwerii.
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
products p
[
{
"id": "03230",
"name": "Winter glove"
},
{
"id": "03238"
},
{
"id": "03229"
}
]
Agregowanie podquerii skalarnych
Agregacja podquery skalarnej to podqueria, która ma funkcję agregacji w projekcji lub filtrze, który daje jedną wartość.
Jako pierwszy przykład rozważ element z następującymi polami.
{
"name": "Snow coat",
"inventory": [
{
"location": "Redmond, WA",
"quantity": 50
},
{
"location": "Seattle, WA",
"quantity": 30
},
{
"location": "Washington, DC",
"quantity": 25
}
]
}
Oto podqueria z pojedynczym wyrażeniem funkcji agregującej w projekcji. To zapytanie zlicza wszystkie tagi dla każdego elementu.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
products p
[
{
"name": "Snow coat",
"locationCount": 3
}
]
Oto ta sama podqueria z filtrem.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE ENDSWITH(i.location, "WA")) AS washingtonLocationCount
FROM
products p
[
{
"name": "Snow coat",
"washingtonLocationCount": 2
}
]
Oto kolejna podqueria z wieloma wyrażeniami funkcji agregującej:
SELECT
p.name,
(SELECT
COUNT(1) AS locationCount,
SUM(i.quantity) AS totalQuantity
FROM i IN p.inventory) AS inventoryData
FROM
products p
[
{
"name": "Snow coat",
"inventoryData": {
"locationCount": 2,
"totalQuantity": 75
}
}
]
Na koniec oto zapytanie z zagregowanym podzapytaniem zarówno w projekcji, jak i w filtrze:
SELECT
p.name,
(SELECT VALUE AVG(q.quantity) FROM q IN p.inventory WHERE q.quantity > 10) AS averageInventory
FROM
products p
WHERE
(SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE i.quantity > 10) >= 1
[
{
"name": "Snow coat",
"averageInventory": 35
}
]
Bardziej optymalnym sposobem na napisanie tego zapytania jest sprzężenie w podzapytaniu i odwołanie się do aliasu podzapytania zarówno w klauzulach SELECT, jak i WHERE. To zapytanie jest bardziej wydajne, ponieważ należy wykonać podzapytywanie tylko w instrukcji join, a nie w projekcji i filtrze.
SELECT
p.name,
inventoryData.inventoryAverage
FROM
products p
JOIN
(SELECT
COUNT(1) AS inventoryCount,
AVG(i.quantity) as inventoryAverage
FROM i IN p.inventory
WHERE i.quantity > 10) AS inventoryData
WHERE
inventoryData.inventoryCount >= 1
Wyrażenie EXISTS
Aparat zapytań usługi Azure Cosmos DB for NoSQL obsługuje EXISTS
wyrażenia. To wyrażenie jest agregowanym podzapytaniem skalarnym wbudowanym w usługę Azure Cosmos DB for NoSQL. EXISTS
przyjmuje wyrażenie podzapytania i zwraca wartość true
, jeśli podzapytywanie zwraca dowolne wiersze. W przeciwnym razie zwraca wartość false
.
Ponieważ aparat zapytań nie rozróżnia wyrażeń logicznych i innych wyrażeń skalarnych, można użyć EXISTS
w SELECT
klauzulach i WHERE
. To zachowanie jest w przeciwieństwie do języka T-SQL, gdzie wyrażenie logiczne jest ograniczone tylko do filtrów.
EXISTS
Jeśli podzapytywanie zwraca pojedynczą wartość undefined
, EXISTS
zwraca wartość false. Rozważmy na przykład następujące zapytanie, które nie zwraca niczego.
SELECT VALUE
undefined
Jeśli używasz EXISTS
wyrażenia i poprzedniego zapytania jako podzapytania, wyrażenie zwraca wartość false
.
SELECT
EXISTS (SELECT VALUE undefined)
[
{
"$1": false
}
]
Jeśli słowo kluczowe VALUE w poprzednim podzapytaniu zostanie pominięte, podzapytywanie daje w wyniku tablicę z pojedynczym pustym obiektem.
SELECT
undefined
[
{}
]
W tym momencie EXISTS
wyrażenie daje wartość , true
ponieważ obiekt ({}
) kończy się technicznie.
SELECT
EXISTS (SELECT undefined)
[
{
"$1": true
}
]
Typowym przypadkiem ARRAY_CONTAINS
użycia metody jest filtrowanie elementu według istnienia elementu w tablicy. W tym przypadku sprawdzamy, czy tablica tags
zawiera element o nazwie "outerwear".
SELECT
p.name,
p.tags
FROM
products p
WHERE
ARRAY_CONTAINS(p.tags, "outerwear")
To samo zapytanie może służyć EXISTS
jako alternatywna opcja.
SELECT
p.name,
p.tags
FROM
products p
WHERE
EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")
Ponadto można sprawdzić tylko, ARRAY_CONTAINS
czy wartość jest równa dowolnemu elementowi w tablicy. Jeśli potrzebujesz bardziej złożonych filtrów we właściwościach tablicy, użyj JOIN
zamiast tego.
Rozważmy ten przykładowy element w zestawie z wieloma elementami zawierającymi tablicę accessories
.
{
"name": "Unobtani road bike",
"accessories": [
{
"name": "Front/rear tire",
"type": "tire",
"quantityOnHand": 5
},
{
"name": "9-speed chain",
"type": "chains",
"quantityOnHand": 25
},
{
"name": "Clip-in pedals",
"type": "pedals",
"quantityOnHand": 15
}
]
}
Teraz rozważ następujące zapytanie, które filtruje na type
podstawie właściwości i quantityOnHand
w tablicy w każdym elemencie.
SELECT
p.name,
a.name AS accessoryName
FROM
products p
JOIN
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10
[
{
"name": "Unobtani road bike",
"accessoryName": "9-speed chain"
}
]
Dla każdego elementu w kolekcji produkt krzyżowy jest wykonywany z jego elementami tablicy. Ta JOIN
operacja umożliwia filtrowanie właściwości w tablicy. Jednak użycie jednostek RU tego zapytania jest znaczne. Na przykład jeśli 1000 elementów ma 100 elementów w każdej tablicy, rozszerza się do 1,000 x 100
(czyli 100 000) krotek.
Użycie EXISTS
może pomóc uniknąć tego kosztownego produktu krzyżowego. W następnym przykładzie zapytanie filtruje elementy tablicy w podzapytaniu EXISTS
. Jeśli element tablicy jest zgodny z filtrem, projektujesz go i EXISTS
obliczasz wartość true.
SELECT VALUE
p.name
FROM
products p
WHERE
EXISTS (SELECT VALUE
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10)
[
"Unobtani road bike"
]
Zapytania mogą również aliasować EXISTS
i odwoływać się do aliasu w projekcji:
SELECT
p.name,
EXISTS (SELECT VALUE
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10) AS chainAccessoryAvailable
FROM
products p
[
{
"name": "Unobtani road bike",
"chainAccessoryAvailable": true
}
]
Wyrażenie ARRAY
Wyrażenie służy ARRAY
do projekcji wyników zapytania jako tablicy. Tego wyrażenia można użyć tylko w SELECT
klauzuli zapytania.
W tych przykładach załóżmy, że istnieje kontener z co najmniej tym elementem.
{
"name": "Radimer mountain bike",
"tags": [
{
"name": "road"
},
{
"name": "bike"
},
{
"name": "competitive"
}
]
}
W tym pierwszym przykładzie wyrażenie jest używane w klauzuli SELECT
.
SELECT
p.name,
ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames
FROM
products p
[
{
"name": "Radimer mountain bike",
"tagNames": [
"road",
"bike",
"competitive"
]
}
]
Podobnie jak w przypadku innych podzapytania, możliwe są filtry z wyrażeniem ARRAY
.
SELECT
p.name,
ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames,
ARRAY (SELECT VALUE t.name FROM t in p.tags WHERE CONTAINS(t.name, "bike")) AS bikeTagNames
FROM
products p
[
{
"name": "Radimer mountain bike",
"tagNames": [
"road",
"bike",
"competitive"
],
"bikeTagNames": [
"bike"
]
}
]
Wyrażenia tablicy mogą również występować po klauzuli FROM
w podzapytaniach.
SELECT
p.name,
n.t.name AS nonBikeTagName
FROM
products p
JOIN
n IN (SELECT VALUE ARRAY(SELECT t FROM t in p.tags WHERE t.name NOT LIKE "%bike%"))
[
{
"name": "Radimer mountain bike",
"nonBikeTagName": "road"
},
{
"name": "Radimer mountain bike",
"nonBikeTagName": "competitive"
}
]