XQuery | Grouping & aggregation

Multiple grouping

Page 57 of 58 1 min read

Multiple grouping

The principle of the multiple grouping is applied if within a single query the original data stock shall be grouped multiple times according to different criteria. Multiple grouping stands in opposition to a (onetime) grouping via several attributes. In the current example, the average age shall be calculated by gender as well as by occupational group with the following result:

<MedicalPersonnel>
  <MedicalPersonnel_according_Occupation>
    <OccupationalGroup>
      <Occupation>Doctor</Occupation>
      <Age>28</Age>
    </OccupationalGroup>
    <OccupationalGroup>
      <Occupation>Nurse</Occupation>
      <Age>53</Age>
    </OccupationalGroup>
  </MedicalPersonnel_according_Occupation>
  <MedicalPersonnel_according_Gender>
    <GenderGroup>
      <Gender>m</Gender>
      <Age>28</Age>
    </GenderGroup>
    <GenderGroup>
      <Gender>w</Gender>
      <Age>53</Age>
    </Geschlechtsgruppe>
  </MedicalPersonnel_according_Gender>
</MedicalPersonnel>

The corresponding XQuery results from two consecutive FLWOR blocks and is as follows:

<MedicalPersonnel>
  <MedicalPersonnel_according_Occupation>
    {
      for $o in fn:distinct-values(
        for $i in fn:doc("...")//MedicalPersonnel/*/*
        return fn:name($i))
      let $x := fn:doc("...")//*[fn:name(.) = $o]/Age
      return <OccupationalGroup>
        <Occupation>{ $o }</Occupation>
        <Age>{ fn:avg($x) }</Age>
      </OccupationalGroup>
    }
  </MedicalPersonnel_according_Occupation>
  <MedicalPersonnel_according_Gender>
    {
      for $g in fn:distinct-values(fn:doc("...")//Gender)
      let $x := fn:doc("...")//*[Gender=$g]/Age
      return <GenderGroup>
        <Gender>{ $g }</Gender>
        <Age>{ fn:avg($x) }</Age>
      </GenderGroup>
    }
  </MedicalPersonnel_according_Gender>
</MedicalPersonnel>

In case two grouping combinations have equal grouping properties, the similarities can be used to the effect that only a single variable must be defined and can be used jointly.