Database Reference
In-Depth Information
nw:monthNumber ?monthNo .
?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 ?stateName ?yearNo ?monthNo
}
# Monthly sales by state for the previous year
OPTIONAL
{
{
SELECT ?stateName ?yearNo1 ?monthNo
(SUM(?sales1) AS ?salesPrevYear)
?o1 qb:dataSet nwi:dataset1 ; nw:Customer ?cust1 ;
nw:OrderDate ?odate1 ; nw:SalesAmount ?sales1 .
?cust1 qb4o:inLevel nw:Customer ; skos:broader ?city1 .
?city1 qb4o:inLevel nw:City ; skos:broader ?state .
?state qb4o:inLevel nw:State ; nw:stateName ?stateName .
?odate1 qb4o:inLevel nw:OrderDate ; skos:broader ?month1 .
?month1 qb4o:inLevel nw:Month ; skos:broader ?quarter1 ;
nw:monthNumber ?monthNo .
?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 ?stateName ?yearNo1 ?monthNo }
FILTER ( ?yearNo = ?yearNo1 + 1) }}
ORDER BY ?stateName ?yearNo ?monthNo
WHERE
{
The first inner query computes the monthly sales by state by rolling up the
customer dimension to the state level and the order date dimension to the
month level. Then, after the OPTIONAL keyword, the second inner query
computes again the monthly sales by state. The FILTER condition makes the
join of the two inner queries relating the sales amount of a month and that
of the corresponding month of the previous year.
Query 14.4. Monthly sales growth per product, that is, total sales per
product compared to those of the previous month.
SELECT ?prodName ?yearNo ?monthNo ?totalSales ?prevMonthSales
(?totalSales - ?prevMonthSales AS ?salesGrowth)
WHERE
{ # Monthly sales by product
{
SELECT ?prodName ?yearNo ?monthNo (SUM(?sales) AS ?totalSales)
WHERE
{
?o qb:dataSet nwi:dataset1 ; nw:Product ?prod ;
nw:OrderDate ?odate ; nw:SalesAmount ?sales .
?prod qb4o:inLevel nw:Product ; nw:productName ?prodName .
?odate qb4o:inLevel nw:OrderDate ; skos:broader ?month .
?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 .
}
GROUP BY ?prodName ?yearNo ?monthNo
}
# Monthly sales by product for the previous month
OPTIONAL
{
{ SELECT ?prodName ?yearNo1 ?monthNo1
(SUM(?sales1) AS ?prevMonthSales)
Search WWH ::




Custom Search