Named Queries
Similar to the Named Views functionality, AnzoGraph 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 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:
- Creating and Saving a Named Query for Reuse: Follow these instructions to create and save a named query for future use.
- Creating a Named Query Inline for One-Time Use: Follow these instructions to create a named query inline for single use.
Creating and Saving a Named 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.
CREATE OR REPLACE QUERY <total_profit> AS
SELECT ?event (sum(?qty) as ?tickets) (sum(?comm) as ?commission_paid)
(sum(?price) as ?total_paid)
WHERE {
?sales <qtysold> ?qty .
?sales <eventid> ?eventid .
?eventid <eventname> ?event .
?sales <commission> ?comm .
?sales <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 <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 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 applies the FROM clause from the main query.
Creating a Named 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 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 <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:
WITH
( QUERY <profit> AS
SELECT ?event (sum(?qty) as ?tickets) (sum(?comm) as ?commission_paid)
(sum(?price) as ?total_paid)
WHERE {
?sales <qtysold> ?qty .
?sales <eventid> ?eventid .
?eventid <eventname> ?event .
?sales <commission> ?comm .
?sales <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 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 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.
WITH ( QUERY <avg_price> AS
SELECT ?eventname (avg(?priceperticket) as ?avg_price)
WHERE {
?listing <eventid> ?eventid .
?eventid <eventname> ?eventname .
?listing <priceperticket> ?priceperticket .
}
GROUP BY ?eventname
)
SELECT ?sellername ?avg_price ?priceperticket ?eventname ?listtime
FROM <tickit>
WHERE {
{ QUERY <avg_price> }
?listing <listtime> ?listtime .
?listing <priceperticket> ?priceperticket .
?listing <sellerid> ?seller .
?seller <firstname> ?firstname .
?seller <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:
WITH ( QUERY <locations> AS
SELECT ?name ?where
WHERE {
?e <venueid> ?v .
?v <venuename> ?where .
?e <dateid> ?d .
?d <month> ?when .
?e <eventname> ?name .
filter (?when = "FEB")
}
)
SELECT *
FROM <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