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: Follow these instructions to create and save a named query for future use.
- Create a Query Inline for One-Time Use: Follow these instructions to create a named query inline for single use.
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 10event | 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 10sellername | 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 100name | 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