Subqueries – Lekérdezési nyelv a Cosmos DB-ben (az Azure-ban és a Fabricben)

  • A következőre érvényes:: ✅ Azure Cosmos DB, ✅ Cosmos DB in Microsoft Fabric

Az al lekérdezés egy lekérdezés, amely egy másik lekérdezésbe ágyazott be a lekérdezés nyelvére. Az al lekérdezéseket belső lekérdezésnek vagy belső SELECTlekérdezésnek is nevezik. Az al lekérdezést tartalmazó utasítást általában külső lekérdezésnek nevezzük.

Az albekérdezések típusai

Az albekérdezéseknek két fő típusa van:

  • Korrelált: A külső lekérdezésből származó értékekre hivatkozó részquery. A rendszer minden olyan sorhoz egyszer kiértékeli az al lekérdezést, amelyet a külső lekérdezés feldolgoz.
  • Nem korrelált: A külső lekérdezésétől független részquery. A külső lekérdezés használata nélkül önállóan is futtatható.

Az albekérdezések tovább besorolhatók a visszaadott sorok és oszlopok száma alapján. Három típus van:

  • Táblázat: Több sort és több oszlopot ad vissza.
  • Többértékű: Több sort és egyetlen oszlopot ad vissza.
  • Skaláris: Egyetlen sort és egyetlen oszlopot ad vissza.

A lekérdezési nyelvben lévő lekérdezések mindig egyetlen oszlopot adnak vissza (egy egyszerű értéket vagy egy összetett elemet). Ezért csak a többértékű és a skaláris al lekérdezések alkalmazhatók. Többértékű részqueryt csak a FROM záradékban használhat relációs kifejezésként. Skaláris alqueryt használhat skaláris kifejezésként a vagy SELECT záradékbanWHERE, vagy relációs kifejezésként a FROM záradékban.

Többértékű al lekérdezések

A többértékű al lekérdezések egy elemkészletet adnak vissza, és mindig a FROM záradékban vannak használva. Ezeket a következő célokra használják:

  • Az (önillesztési) kifejezések optimalizálása JOIN .
  • Költséges kifejezések kiértékelése és többszöri hivatkozás.

Önillesztésű kifejezések optimalizálása

A többértékű al lekérdezések úgy optimalizálhatják JOIN a kifejezéseket, hogy predikátumokat nyomnak az egyes select-many kifejezések után, nem pedig a záradék összes WHERE után.

Fontolja meg a következő lekérdezést:

SELECT VALUE
  COUNT(1)
FROM
  products p
JOIN 
  t in p.tags
JOIN
  s in p.sizes
JOIN
  c in p.colors
WHERE
  t.key IN ("fabric", "material") AND
  s["order"] >= 3 AND
  c LIKE "%gray%"

Ebben a lekérdezésben az index megfelel minden olyan elemnek, amelynek címkéje key egy fabric vagy material, legalább egy méretben order *háromnál nagyobb értékkel és legalább egy színnel gray van alászúrva. Az JOIN itt szereplő kifejezés minden egyező elem összes elemének kereszt-szorzatáttagssizescolors hajtja végre, mielőtt bármilyen szűrőt alkalmaz.

A WHERE záradék ezután alkalmazza a szűrő predikátumát minden $<c, t, n, s>$ rekordra. Ha például egy egyező elemnek mind a három tömbben tíz eleme van, az 1000-es értékre bővül a képlet használatával:

$1 x 10 x 10 x 10$$

Az itt található al lekérdezések segíthetnek kiszűrni az összekapcsolt tömbelemeket, mielőtt csatlakoznának a következő kifejezéshez.

Ez a lekérdezés egyenértékű az előzővel, de al lekérdezéseket használ:

SELECT VALUE
  COUNT(1)
FROM
  products p
JOIN 
  (SELECT VALUE t FROM t IN p.tags WHERE t.key IN ("fabric", "material"))
JOIN 
  (SELECT VALUE s FROM s IN p.sizes WHERE s["order"] >= 3)
