Data Warehouses and the Semantic Web - Data Warehouse Systems: Design and Implementation - page 561

?odate qb4o:inLevel nw:OrderDate ; skos:broader ?month .
?month qb4o:inLevel nw:Month ; skos:broader ?quarter .
?quarter qb4o:inLevel nw:Quarter ; skos:broader ?sem .
?sem qb4o:inLevel nw:Semester ; skos:broader ?year .
?year qb4o:inLevel nw:Year ; nw:year ?yearNo .
FILTER(?yearNo = 1997) }

GROUP BY ?emp }

{ SELECT ?emp (SUM(?sales1) AS ?subordSales)

WHERE ?subord nw:supervisor* ?emp .

?o1 qb:dataSet nwi:dataset1 ; nw:Employee ?subord ;
nw:OrderDate ?odate1 ; nw:SalesAmount ?sales .
?odate1 qb4o:inLevel nw:OrderDate ; skos:broader ?month1 .
?month1 qb4o:inLevel nw:Month ; skos:broader ?quarter1 .
?quarter1 qb4o:inLevel nw:Quarter ; skos:broader ?sem1 .
?sem1 qb4o:inLevel nw:Semester ; skos:broader ?year1 .
?year1 qb4o:inLevel nw:Year ; nw:year ?yearNo1 .
FILTER(?yearNo1 = 1997) }
GROUP BY ?emp }}
ORDER BY ?emp
The first inner query computes by employee the personal sales in 1997.
The second inner query exploits the recursive hierarchy Supervision with
a property path expression in SPARQL. The ' * ' character states that the
transitive closure of the supervision hierarchy must be taken into account for
obtaining all subordinates of an employee. Then, the sales in 1997 of all these
subordinates are aggregated.

{

Query 14.13. Total sales amount, number of products, and sum of the
quantities sold for each order.

SELECT ?orderNo (SUM(?sales) AS ?totalSales)
(COUNT(?prod) AS ?nbProducts) (SUM(?qty) AS ?nbUnits)
WHERE { ?o qb:dataSet nwi:dataset1 ; nw:Order ?order ;
nw:Product ?prod ; nw:SalesAmount ?sales ; nw:Quantity ?qty .
?order qb4o:inLevel nw:Order ; nw:orderNo ?orderNo . }
GROUP BY ?orderNo
ORDER BY ?orderNo

In this query, we group sales by order number and then compute the requested
measures.

Query 14.14. For each month, total number of orders, total sales amount,
and average sales amount by order.
SELECT ?yearNo ?monthNo (COUNT(?orderNo) AS ?nbOrders)
(SUM(?totalSales) AS ?totalSalesMonth)
(AVG(?totalSales) AS ?avgSalesOrder)

WHERE {

{ SELECT ?orderNo ?odate (SUM(?sales) AS ?totalSales)

WHERE { ?o qb:dataSet nwi:dataset1 ; nw:Order ?order ;

nw:OrderDate ?odate ; nw:SalesAmount ?sales .
?order qb4o:inLevel nw:Order ; nw:orderNo ?orderNo . }

GROUP BY ?orderNo ?odate }