Azure Cosmos DB for NoSQL의 하위 쿼리

적용 대상: NoSQL

하위 쿼리는 Azure Cosmos DB for NoSQL 내의 다른 쿼리 내에 중첩된 쿼리입니다. 하위 쿼리는 내부 쿼리 또는 내부 SELECT라고도 합니다. 하위 쿼리가 포함된 문을 일반적으로 외부 쿼리라고 합니다.

하위 쿼리의 유형

하위 쿼리에는 두 가지 주요 유형이 있습니다.

• 상관: 외부 쿼리의 값을 참조하는 하위 쿼리입니다. 하위 쿼리는 외부 쿼리가 처리하는 각 행에 대해 한 번씩 평가됩니다.
• 비상관: 외부 쿼리와 독립적인 하위 쿼리입니다. 외부 쿼리에 의존하지 않고 자체적으로 실행할 수 있습니다.

참고

Azure Cosmos DB는 상호 관련된 하위 쿼리만 지원합니다.

하위 쿼리는 반환하는 행과 열의 수를 기준으로 추가 분류할 수 있습니다. 다음 세 가지 유형이 있습니다.

• 테이블: 여러 행과 여러 열을 반환합니다.
• 다중 값: 여러 행과 단일 열을 반환합니다.
• 스칼라: 단일 행과 단일 열을 반환합니다.

Azure Cosmos DB for NoSQL의 SQL 쿼리는 항상 단일 열(단순 값 또는 복잡한 항목)을 반환합니다. 따라서 다중 값 및 스칼라 하위 쿼리만 적용할 수 있습니다. 다중 값 하위 쿼리는 관계식으로 FROM 절에서만 사용할 수 있습니다. 스칼라 하위 쿼리는 SELECT 또는 WHERE 절에서 스칼라 식으로 사용하거나 FROM 절에서 관계식으로 사용할 수 있습니다.

다중 값 하위 쿼리

다중 값 하위 쿼리는 항목 세트를 반환하고 항상 FROM 절 내에서 사용됩니다. 다음 용도로 사용됩니다.

• JOIN(셀프 조인) 식을 최적화합니다.
• 비용이 많이 드는 식을 한 번 평가하고 여러 번 참조.

셀프 조인 식 최적화

다중 값 하위 쿼리는 조건자를 WHERE 절의 모든 크로스 조인 뒤가 아니라 각 select-many 식 뒤에 푸시하여 JOIN 식을 최적화할 수 있습니다.

다음과 같은 쿼리를 고려해 보세요.

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

이 쿼리의 경우 인덱스는 name이 "winter" 또는 "fall"인 태그, 0~10 중 하나 이상의 quantity 및 backstock이 false인 하나 이상의 창고가 있는 항목과 일치합니다. 여기서 JOIN 식은 필터가 적용되기 전에 일치하는 각 항목에 대해 tags, onHandQuantities 및 warehouseStock 배열의 모든 항목에 대한 교차곱을 수행합니다.

그런 다음, WHERE 절은 필터 조건자를 각 <c, t, n, s> 튜플에 적용합니다. 예를 들어 일치하는 항목의 3개 배열 각각에 10개 항목이 있는 경우 1 x 10 x 10 x 10(즉, 1,000)개 튜플로 확장됩니다. 여기서 하위 쿼리를 사용하여 다음 식으로 조인하기 전에 조인된 배열 항목을 필터링할 수 있습니다.

다음 쿼리는 위의 쿼리와 동일하지만 하위 쿼리를 사용합니다.

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)

태그 배열의 한 항목만 필터와 일치하고 영양소 및 1회 용량 배열 모두 5개 항목이 있다고 가정합니다. 그런 다음, JOIN 식은 첫 번째 쿼리의 1,000개 항목과 달리 1 x 1 x 5 x 5(25)개 항목으로 확장됩니다.

한 번 평가하고 여러 번 참조

하위 쿼리는 UDF(사용자 정의 함수), 복잡한 문자열 또는 산술 식과 같은 비용이 많이 드는 식으로 쿼리를 최적화하는 데 도움이 될 수 있습니다. JOIN 식과 함께 하위 쿼리를 사용하여 식을 한 번 평가하지만 여러 번 참조할 수 있습니다.

다음 UDF(getTotalWithTax)가 정의되어 있다고 가정해 보겠습니다.

function getTotalWithTax(subTotal){
  return subTotal * 1.25;
}

다음 쿼리는 UDF getTotalWithTax를 여러 번 실행합니다.

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

다음은 UDF를 한 번만 실행하는 동일한 쿼리입니다.

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

팁

JOIN 식의 교차곱 동작에 주의하세요. UDF 식이 undefined로 평가될 수 있는 경우 값을 직접 반환하는 대신 하위 쿼리에서 개체를 반환하여 JOIN 식에서 항상 단일 행을 생성하는지 확인해야 합니다.

