Feature:SubSelects

Feature: SubSelects

Feature description

It is sometimes necessary to nest a query within another query.

Examples

I need to find all the people that <http://foo.example/alice> knows as well as a single name for each.

SELECT ?person (SELECT ?name WHERE { ?person foaf:name ?name } LIMIT 1)
WHERE {
   <http://foo.example/alice> :knows ?person .
}

(this one can be written using SAMPLE aggregate:

SELECT ?person (SAMPLE (?name))
WHERE {
   <http://foo.example/alice> :knows ?person .
   OPTIONAL { ?person foaf:name ?name }
}

)

Q2 of TPC-D

" 2.5 Minimum Cost Supplier Query (Q2)

This query finds which supplier should be selected to place an order for a given part in a given region.

2.5.1 Business Question

The Minimum Cost Supplier Query finds, in a given region, for each part of a certain type and size, the supplier who can supply it at minimum cost. If several suppliers in that region offer the desired part type and size at the same (minimum) cost, the query lists the parts from suppliers with the 100 highest account balances. For each supplier, the query lists the supplier's account balance, name and nation; the part's number and manufacturer; the supplier's address, phone number and comment information. "

( from Transaction Processing Performance Council. TPC-D 2.1 available at http://www.tpc.org/tpcd/spec/tpcd_current.pdf )

Relatively readable version:

prefix tpcd: <http://www.openlinksw.com/schemas/tpcd#>
select
  ?supp+>tpcd:acctbal,
  ?supp+>tpcd:name,
  ?supp+>tpcd:has_nation+>tpcd:name as ?nation_name,
  ?part+>tpcd:partkey,
  ?part+>tpcd:mfgr,
  ?supp+>tpcd:address,
  ?supp+>tpcd:phone,
  ?supp+>tpcd:comment
from <http://example.com/tpcd>
where {
  ?ps a tpcd:partsupp; tpcd:has_supplier ?supp; tpcd:has_part ?part .
  ?supp+>tpcd:has_nation+>tpcd:has_region tpcd:name 'EUROPE' .
  ?part tpcd:size 15 .
  ?ps tpcd:supplycost ?minsc .
  { select ?part min(?ps+>tpcd:supplycost) as ?minsc
    where {
        ?ps a tpcd:partsupp; tpcd:has_part ?part; tpcd:has_supplier ?ms .
        ?ms+>tpcd:has_nation+>tpcd:has_region tpcd:name 'EUROPE' .
      } }
    filter (?part+>tpcd:type like '%BRASS') }
order by
  desc (?supp+>tpcd:acctbal)
  ?supp+>tpcd:has_nation+>tpcd:name
  ?supp+>tpcd:name
  ?part+>tpcd:partkey
limit 100

More spec-compliant version:

prefix tpcd: <http://www.openlinksw.com/schemas/tpcd#>
select
  ?supp-acctbal ?supp-name ?supp-nation-name
  ?part-partkey ?part-mfgr ?supp-address
  ?supp-phone ?supp-comment
from <http://example.com/tpcd>
where {
  ?ps a tpcd:partsupp ; tpcd:has_supplier ?supp ;
     tpcd:has_part ?part ; tpcd:supplycost ?minsc  .
  ?supp tpcd:acctbal ?supp-acctbal ; tpcd:name ?supp-name ;
    tpcd:has_nation ?supp-nation ; tpcd:address ?supp-address ;
    tpcd:phone ?supp-phone ; tpcd:comment ?supp-comment .
  ?supp-nation tpcd:name ?supp-nation-name ;
     tpcd:has_region ?supp-nation-region .
  ?supp-nation-region tpcd:name 'EUROPE' .
  ?part tpcd:size 15 ; tpcd:partkey ?part-partkey ;
     tpcd:mfgr ?part-mfgr ; tpcd:type ?part-type .
  { select ?part min(?ps-supplycost) as ?minsc
    where {
        ?ps a tpcd:partsupp; tpcd:has_part ?part;
          tpcd:has_supplier ?ms ; tpcd:supplycost ?ps-supplycost.
        ?ms tpcd:has_nation ?ms-nation .
        ?ms-nation tpcd:has_region ?ms-nation-region .
        ?ms-nation-region tpcd:name 'EUROPE' .
      } }
    filter (?part-type like '%BRASS') }
order by
  desc (?supp-acctbal) ?supp-nation-name ?supp-name ?part-partkey
limit 100

Q15 of TPC-D

" 2.18 Top Supplier Query (Q15)

This query determines the top supplier so it can be rewarded, given more business, or identified for special recognition.

2.18.1 Business Question

The Top Supplier Query finds the supplier who contributed the most to the overall revenue for parts shipped during a given quarter of a given year. In case of a tie, the query lists all suppliers whose contribution was equal to the maximum, presented in supplier number order. "

( from Transaction Processing Performance Council. TPC-D 2.1 available at http://www.tpc.org/tpcd/spec/tpcd_current.pdf )

prefix tpcd: <http://www.openlinksw.com/schemas/tpcd#>
prefix oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
prefix sioc: <http://rdfs.org/sioc/ns#>
prefix foaf: <http://xmlns.com/foaf/0.1/>
select
  ?supplier-suppkey ?supplier-name ?supplier-address ?supplier-phone ?total_revenue
from <http://example.com/tpcd>
where
  {
    ?supplier a tpcd:supplier ; tpcd:suppkey ?supplier-suppkey ; tpcd:name ?supplier-name ;
      tpcd:address ?supplier-address ; tpcd:phone ?supplier-phone .
      {
        select
          ?supplier
          (sum(?l_extendedprice * (1 - ?l_discount))) as ?total_revenue
        where
          {
            [ a tpcd:lineitem ; tpcd:shipdate ?l_shipdate ;
              tpcd:lineextendedprice ?l_extendedprice ; tpcd:linediscount ?l_discount ;
              tpcd:has_supplier ?supplier ] .
            filter (
                ?l_shipdate >= "1996-01-01"^^xsd:date and
                ?l_shipdate < bif:dateadd ("month", 3, "1996-01-01"^^xsd:date) )
          }
      }
      {
        select max (?l2_total_revenue) as ?maxtotal
        where
          {
              {
                select
                  ?supplier2
                  (sum(?l2_extendedprice * (1 - ?l2_discount))) as ?l2_total_revenue
                where
                  {
                    [ a tpcd:lineitem ; tpcd:shipdate ?l2_shipdate ;
                      tpcd:lineextendedprice ?l2_extendedprice ; tpcd:linediscount ?l2_discount ;
                      tpcd:has_supplier ?supplier2 ] .
                    filter (
                        ?l2_shipdate >= "1996-01-01"^^xsd:date and
                        ?l2_shipdate < bif:dateadd ("month", 3, "1996-01-01"^^xsd:date) )
                  }
              }
          }
      }
    filter (?total_revenue = ?maxtotal)
  }
order by
  ?supplier

Existing Implementation(s)

Virtuoso supports both scalar subqueries (in all places where a variable name may occur) and subqueries as derived tables.

Existing Specification / Documentation

ARQ's sub-query

Related Use Cases/Extensions

• Feature:ProjectExpressions
• Feature:AggregateFunctions
• Feature:Negation

Champions

• OpenLink
• Garlik

Use Cases

TPC-H :)

References

• teleconference discussion