Querying the Web of Data easily and efficiently
Kim Ahlstrøm Jakobsen, Alex B. Andersen, Katja Hose, Torben Bach Pedersen
In: Proceedings of the 6th International Workshop on Consuming Linked Data (COLD 2015) co-located with the 14th International Semantic Web Conference (ISWC 2015)
Abstract
In today's data-driven world, analytical querying, typically based on the data cube concept, is the cornerstone of answering important business questions and making data-driven decisions. Traditionally, the underlying analytical data was mostly internal to the organization and stored in relational data ware houses and data cubes. Today, external data sources are essential for analytics and, as the Semantic Web gains popularity, more and more external sources are available in native RDF. With the recent SPARQL 1.1 standard, performing analytical queries over RDF data sources has finally become feasible. However, unlike their relational counterparts, RDF data cubes stores lack optimizations that enable fast querying. In this paper, we present an approach to optimizing RDF data cubes that is based on three novel cube patterns that optimize RDF data cubes, as well as associated algorithms that transform the RDF data cube. An extensive experimental evaluation shows that the approach allows trading additional storage and/or load times in return for significantly increased query performance. We further provide guidelines for which patterns to apply for specific scenarios and systems.
Experiments
Repositories
The implementation and the code for running the experiments are available at the following two GitHub projects.
SWOD Implementation
This program generates a series of SPARQL construct queries that create the snowflake pattern and fully denormalized pattern cubes.
This Java program uses Apache Maven to manage dependencies.
The SWOD Tools project contains generated SPARQL queries, thus it is not necessary to run the SWOD program in order to run the experiments.
SWOD Tools
These tools will allow you to generate the TPC-H data in triples (generate.sh), load the data into Virtuoso and Apache Jena (load.sh), run the TPC-H queries on the triple stores (query.sh), and analyse the results by comparing the queries (extractQueryTimes.py, compareResults.py).
All scripts are written in bash and Python, which might result in some problem on windows systems.
The batch scripts take a series of "sources" as input, these modular configurations files are located in the "source" folder. Be aware the these configuration files need to be set up manually before running any of the programs.
The Python scripts have a help flag (--help) that displays the allowed parameters.
Workflow
Download and install the following program
Create configuration files (source files) that match your system (source/machine/) and wanted configuration (scale factor etc.)
Generate or download the dataset
Generation requires Virtuoso for running the construct queries
Install Virtuoso or Apache Jena
Load the data into the Jena TDB or Virtuoso by using the appropriate configuration files
Change the querymix configuration (source/) to match which queries you want to execute, run the querymix.sh program to propagate these settings.
Run the query.sh script with the appropriate configuration files to start the experiments
Use the extractQueryTimes.py on the generated logfiles (logs/) to extract and aggregate the query times.
The experiments can now be compare using the compareResults.py script
Feel free to post bug report and ask questions.
TPC-H relational diagram
Dataset
| Scale 0.1 | Scale 0.2 | Scale 0.3 | Scale 0.5 | |
|---|---|---|---|---|
| Snowflake | Download | Download | Download | Download |
| Star | Download | Download | Download | Download |
| Denormalized | Download | Download | Download | Download |
Queries
Virtuoso queries Snowflake pattern
Prefix
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
Query 1
select
?l_returnflag
?l_linestatus
(sum(xsd:decimal(?l_linequantity)) as ?sum_qty)
(ROUND(sum(xsd:decimal(?l_lineextendedprice))*100)/100 as ?sum_base_price)
(ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount)))*100)/100 as ?sum_disc_price)
(ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax)))*100)/100 as ?sum_charge)
(ROUND(avg(xsd:decimal(?l_linequantity))*100)/100 as ?avg_qty)
(ROUND(avg(xsd:decimal(?l_lineextendedprice))*100)/100 as ?avg_price)
(ROUND(avg(xsd:decimal(?l_linediscount))*100)/100 as ?avg_disc)
(count(1) as ?count_order)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_linestatus ?l_linestatus ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linetax ?l_linetax ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linediscount ?l_linediscount .
filter (xsd:boolean(xsd:dateTime(?l_shipdate) <= xsd:dateTime(bif:dateadd ("day", -%DELTA%, "1998-12-01"^^xsd:date))))
}
group by
?l_returnflag
?l_linestatus
order by
?l_returnflag
?l_linestatus
Query 2
select
?s_acctbal,
?s_name,
?nation_name,
?p_partkey,
?p_mfgr,
?s_address,
?s_phone,
?s_comment
where {
?ps a ltpch:partsupp;
ltpch:ps_has_supplier ?supp;
ltpch:ps_has_part ?part ;
ltpch:ps_supplycost ?minsc .
?supp a ltpch:supplier ;
ltpch:s_acctbal ?s_acctbal ;
ltpch:s_name ?s_name ;
ltpch:s_has_nation ?s_has_nation ;
ltpch:s_address ?s_address ;
ltpch:s_phone ?s_phone ;
ltpch:s_comment ?s_comment .
?s_has_nation ltpch:n_name ?nation_name ;
ltpch:n_has_region ?s_has_region .
?s_has_region ltpch:r_name "%REGION%" .
?part a ltpch:part ;
ltpch:p_partkey ?p_partkey ;
ltpch:p_mfgr ?p_mfgr ;
ltpch:p_size "%SIZE%" ;
ltpch:p_type ?p_type .
{ select ?part min(?s_cost) as ?minsc
where {
?ps a ltpch:partsupp;
ltpch:ps_has_part ?part;
ltpch:ps_has_supplier ?ms;
ltpch:ps_supplycost ?s_cost .
?ms ltpch:s_has_nation ?m_has_nation .
?m_has_nation ltpch:n_has_region ?m_has_region .
?m_has_region ltpch:r_name "%REGION%" .
}
}
filter (?p_type like "%%TYPE%")
}
order by
desc (?s_acctbal)
?nation_name
?s_name
?p_partkey
limit 100
Query 3
select
?o_orderkey
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
?o_orderdate
?o_shippriority
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_has_order ?ord ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:o_orderdate ?o_orderdate ;
ltpch:o_shippriority ?o_shippriority ;
ltpch:o_orderkey ?o_orderkey ;
ltpch:o_has_customer ?cust .
?cust ltpch:c_mktsegment ?c_mktsegment .
filter ((xsd:dateTime(?o_orderdate) < xsd:dateTime("%DATE%"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) > xsd:dateTime("%DATE%"^^xsd:date)) &&
(?c_mktsegment = "%SEGMENT%") )
}
group by
?o_orderkey
?o_orderdate
?o_shippriority
order by
desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
?o_orderdate
limit 10
Query 4
select
?o_orderpriority
(count(*) as ?order_count)
where
{
{
select distinct
?o_orderpriority
?ord
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate .
?ord ltpch:o_orderpriority ?o_orderpriority ;
ltpch:o_orderdate ?o_orderdate .
filter (
(xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))))
)
}
}
}
group by
?o_orderpriority
order by
?o_orderpriority
Query 5
select
?nation
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:o_has_customer ?cust ;
ltpch:o_orderdate ?o_orderdate .
?ps ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?s_nation .
?s_nation ltpch:n_has_region ?s_region ;
ltpch:n_name ?nation .
?s_region ltpch:r_name ?r_name .
?cust ltpch:c_has_nation ?c_nation.
filter ((?c_nation = ?s_nation) &&
(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("year", 1,"%YEAR%-01-01" ^^xsd:date))) &&
(?r_name = "%REGION%") )
}
group by
?nation
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
Query 6
select
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipdate ?l_shipdate .
filter ( (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date))) &&
(xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
(xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
(xsd:decimal(?l_linequantity) < %QUANTITY%) )
}
Query 7
select
?supp_nation
?cust_nation
?li_year
(sum (?volume) as ?revenue)
where {
{
select
?supp_nation
?cust_nation
?li_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:o_has_customer ?cust .
?cust ltpch:c_has_nation ?custn .
?custn ltpch:n_name ?cust_nation .
?ps ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?suppn .
?suppn ltpch:n_name ?supp_nation .
BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter ((
(?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
(?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
(xsd:dateTime(?l_shipdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) <= xsd:dateTime("1996-12-31"^^xsd:date)) )
}
}
}
group by
?supp_nation
?cust_nation
?li_year
order by
?supp_nation
?cust_nation
?li_year
Query 8
select
?o_year
((?sum1 / ?sum2) as ?mkt_share)
where {
{ select
?o_year
(sum (?volume * bif:equ (?nation, "%NATION%")) as ?sum1)
(sum (?volume) as ?sum2)
where {
{ select
((YEAR (xsd:dateTime(?o_orderdate))) as ?o_year)
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
?nation
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:ps_has_supplier ?s_supplier .
?s_supplier ltpch:s_has_nation ?n2 .
?n2 ltpch:n_name ?nation .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_type ?type .
?ord ltpch:o_orderdate ?o_orderdate ;
ltpch:o_has_customer ?c_customer .
?c_customer ltpch:c_has_nation ?n_nation .
?n_nation ltpch:n_has_region ?r_region .
?r_region ltpch:r_name ?region.
filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) <= xsd:dateTime("1996-12-31"^^xsd:date) &&
?region = "%REGION%" &&
?type = "%TYPE%")
)
}
}
}
group by
?o_year
}
}
order by
?o_year
Query 9
select
?nation
?o_year
(sum(?amount) as ?sum_profit)
where {
{ select
?nation
?o_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:ps_has_part ?part ;
ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?s_nation .
?s_nation ltpch:n_name ?nation .
?ord ltpch:o_orderdate ?o_orderdate .
?ps ltpch:ps_supplycost ?ps_supplycost .
?part ltpch:p_name ?p_name .
filter (REGEX (?p_name, "%COLOR%"))
BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
}
}
}
group by
?nation
?o_year
order by
?nation
desc (?o_year)
Query 10
select
?c_custkey
?c_companyName
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_has_order ?ord ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:o_has_customer ?cust ;
ltpch:o_orderdate ?o_orderdate .
?cust ltpch:c_address ?c_address ;
ltpch:c_phone ?c_phone ;
ltpch:c_comment ?c_comment ;
ltpch:c_acctbal ?c_acctbal ;
ltpch:c_custkey ?c_custkey ;
ltpch:c_has_nation ?c_nation ;
ltpch:c_name ?c_companyName .
?c_nation ltpch:n_name ?nation .
filter ((xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)))) &&
(xsd:boolean(?l_returnflag = "R"))
)
}
group by
?c_custkey
?c_companyName
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20
Query 11
select
?bigpspart,
?bigpsvalue
where {
{ select
?bigpspart,
sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue
where
{
?bigps a ltpch:partsupp ;
ltpch:ps_has_part ?bigpspart ;
ltpch:ps_supplycost ?b_supplycost ;
ltpch:ps_availqty ?b_availqty ;
ltpch:ps_has_supplier ?b_supplier .
?b_supplier ltpch:s_has_nation ?b_nation .
?b_nation ltpch:n_name "%NATION%" .
}
}
filter (?bigpsvalue > (
select
(sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty) * %FRACTION%)) as ?threshold
where
{
?thr_ps a ltpch:partsupp ;
ltpch:ps_has_part ?t_part ;
ltpch:ps_supplycost ?t_supplycost ;
ltpch:ps_availqty ?t_availqty ;
ltpch:ps_has_supplier ?t_supplier .
?t_supplier ltpch:s_has_nation ?t_nation .
?t_nation ltpch:n_name "%NATION%" .
}
)
)
}
order by
desc (?bigpsvalue)
Query 12
select
?l_shipmode
(sum (
bif:__or (
bif:equ (?o_orderpriority, "1-URGENT"),
bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
(sum (1 -
bif:__or (
bif:equ (?o_orderpriority, "1-URGENT"),
bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:o_orderpriority ?o_orderpriority .
filter (xsd:boolean(?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%")) &&
(xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
(xsd:boolean(xsd:dateTime(?l_shipdate) < xsd:dateTime(?l_commitdate))) &&
(xsd:boolean(xsd:dateTime(?l_receiptdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?l_receiptdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))) )
}
group by
?l_shipmode
order by
?l_shipmode
Query 13
select
?c_count
(count(1) as ?custdist)
where {
{ select
?c_custkey
(count (?ord) as ?c_count)
where
{
?cust ltpch:c_custkey ?c_custkey .
optional {
?ord a ltpch:orders ;
ltpch:o_has_customer ?cust ;
ltpch:o_comment ?o_comment .
filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
}
}
group by
?c_custkey
}
}
group by
?c_count
order by
desc (count(1))
desc (?c_count)
Query 14
select
(100 * sum(bif:equ(bif:LEFT(?p_type, 5), "PROMO") * xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount))) / sum(xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_type ?p_type .
filter (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd("month", 1, "%MONTH%-01"^^xsd:date))) )
}
Query 15
select
?s_suppkey
?s_name
?s_address
?s_phone
?total_revenue
where {
?supplier a ltpch:supplier ;
ltpch:s_suppkey ?s_suppkey ;
ltpch:s_name ?s_name ;
ltpch:s_address ?s_address ;
ltpch:s_phone ?s_phone .
{ select
?supplier
(sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount))) as ?total_revenue)
where {
?li1 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_extendedprice ;
ltpch:l_linediscount ?l_discount ;
ltpch:l_has_partsupplier ?ps1 .
?ps1 ltpch:ps_has_supplier ?supplier .
filter (
xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
}
group by
?supplier
}
{ select (max (?l2_total_revenue) as ?maxtotal)
where {
{ select
?supplier2
(sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount))) as ?l2_total_revenue)
where {
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l2_shipdate ;
ltpch:l_lineextendedprice ?l2_extendedprice ;
ltpch:l_linediscount ?l2_discount ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:ps_has_supplier ?supplier2 .
filter (
xsd:dateTime(?l2_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
xsd:dateTime(?l2_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
}
group by
?supplier2
}
}
}
filter (?total_revenue = ?maxtotal)
}
order by
?supplier
Query 16
select
?p_brand,
?p_type,
?p_size,
(count(distinct ?supp)) as ?supplier_cnt
where {
?ps a ltpch:partsupp ;
ltpch:ps_has_part ?part ;
ltpch:ps_has_supplier ?supp .
?part ltpch:p_brand ?p_brand ;
ltpch:p_type ?p_type ;
ltpch:p_size ?p_size .
filter (
(?p_brand != "%BRAND%") &&
!(?p_type like "%TYPE%%") &&
(xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
)
filter NOT EXISTS {
?supp a ltpch:supplier;
ltpch:s_comment ?badcomment .
filter (?badcomment like "%Customer%Complaints%")
}
}
group by
?p_brand
?p_type
?p_size
order by
desc ((count(distinct ?supp)))
?p_brand
?p_type
?p_size
Query 17
select
((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_brand ?p_brand ;
ltpch:p_container ?p_container .
{
select
?part
((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
where {
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:ps_has_part ?part .
}
group by
?part
}
filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%")
}
Query 18
select
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
(sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_order ?ord .
?ord ltpch:o_orderkey ?o_orderkey ;
ltpch:o_orderdate ?o_orderdate ;
ltpch:o_ordertotalprice ?o_ordertotalprice ;
ltpch:o_has_customer ?cust .
?cust ltpch:c_custkey ?c_custkey ;
ltpch:c_name ?c_name .
{ select
?sum_order
(sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
where {
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_order ?sum_order .
}
group by ?sum_order
} .
filter (?sum_order = ?ord && xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
order by
desc (?o_ordertotalprice)
?o_orderdate
limit 100
Query 19
select
((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipinstruct ?l_shipinstruct ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_brand ?p_brand ;
ltpch:p_size ?p_size ;
ltpch:p_container ?p_container .
filter (?l_shipmode in ("AIR", "AIR REG") &&
?l_shipinstruct = "DELIVER IN PERSON" &&
( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
(?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
(?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY2%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
(?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
}
Query 20
select
?s_name
?s_address
where
{
?supp ltpch:s_name ?s_name ;
ltpch:s_address ?s_address .
{
select distinct
?supp
where
{
?big_ps ltpch:ps_has_part ?part ;
ltpch:ps_availqty ?big_ps_availqty ;
ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?s_nation .
?s_nation ltpch:n_name ?n_name .
?part ltpch:p_name ?p_name .
filter (REGEX (?p_name , "^%COLOR%") &&
?n_name = "%NATION%" &&
xsd:decimal(?big_ps_availqty) > ?qty_threshold)
{
select
((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
?big_ps
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?big_ps .
filter ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))
)
}
group by
?big_ps
}
}
}
}
order by ?s_name
Query 21
select
?s_name
(count(1) as ?numwait)
where {
?li1 qb:dataSet ltpch:lineitemCube;
ltpch:l_receiptdate ?l1_receiptdate ;
ltpch:l_commitdate ?l1_commitdate ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord .
?ps ltpch:ps_has_supplier ?supp .
?supp ltpch:s_name ?s_name ;
ltpch:s_has_nation ?s_nation .
?ord ltpch:o_orderstatus ?orderstatus .
?s_nation ltpch:n_name ?name
filter (
xsd:boolean(xsd:dateTime(?l1_receiptdate) > xsd:dateTime(?l1_commitdate)) &&
?name = "%NATION%" &&
?orderstatus = "F"
)
filter exists {
?li2 ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:ps_has_supplier ?supp2 .
filter (?supp != ?supp2)
}
filter not exists {
?li3 ltpch:l_has_order ?ord ;
ltpch:l_receiptdate ?l3_receiptdate ;
ltpch:l_commitdate ?l3_commitdate ;
ltpch:l_has_partsupplier ?ps3 .
?ps3 ltpch:ps_has_supplier ?supp3 .
filter (
xsd:boolean(xsd:dateTime(?l3_receiptdate) > xsd:dateTime(?l3_commitdate)) &&
?supp3 != ?supp
)
}
}
group by
?s_name
order by
desc (count(1))
?s_name
limit 100
Query 22
select
(bif:LEFT (?c_phone, 2)) as ?cntrycode,
(count (1)) as ?numcust,
sum (xsd:decimal(?c_acctbal)) as ?totacctbal
where {
?cust a ltpch:customer ;
ltpch:c_acctbal ?c_acctbal ;
ltpch:c_phone ?c_phone .
{
select (avg (xsd:decimal(?c_acctbal2))) as ?acctbal_threshold
where
{
?cust2 a ltpch:customer ;
ltpch:c_acctbal ?c_acctbal2 ;
ltpch:c_phone ?c_phone2 .
filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
bif:LEFT (?c_phone2, 2) in (%COUNTRY_CODE_SET%) )
}
}
filter (
bif:LEFT (?c_phone, 2) in (%COUNTRY_CODE_SET%) &&
(xsd:decimal(?c_acctbal) > ?acctbal_threshold )
)
filter not exists { ?ord ltpch:o_has_customer ?cust }
}
group by (bif:LEFT (?c_phone, 2))
order by (bif:LEFT (?c_phone, 2))
Virtuoso queries star pattern
Prefix
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
Query 1
select
?l_returnflag
?l_linestatus
(sum(xsd:decimal(?l_linequantity)) as ?sum_qty)
(ROUND(sum(xsd:decimal(?l_lineextendedprice))*100)/100 as ?sum_base_price)
(ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount)))*100)/100 as ?sum_disc_price)
(ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax)))*100)/100 as ?sum_charge)
(ROUND(avg(xsd:decimal(?l_linequantity))*100)/100 as ?avg_qty)
(ROUND(avg(xsd:decimal(?l_lineextendedprice))*100)/100 as ?avg_price)
(ROUND(avg(xsd:decimal(?l_linediscount))*100)/100 as ?avg_disc)
(count(1) as ?count_order)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_linestatus ?l_linestatus ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linetax ?l_linetax ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linediscount ?l_linediscount .
filter (xsd:dateTime(?l_shipdate) <= xsd:dateTime(bif:dateadd ("day", -%DELTA%, "1998-12-01"^^xsd:date)))
}
group by
?l_returnflag
?l_linestatus
order by
?l_returnflag
?l_linestatus
Query 2
select
?s_acctbal,
?s_name,
?nation_name,
?p_partkey,
?p_mfgr,
?s_address,
?s_phone,
?s_comment
where {
?ps ltpch:supplier_acctbal ?s_acctbal ;
ltpch:supplier_name ?s_name ;
ltpch:partsupplier_supplycost ?minsc ;
ltpch:supplier_address ?s_address ;
ltpch:supplier_phone ?s_phone ;
ltpch:supplier_comment ?s_comment ;
ltpch:nation_name ?nation_name ;
ltpch:region_name "%REGION%" ;
ltpch:part_partkey ?p_partkey ;
ltpch:part_mfgr ?p_mfgr ;
ltpch:part_size ?size ;
ltpch:part_type ?p_type .
FILTER (?size = str(%SIZE%) && contains(?p_type, "%TYPE%"))
{ select ?p_partkey min(?s_cost) as ?minsc
where {
?ps ltpch:part_partkey ?p_partkey;
ltpch:partsupplier_supplycost ?s_cost ;
ltpch:region_name ?region2 .
filter (?region2 = "%REGION%")
}
}
}
order by
desc (?s_acctbal)
?nation_name
?s_name
?p_partkey
limit 100
Query 3
select
?o_orderkey
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
?o_orderdate
?o_shippriority
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_has_order ?ord ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:order_shippriority ?o_shippriority ;
ltpch:order_orderkey ?o_orderkey ;
ltpch:customer_mktsegment ?c_mktsegment .
filter ((xsd:dateTime(?o_orderdate) < xsd:dateTime("%DATE%"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) > xsd:dateTime("%DATE%"^^xsd:date)) &&
(?c_mktsegment = "%SEGMENT%") )
}
group by
?o_orderkey
?o_orderdate
?o_shippriority
order by
desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
?o_orderdate
limit 10
Query 4
select
?o_orderpriority
(count(*) as ?order_count)
where
{
{
select distinct
?o_orderpriority
?ord
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate .
?ord ltpch:order_orderpriority ?o_orderpriority ;
ltpch:order_orderdate ?o_orderdate .
filter (
(xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))))
)
}
}
}
group by
?o_orderpriority
order by
?o_orderpriority
Query 5
select
?nation
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:nation_name ?c_nation .
?ps ltpch:nation_name ?nation ;
ltpch:region_name ?r_name .
filter ((?c_nation = ?nation) &&
(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("year", 1,"%YEAR%-01-01" ^^xsd:date))) &&
(?r_name = "%REGION%") )
}
group by
?nation
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
Query 6
select
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipdate ?l_shipdate .
filter ( (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date))) &&
(xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
(xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
(xsd:decimal(?l_linequantity) < %QUANTITY%) )
}
Query 7
select
?supp_nation
?cust_nation
?li_year
(sum (xsd:decimal(?volume)) as ?revenue)
where {
{
select
?supp_nation
?cust_nation
?li_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:nation_name ?cust_nation .
?ps ltpch:nation_name ?supp_nation .
BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter ((
(?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
(?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
(xsd:dateTime(?l_shipdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) <= xsd:dateTime("1996-12-31"^^xsd:date)) )
}
}
}
group by
?supp_nation
?cust_nation
?li_year
order by
?supp_nation
?cust_nation
?li_year
Query 8
select
?o_year
((?sum1 / ?sum2) as ?mkt_share)
where {
{ select
?o_year
(sum (?volume * bif:equ (?nation, "%NATION%")) as ?sum1)
(sum (?volume) as ?sum2)
where {
{ select
((YEAR(xsd:dateTime(?o_orderdate))) as ?o_year)
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
?nation
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:nation_name ?nation ;
ltpch:part_type ?type .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:region_name ?region .
filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) <= xsd:dateTime("1996-12-31"^^xsd:date) &&
?region = "%REGION%" &&
?type = "%TYPE%")
)
}
}
}
group by
?o_year
}
}
order by
?o_year
Query 9
select
?nation
?o_year
(sum(?amount) as ?sum_profit)
where {
{ select
?nation
?o_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:nation_name ?nation ;
ltpch:partsupplier_supplycost ?ps_supplycost ;
ltpch:part_name ?p_name .
?ord ltpch:order_orderdate ?o_orderdate .
filter (REGEX (?p_name, "%COLOR%"))
BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
}
}
}
group by
?nation
?o_year
order by
?nation
desc (?o_year)
Query 10
select
?c_custkey
?c_companyName
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_has_order ?ord ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:customer_address ?c_address ;
ltpch:customer_phone ?c_phone ;
ltpch:customer_comment ?c_comment ;
ltpch:customer_acctbal ?c_acctbal ;
ltpch:customer_custkey ?c_custkey ;
ltpch:customer_name ?c_companyName ;
ltpch:nation_name ?nation .
filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))) &&
(?l_returnflag = "R")
)
}
group by
?c_custkey
?c_companyName
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20
Query 11
select
?bigpspart,
?bigpsvalue
where {
{ select
?bigpspart,
sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue
where
{
?bigps ltpch:part_partkey ?bigpspart ;
ltpch:partsupplier_supplycost ?b_supplycost ;
ltpch:partsupplier_availqty ?b_availqty ;
ltpch:nation_name "%NATION%" .
}
}
filter (?bigpsvalue > (
select
(sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION%) as ?threshold
where
{
?thr_ps ltpch:partsupplier_supplycost ?t_supplycost ;
ltpch:partsupplier_availqty ?t_availqty ;
ltpch:nation_name "%NATION%" .
}
))
}
order by
desc (?bigpsvalue)
Query 12
select
?l_shipmode
(sum (
bif:__or (
bif:equ (?o_orderpriority, "1-URGENT"),
bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
(sum (1 -
bif:__or (
bif:equ (?o_orderpriority, "1-URGENT"),
bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:order_orderpriority ?o_orderpriority .
filter (xsd:boolean(?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%")) &&
(xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
(xsd:boolean(xsd:dateTime(?l_shipdate) < xsd:dateTime(?l_commitdate))) &&
(xsd:boolean(xsd:dateTime(?l_receiptdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?l_receiptdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))) )
}
group by
?l_shipmode
order by
?l_shipmode
Query 13
select
?c_count
(count(1) as ?custdist)
where {
{ select
?c_custkey
(count (?o_comment) as ?c_count)
where
{
?ord ltpch:customer_custkey ?c_custkey .
optional {
?ord ltpch:order_comment ?o_comment .
filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
}
}
group by
?c_custkey
}
}
group by
?c_count
order by
desc (count(1))
desc (?c_count)
Query 14
select
((100 * ?sum1 / ?sum2 ) as ?promo_revenue)
where
{
select
(sum (
bif:equ(SUBSTR(?p_type, 1, 5), "PROMO") *
xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum1)
(sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum2)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_has_partsupplier ?part .
?part ltpch:part_type ?p_type .
filter ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd("month", 1, "%MONTH%-01"^^xsd:date))) )
}
}
Query 15
select distinct
?s_suppkey
?s_name
?s_address
?s_phone
?total_revenue
where
{
?partsupp ltpch:supplier_suppkey ?s_suppkey ;
ltpch:supplier_name ?s_name ;
ltpch:supplier_address ?s_address ;
ltpch:supplier_phone ?s_phone .
{
select
?s_suppkey
((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_extendedprice ;
ltpch:l_linediscount ?l_discount ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:supplier_suppkey ?s_suppkey .
filter (
xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
}
group by
?s_suppkey
} .
{
select
(max (?l2_total_revenue) as ?maxtotal)
where
{
{
select
((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
where
{
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l2_shipdate ;
ltpch:l_lineextendedprice ?l2_extendedprice ;
ltpch:l_linediscount ?l2_discount ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:supplier_suppkey ?s_suppkey2 .
filter (
xsd:dateTime(?l2_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
xsd:dateTime(?l2_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
}
group by
?s_suppkey2
}
}
}
filter (?total_revenue = ?maxtotal)
}
order by
?s_suppkey
Query 16
select
?p_brand,
?p_type,
?p_size,
(count(distinct ?supp)) as ?supplier_cnt
where {
?ps ltpch:part_brand ?p_brand ;
ltpch:part_type ?p_type ;
ltpch:part_size ?p_size ;
ltpch:supplier_suppkey ?supp .
filter (
(?p_brand != "%BRAND%") &&
!(?p_type like "%TYPE%%") &&
(xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
)
filter NOT EXISTS {
?ps ltpch:supplier_comment ?badcomment .
filter (?badcomment like "%Customer%Complaints%")
}
}
group by
?p_brand
?p_type
?p_size
order by
desc ((count(distinct ?supp)))
?p_brand
?p_type
?p_size
Query 17
select
((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:part_partkey ?p_partkey.
{
select
?p_partkey
((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
where {
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:part_partkey ?p_partkey ;
ltpch:part_container ?p_container ;
ltpch:part_brand ?p_brand .
filter (REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%" )
}
group by
?p_partkey
}
filter (xsd:decimal(?l_linequantity) < xsd:decimal(?threshold))
}
Query 18
select
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
(sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_order ?ord .
?ord ltpch:order_orderkey ?o_orderkey ;
ltpch:order_orderdate ?o_orderdate ;
ltpch:order_ordertotalprice ?o_ordertotalprice ;
ltpch:customer_custkey ?c_custkey ;
ltpch:customer_name ?c_name .
{
select
?ord
(sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
where
{
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_order ?ord .
}
group by
?ord
} .
filter (?sum_q > %QUANTITY%)
}
group by
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
order by
desc (?o_ordertotalprice)
?o_orderdate
limit 100
Query 19
select
((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipinstruct ?l_shipinstruct .
?ps ltpch:part_brand ?p_brand ;
ltpch:part_size ?p_size ;
ltpch:part_container ?p_container .
filter (?l_shipmode in ("AIR", "AIR REG") &&
?l_shipinstruct = "DELIVER IN PERSON" &&
( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
(?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
(?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY2%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
(?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
}
Query 20
select distinct
?s_name
?s_address
where
{
?supp ltpch:supplier_name ?s_name ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:supplier_address ?s_address .
{
select
distinct ?suppkey
where
{
?big_ps ltpch:partsupplier_availqty ?big_ps_availqty ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:nation_name ?n_name ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:part_partkey ?partkey ;
ltpch:part_name ?p_name .
FILTER(REGEX (?p_name , "^%COLOR%") && ?n_name = "%NATION%") .
{
select
?partkey ?suppkey
((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?big_ps .
?big_ps ltpch:part_partkey ?partkey ;
ltpch:supplier_suppkey ?suppkey .
FILTER ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))
)
}
group by
?partkey ?suppkey
} .
FILTER(xsd:decimal(?big_ps_availqty) > ?qty_threshold) .
}
}
}
order by ?s_name
Query 21
select
?s_name
((count(1)) as ?numwait)
where {
?li1 qb:dataSet ltpch:lineitemCube ;
ltpch:l_receiptdate ?l1_receiptdate ;
ltpch:l_commitdate ?l1_commitdate ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord .
?ps ltpch:supplier_name ?s_name ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:nation_name ?n_name .
?ord ltpch:order_orderstatus ?o_orderstatus .
filter ( xsd:boolean(xsd:dateTime(?l1_receiptdate) > xsd:dateTime(?l1_commitdate)) && ?n_name = "%NATION%" && ?o_orderstatus = "F")
filter exists {
?li2 ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:supplier_suppkey ?suppkey2 .
filter (?suppkey != ?suppkey2)
}
filter not exists {
?li3 ltpch:l_has_order ?ord ;
ltpch:l_receiptdate ?l3_receiptdate ;
ltpch:l_commitdate ?l3_commitdate ;
ltpch:l_has_partsupplier ?ps3 .
?ps3 ltpch:supplier_suppkey ?suppkey3 .
filter (
xsd:boolean(xsd:dateTime(?l3_receiptdate) > xsd:dateTime(?l3_commitdate)) &&
?suppkey3 != ?suppkey
)
}
}
group by
?s_name
order by
desc (count(1))
?s_name
limit 100
Query 22
select
(bif:LEFT (?c_phone, 2)) as ?cntrycode,
(count (1)) as ?numcust,
sum (xsd:decimal(?c_acctbal)) as ?totacctbal
where {
?cust ltpch:customer_acctbal ?c_acctbal ;
ltpch:customer_phone ?c_phone .
{
select (avg (?acctbal2)) as ?acctbal_threshold
where {
select (avg (xsd:decimal(?c_acctbal2))) as ?acctbal2
where
{
?cust2 ltpch:customer_acctbal ?c_acctbal2 ;
ltpch:customer_custkey ?custkey2 ;
ltpch:customer_phone ?c_phone2 .
filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
bif:LEFT (?c_phone2, 2) in (%COUNTRY_CODE_SET%) )
}
group by ?custkey2
}
}
filter (
bif:LEFT (?c_phone, 2) in (%COUNTRY_CODE_SET%) &&
(xsd:decimal(?c_acctbal) > ?acctbal_threshold )
)
filter not exists { ?cust ltpch:order_orderkey ?orderkey }
}
group by (bif:LEFT (?c_phone, 2))
order by (bif:LEFT (?c_phone, 2))
Virtuoso queries denormalized pattern
Prefix
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
Query 1
select
?l_returnflag
?l_linestatus
(sum(xsd:decimal(?l_linequantity)) as ?sum_qty)
(ROUND(sum(xsd:decimal(?l_lineextendedprice))*100)/100 as ?sum_base_price)
(ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount)))*100)/100 as ?sum_disc_price)
(ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax)))*100)/100 as ?sum_charge)
(ROUND(avg(xsd:decimal(?l_linequantity))*100)/100 as ?avg_qty)
(ROUND(avg(xsd:decimal(?l_lineextendedprice))*100)/100 as ?avg_price)
(ROUND(avg(xsd:decimal(?l_linediscount))*100)/100 as ?avg_disc)
(count(1) as ?count_order)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_linestatus ?l_linestatus ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linetax ?l_linetax ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linediscount ?l_linediscount .
filter (xsd:boolean(xsd:dateTime(?l_shipdate) <= xsd:dateTime(bif:dateadd ("day", -%DELTA%, "1998-12-01"^^xsd:date))))
}
group by
?l_returnflag
?l_linestatus
order by
?l_returnflag
?l_linestatus
Query 2
select distinct
?s_acctbal,
?s_name,
?nation_name,
?p_partkey,
?p_mfgr,
?s_address,
?s_phone,
?s_comment
where {
?ps ltpch:partsupplier_supplier_acctbal ?s_acctbal ;
ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_partsupplier_supplycost ?minsc ;
ltpch:partsupplier_supplier_address ?s_address ;
ltpch:partsupplier_supplier_phone ?s_phone ;
ltpch:partsupplier_supplier_comment ?s_comment ;
ltpch:partsupplier_nation_name ?nation_name ;
ltpch:partsupplier_region_name "%REGION%" ;
ltpch:partsupplier_part_partkey ?p_partkey ;
ltpch:partsupplier_part_mfgr ?p_mfgr ;
ltpch:partsupplier_part_size ?size ;
ltpch:partsupplier_part_type ?p_type .
FILTER (?size = str(%SIZE%) && contains(?p_type, "%TYPE%"))
{ select ?p_partkey min(?s_cost) as ?minsc
where {
?ps ltpch:partsupplier_part_partkey ?p_partkey;
ltpch:partsupplier_partsupplier_supplycost ?s_cost ;
ltpch:partsupplier_region_name ?region2 .
filter (?region2 = "%REGION%")
}
}
}
order by
desc (?s_acctbal)
?nation_name
?s_name
?p_partkey
limit 100
Query 3
select
?o_orderkey
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
?o_orderdate
?o_shippriority
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_order_shippriority ?o_shippriority ;
ltpch:order_order_orderkey ?o_orderkey ;
ltpch:order_customer_mktsegment ?c_mktsegment .
filter ((xsd:dateTime(?o_orderdate) < xsd:dateTime("%DATE%"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) > xsd:dateTime("%DATE%"^^xsd:date)) &&
(?c_mktsegment = "%SEGMENT%") )
}
group by
?o_orderkey
?o_orderdate
?o_shippriority
order by
desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
?o_orderdate
limit 10
Query 4
select
?o_orderpriority
(count(*) as ?order_count)
where
{
{
select distinct
?o_orderpriority
?ordkey
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:order_order_orderpriority ?o_orderpriority ;
ltpch:order_order_orderkey ?ordkey ;
ltpch:order_order_orderdate ?o_orderdate .
filter (
(xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))))
)
}
}
}
group by
?o_orderpriority
order by
?o_orderpriority
Query 5
select
?nation
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_nation_name ?c_nation ;
ltpch:partsupplier_nation_name ?nation ;
ltpch:partsupplier_region_name ?r_name .
filter ((?c_nation = ?nation) &&
(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("year", 1,"%YEAR%-01-01" ^^xsd:date))) &&
(?r_name = "%REGION%") )
}
group by
?nation
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
Query 6
select
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipdate ?l_shipdate .
filter ( (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date))) &&
(xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
(xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
(xsd:decimal(?l_linequantity) < %QUANTITY%) )
}
Query 7
select
?supp_nation
?cust_nation
?li_year
(sum (xsd:decimal(?volume)) as ?revenue)
where {
{
select
?supp_nation
?cust_nation
?li_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:order_nation_name ?cust_nation ;
ltpch:partsupplier_nation_name ?supp_nation .
BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter ((
(?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
(?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
(xsd:dateTime(?l_shipdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) <= xsd:dateTime("1996-12-31"^^xsd:date)) )
}
}
}
group by
?supp_nation
?cust_nation
?li_year
order by
?supp_nation
?cust_nation
?li_year
Query 8
select
?o_year
((?sum1 / ?sum2) as ?mkt_share)
where {
{ select
?o_year
(sum (?volume * bif:equ (?nation, "%NATION%")) as ?sum1)
(sum (?volume) as ?sum2)
where {
{ select
((YEAR(xsd:dateTime(?o_orderdate))) as ?o_year)
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
?nation
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:partsupplier_nation_name ?nation ;
ltpch:partsupplier_part_type ?type ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_region_name ?region .
filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) <= xsd:dateTime("1996-12-31"^^xsd:date) &&
?region = "%REGION%" &&
?type = "%TYPE%")
)
}
}
}
group by
?o_year
}
}
order by
?o_year
Query 9
select
?nation
?o_year
(sum(?amount) as ?sum_profit)
where {
{ select
?nation
?o_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:partsupplier_nation_name ?nation ;
ltpch:partsupplier_partsupplier_supplycost ?ps_supplycost ;
ltpch:partsupplier_part_name ?p_name ;
ltpch:order_order_orderdate ?o_orderdate .
filter (REGEX (?p_name, "%COLOR%"))
BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
}
}
}
group by
?nation
?o_year
order by
?nation
desc (?o_year)
Query 10
select
?c_custkey
?c_companyName
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_customer_address ?c_address ;
ltpch:order_customer_phone ?c_phone ;
ltpch:order_customer_comment ?c_comment ;
ltpch:order_customer_acctbal ?c_acctbal ;
ltpch:order_customer_custkey ?c_custkey ;
ltpch:order_customer_name ?c_companyName ;
ltpch:order_nation_name ?nation .
filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))) &&
(?l_returnflag = "R")
)
}
group by
?c_custkey
?c_companyName
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20
Query 11
select
?bigpspart,
?bigpsvalue
where {
{
select
?bigpspart,
sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue
where
{
{
select
?bigpspart,
?b_supplycost,
?b_availqty
where
{
?bigps ltpch:partsupplier_part_partkey ?bigpspart ;
ltpch:partsupplier_partsupplier_supplycost ?b_supplycost ;
ltpch:partsupplier_supplier_suppkey ?b_suppkey ;
ltpch:partsupplier_partsupplier_availqty ?b_availqty ;
ltpch:partsupplier_nation_name "%NATION%" .
}
group by ?bigpspart ?b_suppkey
}
}
}
filter (?bigpsvalue > (
select
(sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION%) as ?threshold
where
{
{
select
?t_partkey,
?t_supplycost,
?t_availqty
where
{
?thr_ps ltpch:partsupplier_partsupplier_supplycost ?t_supplycost ;
ltpch:partsupplier_partsupplier_availqty ?t_availqty ;
ltpch:partsupplier_nation_name "%NATION%" ;
ltpch:partsupplier_supplier_suppkey ?t_suppkey ;
ltpch:partsupplier_part_partkey ?t_partkey .
}
group by ?t_partkey ?t_suppkey
}
}
))
}
order by
desc (?bigpsvalue)
Query 12
select
?l_shipmode
(sum (
bif:__or (
bif:equ (?o_orderpriority, "1-URGENT"),
bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
(sum (1 -
bif:__or (
bif:equ (?o_orderpriority, "1-URGENT"),
bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:order_order_orderpriority ?o_orderpriority .
filter (xsd:boolean(?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%")) &&
(xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
(xsd:boolean(xsd:dateTime(?l_shipdate) < xsd:dateTime(?l_commitdate))) &&
(xsd:boolean(xsd:dateTime(?l_receiptdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date))) &&
(xsd:boolean(xsd:dateTime(?l_receiptdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))) )
}
group by
?l_shipmode
order by
?l_shipmode
Query 13
select
?c_count
(count(1) as ?custdist)
where {
{ select
?c_custkey
(count (distinct ?o_comment) as ?c_count)
where
{
?li2 ltpch:order_customer_custkey ?c_custkey .
optional {
?li2 ltpch:order_order_comment ?o_comment .
filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
}
}
group by
?c_custkey
order by
?c_custkey
}
}
group by
?c_count
order by
desc (count(1))
desc (?c_count)
Query 14
select
((100 * ?sum1 / ?sum2 ) as ?promo_revenue)
where
{
select
(sum (
bif:equ(SUBSTR(?p_type, 1, 5), "PROMO") *
xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum1)
(sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum2)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:partsupplier_part_type ?p_type .
filter ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd("month", 1, "%MONTH%-01"^^xsd:date))) )
}
}
Query 15
select distinct
?s_suppkey
?s_name
?s_address
?s_phone
?total_revenue
where
{
?partsupp ltpch:partsupplier_supplier_suppkey ?s_suppkey ;
ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_address ?s_address ;
ltpch:partsupplier_supplier_phone ?s_phone .
{
select
?s_suppkey
((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_extendedprice ;
ltpch:l_linediscount ?l_discount ;
ltpch:l_has_partsupplier ?ps ;
ltpch:partsupplier_supplier_suppkey ?s_suppkey .
filter (
xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
}
group by
?s_suppkey
} .
{
select
(max (?l2_total_revenue) as ?maxtotal)
where
{
{
select
((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
where
{
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l2_shipdate ;
ltpch:l_lineextendedprice ?l2_extendedprice ;
ltpch:l_linediscount ?l2_discount ;
ltpch:l_has_partsupplier ?ps2 ;
ltpch:partsupplier_supplier_suppkey ?s_suppkey2 .
filter (
xsd:dateTime(?l2_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
xsd:dateTime(?l2_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
}
group by
?s_suppkey2
}
}
}
filter (?total_revenue = ?maxtotal)
}
order by
?s_suppkey
Query 16
select
?p_brand,
?p_type,
?p_size,
(count(distinct ?supp)) as ?supplier_cnt
where {
?ps ltpch:partsupplier_part_brand ?p_brand ;
ltpch:partsupplier_part_type ?p_type ;
ltpch:partsupplier_part_size ?p_size ;
ltpch:partsupplier_supplier_suppkey ?supp .
filter (
(?p_brand != "%BRAND%") &&
!(?p_type like "%TYPE%%") &&
(xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
)
filter NOT EXISTS {
?ps ltpch:partsupplier_supplier_comment ?badcomment .
filter (?badcomment like "%Customer%Complaints%")
}
}
group by
?p_brand
?p_type
?p_size
order by
desc ((count(distinct ?supp)))
?p_brand
?p_type
?p_size
Query 17
select
((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?ps ;
ltpch:partsupplier_part_partkey ?p_partkey.
{
select
?p_partkey
((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
where {
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:partsupplier_part_partkey ?p_partkey ;
ltpch:partsupplier_part_container ?p_container ;
ltpch:partsupplier_part_brand ?p_brand .
filter (REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%" )
}
group by
?p_partkey
}
filter (xsd:decimal(?l_linequantity) < xsd:decimal(?threshold))
}
Query 18
select
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
(sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:order_order_orderkey ?o_orderkey ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_order_ordertotalprice ?o_ordertotalprice ;
ltpch:order_customer_custkey ?c_custkey ;
ltpch:order_customer_name ?c_name .
{
select
?o_orderkey
(sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
where
{
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:order_order_orderkey ?o_orderkey .
}
group by
?o_orderkey
} .
filter (?sum_q > %QUANTITY%)
}
group by
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
order by
desc (?o_ordertotalprice)
?o_orderdate
limit 100
Query 19
select
((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipinstruct ?l_shipinstruct ;
ltpch:partsupplier_part_brand ?p_brand ;
ltpch:partsupplier_part_size ?p_size ;
ltpch:partsupplier_part_container ?p_container .
filter (?l_shipmode in ("AIR", "AIR REG") &&
?l_shipinstruct = "DELIVER IN PERSON" &&
( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
(?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
(?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY2%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
(?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
}
Query 20
select distinct
?s_name
?s_address
where
{
?supp ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_supplier_address ?s_address .
{
select
distinct ?suppkey
where
{
?li ltpch:partsupplier_partsupplier_availqty ?big_ps_availqty ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_part_partkey ?partkey ;
ltpch:partsupplier_nation_name ?n_name ;
ltpch:partsupplier_part_name ?p_name .
FILTER(REGEX (?p_name , "^%COLOR%") && ?n_name = "%NATION%") .
{
select
?suppkey ?partkey
((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_part_partkey ?partkey ;
ltpch:l_linequantity ?l_linequantity .
FILTER ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
(xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))
)
}
group by
?suppkey ?partkey
} .
FILTER(xsd:decimal(?big_ps_availqty) > ?qty_threshold) .
}
}
}
order by ?s_name
Query 21
select
?s_name
((count(1)) as ?numwait)
where {
?li1 qb:dataSet ltpch:lineitemCube ;
ltpch:l_receiptdate ?l1_receiptdate ;
ltpch:l_commitdate ?l1_commitdate ;
ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_nation_name ?n_name ;
ltpch:order_order_orderkey ?orderkey ;
ltpch:order_order_orderstatus ?o_orderstatus .
filter (
xsd:boolean(xsd:dateTime(?l1_receiptdate) > xsd:dateTime(?l1_commitdate)) &&
?n_name = "%NATION%" &&
?o_orderstatus = "F"
)
filter exists {
?li2 ltpch:order_order_orderkey ?orderkey ;
ltpch:partsupplier_supplier_suppkey ?suppkey2 .
filter (?suppkey != ?suppkey2)
}
filter not exists {
?li3 ltpch:order_order_orderkey ?orderkey ;
ltpch:l_receiptdate ?l3_receiptdate ;
ltpch:l_commitdate ?l3_commitdate ;
ltpch:partsupplier_supplier_suppkey ?suppkey3 .
filter (
xsd:boolean(xsd:dateTime(?l3_receiptdate) > xsd:dateTime(?l3_commitdate)) &&
?suppkey3 != ?suppkey
)
}
}
group by
?s_name
order by
desc (count(1))
?s_name
limit 100
Query 22
select
(bif:LEFT (?c_phone, 2)) as ?cntrycode,
(count (1)) as ?numcust,
sum (xsd:decimal(?c_acctbal)) as ?totacctbal
where {
?cust ltpch:order_customer_acctbal ?c_acctbal ;
ltpch:order_customer_phone ?c_phone .
{
select (avg(?acctbal2)) as ?acctbal_threshold
where
{
select (avg (xsd:decimal(?c_acctbal2))) as ?acctbal2
where
{
?li ltpch:order_customer_acctbal ?c_acctbal2 ;
ltpch:order_customer_custkey ?custkey2 ;
ltpch:order_customer_phone ?c_phone2 .
filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
bif:LEFT (?c_phone2, 2) in (%COUNTRY_CODE_SET%) )
}
group by ?custkey2
}
}
filter (
bif:LEFT (?c_phone, 2) in (%COUNTRY_CODE_SET%) &&
(xsd:decimal(?c_acctbal) > ?acctbal_threshold )
)
filter not exists { ?cust ltpch:order_order_orderkey ?orderkey }
}
group by (bif:LEFT (?c_phone, 2))
order by (bif:LEFT (?c_phone, 2))
Jena queries snowflake pattern
Prefix
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
Query 1
select
?l_returnflag
?l_linestatus
(sum(xsd:decimal(?l_linequantity)) as ?sum_qty)
(sum(xsd:decimal(?l_lineextendedprice)) as ?sum_base_price)
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?sum_disc_price)
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax))) as ?sum_charge)
(avg(xsd:decimal(?l_linequantity)) as ?avg_qty)
(avg(xsd:decimal(?l_lineextendedprice)) as ?avg_price)
(avg(xsd:decimal(?l_linediscount)) as ?avg_disc)
(count(1) as ?count_order)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_linestatus ?l_linestatus ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linetax ?l_linetax ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linediscount ?l_linediscount .
filter (xsd:date(?l_shipdate) <= ("1998-12-01"^^xsd:date + "-P%DELTA%D"^^xsd:duration))
}
group by
?l_returnflag
?l_linestatus
order by
?l_returnflag
?l_linestatus
Query 2
select
?s_acctbal
?s_name
?nation_name
?p_partkey
?p_mfgr
?s_address
?s_phone
?s_comment
where {
?ps a ltpch:partsupp;
ltpch:ps_has_supplier ?supp;
ltpch:ps_has_part ?part ;
ltpch:ps_supplycost ?minsc .
?supp a ltpch:supplier ;
ltpch:s_acctbal ?s_acctbal ;
ltpch:s_name ?s_name ;
ltpch:s_has_nation ?s_has_nation ;
ltpch:s_address ?s_address ;
ltpch:s_phone ?s_phone ;
ltpch:s_comment ?s_comment .
?s_has_nation ltpch:n_name ?nation_name ;
ltpch:n_has_region ?s_has_region .
?s_has_region ltpch:r_name "%REGION%" .
?part a ltpch:part ;
ltpch:p_partkey ?p_partkey ;
ltpch:p_mfgr ?p_mfgr ;
ltpch:p_size ?size ;
ltpch:p_type ?p_type .
FILTER (?size = str(%SIZE%) && fn:contains(?p_type, "%TYPE%"))
{ select ?part (min(?s_cost) as ?minsc)
where {
?ps a ltpch:partsupp;
ltpch:ps_has_part ?part;
ltpch:ps_has_supplier ?ms;
ltpch:ps_supplycost ?s_cost .
?ms ltpch:s_has_nation ?m_has_nation .
?m_has_nation ltpch:n_has_region ?m_has_region .
?m_has_region ltpch:r_name "%REGION%" .
}
group by ?part
}
}
order by
desc (?s_acctbal)
?nation_name
?s_name
?p_partkey
limit 100
Query 3
select
?o_orderkey
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
?o_orderdate
?o_shippriority
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_has_order ?ord ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:o_orderdate ?o_orderdate ;
ltpch:o_shippriority ?o_shippriority ;
ltpch:o_orderkey ?o_orderkey ;
ltpch:o_has_customer ?cust .
?cust ltpch:c_mktsegment ?c_mktsegment .
filter ((xsd:date(?o_orderdate) < "%DATE%"^^xsd:date) &&
(xsd:date(?l_shipdate) > "%DATE%"^^xsd:date) &&
(?c_mktsegment = "%SEGMENT%") )
}
group by
?o_orderkey
?o_orderdate
?o_shippriority
order by
desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
?o_orderdate
limit 10
Query 4
select
?o_orderpriority
(count(*) as ?order_count)
where
{
{
select distinct
?o_orderpriority
?ord
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate .
?ord ltpch:o_orderpriority ?o_orderpriority ;
ltpch:o_orderdate ?o_orderdate .
filter (
(xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
(xsd:date(?o_orderdate) >= "%MONTH%-01"^^xsd:date) &&
(xsd:date(?o_orderdate) < ("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration))
)
}
}
}
group by
?o_orderpriority
order by
?o_orderpriority
Query 5
select
?nation
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:o_has_customer ?cust ;
ltpch:o_orderdate ?o_orderdate .
?ps ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?s_nation .
?s_nation ltpch:n_has_region ?s_region ;
ltpch:n_name ?nation .
?s_region ltpch:r_name ?r_name .
?cust ltpch:c_has_nation ?c_nation.
filter ((?c_nation = ?s_nation) &&
(xsd:date(?o_orderdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?o_orderdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
(?r_name = "%REGION%") )
}
group by
?nation
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
Query 6
select
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipdate ?l_shipdate .
filter ( (xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
(xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration )) &&
(xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
(xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
(xsd:decimal(?l_linequantity) < %QUANTITY%) )
}
Query 7
select
?supp_nation
?cust_nation
?li_year
(sum (?volume) as ?revenue)
where {
{
select
?supp_nation
?cust_nation
?li_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:o_has_customer ?cust .
?cust ltpch:c_has_nation ?custn .
?custn ltpch:n_name ?cust_nation .
?ps ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?suppn .
?suppn ltpch:n_name ?supp_nation .
BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter (xsd:boolean(
(?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
(?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
xsd:boolean(xsd:date(?l_shipdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
xsd:boolean(xsd:date(?l_shipdate) <= xsd:date("1996-12-31"^^xsd:date)) )
}
}
}
group by
?supp_nation
?cust_nation
?li_year
order by
?supp_nation
?cust_nation
?li_year
Query 8
select
?o_year
((?sum1 / ?sum2) as ?mkt_share)
where {
{ select
?o_year
(sum (?volume * xsd:integer(fn:starts-with(?nation, "%NATION%"))) as ?sum1)
(sum (?volume) as ?sum2)
where {
{ select
((YEAR (xsd:date(?o_orderdate))) as ?o_year)
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
?nation
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:ps_has_supplier ?s_supplier .
?s_supplier ltpch:s_has_nation ?n2 .
?n2 ltpch:n_name ?nation .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_type ?type .
?ord ltpch:o_orderdate ?o_orderdate ;
ltpch:o_has_customer ?c_customer .
?c_customer ltpch:c_has_nation ?n_nation .
?n_nation ltpch:n_has_region ?r_region .
?r_region ltpch:r_name ?region.
filter ((xsd:date(?o_orderdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
(xsd:date(?o_orderdate) <= xsd:date("1996-12-31"^^xsd:date)) &&
(?region = "%REGION%") &&
(?type = "%TYPE%")
)
}
}
}
group by
?o_year
}
}
order by
?o_year
Query 9
select
?nation
?o_year
(sum(?amount) as ?sum_profit)
where {
{ select
?nation
?o_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:ps_has_part ?part ;
ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?s_nation .
?s_nation ltpch:n_name ?nation .
?ord ltpch:o_orderdate ?o_orderdate .
?ps ltpch:ps_supplycost ?ps_supplycost .
?part ltpch:p_name ?p_name .
filter (REGEX (?p_name, "%COLOR%"))
BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
}
}
}
group by
?nation
?o_year
order by
?nation
desc (?o_year)
Query 10
select
?c_custkey
?c_companyName
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_has_order ?ord ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:o_has_customer ?cust ;
ltpch:o_orderdate ?o_orderdate .
?cust ltpch:c_address ?c_address ;
ltpch:c_phone ?c_phone ;
ltpch:c_comment ?c_comment ;
ltpch:c_acctbal ?c_acctbal ;
ltpch:c_custkey ?c_custkey ;
ltpch:c_has_nation ?c_nation ;
ltpch:c_name ?c_companyName .
?c_nation ltpch:n_name ?nation .
filter ((xsd:date(?o_orderdate) >= xsd:date("%MONTH%-01"^^xsd:date)) &&
(xsd:date(?o_orderdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration)) &&
(?l_returnflag = "R")
)
}
group by
?c_custkey
?c_companyName
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20
Query 11
select
?bigpspart
?bigpsvalue
where {
{ select
?bigpspart
(sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue)
where
{
?bigps a ltpch:partsupp ;
ltpch:ps_has_part ?bigpspart ;
ltpch:ps_supplycost ?b_supplycost ;
ltpch:ps_availqty ?b_availqty ;
ltpch:ps_has_supplier ?b_supplier .
?b_supplier ltpch:s_has_nation ?b_nation .
?b_nation ltpch:n_name "%NATION%" .
}
group by ?bigpspart
}
{
select
(sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION% as ?threshold)
where
{
?thr_ps a ltpch:partsupp ;
ltpch:ps_supplycost ?t_supplycost ;
ltpch:ps_availqty ?t_availqty ;
ltpch:ps_has_supplier ?t_supplier .
?t_supplier ltpch:s_has_nation ?t_nation .
?t_nation ltpch:n_name "%NATION%" .
}
}
filter (?bigpsvalue > ?threshold )
}
order by
desc (?bigpsvalue)
Query 12
select
?l_shipmode
(sum (
xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
(sum (1 -
xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:o_orderpriority ?o_orderpriority .
filter (?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%") &&
(xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
(xsd:date(?l_shipdate) < xsd:date(?l_commitdate)) &&
(xsd:date(?l_receiptdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?l_receiptdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) )
}
group by
?l_shipmode
order by
?l_shipmode
Query 13
select
?c_count
(count(1) as ?custdist)
where {
{ select
?c_custkey
(count (?ord) as ?c_count)
where
{
?cust ltpch:c_custkey ?c_custkey .
optional {
?ord a ltpch:orders ;
ltpch:o_has_customer ?cust ;
ltpch:o_comment ?o_comment .
filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
}
}
group by
?c_custkey
}
}
group by
?c_count
order by
desc (count(1))
desc (?c_count)
Query 14
select
((100 * sum(xsd:integer(fn:starts-with(?p_type, "PROMO")) * xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount))) / sum(xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_type ?p_type .
filter (xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
(xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P1M"^^xsd:duration)) )
}
Query 15
select
?s_suppkey
?s_name
?s_address
?s_phone
?total_revenue
where {
?supplier a ltpch:supplier ;
ltpch:s_suppkey ?s_suppkey ;
ltpch:s_name ?s_name ;
ltpch:s_address ?s_address ;
ltpch:s_phone ?s_phone .
{ select
?supplier
(sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount))) as ?total_revenue)
where {
?li1 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_extendedprice ;
ltpch:l_linediscount ?l_discount ;
ltpch:l_has_partsupplier ?ps1 .
?ps1 ltpch:ps_has_supplier ?supplier .
filter (
xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
}
group by
?supplier
}
{ select (max (?l2_total_revenue) as ?maxtotal)
where {
{ select
?supplier2
(sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount))) as ?l2_total_revenue)
where {
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l2_shipdate ;
ltpch:l_lineextendedprice ?l2_extendedprice ;
ltpch:l_linediscount ?l2_discount ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:ps_has_supplier ?supplier2 .
filter (
xsd:date(?l2_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
xsd:date(?l2_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
}
group by
?supplier2
}
}
}
filter (?total_revenue = ?maxtotal)
}
order by
?supplier
Query 16
select
?p_brand
?p_type
?p_size
(count(distinct ?supp) as ?supplier_cnt)
where {
?ps a ltpch:partsupp ;
ltpch:ps_has_part ?part ;
ltpch:ps_has_supplier ?supp .
?part ltpch:p_brand ?p_brand ;
ltpch:p_type ?p_type ;
ltpch:p_size ?p_size .
filter (
(?p_brand != "%BRAND%") &&
!(fn:starts-with(?p_type,"%TYPE%")) &&
(xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
)
filter NOT EXISTS {
?supp a ltpch:supplier;
ltpch:s_comment ?badcomment .
filter ( fn:matches (?badcomment ,"Customer.*Complaints") )
}
}
group by
?p_brand
?p_type
?p_size
order by
desc ((count(distinct ?supp)))
?p_brand
?p_type
?p_size
Query 17
select
((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_brand ?p_brand ;
ltpch:p_container ?p_container .
{
select
?part
((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
where {
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:ps_has_part ?part .
}
group by
?part
}
filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%")
}
Query 18
select
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
(sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_order ?ord .
?ord ltpch:o_orderkey ?o_orderkey ;
ltpch:o_orderdate ?o_orderdate ;
ltpch:o_ordertotalprice ?o_ordertotalprice ;
ltpch:o_has_customer ?cust .
?cust ltpch:c_custkey ?c_custkey ;
ltpch:c_name ?c_name .
{ select
?ord
(sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
where {
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_order ?ord .
}
group by ?ord
} .
filter (xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
order by
desc (?o_ordertotalprice)
?o_orderdate
limit 100
Query 19
select
((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipinstruct ?l_shipinstruct ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:ps_has_part ?part .
?part ltpch:p_brand ?p_brand ;
ltpch:p_size ?p_size ;
ltpch:p_container ?p_container .
filter (?l_shipmode in ("AIR", "AIR REG") &&
?l_shipinstruct = "DELIVER IN PERSON" &&
( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
(?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
(xsd:integer(?l_linequantity) <= (%QUANTITY1% + 10)) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
(?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY2%) &&
(xsd:integer(?l_linequantity) <= (%QUANTITY2% + 10)) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
(?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
(xsd:integer(?l_linequantity) <= (%QUANTITY3% + 10)) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
}
Query 20
select
?s_name
?s_address
where
{
?supp ltpch:s_name ?s_name ;
ltpch:s_address ?s_address .
{
select distinct
?supp
where
{
?big_ps ltpch:ps_has_part ?part ;
ltpch:ps_availqty ?big_ps_availqty ;
ltpch:ps_has_supplier ?supp .
?supp ltpch:s_has_nation ?s_nation .
?s_nation ltpch:n_name ?n_name .
?part ltpch:p_name ?p_name .
filter (REGEX (?p_name , "^%COLOR%") &&
?n_name = "%NATION%" &&
xsd:decimal(?big_ps_availqty) > ?qty_threshold)
{
select
((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
?big_ps
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?big_ps .
filter ((xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
(xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration))
)
}
group by
?big_ps
}
}
}
}
order by ?s_name
Query 21
select
?s_name
(count(1) as ?numwait)
where {
?li1 qb:dataSet ltpch:lineitemCube;
ltpch:l_receiptdate ?l1_receiptdate ;
ltpch:l_commitdate ?l1_commitdate ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord .
?ps ltpch:ps_has_supplier ?supp .
?supp ltpch:s_name ?s_name ;
ltpch:s_has_nation ?s_nation .
?ord ltpch:o_orderstatus ?orderstatus .
?s_nation ltpch:n_name ?name
filter (
xsd:date(?l1_receiptdate) > xsd:date(?l1_commitdate) &&
?name = "%NATION%" &&
?orderstatus = "F"
)
filter exists {
?li2 ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:ps_has_supplier ?supp2 .
filter (?supp != ?supp2)
}
filter not exists {
?li3 ltpch:l_has_order ?ord ;
ltpch:l_receiptdate ?l3_receiptdate ;
ltpch:l_commitdate ?l3_commitdate ;
ltpch:l_has_partsupplier ?ps3 .
?ps3 ltpch:ps_has_supplier ?supp3 .
filter (
xsd:date(?l3_receiptdate) > xsd:date(?l3_commitdate) &&
?supp3 != ?supp
)
}
}
group by
?s_name
order by
desc (count(1))
?s_name
limit 100
Query 22
select
?cntrycode
(count (1) as ?numcust)
(sum (xsd:decimal(?c_acctbal)) as ?totacctbal)
where {
?cust a ltpch:customer ;
ltpch:c_acctbal ?c_acctbal ;
ltpch:c_phone ?c_phone .
BIND (fn:substring(?c_phone,0, 3) as ?cntrycode)
{
select (avg (xsd:decimal(?c_acctbal2)) as ?acctbal_threshold)
where
{
?cust2 a ltpch:customer ;
ltpch:c_acctbal ?c_acctbal2 ;
ltpch:c_phone ?c_phone2 .
filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
fn:substring(?c_phone2,0, 3) in (%COUNTRY_CODE_SET%) )
}
}
filter (
?cntrycode in (%COUNTRY_CODE_SET%) &&
(xsd:decimal(?c_acctbal) > ?acctbal_threshold ) )
filter not exists { ?ord ltpch:o_has_customer ?cust }
}
group by ?cntrycode
order by ?cntrycode
Jena queries star pattern
Prefix
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
Query 1
select
?l_returnflag
?l_linestatus
(sum(xsd:decimal(?l_linequantity)) as ?sum_qty)
(sum(xsd:decimal(?l_lineextendedprice)) as ?sum_base_price)
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?sum_disc_price)
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax))) as ?sum_charge)
(avg(xsd:decimal(?l_linequantity)) as ?avg_qty)
(avg(xsd:decimal(?l_lineextendedprice)) as ?avg_price)
(avg(xsd:decimal(?l_linediscount)) as ?avg_disc)
(count(1) as ?count_order)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_linestatus ?l_linestatus ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linetax ?l_linetax ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linediscount ?l_linediscount .
filter (xsd:date(?l_shipdate) <= ("1998-12-01"^^xsd:date + "-P%DELTA%D"^^xsd:duration))
}
group by
?l_returnflag
?l_linestatus
order by
?l_returnflag
?l_linestatus
Query 2
select
?s_acctbal
?s_name
?nation_name
?p_partkey
?p_mfgr
?s_address
?s_phone
?s_comment
where {
?ps ltpch:supplier_acctbal ?s_acctbal ;
ltpch:supplier_name ?s_name ;
ltpch:supplier_address ?s_address ;
ltpch:supplier_phone ?s_phone ;
ltpch:supplier_comment ?s_comment ;
ltpch:nation_name ?nation_name ;
ltpch:region_name "%REGION%" ;
ltpch:part_partkey ?p_partkey ;
ltpch:part_mfgr ?p_mfgr ;
ltpch:part_size ?size ;
ltpch:part_type ?p_type .
FILTER (?size = str(%SIZE%) && fn:contains(?p_type, "%TYPE%"))
{ select ?p_partkey (min(?s_cost) as ?minsc)
where {
?ps ltpch:part_partkey ?p_partkey;
ltpch:partsupplier_supplycost ?s_cost ;
ltpch:region_name ?region2 .
filter (?region2 = "%REGION%")
}
group by ?p_partkey
}
}
order by
desc (?s_acctbal)
?nation_name
?s_name
?p_partkey
limit 100
Query 3
select
?o_orderkey
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
?o_orderdate
?o_shippriority
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_has_order ?ord ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:order_shippriority ?o_shippriority ;
ltpch:order_orderkey ?o_orderkey ;
ltpch:customer_mktsegment ?c_mktsegment .
filter ((xsd:date(?o_orderdate) < "%DATE%"^^xsd:date) &&
(xsd:date(?l_shipdate) > "%DATE%"^^xsd:date) &&
(?c_mktsegment = "%SEGMENT%") )
}
group by
?o_orderkey
?o_orderdate
?o_shippriority
order by
desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
?o_orderdate
limit 10
Query 4
select
?o_orderpriority
(count(*) as ?order_count)
where
{
{
select distinct
?o_orderpriority
?ordkey
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate .
?ord ltpch:order_orderpriority ?o_orderpriority ;
ltpch:order_orderkey ?ordkey ;
ltpch:order_orderdate ?o_orderdate .
filter (
(xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
(xsd:date(?o_orderdate) >= "%MONTH%-01"^^xsd:date) &&
(xsd:date(?o_orderdate) < ("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration))
)
}
}
}
group by
?o_orderpriority
order by
?o_orderpriority
Query 5
select
?nation
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:nation_name ?c_nation .
?ps ltpch:nation_name ?nation ;
ltpch:region_name ?r_name .
filter ((?c_nation = ?nation) &&
(xsd:date(?o_orderdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?o_orderdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
(?r_name = "%REGION%") )
}
group by
?nation
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
Query 6
select
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipdate ?l_shipdate .
filter ( (xsd:date(?l_shipdate) >= ("%YEAR%-01-01"^^xsd:date)) &&
(xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
(xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
(xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
(xsd:decimal(?l_linequantity) < %QUANTITY%) )
}
Query 7
select
?supp_nation
?cust_nation
?li_year
(sum (xsd:decimal(?volume)) as ?revenue)
where {
{
select
?supp_nation
?cust_nation
?li_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:nation_name ?cust_nation .
?ps ltpch:nation_name ?supp_nation .
BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter (xsd:boolean(
(?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
(?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
xsd:boolean(xsd:date(?l_shipdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
xsd:boolean(xsd:date(?l_shipdate) <= xsd:date("1996-12-31"^^xsd:date)) )
}
}
}
group by
?supp_nation
?cust_nation
?li_year
order by
?supp_nation
?cust_nation
?li_year
Query 8
select
?o_year
((?sum1 / ?sum2) as ?mkt_share)
where {
{ select
?o_year
(sum (?volume * xsd:integer(fn:starts-with(?nation, "%NATION%"))) as ?sum1)
(sum (?volume) as ?sum2)
where {
{ select
((YEAR(xsd:date(?o_orderdate))) as ?o_year)
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
?nation
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:nation_name ?nation ;
ltpch:part_type ?type .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:region_name ?region .
filter ((xsd:date(?o_orderdate) >= "1995-01-01"^^xsd:date) &&
(xsd:date(?o_orderdate) <= "1996-12-31"^^xsd:date) &&
(?region = "%REGION%") &&
(?type = "%TYPE%")
)
}
}
}
group by
?o_year
}
}
order by
?o_year
Query 9
select
?nation
?o_year
(sum(?amount) as ?sum_profit)
where {
{ select
?nation
?o_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ps ltpch:nation_name ?nation ;
ltpch:partsupplier_supplycost ?ps_supplycost ;
ltpch:part_name ?p_name .
?ord ltpch:order_orderdate ?o_orderdate .
filter (REGEX (?p_name, "%COLOR%"))
BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
}
}
}
group by
?nation
?o_year
order by
?nation
desc (?o_year)
Query 10
select
?c_custkey
?c_companyName
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_has_order ?ord ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount .
?ord ltpch:order_orderdate ?o_orderdate ;
ltpch:customer_address ?c_address ;
ltpch:customer_phone ?c_phone ;
ltpch:customer_comment ?c_comment ;
ltpch:customer_acctbal ?c_acctbal ;
ltpch:customer_custkey ?c_custkey ;
ltpch:customer_name ?c_companyName ;
ltpch:nation_name ?nation .
filter ((xsd:date(?o_orderdate) >= xsd:date("%MONTH%-01"^^xsd:date)) &&
(xsd:date(?o_orderdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration)) &&
(?l_returnflag = "R")
) }
group by
?c_custkey
?c_companyName
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20
Query 11
select
?bigpspart
?bigpsvalue
where {
{ select
?bigpspart
(sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue)
where
{
?bigps ltpch:part_partkey ?bigpspart ;
ltpch:partsupplier_supplycost ?b_supplycost ;
ltpch:partsupplier_availqty ?b_availqty ;
ltpch:nation_name "%NATION%" .
}
group by ?bigpspart
}
{
select
(sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION% as ?threshold)
where
{
?thr_ps ltpch:partsupplier_supplycost ?t_supplycost ;
ltpch:partsupplier_availqty ?t_availqty ;
ltpch:nation_name "%NATION%" .
}
}
filter (?bigpsvalue > ?threshold )
}
order by
desc (?bigpsvalue)
Query 12
select
?l_shipmode
(sum (
xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
(sum (1 -
xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_order ?ord ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipdate ?l_shipdate .
?ord ltpch:order_orderpriority ?o_orderpriority .
filter (?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%") &&
(xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
(xsd:date(?l_shipdate) < xsd:date(?l_commitdate)) &&
(xsd:date(?l_receiptdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?l_receiptdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) )
}
group by
?l_shipmode
order by
?l_shipmode
Query 13
select
?c_count
(count(1) as ?custdist)
where {
{ select
?c_custkey
(count (?o_comment) as ?c_count)
where
{
?ord ltpch:customer_custkey ?c_custkey .
optional {
?ord ltpch:order_comment ?o_comment .
filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
}
}
group by
?c_custkey
}
}
group by
?c_count
order by
desc (count(1))
desc (?c_count)
Query 14
select
((100 * sum(xsd:integer(fn:starts-with(?p_type, "PROMO")) * xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount))) / sum(xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_has_partsupplier ?part .
?part ltpch:part_type ?p_type .
filter (xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
(xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P1M"^^xsd:duration)) )
}
Query 15
select distinct
?s_suppkey
?s_name
?s_address
?s_phone
?total_revenue
where
{
?partsupp ltpch:supplier_suppkey ?s_suppkey ;
ltpch:supplier_name ?s_name ;
ltpch:supplier_address ?s_address ;
ltpch:supplier_phone ?s_phone .
{
select
?s_suppkey
((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_extendedprice ;
ltpch:l_linediscount ?l_discount ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:supplier_suppkey ?s_suppkey .
filter (
xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
}
group by
?s_suppkey
} .
{
select
(max (?l2_total_revenue) as ?maxtotal)
where
{
{
select
((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
where
{
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l2_shipdate ;
ltpch:l_lineextendedprice ?l2_extendedprice ;
ltpch:l_linediscount ?l2_discount ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:supplier_suppkey ?s_suppkey2 .
filter (
xsd:date(?l2_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
xsd:date(?l2_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
}
group by
?s_suppkey2
}
}
}
filter (?total_revenue = ?maxtotal)
}
order by
?s_suppkey
Query 16
select
?p_brand
?p_type
?p_size
(count(distinct ?supp) as ?supplier_cnt)
where {
?ps ltpch:part_brand ?p_brand ;
ltpch:part_type ?p_type ;
ltpch:part_size ?p_size ;
ltpch:supplier_suppkey ?supp .
filter (
(?p_brand != "%BRAND%") &&
!(fn:starts-with(?p_type,"%TYPE%")) &&
(xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
)
filter NOT EXISTS {
?supp a ltpch:supplier;
ltpch:s_comment ?badcomment .
filter ( fn:matches (?badcomment ,"Customer.*Complaints") )
}
}
group by
?p_brand
?p_type
?p_size
order by
desc ((count(distinct ?supp)))
?p_brand
?p_type
?p_size
Query 17
select
((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?ps .
?ps ltpch:part_partkey ?p_partkey.
{
select
?p_partkey
((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
where {
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:part_partkey ?p_partkey ;
ltpch:part_container ?p_container ;
ltpch:part_brand ?p_brand .
}
group by
?p_partkey
}
filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%")
}
Query 18
select
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
(sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_order ?ord .
?ord ltpch:order_orderkey ?o_orderkey ;
ltpch:order_orderdate ?o_orderdate ;
ltpch:order_ordertotalprice ?o_ordertotalprice ;
ltpch:customer_custkey ?c_custkey ;
ltpch:customer_name ?c_name .
{
select
?ord
(sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
where
{
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:l_has_order ?ord .
}
group by
?ord
} .
filter (xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
order by
desc (?o_ordertotalprice)
?o_orderdate
limit 100
Query 19
select
((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipinstruct ?l_shipinstruct .
?ps ltpch:part_brand ?p_brand ;
ltpch:part_size ?p_size ;
ltpch:part_container ?p_container .
filter (?l_shipmode in ("AIR", "AIR REG") &&
?l_shipinstruct = "DELIVER IN PERSON" &&
( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
(?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
(?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY2%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
(?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
}
Query 20
select distinct
?s_name
?s_address
where
{
?supp ltpch:supplier_name ?s_name ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:supplier_address ?s_address .
{
select
distinct ?suppkey
where
{
?big_ps ltpch:partsupplier_availqty ?big_ps_availqty ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:nation_name ?n_name ;
ltpch:part_name ?p_name .
filter (REGEX (?p_name , "^%COLOR%") &&
?n_name = "%NATION%" &&
xsd:decimal(?big_ps_availqty) > ?qty_threshold)
{
select
?big_ps
((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?big_ps .
filter ((xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
(xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration))
)
}
group by
?big_ps
} .
}
}
}
order by ?s_name
Query 21
select
?s_name
((count(1)) as ?numwait)
where {
?li1 qb:dataSet ltpch:lineitemCube ;
ltpch:l_receiptdate ?l1_receiptdate ;
ltpch:l_commitdate ?l1_commitdate ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_has_order ?ord .
?ps ltpch:supplier_name ?s_name ;
ltpch:supplier_suppkey ?suppkey ;
ltpch:nation_name ?n_name .
?ord ltpch:order_orderstatus ?o_orderstatus .
filter (
xsd:date(?l1_receiptdate) > xsd:date(?l1_commitdate) &&
?n_name = "%NATION%" &&
?o_orderstatus = "F"
)
filter exists {
?li2 ltpch:l_has_order ?ord ;
ltpch:l_has_partsupplier ?ps2 .
?ps2 ltpch:supplier_suppkey ?suppkey2 .
filter (?suppkey != ?suppkey2)
}
filter not exists {
?li3 ltpch:l_has_order ?ord ;
ltpch:l_receiptdate ?l3_receiptdate ;
ltpch:l_commitdate ?l3_commitdate ;
ltpch:l_has_partsupplier ?ps3 .
?ps3 ltpch:supplier_suppkey ?suppkey3 .
filter (
xsd:date(?l3_receiptdate) > xsd:date(?l3_commitdate) &&
?suppkey3 != ?suppkey
)
}
}
group by
?s_name
order by
desc (count(1))
?s_name
limit 100
Query 22
select
?cntrycode
(count (1) as ?numcust)
(sum (xsd:decimal(?c_acctbal)) as ?totacctbal)
where {
?cust ltpch:customer_acctbal ?c_acctbal ;
ltpch:customer_phone ?c_phone .
BIND (fn:substring(?c_phone,0, 3) as ?cntrycode)
{
select (avg (xsd:decimal(?c_acctbal2)) as ?acctbal_threshold)
where
{
?cust2 ltpch:customer_acctbal ?c_acctbal2 ;
ltpch:customer_phone ?c_phone2 .
filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
fn:substring(?c_phone2,0, 3) in (%COUNTRY_CODE_SET%) )
}
}
filter (
?cntrycode in (%COUNTRY_CODE_SET%) &&
(xsd:decimal(?c_acctbal) > ?acctbal_threshold ) )
filter not exists { ?cust ltpch:order_orderkey ?orderkey }
}
group by ?cntrycode
order by ?cntrycode
Jena queries denormalized pattern
Prefix
prefix qb4o: <http://publishing-multidimensional-data.googlecode.com/git/index.html#ref_qbplus_>
prefix qb: <http://purl.org/linked-data/cube#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix fn: <http://www.w3.org/2005/xpath-functions#>
Query 1
select
?l_returnflag
?l_linestatus
(sum(xsd:decimal(?l_linequantity)) as ?sum_qty)
(sum(xsd:decimal(?l_lineextendedprice)) as ?sum_base_price)
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?sum_disc_price)
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax))) as ?sum_charge)
(avg(xsd:decimal(?l_linequantity)) as ?avg_qty)
(avg(xsd:decimal(?l_lineextendedprice)) as ?avg_price)
(avg(xsd:decimal(?l_linediscount)) as ?avg_disc)
(count(1) as ?count_order)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_linestatus ?l_linestatus ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linetax ?l_linetax ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linediscount ?l_linediscount .
filter (xsd:date(?l_shipdate) <= ("1998-12-01"^^xsd:date + "-P%DELTA%D"^^xsd:duration))
}
group by
?l_returnflag
?l_linestatus
order by
?l_returnflag
?l_linestatus
Query 2
select distinct
?s_acctbal
?s_name
?nation_name
?p_partkey
?p_mfgr
?s_address
?s_phone
?s_comment
where {
?li ltpch:partsupplier_partsupplier_supplycost ?minsc ;
ltpch:partsupplier_supplier_acctbal ?s_acctbal ;
ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_address ?s_address ;
ltpch:partsupplier_supplier_phone ?s_phone ;
ltpch:partsupplier_supplier_comment ?s_comment ;
ltpch:partsupplier_nation_name ?nation_name ;
ltpch:partsupplier_region_name "%REGION%" ;
ltpch:partsupplier_part_partkey ?p_partkey ;
ltpch:partsupplier_part_mfgr ?p_mfgr ;
ltpch:partsupplier_part_size ?size ;
ltpch:partsupplier_part_type ?p_type .
FILTER (?size = str(%SIZE%) && fn:contains(?p_type, "%TYPE%"))
{ select ?p_partkey (min(?s_cost) as ?minsc)
where {
?li ltpch:partsupplier_part_partkey ?p_partkey;
ltpch:partsupplier_partsupplier_supplycost ?s_cost ;
ltpch:partsupplier_region_name ?region2 .
filter (?region2 = "%REGION%")
}
group by ?p_partkey
}
}
order by
desc (?s_acctbal)
?nation_name
?s_name
?p_partk
Query 3
select
?o_orderkey
(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
?o_orderdate
?o_shippriority
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_order_shippriority ?o_shippriority ;
ltpch:order_order_orderkey ?o_orderkey ;
ltpch:order_customer_mktsegment ?c_mktsegment .
filter ((xsd:date(?o_orderdate) < "%DATE%"^^xsd:date) &&
(xsd:date(?l_shipdate) > "%DATE%"^^xsd:date) &&
(?c_mktsegment = "%SEGMENT%") )
}
group by
?o_orderkey
?o_orderdate
?o_shippriority
order by
desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
?o_orderdate
limit 10
Query 4
select
?o_orderpriority
(count(*) as ?order_count)
where
{
{
select distinct
?o_orderpriority
?ordkey
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:order_order_orderpriority ?o_orderpriority ;
ltpch:order_order_orderkey ?ordkey ;
ltpch:order_order_orderdate ?o_orderdate .
filter (
(xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
(xsd:date(?o_orderdate) >= "%MONTH%-01"^^xsd:date) &&
(xsd:date(?o_orderdate) < ("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration))
)
}
}
}
group by
?o_orderpriority
order by
?o_orderpriority
Query 5
select
?nation
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_nation_name ?c_nation ;
ltpch:partsupplier_nation_name ?nation ;
ltpch:partsupplier_region_name ?r_name .
filter ((?c_nation = ?nation) &&
(xsd:date(?o_orderdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?o_orderdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
(?r_name = "%REGION%") )
}
group by
?nation
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
Query 6
select
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipdate ?l_shipdate .
filter ( (xsd:date(?l_shipdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
(xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
(xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
(xsd:decimal(?l_linequantity) < %QUANTITY%) )
}
Query 7
select
?supp_nation
?cust_nation
?li_year
(sum (xsd:decimal(?volume)) as ?revenue)
where {
{
select
?supp_nation
?cust_nation
?li_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:order_nation_name ?cust_nation ;
ltpch:partsupplier_nation_name ?supp_nation .
BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter (xsd:boolean(
(?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
(?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
xsd:boolean(xsd:date(?l_shipdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
xsd:boolean(xsd:date(?l_shipdate) <= xsd:date("1996-12-31"^^xsd:date)) )
}
}
}
group by
?supp_nation
?cust_nation
?li_year
order by
?supp_nation
?cust_nation
?li_year
Query 8
select
?o_year
((?sum1 / ?sum2) as ?mkt_share)
where {
{ select
?o_year
(sum (?volume * xsd:integer(fn:starts-with(?nation, "%NATION%"))) as ?sum1)
(sum (?volume) as ?sum2)
where {
{ select
((YEAR(xsd:date(?o_orderdate))) as ?o_year)
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
?nation
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_has_partsupplier ?ps ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:partsupplier_nation_name ?nation ;
ltpch:partsupplier_part_type ?type ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_region_name ?region .
filter ((xsd:date(?o_orderdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
(xsd:date(?o_orderdate) <= xsd:date("1996-12-31"^^xsd:date)) &&
(?region = "%REGION%") &&
(?type = "%TYPE%")
)
}
}
}
group by
?o_year
}
}
order by
?o_year
Query 9
select
?nation
?o_year
(sum(?amount) as ?sum_profit)
where {
{ select
?nation
?o_year
((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:partsupplier_nation_name ?nation ;
ltpch:partsupplier_partsupplier_supplycost ?ps_supplycost ;
ltpch:partsupplier_part_name ?p_name ;
ltpch:order_order_orderdate ?o_orderdate .
filter (REGEX (?p_name, "%COLOR%"))
BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
}
}
}
group by
?nation
?o_year
order by
?nation
desc (?o_year)
Query 10
select
?c_custkey
?c_companyName
(sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_returnflag ?l_returnflag ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_customer_address ?c_address ;
ltpch:order_customer_phone ?c_phone ;
ltpch:order_customer_comment ?c_comment ;
ltpch:order_customer_acctbal ?c_acctbal ;
ltpch:order_customer_custkey ?c_custkey ;
ltpch:order_customer_name ?c_companyName ;
ltpch:order_nation_name ?nation .
filter ((xsd:date(?o_orderdate) >= xsd:date("%MONTH%-01"^^xsd:date)) &&
(xsd:date(?o_orderdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration)) &&
(?l_returnflag = "R")
)
}
group by
?c_custkey
?c_companyName
?c_acctbal
?nation
?c_address
?c_phone
?c_comment
order by
desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20
Query 11
select
?bigpspart
?bigpsvalue
where {
{ select
?bigpspart
(sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue)
where
{
?bigps ltpch:partsupplier_part_partkey ?bigpspart ;
ltpch:partsupplier_partsupplier_supplycost ?b_supplycost ;
ltpch:partsupplier_partsupplier_availqty ?b_availqty ;
ltpch:partsupplier_nation_name "%NATION%" .
}
group by ?bigpspart
}
{
select
(sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION% as ?threshold)
where
{
?thr_ps ltpch:partsupplier_partsupplier_supplycost ?t_supplycost ;
ltpch:partsupplier_partsupplier_availqty ?t_availqty ;
ltpch:partsupplier_nation_name "%NATION%" .
}
}
filter (?bigpsvalue > ?threshold )
}
order by
desc (?bigpsvalue)
Query 12
select
?l_shipmode
(sum (
xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
(sum (1 -
xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_commitdate ?l_commitdate ;
ltpch:l_receiptdate ?l_receiptdate ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:order_order_orderpriority ?o_orderpriority .
filter (?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%") &&
(xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
(xsd:date(?l_shipdate) < xsd:date(?l_commitdate)) &&
(xsd:date(?l_receiptdate) >= "%YEAR%-01-01"^^xsd:date) &&
(xsd:date(?l_receiptdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) )
}
group by
?l_shipmode
order by
?l_shipmode
Query 13
select
?c_count
(count(1) as ?custdist)
where {
{ select
?c_custkey
(count (?orderkey) as ?c_count)
where
{
{
select distinct ?c_custkey
where
{
?li ltpch:order_customer_custkey ?c_custkey .
}
}
optional {
{
select ?orderkey
where {
?li2 ltpch:order_customer_custkey ?c_custkey ;
ltpch:order_order_orderkey ?orderkey ;
ltpch:order_order_comment ?o_comment .
filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
}
group by ?orderkey
}
}
}
group by
?c_custkey
}
}
group by
?c_count
order by
desc (count(1))
desc (?c_count)
Query 14
select
((100 * sum(xsd:integer(fn:starts-with(?p_type, "PROMO")) * xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount))) / sum(xsd:decimal(?l_lineextendedprice) * (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:partsupplier_part_type ?p_type .
filter (xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
(xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P1M"^^xsd:duration)) )
}
Query 15
select distinct
?s_suppkey
?s_name
?s_address
?s_phone
?total_revenue
where
{
?partsupp ltpch:partsupplier_supplier_suppkey ?s_suppkey ;
ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_address ?s_address ;
ltpch:partsupplier_supplier_phone ?s_phone .
{
select
?s_suppkey
((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_lineextendedprice ?l_extendedprice ;
ltpch:l_linediscount ?l_discount ;
ltpch:l_has_partsupplier ?ps ;
ltpch:partsupplier_supplier_suppkey ?s_suppkey .
filter (
xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
}
group by
?s_suppkey
} .
{
select
(max (?l2_total_revenue) as ?maxtotal)
where
{
{
select
((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
where
{
?li2 qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l2_shipdate ;
ltpch:l_lineextendedprice ?l2_extendedprice ;
ltpch:l_linediscount ?l2_discount ;
ltpch:l_has_partsupplier ?ps2 ;
ltpch:partsupplier_supplier_suppkey ?s_suppkey2 .
filter (
xsd:date(?l2_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
xsd:date(?l2_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
}
group by
?s_suppkey2
}
}
}
filter (?total_revenue = ?maxtotal)
}
order by
?s_suppkey
Query 16
select
?p_brand
?p_type
?p_size
(count(distinct ?supp) as ?supplier_cnt)
where {
?ps ltpch:partsupplier_part_brand ?p_brand ;
ltpch:partsupplier_part_type ?p_type ;
ltpch:partsupplier_part_size ?p_size ;
ltpch:partsupplier_supplier_suppkey ?supp .
filter (
(?p_brand != "%BRAND%") &&
!(fn:starts-with(?p_type,"%TYPE%")) &&
(xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
)
filter NOT EXISTS {
?supp a ltpch:supplier;
ltpch:s_comment ?badcomment .
filter ( fn:matches (?badcomment ,"Customer.*Complaints") )
}
}
group by
?p_brand
?p_type
?p_size
order by
desc ((count(distinct ?supp)))
?p_brand
?p_type
?p_size
Query 17
select
((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_has_partsupplier ?ps ;
ltpch:partsupplier_part_partkey ?p_partkey.
{
select
?p_partkey
((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
where {
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:partsupplier_part_partkey ?p_partkey ;
ltpch:partsupplier_part_container ?p_container ;
ltpch:partsupplier_part_brand ?p_brand .
}
group by
?p_partkey
}
filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%")
}
Query 18
select
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
(sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:order_order_orderkey ?o_orderkey ;
ltpch:order_order_orderdate ?o_orderdate ;
ltpch:order_order_ordertotalprice ?o_ordertotalprice ;
ltpch:order_customer_custkey ?c_custkey ;
ltpch:order_customer_name ?c_name .
{
select
?o_orderkey
(sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
where
{
?li2 a ltpch:lineitem ;
ltpch:l_linequantity ?l2_linequantity ;
ltpch:order_order_orderkey ?o_orderkey .
}
group by
?o_orderkey
} .
filter (xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
?c_name
?c_custkey
?o_orderkey
?o_orderdate
?o_ordertotalprice
order by
desc (?o_ordertotalprice)
?o_orderdate
limit 100
Query 19
select
((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_lineextendedprice ?l_lineextendedprice ;
ltpch:l_linediscount ?l_linediscount ;
ltpch:l_linequantity ?l_linequantity ;
ltpch:l_shipmode ?l_shipmode ;
ltpch:l_shipinstruct ?l_shipinstruct ;
ltpch:partsupplier_part_brand ?p_brand ;
ltpch:partsupplier_part_size ?p_size ;
ltpch:partsupplier_part_container ?p_container .
filter (?l_shipmode in ("AIR", "AIR REG") &&
?l_shipinstruct = "DELIVER IN PERSON" &&
( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
(?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
(?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY2%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
(?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
(xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
(xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
}
Query 20
select distinct
?s_name
?s_address
where
{
?supp ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_supplier_address ?s_address .
{
select
distinct ?suppkey
where
{
?li ltpch:partsupplier_partsupplier_availqty ?big_ps_availqty ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_nation_name ?n_name ;
ltpch:partsupplier_part_name ?p_name .
filter (REGEX (?p_name , "^%COLOR%") &&
?n_name = "%NATION%" &&
xsd:decimal(?big_ps_availqty) > ?qty_threshold)
{
select
?li
((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
where
{
?li qb:dataSet ltpch:lineitemCube ;
ltpch:l_shipdate ?l_shipdate ;
ltpch:l_linequantity ?l_linequantity .
filter ((xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
(xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration))
)
}
group by
?li
} .
}
}
}
order by ?s_name
Query 21
select
?s_name
((count(1)) as ?numwait)
where {
?li1 qb:dataSet ltpch:lineitemCube ;
ltpch:l_receiptdate ?l1_receiptdate ;
ltpch:l_commitdate ?l1_commitdate ;
ltpch:partsupplier_supplier_name ?s_name ;
ltpch:partsupplier_supplier_suppkey ?suppkey ;
ltpch:partsupplier_nation_name ?n_name ;
ltpch:order_order_orderkey ?orderkey ;
ltpch:order_order_orderstatus ?o_orderstatus .
filter (
xsd:date(?l1_receiptdate) > xsd:date(?l1_commitdate) &&
?n_name = "%NATION%" &&
?o_orderstatus = "F"
)
filter exists {
?li2 ltpch:order_order_orderkey ?orderkey ;
ltpch:partsupplier_supplier_suppkey ?suppkey2 .
filter (?suppkey != ?suppkey2)
}
filter not exists {
?li3 ltpch:order_order_orderkey ?orderkey ;
ltpch:l_receiptdate ?l3_receiptdate ;
ltpch:l_commitdate ?l3_commitdate ;
ltpch:partsupplier_supplier_suppkey ?suppkey3 .
filter (
xsd:date(?l3_receiptdate) > xsd:date(?l3_commitdate) &&
?suppkey3 != ?suppkey
)
}
}
group by
?s_name
order by
desc (count(1))
?s_name
limit 100
Query 22
select
?cntrycode
(count (1) as ?numcust)
(sum (xsd:decimal(?c_acctbal)) as ?totacctbal)
where {
?cust ltpch:order_customer_acctbal ?c_acctbal ;
ltpch:order_customer_phone ?c_phone .
BIND (fn:substring(?c_phone,0, 3) as ?cntrycode)
{ select (avg (xsd:decimal(?c_acctbal2)) as ?acctbal_threshold)
where
{
?li ltpch:order_customer_acctbal ?c_acctbal2 ;
ltpch:order_customer_phone ?c_phone2 .
filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
fn:substring(?c_phone2,0, 3) in (%COUNTRY_CODE_SET%) )
}
}
filter (
?cntrycode in (%COUNTRY_CODE_SET%) &&
(xsd:decimal(?c_acctbal) > ?acctbal_threshold ) )
filter not exists { ?cust ltpch:order_order_orderkey ?orderkey }
}
group by ?cntrycode
order by ?cntrycode
© Copyright 2015- by QWeb. All Rights Reserved.