외부 참조 데이터를 사용한 모방 조인

측정 단위와 같이 거의 변경되지 않는 정적 데이터를 참조해야 하는 경우가 많습니다. 쿼리의 각 항목에 대해 정적 데이터를 복제하지 않는 것이 좋습니다. 이러한 중복을 방지하면 개별 항목 크기를 더 작게 유지하여 스토리지가 절약되고 쓰기 성능이 향상됩니다. 하위 쿼리를 사용하여 정적 참조 데이터 컬렉션이 포함된 내부 조인 의미 체계를 모방할 수 있습니다.

예를 들어 다음과 같은 측정 세트를 고려합니다.

	이름	승수	기준 단위
ng	나노그램	1.00E-09	그램
µg	Microgram	1.00E-06	그램
mg	밀리그램	1.00E-03	그램
g	그램	1.00E+00	그램
kg	킬로그램	1.00E+03	그램
Mg	메가그램	1.00E+06	그램
Gg	기가그램	1.00E+09	그램

다음 쿼리는 이 데이터와의 조인을 모방하여 출력에 단위 이름을 추가합니다.

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

스칼라 하위 쿼리

스칼라 하위 쿼리 식은 단일 값으로 평가되는 하위 쿼리입니다. 스칼라 하위 쿼리 식의 값은 하위 쿼리의 프로젝션(SELECT 절) 값입니다. 스칼라 식이 유효한 여러 위치에서 스칼라 하위 쿼리 식을 사용할 수 있습니다. 예를 들어 스칼라 하위 쿼리를 SELECT 및 WHERE 절의 식에 사용할 수 있습니다.

스칼라 하위 쿼리를 사용하더라도 항상 쿼리 최적화에 도움이 되는 것은 아닙니다. 예를 들어 스칼라 하위 쿼리를 시스템 또는 사용자 정의 함수에 대한 인수로 전달하면 RU(리소스 단위) 사용 또는 대기 시간을 줄이는 데 아무런 이점이 없습니다.

스칼라 하위 쿼리는 다음과 같이 추가로 분류할 수 있습니다.

• 단순 식 스칼라 하위 쿼리
• 스칼라 하위 쿼리 집계

단순 식 스칼라 하위 쿼리

단순 식 스칼라 하위 쿼리는 집계 식을 포함하지 않는 SELECT 절이 있는 상호 관련된 하위 쿼리입니다. 컴파일러가 하나의 더 큰 단순 식으로 변환하기 때문에 이러한 하위 쿼리는 최적화 이점을 제공하지 않습니다. 내부 쿼리와 외부 쿼리 간에 상호 관련된 컨텍스트가 없습니다.

첫 번째 예제로 다음과 같은 간단한 쿼리를 고려합니다.

SELECT
    1 AS a,
    2 AS b

단순 식 스칼라 하위 쿼리를 사용하여 이 쿼리를 다시 작성할 수 있습니다.

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

두 쿼리는 모두 동일한 출력을 생성합니다.

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

다음 예제 쿼리는 고유 식별자와 접두사를 단순 식 스칼라 하위 쿼리로 연결합니다.

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

다음 예제에서는 단순 식 스칼라 하위 쿼리를 사용하여 각 항목에 대한 관련 필드만 반환합니다. 쿼리는 각 항목에 대해 무언가를 출력하지만 하위 쿼리 내의 필터를 충족하는 경우에만 프로젝션된 필드를 포함합니다.

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

[
  {
    "id": "03230",
    "name": "Winter glove"
  },
  {
    "id": "03238"
  },
  {
    "id": "03229"
  }
]

스칼라 하위 쿼리 집계

집계 스칼라 하위 쿼리는 단일 값으로 평가되는 프로젝션 또는 필터에 집계 함수가 있는 하위 쿼리입니다.

첫 번째 예제로 다음 필드가 있는 항목을 고려합니다.

{
  "name": "Snow coat",
  "inventory": [
    {
      "location": "Redmond, WA",
      "quantity": 50
    },
    {
      "location": "Seattle, WA",
      "quantity": 30
    },
    {
      "location": "Washington, DC",
      "quantity": 25
    }
  ]
}

다음은 프로젝션에 단일 집계 함수 식이 있는 하위 쿼리입니다. 이 쿼리는 각 항목에 대한 모든 태그를 계산합니다.