JOIN 
  (SELECT VALUE c FROM c in p.colors WHERE c LIKE "%gray%")

Tegyük fel, hogy a címketömbben csak egy elem felel meg a szűrőnek, és a mennyiségi és a készlettömbhöz is öt elem tartozik. A JOIN kifejezés ezután a képlet használatával 25 műveletet hajt ki, szemben az első lekérdezés 1000 elemével:

$1 x 1 x 5 x 5$$

Kiértékelés egyszer és hivatkozás többször

Az al lekérdezések segíthetnek optimalizálni a lekérdezéseket olyan költséges kifejezésekkel, mint a felhasználó által definiált függvények (UDF-ek), az összetett sztringek vagy az aritmetikai kifejezések. A kifejezés kiértékeléséhez egy részkikérdezés és egy JOIN kifejezés is használható, de sokszor hivatkozhat rá.

Ez a minta lekérdezés 25%-szoros kiegészítéssel számítja ki az árat a lekérdezésben.

SELECT VALUE {
  subtotal: p.price,
  total: (p.price * 1.25)
}
FROM
  products p
WHERE
  (p.price * 1.25) < 22.25

Íme egy egyenértékű lekérdezés, amely csak egyszer futtatja a számítást:

SELECT VALUE {
  subtotal: p.price,
  total: totalPrice
}
FROM
  products p
JOIN
  (SELECT VALUE p.price * 1.25) totalPrice
WHERE
  totalPrice < 22.25

[
  {
    "subtotal": 15,
    "total": 18.75
  },
  {
    "subtotal": 10,
    "total": 12.5
  },
  ...
]

Jótanács

Tartsa szem előtt a kifejezések termékközi viselkedését JOIN . Ha a kifejezés kiértékelhető undefined, győződjön meg arról, hogy a JOIN kifejezés mindig egyetlen sort hoz létre úgy, hogy egy objektumot ad vissza az al lekérdezésből, nem pedig közvetlenül az értéket.

Relációs illesztés utánzata külső referenciaadatokkal

Előfordulhat, hogy gyakran olyan statikus adatokra kell hivatkoznia, amelyek ritkán változnak, például mértékegységek. Ideális, ha nem duplikálja a statikus adatokat a lekérdezés minden eleméhez. Ennek a duplikációnak a elkerülése a tárterületen takarítható meg, és az egyes elemek méretének csökkentése révén javíthatja az írási teljesítményt. Egy alquery használatával statikus referenciaadatok gyűjteményével utánozhatja a belső illesztésű szemantikát.

Vegyük például ezt a ruhadarab hosszát ábrázoló mérési halmazt:

Méret Length Units
xs 63.5 cm
s 64.5 cm
m 66.0 cm
l 67.5 cm
xl 69.0 cm
xxl 70.5 cm

Az alábbi lekérdezés az adatokkal való összekapcsolás után adja hozzá az egység nevét a kimenethez:

SELECT
  p.name,
  p.subCategory,
  s.description AS size,
  m.length,
  m.unit
FROM
  products p
JOIN
  s IN p.sizes
JOIN m IN (
  SELECT VALUE [
    {size: 'xs', length: 63.5, unit: 'cm'},
    {size: 's', length: 64.5, unit: 'cm'},
    {size: 'm', length: 66, unit: 'cm'},
    {size: 'l', length: 67.5, unit: 'cm'},
    {size: 'xl', length: 69, unit: 'cm'},
    {size: 'xxl', length: 70.5, unit: 'cm'}
  ]
)
WHERE
  s.key = m.size

Skaláris alqueries

A skaláris subquery kifejezés egy olyan részkikérdezés, amely egyetlen értékre van kiértékelve. A skaláris részquery kifejezés értéke az alquery kivetülésének (SELECT záradékának) értéke. A skaláris részquery kifejezéseket számos helyen használhatja, ahol a skaláris kifejezés érvényes. Használhat például skaláris alqueryt a kifejezésekben és a SELECTWHERE záradékokban is.

