Database Reference
In-Depth Information
WHERE
{
?o1 qb:dataSet nwi:dataset1 ; nw:Product ?prod ;
nw:OrderDate ?odate1 ; nw:SalesAmount ?sales1 .
?prod qb4o:inLevel nw:Product ; nw:productName ?prodName .
?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 ?prodName ?yearNo1 ?monthNo1
}
FILTER( ( (?monthNo = ?monthNo1 + 1) && (?yearNo = ?yearNo1) )
||
( (?monthNo = 1) && (?monthNo1 = 12) &&
(?yearNo = ?yearNo1+1) ) )
}}
ORDER BY ?prodName ?yearNo ?monthNo
The first inner query computes the monthly sales by product. Then, after the
OPTIONAL
keyword, the second inner query computes again the monthly
sales by product. The
FILTER
condition makes the join of the two inner
queries relating the sales amount of a month and that of the previous month.
The condition must take into account whether the previous month is in the
same year or in the previous year.
Query 14.5.
Three best-selling employees.
SELECT ?fName ?lName (SUM(?sales) AS ?totalSales)
WHERE
{
?o qb:dataSet nwi:dataset1 ; nw:Employee ?emp ; nw:SalesAmount ?sales .
?emp qb4o:inLevel nw:Employee ; nw:firstName ?fName ;
nw:lastName ?lName .
}
GROUP BY ?fName ?lName
ORDER BY DESC (?totalSales)
LIMIT 3
This query computes the total sales by employee, sorts them in descending
order of total sales, and keeps the first three results.
Query 14.6.
Best-selling employee per product and year.
SELECT ?prodName ?yearNo ?maxSales ?fName ?lName
WHERE
{
# Maximum employee sales per product and year
{
SELECT ?prodName ?yearNo (MAX(?totalSales) as ?maxSales)
WHERE
{
{
SELECT ?prodName ?yearNo ?emp (SUM(?sales) AS ?totalSales)
WHERE
?o qb:dataSet nwi:dataset1 ; nw:Product ?prod ;
nw:OrderDate ?odate ; nw:Employee ?emp ;
nw:SalesAmount ?sales .
?prod qb4o:inLevel nw:Product ; nw:productName ?prodName .
?emp qb4o:inLevel nw:Employee .
?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 .
{
}
GROUP BY ?prodName ?yearNo ?emp
}}
Search WWH ::
Custom Search