Database Reference
In-Depth Information
In this query, we select the customer, order date, product, and sales amount
of all sales, roll up the date to the year level, roll up the product to the
category level, and aggregate the sales amount measure.
Query 14.2.
Yearly sales amount for each pair of customer country and
supplier countries.
SELECT ?custCountryName ?supCountryName ?yearNo (SUM(?sales) AS ?totalSales)
WHERE
{
?o qb:dataSet nwi:dataset1 ; nw:Customer ?cust ; nw:Supplier ?sup ;
nw:OrderDate ?odate ; nw:SalesAmount ?sales .
?cust qb4o:inLevel nw:Customer ; skos:broader ?custCity .
?custCity qb4o:inLevel nw:City ; skos:broader ?custState .
?custState qb4o:inLevel nw:State .
{
?custState skos:broader ?custRegion .
?custRegion qb4o:inLevel nw:Region ; skos:broader ?custCountry .
}
UNION
}
?custCountry qb4o:inLevel nw:Country ; nw:countryName ?custCountryName.
?sup qb4o:inLevel nw:Supplier ; skos:broader ?supCity .
?supCity qb4o:inLevel nw:City ; skos:broader ?supState .
?supState qb4o:inLevel nw:State .
{
{
?custState skos:broader ?custCountry .
?supState skos:broader ?supRegion .
?supRegion qb4o:inLevel nw:Region ; skos:broader ?supCountry .
}
UNION
{
?supState skos:broader ?supCountry .
}
?supCountry qb4o:inLevel nw:Country ; nw:countryName ?supCountryName.
?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 ?custCountryName ?supCountryName ?yearNo
ORDER BY ?custCountryName ?supCountryName ?yearNo
The above query performs a roll-up of the customer and supplier dimen-
sions to the country level and a roll-up of the order date to the year level and
then aggregates the measure sales amount. Since a state rolls up to either a
region or a country, the patterns between curly brackets before and after the
UNION
operator are needed to take into account both alternative aggregation
paths.
Query 14.3.
Monthly sales by customer state compared to those of the
previous year.
SELECT ?stateName ?yearNo ?monthNo ?totalSales ?salesPrevYear
WHERE
{
# Monthly sales by state
{
SELECT ?stateName ?yearNo ?monthNo (SUM(?sales) AS ?totalSales)
WHERE
{
?o qb:dataSet nwi:dataset1 ; nw:Customer ?cust ;
nw:OrderDate ?odate ; nw:SalesAmount ?sales .
?cust qb4o:inLevel nw:Customer ; skos:broader ?city .
?city qb4o:inLevel nw:City ; skos:broader ?state .
?state qb4o:inLevel nw:State ; nw:stateName ?stateName .
?odate qb4o:inLevel nw:OrderDate ; skos:broader ?month .
?month qb4o:inLevel nw:Month ; skos:broader ?quarter ;
Search WWH ::
Custom Search