Save Queries for Reuse

Similar to the Create and Save Views functionality, AnzoGraph DB enables you to create query definitions that you can reference as subqueries in other queries. Naming queries for later use enables you to simplify complex queries and quickly add commonly used subqueries to other queries. Using named queries can also increase query performance since AnzoGraph DB can identify and execute repetitive patterns once and then reuse the results.

There are two ways to create a named query, depending on whether you want to create a query definition that is saved in the triplestore and can be used as a subquery in various queries or whether you want to write a query inline to be used once in the query that immediately follows the inline named query definition. This topic provides instructions for creating each type of named query:

Create and Save a Query for Reuse

Use the following syntax to create a query and save the query definition so that you can run the query as a subquery in subsequent queries:

CREATE [ OR REPLACE ] QUERY <query_URI> AS
  query_text

Include the OR REPLACE keywords when you want to replace a previously defined query with the same name. For example, the query below creates a named query called total_profit.

PREFIX tickit: <http://anzograph.com/tickit/>
CREATE OR REPLACE QUERY <total_profit> AS 
SELECT ?event (sum(?qty) as ?tickets) (sum(?comm) as ?commission_paid)
       (sum(?price) as ?total_paid
FROM <http://anzograph.com/tickit>
WHERE {
    ?sales tickit:qtysold ?qty .
    ?sales tickit:eventid ?eventid .
    ?eventid tickit:eventname ?event .
    ?sales tickit:commission ?comm .
    ?sales tickit:pricepaid ?price .
  }
GROUP BY ?event
ORDER BY ?event

To reference a predefined query as a subquery, use the following syntax in the WHERE clause:

{ QUERY <query_URI> }

For example, the query below includes the predefined query, total_profit, as a subquery:

SELECT ?event ?tickets ((?total_paid - ?commission_paid) as ?profit)
FROM <http://anzograph.com/tickit>
WHERE { QUERY <total_profit> }
ORDER BY desc(?profit)
LIMIT 10
event            | tickets | profit
-----------------+---------+---------------
Mamma Mia!       |    3658 | 965135.900000
Spring Awakening |    3025 | 826926.750000
The Country Girl |    2871 | 773978.550000
Macbeth          |    2733 | 733193.000000
Jersey Boys      |    2781 | 690095.450000
Legally Blonde   |    2272 | 683895.550000
Chicago          |    2535 | 672344.050000
Spamalot         |    2199 | 607160.950000
Hedda Gabler     |    1891 | 561865.300000
Thurgood         |    1894 | 543895.450000
10 rows

When you include a FROM clause in a named query, AnzoGraph DB always applies that FROM list to the query. If the named query becomes a subquery in another query, the subquery does not inherit the FROM clause from the main query. If the named query does not include a FROM clause, AnzoGraph DB applies the FROM clause from the main query.

Create a Query Inline for One-Time Use

If you want to create a named query on-the-fly to use in a query that you are writing, you can include a WITH clause to define a named query at the beginning of that query.

AnzoGraph DB does not save the query definition for named queries that are defined in a WITH clause. The named query can only be referenced in the query that immediately follows the WITH clause; it is not available to use in subsequent queries. To create a named query whose definition is persisted and can be referenced in future queries, use the CREATE OR REPLACE syntax..

WITH Syntax

WITH ( QUERY <query_name> AS select_query )
     [ ( ... ) ]

Where select_query is the query that you want to name and reference in the WHERE clause of the main query. You can define multiple named queries in one WITH clause. For example, the WITH clause below defines a query named profit. The profit query is then referenced as a subquery in the main query:

PREFIX tickit: <http://anzograph.com/tickit/>
WITH 
( QUERY <profit> AS 
  SELECT ?event (sum(?qty) as ?tickets) (sum(?comm) as ?commission_paid)
         (sum(?price) as ?total_paid)
FROM <http://anzograph.com/tickit>
  WHERE {
    ?sales tickit:qtysold ?qty .
    ?sales tickit:eventid ?eventid .
    ?eventid tickit:eventname ?event .
    ?sales tickit:commission ?comm .
    ?sales tickit:pricepaid ?price .
  }
  GROUP BY ?event
)
SELECT ?event ?tickets ((?total_paid - ?commission_paid) as ?profit)
FROM <tickit>
WHERE { QUERY <profit> }
ORDER BY desc(?profit)
LIMIT 10

Examples

The example queries in this section run against the AnzoGraph DB sample Tickit data set, which captures sales activity for a fictional Tickit website where people buy and sell tickets for sporting events, shows, and concerts. You can load and explore this data set. For more information, see Working with SPARQL and the Tickit Data.

The example below creates a named query and uses it to query the sample Tickit data set to identify possible ticket scalpers by calculating the average price per ticket for events and then finding cases where tickets are listed for a higher price.

PREFIX tickit: <http://anzograph.com/tickit/>
WITH ( QUERY <avg_price> AS
  SELECT ?eventname (avg(?priceperticket) as ?avg_price)
  WHERE {
    ?listing tickit:eventid ?eventid .
    ?eventid tickit:eventname ?eventname .
    ?listing tickit:priceperticket ?priceperticket .
  }
  GROUP BY ?eventname
)
SELECT ?sellername ?avg_price ?priceperticket ?eventname ?listtime
FROM <http://anzograph.com/tickit>
WHERE { 
  { QUERY <avg_price> }
  ?listing tickit:listtime ?listtime .
  ?listing tickit:priceperticket ?priceperticket .
  ?listing tickit:sellerid ?seller .
  ?seller tickit:firstname ?firstname .
  ?seller tickit:lastname ?lastname .
  BIND(CONCAT(?firstname, " ", ?lastname) AS ?sellername)
  FILTER (?priceperticket > ?avg_price)
}
ORDER BY ?avg_price ?listtime ?sellername ?eventname
LIMIT 10
sellername        | avg_price  | priceperticket | eventname       | listtime
------------------+------------+----------------+-----------------+---------------
Garrett Rasmussen | 249.181818 |     277.000000 | White Christmas | 2008-01-01T01:03:16Z
Ivan Trevino      | 249.181818 |     415.000000 | White Christmas | 2008-01-01T01:03:17Z
Liberty Hopkins   | 249.181818 |    2120.000000 | White Christmas | 2008-01-01T01:03:53Z
Jenette Norton    | 249.181818 |    1031.000000 | White Christmas | 2008-01-01T01:04:15Z
Tana Mcguire      | 249.181818 |     455.000000 | White Christmas | 2008-01-01T01:07:02Z
Lee Prince        | 249.181818 |     377.000000 | White Christmas | 2008-01-01T01:14:34Z
Aileen Nicholson  | 249.181818 |     413.000000 | White Christmas | 2008-01-01T01:14:40Z
Wylie Kemp        | 249.181818 |     372.000000 | White Christmas | 2008-01-01T01:14:48Z
Allistair Yang    | 249.181818 |     491.000000 | White Christmas | 2008-01-01T01:15:54Z
Quinn Porter      | 249.181818 |     977.000000 | White Christmas | 2008-01-01T01:17:19Z
10 rows

The example below uses a WITH clause to define the subquery, locations, which queries the example Tickit data to return a list of the locations for events that took place in February:

PREFIX tickit: <http://anzograph.com/tickit/>
WITH ( QUERY <locations> AS
  SELECT ?name ?where 
  WHERE { 
    ?e tickit:venueid ?v .
    ?v tickit:venuename ?where .
    ?e tickit:dateid ?d .
    ?d tickit:month ?when .
    ?e tickit:eventname ?name .
    filter (?when = "FEB")
  }
)
SELECT *
FROM <http://anzograph.com/tickit>
WHERE {
 {QUERY <locations>}
}
ORDER BY ?where ?name
LIMIT 100
name                                               | where
---------------------------------------------------+----------------------
G. Love and Special Sauce                          | ARCO Arena
Oasis                                              | ARCO Arena
Rush                                               | ARCO Arena
Smash Mouth                                        | ARCO Arena
Steve Miller Band                                  | ARCO Arena
The Guess Who                                      | ARCO Arena
Tokio Hotel                                        | ARCO Arena
Projekt Revolution                                 | AT&T Center
Chromeo                                            | AT&T Park
Citizen Cope                                       | AT&T Park
Counting Crows and Maroon 5                        | AT&T Park
Extreme                                            | AT&T Park
Oliver Dragojevic                                  | AT&T Park
Nashville Star                                     | Air Canada Centre
Stone Temple Pilots                                | Air Canada Centre
Taylor Swift                                       | Air Canada Centre
Wallflowers                                        | Air Canada Centre
ZZ Top                                             | Air Canada Centre
Dirty Dancing                                      | Al Hirschfeld Theatre
...
100 rows