A skaláris részquery használata nem mindig segít optimalizálni a lekérdezést. Ha például egy skaláris alqueryt argumentumként ad át egy rendszernek vagy felhasználó által definiált függvénynek, az nem jár előnyökkel az erőforrásegységek (RU) felhasználásának vagy késésének csökkentésében.

A skaláris al lekérdezések további besorolása:

  • Egyszerű kifejezés skaláris alqueries
  • Skaláris al lekérdezések összesítése

Egyszerű kifejezés skaláris alqueries

Az egyszerű kifejezéssel rendelkező skaláris részquery egy korrelált alquery, amely olyan SELECT záradékkal rendelkezik, amely nem tartalmaz összesítő kifejezéseket. Ezek az al lekérdezések nem biztosítanak optimalizálási előnyöket, mivel a fordító egy nagyobb egyszerű kifejezéssé alakítja őket. Nincs összefüggés a belső és a külső lekérdezés között.

Első példaként tekintse meg ezt a triviális lekérdezést.

SELECT
  1 AS a,
  2 AS b

Ezt a lekérdezést egy egyszerű kifejezéssel rendelkező skaláris alquery használatával újraírhatja.

SELECT
  (SELECT VALUE 1) AS a, 
  (SELECT VALUE 2) AS b

Mindkét lekérdezés ugyanazt a kimenetet hozza létre.

[
  {
    "a": 1,
    "b": 2
  }
]

Ez a következő példa lekérdezés összefűzi az egyedi azonosítót egy előtaggal egyszerű kifejezéssel rendelkező skaláris alqueryként.

SELECT 
  (SELECT VALUE CONCAT('ID-', p.id)) AS internalId
FROM
  products p

Ez a példa egy egyszerű kifejezéssel rendelkező skaláris részlekérdezés használatával csak az egyes elemek megfelelő mezőit adja vissza. A lekérdezés minden elemhez kimenetet ad ki, de csak akkor tartalmazza a kivetített mezőt, ha megfelel az al lekérdezés szűrőjének.

SELECT
  p.id,
  (SELECT p.name WHERE CONTAINS(p.name, "Shoes")).name
FROM
  products p

[
  {
    "id": "00000000-0000-0000-0000-000000004041",
    "name": "Remdriel Shoes"
  },
  {
    "id": "00000000-0000-0000-0000-000000004322"
  },
  {
    "id": "00000000-0000-0000-0000-000000004055"
  }
]

Skaláris al lekérdezések összesítése

Az aggregált skaláris alquery egy olyan alquery, amelynek a vetületében vagy szűrőjében aggregátumfüggvény található, amely egyetlen értékre kiértékelhető.

Első példaként vegye figyelembe az alábbi mezőkkel rendelkező elemet.

[
  {
    "name": "Blators Snowboard Boots",
    "colors": [
      "turquoise",
      "cobalt",
      "jam",
      "galliano",
      "violet"
    ],
    "sizes": [ ... ],
    "tags": [ ... ]
  }
]

Íme egy alquery, amely egyetlen aggregátumfüggvény-kifejezéssel rendelkezik a vetületében. Ez a lekérdezés minden elemhez megszámolja az összes címkét.

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount
FROM
  products p
WHERE
  p.id = "00000000-0000-0000-0000-000000004389"

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5
  }
]

Ugyanez az al lekérdezés egy szűrővel.

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
  (SELECT VALUE COUNT(1) FROM c IN p.colors WHERE c LIKE "%t") AS colorsEndsWithTCount
FROM
  products p

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5,
    "colorsEndsWithTCount": 2
  }
]

Íme egy másik, több aggregátumfüggvény-kifejezéssel rendelkező alquery:

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorsCount,
  (SELECT VALUE COUNT(1) FROM s in p.sizes) AS sizesCount,
  (SELECT VALUE COUNT(1) FROM t IN p.tags) AS tagsCount
FROM
  products p

[
  {
    "name": "Blators Snowboard Boots",
    "colorsCount": 5,
    "sizesCount": 7,
    "tagsCount": 2
  }
]

Végül íme egy lekérdezés, amely a vetületben és a szűrőben is aggregátum-alqueryt használ:

SELECT
  p.name,
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount,
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
FROM
  products p
WHERE
  (SELECT VALUE COUNT(1) FROM c IN p.colors) >= 5

A lekérdezés írásának optimálisabb módja, ha csatlakozik az alqueryhez, és hivatkozik az alquery aliasra a SELECT és a WHERE záradékokban is. Ez a lekérdezés hatékonyabb, mert az al lekérdezést csak az illesztés utasításán belül kell végrehajtania, a vetítésben és a szűrőben nem.

SELECT
  p.name,
  colorCount,
  smallSizesCount,
  largeSizesCount
FROM
  products p
JOIN
  (SELECT VALUE COUNT(1) FROM c IN p.colors) AS colorCount
JOIN
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Small") AS smallSizesCount
JOIN
  (SELECT VALUE COUNT(1) FROM s in p.sizes WHERE s.description LIKE "%Large") AS largeSizesCount
WHERE
  colorCount >= 5 AND
  largeSizesCount > 0 AND
  smallSizesCount > 0

EXISTS kifejezés

A lekérdezési nyelv támogatja EXISTS a kifejezéseket. Ez a kifejezés a lekérdezési nyelvbe beépített összesítő skaláris alquery. EXISTS egy subquery kifejezést vesz fel, és visszaadja true , ha az al lekérdezés bármilyen sort ad vissza. Ellenkező esetben visszaadja a falseértéket.

Mivel a lekérdezési motor nem tesz különbséget a logikai kifejezések és más skaláris kifejezések között, EXISTS használhatja mindkettőt SELECT és WHERE záradékot is. Ez a viselkedés ellentétben áll a T-SQL-sel, ahol a logikai kifejezések csak szűrőkre korlátozódnak.

Ha az EXISTS al lekérdezés egyetlen értéket undefinedad vissza, EXISTS akkor a kiértékelés eredménye hamis lesz. Vegyük például az alábbi lekérdezést, amely semmit sem ad vissza.

SELECT VALUE
  undefined

Ha a EXISTS kifejezést és az előző lekérdezést használja alqueryként, a kifejezés visszaadja false.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Ha az előző részkikérdezés ÉRTÉK kulcsszója nincs megadva, az al lekérdezés egyetlen üres objektummal rendelkező tömbre lesz kiértékelve.

SELECT
  undefined

[
  {}
]

Ezen a ponton a EXISTS kifejezés kiértékeli, mivel true az objektum ({}) technikailag kilép.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Gyakori használati eset ARRAY_CONTAINS , ha egy elemet egy tömbben lévő elem megléte alapján szűr. Ebben az esetben ellenőrizzük, hogy a tags tömb tartalmaz-e "felsőruházat" nevű elemet.

SELECT
  p.name,
  p.colors
FROM
  products p
WHERE
  ARRAY_CONTAINS(p.colors, "cobalt")

Ugyanez a lekérdezés alternatív lehetőségként is használható EXISTS .

SELECT
  p.name,
  p.colors
FROM
  products p
WHERE
  EXISTS (SELECT VALUE c FROM c IN p.colors WHERE c = "cobalt")

Emellett csak azt tudja ellenőrizni, ARRAY_CONTAINS hogy egy érték megegyezik-e a tömb bármely eleméhez. Ha összetettebb szűrőkre van szüksége a tömbtulajdonságokon, használja JOIN inkább.

Tekintse meg ezt a példaelemet egy olyan készletben, amelyben több elem található, amelyek mindegyike egy tömböt accessories tartalmaz.

[
  {
    "name": "Cosmoxy Pack",
    "tags": [
      {
        "key": "fabric",
        "value": "leather",
        "description": "Leather"
      },
      {
        "key": "volume",
        "value": "68-gal",
        "description": "6.8 Gal"
      }
    ]
  }
]

Most vegye figyelembe a következő lekérdezést, amely az type egyes elemek tömbjének tulajdonságai és quantityOnHand tulajdonságai alapján szűr.

SELECT
  p.name,
  t.description AS tag
FROM
  products p
