Database Reference
In-Depth Information
This query starts by selecting the category, month, and year levels. Then,
for each category, month, and year, the query selects all facts whose order
date is in the same year but whose month is less than or equal to the current
month.
Query 14.11.
Moving average over the last 3 months of the sales amount
by product category.
SELECT ?catName ?yearNo ?monthNo (AVG(?totalSales1) AS ?MovAvgSales)
WHERE
{
?cat qb4o:inLevel nw:Category ; nw:categoryName ?catName .
?month qb4o:inLevel nw:Month ; nw:monthNumber ?monthNo ;
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.
OPTIONAL
{
{
SELECT ?catName ?yearNo1 ?monthNo1 (SUM(?sales1) AS ?totalSales1)
WHERE
?o1 qb:dataSet nwi:dataset1 ; nw:Product ?prod1 ;
nw:OrderDate ?odate1 ; nw:SalesAmount ?sales1 .
?prod1 qb4o:inLevel nw:Product ; skos:broader ?cat1 .
?cat1 qb4o:inLevel nw:Category ; nw:categoryName ?catName .
?odate1 qb4o:inLevel nw:OrderDate ; skos:broader ?month1 .
?month1 qb4o:inLevel nw:Month ; nw:monthNumber ?monthNo1 ;
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.
}
GROUP BY ?catName ?yearNo1 ?monthNo1
}
FILTER( (( ?monthNo
>
=
3 && ?yearNo = ?yearNo1 &&
?monthNo
>
=
?monthNo1 && ?monthNo-2
<
=
?monthNo1 )
||
( ?monthNo = 2 && (( ?yearNo = ?yearNo1 && ?monthNo1
<
=
2)
||
( ?yearNo = ?yearNo1+1 && ?monthNo1 = 12 )))
||
( ?monthNo = 1 && ((?yearNo = ?yearNo1 && ?monthNo1 = 1 )
||
(?yearNo = ?yearNo1+1 && ?monthNo1
>
=
11 )))))
{
}}
GROUP BY ?catName ?yearNo ?monthNo
ORDER BY ?catName ?yearNo ?monthNo
This query starts by selecting the category, month, and year levels. Then,
for each category, month, and year, the query selects all facts whose order
date is within a 3-month window from the current month. This selection
involves an elaborated condition in the
FILTER
clause, which covers three
cases, depending on whether the month is March or later, the month is
February, or the month is January.
Query 14.12.
Personal sales amount made by an employee compared with
the total sales amount made by herself and her subordinates during 1997.
SELECT ?fName ?lName ?persSales ?subordSales
WHERE
{
?emp qb4o:inLevel nw:Employee; nw:firstName ?fName ;
nw:lastName ?lName .
{
SELECT ?emp (SUM(?sales) AS ?persSales)
WHERE
{
?o qb:dataSet nwi:dataset1 ; nw:Employee ?emp ;
nw:OrderDate ?odate ; nw:SalesAmount ?sales .
Search WWH ::
Custom Search