Bagikan melalui

Subkueri - Bahasa kueri di Cosmos DB (di Azure dan Fabric)

  • Berlaku untuk: ✅ Azure Cosmos DB for NoSQL, ✅ Cosmos DB in Microsoft Fabric

Subkueri adalah kueri yang ditumpuk dalam kueri lain dalam bahasa kueri. Subkueri juga disebut kueri dalam atau dalam SELECT. Pernyataan yang berisi subkueri biasanya disebut kueri luar.

Jenis subkueri

Ada dua jenis utama subkueri:

  • Berkorelasi: Subkueri yang mereferensikan nilai dari kueri luar. Subkueri dievaluasi sekali untuk setiap baris yang diproses kueri luar.
  • Tidak berkorelasi: Subkueri yang independen dari kueri luar. Ini dapat dijalankan sendiri tanpa mengandalkan kueri luar.

Subkueri dapat diklasifikasikan lebih lanjut berdasarkan jumlah baris dan kolom yang dikembalikan. Ada tiga jenis:

  • Tabel: Mengembalikan beberapa baris dan beberapa kolom.
  • Multinilai: Mengembalikan beberapa baris dan satu kolom.
  • Skalar: Mengembalikan satu baris dan satu kolom.

Kueri dalam bahasa kueri selalu mengembalikan satu kolom (baik nilai sederhana atau item kompleks). Oleh karena itu, hanya subkueri multinilai dan skalar yang berlaku. Anda dapat menggunakan subkueri multinilai hanya dalam FROM klausa sebagai ekspresi relasional. Anda dapat menggunakan subkueri skalar sebagai ekspresi skalar dalam SELECT klausa atau WHERE , atau sebagai ekspresi relasional dalam FROM klausa.

Subkueri multinilai

Subkueri multinilai mengembalikan sekumpulan item dan selalu digunakan dalam FROM klausa. Mereka digunakan untuk:

  • Mengoptimalkan JOIN ekspresi (gabungan mandiri).
  • Mengevaluasi ekspresi mahal sekali dan mereferensikan beberapa kali.

Mengoptimalkan ekspresi gabungan mandiri

Subkueri multinilai dapat mengoptimalkan JOIN ekspresi dengan mendorong predikat setelah setiap ekspresi pilih-banyak daripada setelah semua gabungan silang dalam WHERE klausa.

Pertimbangkan kueri berikut:

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%"

Untuk kueri ini, indeks cocok dengan item apa pun yang memiliki tag dengan key salah satu fabric atau material, setidaknya satu ukuran dengan order nilai yang lebih besar dari *tiga, dan setidaknya satu warna dengan gray sebagai substring. Ekspresi JOIN di sini melakukan lintas produk dari semua item tags, , sizesdan colors array untuk setiap item yang cocok sebelum filter apa pun diterapkan.

Klausul WHERE kemudian menerapkan predikat filter pada setiap $<c, t, n, s>$ tuple. Misalnya, jika item yang cocok memiliki sepuluh item di masing-masing dari tiga array, item akan diperluas menjadi 1.000 tuple menggunakan rumus ini:

$$1 x 10 x 10 x 10$$

Menggunakan subkueri di sini dapat membantu memfilter item array gabungan sebelum bergabung dengan ekspresi berikutnya.

Kueri ini setara dengan kueri sebelumnya tetapi menggunakan subkueri:

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%")

Asumsikan bahwa hanya satu item dalam array tag yang cocok dengan filter, dan ada lima item untuk array kuantitas dan stok. JOIN Ekspresi kemudian meluas ke 25 tuple menggunakan rumus ini dibandingkan dengan 1.000 item dalam kueri pertama:

$$1 x 1 x 5 x 5$$

Mengevaluasi sekali dan mereferensikan berkali-kali

Subkueri dapat membantu mengoptimalkan kueri dengan ekspresi mahal seperti fungsi yang ditentukan pengguna (UDF), string kompleks, atau ekspresi aritmatika. Anda dapat menggunakan subkueri bersama dengan JOIN ekspresi untuk mengevaluasi ekspresi sekali tetapi mereferensikannya berkali-kali.

Kueri sampel ini menghitung harga dengan suplemen 25% kali dalam kueri.

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

