XML/XQuery/Aggregate — различия между версиями

Материал из Web эксперт
Перейти к: навигация, поиск
 
м (1 версия)
 
(нет различий)

Текущая версия на 11:25, 26 мая 2010

Aggregating values

   <source lang="xml">

File: Data.xml <employees>

 <employee name="James" salary="1234" yearServed="1" location="North"/>
 <employee name="Joe"   salary="3211" yearServed="2" location="South"/>
 <employee name="Jod"   salary="3422" yearServed="3" location="North"/>
 <employee name="Jason" salary="1321" yearServed="4" location="East"/>
 <employee name="Jack"  salary="2324" yearServed="3" location="North"/>
 <employee name="Jeep"  salary="4321" yearServed="2" location="East"/>
 <employee name="Jane"  salary="2344" yearServed="1" location="South"/>

</employees> File: Query.xquery for $d in distinct-values(doc("Data.xml")//employee/@location) let $employees := doc("Data.xml")//employee[@location= $d] order by $d return <department name="{$d}" total="{sum($employees/@yearServed)}"/> Output: <?xml version="1.0" encoding="UTF-8"?> <department total="6" name="East"/> <department total="7" name="North"/> <department total="3" name="South"/>

</source>
   
  


Aggregation

   <source lang="xml">

File: Data.xml <employees>

 <employee name="James" salary="1234" yearServed="1" location="North"/>
 <employee name="Joe"   salary="3211" yearServed="2" location="South"/>
 <employee name="Jod"   salary="3422" yearServed="3" location="North"/>
 <employee name="Jason" salary="1321" yearServed="4" location="East"/>
 <employee name="Jack"  salary="2324" yearServed="3" location="North"/>
 <employee name="Jeep"  salary="4321" yearServed="2" location="East"/>
 <employee name="Jane"  salary="2344" yearServed="1" location="South"/>

</employees> File: Query.xquery for $d in distinct-values(doc("Data.xml")//employee/@location) let $employees := doc("Data.xml")//employee[@location = $d] order by $d return <department code="{$d}"

                  numemployees="{count($employees)}"
                  distinct="{count(distinct-values($employees/@yearServed))}"
                  total="{sum($employees/@yearServed)}"/>

Output: <?xml version="1.0" encoding="UTF-8"?> <department total="6" code="East" distinct="2" numemployees="2"/> <department total="7" code="North" distinct="2" numemployees="3"/> <department total="3" code="South" distinct="2" numemployees="2"/>

</source>
   
  


Aggregation on multiple values

   <source lang="xml">

File: Data.xml <employees>

 <employee name="James" salary="1234" yearServed="1" location="North"/>
 <employee name="Joe"   salary="3211" yearServed="2" location="South"/>
 <employee name="Jod"   salary="3422" yearServed="3" location="North"/>
 <employee name="Jason" salary="1321" yearServed="4" location="East"/>
 <employee name="Jack"  salary="2324" yearServed="3" location="North"/>
 <employee name="Jeep"  salary="4321" yearServed="2" location="East"/>
 <employee name="Jane"  salary="2344" yearServed="1" location="South"/>

</employees> File: Query.xquery let $allemployees := doc("Data.xml")//employee for $d in distinct-values($allemployees/@salary) for $n in distinct-values($allemployees[@salary = $d]/@yearServed) let $employees := $allemployees[@salary = $d and @yearServed = $n] order by $d, $n return <group salary="{$d}"

             yearServed="{$n}"
             numemployees="{count($employees)}"
             total="{sum($employees/@yearServed)}"/>

Output: <?xml version="1.0" encoding="UTF-8"?> <group total="1" yearServed="1" salary="1234" numemployees="1"/> <group total="4" yearServed="4" salary="1321" numemployees="1"/> <group total="3" yearServed="3" salary="2324" numemployees="1"/> <group total="1" yearServed="1" salary="2344" numemployees="1"/> <group total="2" yearServed="2" salary="3211" numemployees="1"/> <group total="3" yearServed="3" salary="3422" numemployees="1"/> <group total="2" yearServed="2" salary="4321" numemployees="1"/>

</source>
   
  


Constraining and sorting on aggregated values

   <source lang="xml">

File: Data.xml <inventory id="inv0001" date="2008-12-19" loc="USA">

 <car model="A" num="0001" quantity="1" color="navy"/>
 <car model="B" num="0002" quantity="1" color="red"/>
 <car model="B" num="0003" quantity="2" color="red"/>
 <car model="C" num="0004" quantity="1" color="white"/>
 <car model="C" num="0004" quantity="1" color="gray"/>
 <car model="A" num="0001" quantity="1" color="black"/>

</inventory>

File: Query.xquery let $allcars := doc("Data.xml")//car for $d in distinct-values($allcars/@model) for $n in distinct-values($allcars/@num) let $cars := $allcars[@model = $d and @num = $n] where sum($cars/@quantity) > 1 order by count($cars) return if (exists($cars))

      then <group model="{$d}" num="{$n}" numcars="{count($cars)}"
                  totQuant="{sum($cars/@quantity)}"/>
      else ()

Output: <?xml version="1.0" encoding="UTF-8"?> <group totQuant="2" num="0003" model="B" numcars="1"/> <group totQuant="2" num="0001" model="A" numcars="2"/> <group totQuant="2" num="0004" model="C" numcars="2"/>

</source>