Interstage Shunsaku Data Manager Application Development Guide - Microsoft(R) Windows(R) 2000/ Microsoft(R) Windows Server(TM) 2003 - - UNIX -

3.7.2 Aggregating Search Results

When an aggregation is performed, a group key is also extracted at the same time. The aggregation function specification and the key specified by the sort expression can be included in the return expression. Each return parameter is separated with a comma. If multiple return parameters are specified, the values returned by those parameters are separated with a delimiter.

• When the Java APIs are used, the delimiter is a comma.
• When the .NET APIs are used, the delimiter is 0x0001 of the hexadecimal number.
• When the C APIs are used, the delimiter is the character represented by character code '\001'.

The explanations accompanying the following examples assume that the following six XML documents have been found using a search expression.

Document A

<doc>
    :
<basic date="2006/11/3" expense="$155.00">
    :
</basic>
  <detail>
    <destination area="Clearview, Belair, Adelaide">Head Office</destination>
    :
    <train>$86.00</train><taxi></taxi><hotel>$69.00</hotel>
    :
  </detail>
    :
</doc>

Document B

<doc>
    :
<basic date="2006/10/11" expense="$10.00">
    :
</basic>
  <detail>
    <destination area="Clearview, Belair, Adelaide">Head Office</destination>
    :
    <train>$9.00</train><taxi></taxi><hotel></hotel>
    :
  </detail>
    :
</doc>

Document C

<doc>
    :
  <basic date="2006/11/25" expense="$210.00">
    :
</basic>
  <detail>
    <destination area="Clearview, Belair, Adelaide">Head Office</destination>
    :
    <train>$130.00</train><taxi></taxi><hotel>$80.00</hotel>
    :
  </detail>
    :
</doc>

Document D

<doc>
    :

<basic date="2006/9/29" expense="$42.00">
    :
</basic>
  <detail>
    <destination area="Richmond, Melbourne">Melbourne Office</destination>
    :
    <train>$16.00</train><taxi>$6.00</taxi><hotel></hotel>
    :
  </detail>
    :
</doc>

Document E

<doc>
    :
<basic date="2006/11/12" expense="$13.00">
    :
</basic>
  <detail>
    <destination area="Richmond, Melbourne">Melbourne Office</destination>
    :
    <train>$4.00</train><taxi>$9.00</taxi><hotel></hotel>
    :
  </detail>
    :
</doc>

Document F

<doc>
    :
<basic date="2006/10/30" expense="$95.00">
    :
</basic>
  <detail>
    <destination area="Richmond, Melbourne">Melbourne Office</destination>
    :
    <train>$2.80</train><taxi>$6.80</taxi><hotel>$85.00</hotel>
    :
  </detail>
    :
</doc>

It is assumed that the following sort expression has been specified in all the examples that follow:

Sort expression : /doc/detail/destination/@area

The sort expression used for aggregation specifies the grouping key. Sorting is also performed using that key. 'DESC' can also be specified in a sort expression used for aggregation. When 'DESC' is specified, aggregation results can be extracted in descending order according to the size of the grouping key.

The following examples are the results when "UTF-8" is used for the character code.

Example 1

The following example shows how to find the average of traveling expenses (train):

Return expression : /doc/detail/destination/@area,avg(/doc/detail/train/text())

Result: When the Java APIs are used

Clearview, Belair, Adelaide,75
Richmond, Melbourne,7.6

Example 2

The following example shows how to count the number of people who used taxis:

Return expression :/doc/detail/destination/@area,count(/doc/detail/taxi/text())

Result: When the Java APIs are used

Clearview, Belair, Adelaide,0
Richmond, Melbourne,3

If no value is indicated by the text expression used as the argument of the aggregation function, it will not be targeted for aggregation. In the above example, no one used a taxi to travel to the head office, so the count result is 0.

If the value '0' is stored in the taxi element node, it will be targeted for aggregation. To prevent data stored in this way from being aggregated, the data can be excluded from the search process by specifying in the search expression the condition that the taxi fare must be greater than 0.

Example 3

This example shows how to obtain the maximum taxi fare (taxi), the total accommodation costs (hotel), and the number of trips:

Return expression : /doc/detail/destination/@area,max(/doc/detail/taxi/text()),sum(/doc/detail/hotel/text()),count(/doc/detail/destination/text())

Result: When the Java APIs are used

Clearview, Belair, Adelaide,,149,3
Richmond, Melbourne,9,85,3

If no value is indicated by the text expression used as the argument of the aggregation function, it will not be targeted for aggregation. In the above example, no one used a taxi to visit the head office, so there is no maximum value.