Berikut adalah kueri yang setara yang menjalankan penghitungan hanya sekali:

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
  },
  ...
]

Petunjuk / Saran

Perlu diingat perilaku JOIN ekspresi lintas produk. Jika ekspresi dapat mengevaluasi ke undefined, Anda harus memastikan bahwa JOIN ekspresi selalu menghasilkan satu baris dengan mengembalikan objek dari subkueri daripada nilai secara langsung.

Meniru gabungan relasional dengan data referensi eksternal

Anda mungkin sering perlu mereferensikan data statis yang jarang berubah, seperti satuan pengukuran. Sangat ideal untuk tidak menduplikasi data statis untuk setiap item dalam kueri. Menghindari duplikasi ini menghemat penyimpanan dan meningkatkan performa tulis dengan menjaga ukuran item individual lebih kecil. Anda dapat menggunakan subkueri untuk meniru semantik gabungan dalam dengan kumpulan data referensi statis.

Misalnya, pertimbangkan serangkaian pengukuran ini yang mewakili panjang garmen:

Ukuran Length Unit
xs 63.5 cm
s 64.5 cm
m 66.0 cm
l 67.5 cm
xl 69.0 cm
xxl 70.5 cm

Kueri berikut menimbuhkan bergabung dengan data ini sehingga Anda menambahkan nama unit ke output:

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

Subkueri skalar

Ekspresi subkueri skalar adalah subkueri yang mengevaluasi ke satu nilai. Nilai ekspresi subkueri skalar adalah nilai proyeksi (SELECT klausa) subkueri. Anda dapat menggunakan ekspresi subkueri skalar di banyak tempat di mana ekspresi skalar valid. Misalnya, Anda dapat menggunakan subkueri skalar dalam ekspresi apa pun dalam SELECT klausa dan WHERE .

Menggunakan subkueri skalar tidak selalu membantu mengoptimalkan kueri Anda. Misalnya, meneruskan subkueri skalar sebagai argumen ke fungsi sistem atau yang ditentukan pengguna tidak memberikan manfaat dalam mengurangi konsumsi atau latensi unit sumber daya (RU).

Subkueri skalar dapat diklasifikasikan lebih lanjut sebagai:

  • Subkueri skalar ekspresi sederhana
  • Subkueri skalar agregat

Subkueri skalar ekspresi sederhana

Subkueri skalar ekspresi sederhana adalah subkueri berkorelasi yang memiliki SELECT klausa yang tidak berisi ekspresi agregat apa pun. Subkueri ini tidak memberikan manfaat pengoptimalan karena pengkompilasi mengonversinya menjadi satu ekspresi sederhana yang lebih besar. Tidak ada konteks yang berkorelasi antara kueri dalam dan luar.

Sebagai contoh pertama, pertimbangkan kueri sepele ini.

SELECT
  1 AS a,
  2 AS b

Anda dapat menulis ulang kueri ini, dengan menggunakan subkueri skalar ekspresi sederhana.

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

Kedua kueri menghasilkan output yang sama.

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

Contoh kueri berikutnya ini menggabungkan pengidentifikasi unik dengan awalan sebagai subkueri skalar ekspresi sederhana.

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

Contoh ini menggunakan subkueri skalar ekspresi sederhana untuk hanya mengembalikan bidang yang relevan untuk setiap item. Kueri menghasilkan sesuatu untuk setiap item, tetapi hanya menyertakan bidang yang diproyeksikan jika memenuhi filter dalam subkueri.

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"
  }
]

Subkueri skalar agregat

Subkueri skalar agregat adalah subkueri yang memiliki fungsi agregat dalam proyeksi atau filternya yang mengevaluasi ke satu nilai.

Sebagai contoh pertama, pertimbangkan item dengan bidang berikut.

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

Berikut adalah subkueri dengan ekspresi fungsi agregat tunggal dalam proyeksinya. Kueri ini menghitung semua tag untuk setiap item.

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

Berikut adalah subkueri yang sama dengan filter.

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

Berikut adalah subkueri lain dengan beberapa ekspresi fungsi agregat:

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

Terakhir, berikut adalah kueri dengan subkueri agregat dalam proyeksi dan filter:

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