JOIN
  t in p.tags
WHERE
  t.key = "fabric" AND
  t["value"] = "leather"

[
  {
    "name": "Cosmoxy Pack",
    "tag": "Leather"
  }
]

A gyűjtemény minden egyes eleme esetében a rendszer a tömbelemekkel végez keresztterméket. Ez a JOIN művelet lehetővé teszi a tömb tulajdonságainak szűrését. A lekérdezés ru-felhasználása azonban jelentős. Ha például 1000 elem minden tömbben 100 elemet tartalmazott, az a képlet használatával 100 000 műveletet tartalmaz:

$$1000 x 100$$

A használat EXISTS segít elkerülni ezt a drága keresztterméket. Ebben a következő példában a lekérdezés az alkérés tömbelemeire EXISTS szűr. Ha egy tömbelem megfelel a szűrőnek, akkor kivetíti, és EXISTS igaz értékre értékeli.

SELECT VALUE
  p.name
FROM
  products p
WHERE
  EXISTS (
    SELECT VALUE
      t
    FROM
      t IN p.tags
    WHERE
      t.key = "fabric" AND
      t["value"] = "leather"
  )

[
  "Cosmoxy Pack"
]

A lekérdezések aliast EXISTS is használhatnak, és hivatkozhatnak az aliasra a kivetítésben:

SELECT
  p.name,
  EXISTS (
    SELECT VALUE
      t
    FROM
      t IN p.tags
    WHERE
      t.key = "fabric" AND
      t["value"] = "leather"
  ) AS containsFabricLeatherTag
FROM
  products p

[
  {
    "name": "Cosmoxy Pack",
    "containsFabricLeatherTag": true
  }
]

TÖMB kifejezés

A kifejezéssel ARRAY tömbként vetítheti ki a lekérdezés eredményeit. Ezt a kifejezést csak a SELECT lekérdezés záradékában használhatja.

Ezekben a példákban tegyük fel, hogy van egy tároló, amely legalább ezt az elemet tartalmazza.

[
  {
    "name": "Menti Sandals",
    "sizes": [
      {
        "key": "5"
      },
      {
        "key": "6"
      },
      {
        "key": "7"
      },
      {
        "key": "8"
      },
      {
        "key": "9"
      }
    ]
  }
]

Ebben az első példában a kifejezést a záradékban használja a SELECT rendszer.

SELECT
  p.name,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
  ) AS sizes
FROM
  products p
WHERE
  p.name = "Menti Sandals"

[
  {
    "name": "Menti Sandals",
    "sizes": [
      "5",
      "6",
      "7",
      "8",
      "9"
    ]
  }
]

Más al lekérdezésekhez hasonlóan a ARRAY kifejezéssel rendelkező szűrők is lehetségesek.

SELECT
  p.name,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
    WHERE
      STRINGTONUMBER(s.key) <= 6
  ) AS smallSizes,
  ARRAY (
    SELECT VALUE
      s.key
    FROM
      s IN p.sizes
    WHERE
      STRINGTONUMBER(s.key) >= 9
  ) AS largeSizes
FROM
  products p
WHERE
  p.name = "Menti Sandals"

[
  {
    "name": "Menti Sandals",
    "smallSizes": [
      "5",
      "6"
    ],
    "largeSizes": [
      "9"
    ]
  }
]

A tömbkifejezések a záradék után FROM is jöhetnek az al lekérdezésekben.

SELECT
  p.name,
  z.s.key AS sizes
FROM
  products p
JOIN
  z IN (
    SELECT VALUE
      ARRAY (
        SELECT
          s
        FROM
          s IN p.sizes
        WHERE
          STRINGTONUMBER(s.key) <= 8
      )
  )

[
  {
    "name": "Menti Sandals",
    "sizes": "5"
  },
  {
    "name": "Menti Sandals",
    "sizes": "6"
  },
  {
    "name": "Menti Sandals",
    "sizes": "7"
  },
  {
    "name": "Menti Sandals",
    "sizes": "8"
  }
]

Kapcsolódó tartalom

  • Last updated on