집계

  • 릴리스 버전: Xanadu
  • 업데이트 날짜 2024년 08월 01일
  • 읽기8분

    • SUM, COUNT, MIN, MAX, LAST 및 AVG를 포함한 집계 함수를 사용하여 테이블을 쿼리합니다.

    주:
    여기에 설명된 기능을 사용하려면 Aggregate Web Service 플러그인이 필요합니다.

    입력 필드

    대상 테이블의 모든 요소입니다. 또한 하나 이상의 집계 함수(SUM, COUNT, MIN, MAX, LAST 및 AVG)가 있습니다.

    GROUP BY 및 HAVING 절도 추가할 수 있습니다.

    출력 필드

    검색된 기록에 대한 모든 필드 값을 캡슐화하는 aggregateResponse 요소입니다.

    샘플 SOAP 메시지

    COUNT 집계 함수를 사용하는 샘플 SOAP 요청.

    <?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/encoding/"
                   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:m="http://www.service-now.com"
                   xmlns:tns="http://www.service-now.com/map"
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <aggregate>
      <COUNT>number</COUNT>
      <active>true</active>
    </aggregate>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

    COUNT 집계 함수 호출의 결과 응답은 다음과 같습니다.

    <?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
                   xmlns:m="http://www.service-now.com"
                   xmlns:tns="http://www.service-now.com/map"
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <aggregateResponse>
        <aggregateResult>
          <avg>2.7200</avg>
        </aggregateResult>
    </aggregateResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

    GROUP BY 절과 함께 AVG 집계 함수를 사용하는 샘플 SOAP 요청입니다.

    <?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
                   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:m="http://www.service-now.com"
                   xmlns:tns="http://www.service-now.com/map"
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <aggregate xmlns="http://www.service-now.com">
      <GROUP_BY>category</GROUP_BY>
      <active>true</active>
      <AVG>severity</AVG>
    </aggregate>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

    GROUP BY 절이 있는 AVG 집계 함수 호출의 결과 응답은 다음과 같습니다.

    <?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
                   xmlns:m="http://www.service-now.com"
                   xmlns:tns="http://www.service-now.com/map"
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <aggregateResponse>
        <aggregateResult>
          <avg>1.0000</avg>
          <category>database</category>
        </aggregateResult>
        <aggregateResult>
          <avg>3.0000</avg>
          <category>hardware</category>
        </aggregateResult>
        <aggregateResult>
          <avg>3.0000</avg>
          <category>inquiry</category>
        </aggregateResult>
        <aggregateResult>
          <avg>2.0000</avg>
          <category>network</category>
        </aggregateResult>
        <aggregateResult>
          <avg>2.6923</avg>
          <category>software</category>
        </aggregateResult>
    </aggregateResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

    집계를 필터링하기 위해 인코딩된 쿼리를 사용하는 샘플 SOAP 요청:

    <?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/encoding/"
                   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:m="http://www.service-now.com"
                   xmlns:tns="http://www.service-now.com/map"
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <aggregate>
      <COUNT>number</COUNT>
      <active>true</active>
      <__encoded_query>number=INC0000001^ORnumber=INC0000002</__encoded_query>
    </aggregate>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

    결과 범위를 좁히기 위해 HAVING을 사용하는 샘플 집계 요청입니다.

    HAVING은 4개의 필드를 사용합니다. 각 필드는 집계 유형, 집계 필드, 운영 유형 및 비교할 값의 "^"로 구분됩니다.

    요청에 둘 이상의 HAING을 추가할 수 있으므로 HAVING 식을 사용할 수 있지만 OR은 지원되지 않습니다.

    <?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/encoding/"
                   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:m="http://www.service-now.com"
                   xmlns:tns="http://www.service-now.com/map"
                   xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
  <SOAP-ENV:Body>
    <aggregate>
      <COUNT>sys_id</COUNT>
      <GROUP_BY>internal_type</GROUP_BY> 
      <HAVING>COUNT^*^>^10</HAVING>
      <HAVING>COUNT^*^<^20</HAVING>
      <COUNT>sys_id</COUNT>
      <active>true</active>
    </aggregate>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>