Cara yang lebih optimal untuk menulis kueri ini adalah dengan bergabung pada subkueri dan mereferensikan alias subkueri dalam klausa SELECT dan WHERE. Kueri ini lebih efisien karena Anda perlu menjalankan subkueri hanya dalam pernyataan gabungan, dan bukan dalam proyeksi dan filter.

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

Ekspresi EXISTS

Bahasa kueri mendukung EXISTS ekspresi. Ekspresi ini adalah subkueri skalar agregat yang disertakan dalam bahasa kueri. EXISTS mengambil ekspresi subkueri dan mengembalikan true jika subkueri mengembalikan baris apa pun. Jika tidak, ia mengembalikan false.

Karena mesin kueri tidak membedakan antara ekspresi boolean dan ekspresi skalar lainnya, Anda dapat menggunakan EXISTS dalam klausa SELECT dan WHERE . Perilaku ini tidak seperti T-SQL, di mana ekspresi boolean dibatasi hanya untuk filter.

EXISTS Jika subkueri mengembalikan nilai tunggal yaitu undefined, EXISTS mengevaluasi ke false. Misalnya, pertimbangkan kueri berikut yang tidak mengembalikan apa pun.

SELECT VALUE
  undefined

Jika Anda menggunakan EXISTS ekspresi dan kueri sebelumnya sebagai subkueri, ekspresi mengembalikan false.

SELECT VALUE
  EXISTS (SELECT VALUE undefined)

[
  false
]

Jika kata kunci VALUE dalam subkueri sebelumnya dihilangkan, subkueri mengevaluasi ke array dengan satu objek kosong.

SELECT
  undefined

[
  {}
]

Pada titik itu EXISTS , ekspresi mengevaluasi karena true objek ({}) secara teknis keluar.

SELECT VALUE
  EXISTS (SELECT undefined)

[
  true
]

Kasus ARRAY_CONTAINS penggunaan umum adalah memfilter item berdasarkan keberadaan item dalam array. Dalam hal ini, kami sedang memeriksa apakah tags array berisi item bernama "outerwear."

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

Kueri yang sama dapat digunakan EXISTS sebagai opsi alternatif.

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

Selain itu, ARRAY_CONTAINS hanya dapat memeriksa apakah nilai sama dengan elemen apa pun dalam array. Jika Anda memerlukan filter yang lebih kompleks pada properti array, gunakan JOIN sebagai gantinya.

Pertimbangkan item contoh ini dalam satu set dengan beberapa item yang masing-masing berisi accessories array.

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

Sekarang, pertimbangkan kueri berikut yang memfilter berdasarkan type properti dan quantityOnHand dalam array dalam setiap item.

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"
  }
]

Untuk setiap item dalam koleksi, lintas produk dilakukan dengan elemen array-nya. Operasi ini JOIN memungkinkan untuk memfilter properti dalam array. Namun, konsumsi RU kueri ini signifikan. Misalnya, jika 1.000 item memiliki 100 item di setiap array, item akan diperluas menjadi 100.000 tuple menggunakan rumus ini:

$$1.000 x 100$$

Menggunakan EXISTS bantuan untuk menghindari produk silang yang mahal ini. Dalam contoh berikutnya ini, kueri memfilter pada elemen array dalam subkueri EXISTS . Jika elemen array cocok dengan filter, maka Anda memproyeksikannya dan EXISTS mengevaluasinya ke true.

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

Kueri diizinkan untuk juga alias EXISTS dan mereferensikan alias dalam proyeksi:

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

Ekspresi ARRAY

Anda bisa menggunakan ARRAY ekspresi untuk memproyeksikan hasil kueri sebagai array. Anda hanya dapat menggunakan ekspresi ini dalam SELECT klausa kueri.

Untuk contoh ini, mari kita asumsikan ada kontainer dengan setidaknya item ini.

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

Dalam contoh pertama ini, ekspresi digunakan dalam SELECT klausa.

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"
    ]
  }
]

Seperti halnya subkueri lainnya, filter dengan ekspresi dimungkinkan ARRAY .

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"
    ]
  }
]

Ekspresi array juga dapat datang setelah FROM klausul dalam subkueri.

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"
  }
]

Konten terkait

  • Last updated on