SELECT
    p.name,
    (SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
    products p

[
  {
    "name": "Snow coat",
    "locationCount": 3
  }
]

다음은 필터가 있는 동일한 하위 쿼리입니다.

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

다음은 여러 집계 함수 식이 있는 또 다른 하위 쿼리입니다.

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

마지막으로 다음은 집계 하위 쿼리가 프로젝션과 필터 모두에 있는 쿼리입니다.

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

이 쿼리를 작성하는 더 최적의 방법은 하위 쿼리에 조인하고 SELECT 및 WHERE 절 모두에서 하위 쿼리 별칭을 참조하는 것입니다. 이 쿼리는 프로젝션과 필터가 아닌 조인 문 내에서만 하위 쿼리를 실행해야 하므로 더 효율적입니다.

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

EXISTS 식

Azure Cosmos DB for NoSQL의 쿼리 엔진은 EXISTS 식을 지원합니다. 이 식은 Azure Cosmos DB for NoSQL에 기본 제공되는 집계 스칼라 하위 쿼리입니다. EXISTS는 하위 쿼리 식을 사용하고, 하위 쿼리에서 행을 반환하는 경우 true를 반환합니다. 그 외의 경우 false를 반환합니다.

쿼리 엔진에서 부울 식과 다른 스칼라 식을 구분하지 않으므로 SELECT 및 WHERE 절 모두에서 EXISTS를 사용할 수 있습니다. 이 동작은 부울 식이 필터로만 제한되는 T-SQL과 다릅니다.

EXISTS 하위 쿼리에서 undefined라는 단일 값을 반환하는 경우 EXISTS는 false로 평가됩니다. 예를 들어 아무것도 반환하지 않는 다음 쿼리를 고려합니다.

SELECT VALUE
    undefined

EXISTS 식과 이전 쿼리를 하위 쿼리로 사용하는 경우 식에서 false를 반환합니다.

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

이전 하위 쿼리에서 VALUE 키워드를 생략하면 하위 쿼리는 단일 빈 개체가 있는 배열로 평가됩니다.

SELECT
    undefined

[
  {}
]

이 시점에서 개체({})가 기술적으로 종료되므로 EXISTS 식은 true로 평가됩니다.

SELECT 
    EXISTS (SELECT undefined) 

[
  {
    "$1": true
  }
]

ARRAY_CONTAINS의 일반적인 사용 사례는 배열의 항목 존재 여부를 기준으로 항목을 필터링하는 것입니다. 이 경우 여기서는 "outerwear"라는 항목이 tags 배열에 포함되어 있는지 확인하고 있습니다.

SELECT
    p.name,
    p.tags
FROM
    products p
WHERE
    ARRAY_CONTAINS(p.tags, "outerwear")

동일한 쿼리에서 EXISTS를 대체 옵션으로 사용할 수 있습니다.

SELECT
    p.name,
    p.tags
FROM
    products p
WHERE
    EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")

또한 ARRAY_CONTAINS는 값이 배열 내의 요소와 같은지 여부만 확인할 수 있습니다. 더 복잡한 필터가 배열 속성에 필요한 경우 JOIN을 대신 사용합니다.

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

이제 각 항목 내 배열의 type 및 quantityOnHand 속성을 기준으로 필터링하는 다음 쿼리를 고려합니다.

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

컬렉션의 각 항목에 대해 배열 요소를 사용하여 교차곱이 수행됩니다. 이 JOIN 작업을 통해 배열 내의 속성을 필터링할 수 있습니다. 그러나 이 쿼리의 RU 사용량은 상당합니다. 예를 들어 1,000개 항목의 각 배열에 100개 항목이 있는 경우 1,000 x 100(즉, 100,000)개 튜플로 확장됩니다.

EXISTS를 사용하면 비용이 많이 드는 교차곱을 방지하는 데 도움이 될 수 있습니다. 다음 예제에서 쿼리는 EXISTS 하위 쿼리 내의 배열 요소를 필터링합니다. 배열 요소가 필터와 일치하면 이를 프로젝션하고 EXISTS가 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"
]

쿼리는 EXISTS의 별칭을 지정하고 프로젝션에서 이 별칭을 참조할 수도 있습니다.

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

ARRAY 식

ARRAY 식을 사용하여 쿼리 결과를 배열로 프로젝션할 수 있습니다. 이 식은 쿼리의 SELECT 절 내에서만 사용할 수 있습니다.

다음 예제에서는 적어도 이 항목이 포함된 컨테이너가 있다고 가정해 보겠습니다.

{
  "name": "Radimer mountain bike",
  "tags": [
    {
      "name": "road"
    },
    {
      "name": "bike"
    },
    {
      "name": "competitive"
    }
  ]
}

다음 첫 번째 예제에서는 식이 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"
    ]
  }
]

다른 하위 쿼리와 마찬가지로 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"
    ]
  }
]

배열 식은 하위 쿼리의 FROM 절 뒤에 있을 수도 있습니다.

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

관련 콘텐츠

• JOIN 절
• 상수
• C++ 키워드