Sub queries in TQL enable you to take your queries to the next level. A sub query is a complete TQL query by itself, though it is embedded within a main query. In the selection part of the main query you can access the results of the sub query, which effectively adds an extra level of processing you would otherwise not have. We introduced sub queries briefly on the page about constraints. On the page about query variables, we showed you how to add a Safetynet counter to your solution. Let's revisit that example now to see how a sub query makes it possible to learn more about Safetynet activation across sessions.
Referring back to the Safetynet counter we added, we can run a simple query to show the total number of times the Safetynet became active per session:
The result of the query delivers raw information that, by itself, is not very helpful:
For example, you may want to know the average number of Safetynet hits across sessions or other aggregate information. Obviously, it would be extremely inefficient to compute this by hand. This is where a sub query can help us, combined with aggregate functions available in TQL. You can embed your simple query into a larger context to get the mean, minimum, and maximum value of the Safetynet counter. This is a query to achieve that:
lu mean result.count as average, min result.count as minValue, max result.count as maxValue: result = @(la s.sv:n:safetyNetCounter as count)
The main query is able to look at the results of the sub query and process information about it:
Here are some additional examples showing how you can use sub queries.
This query retrieves the mean number of sessions per day in a particular month:
lu mean x.count as meanCount: x=@(d date: s.beginTime == '2016-04' , catd(model="date") s.beginTime as date order by count)
If your bot uses multiple channels and you require usage information about each of the channels, you can use a query similar to this, based on the channel parameter used:
d result.date as date, result.channel as channel : result = @( lu s.id as id, t.e.requestParameters.channel as channel, date: catd(model="date") s.beginTime as date) order by date desc
One strength of sub queries is that you can combine multiple sub queries into a single query, and then present results from each of them.
This example shows relative occurrences of a particular product name in a single query. You would otherwise have to run each query separately to obtain the complete results:
la sum espresso.count, sum macchiato.count: espresso = @(ca t.id as count : t.e.type == "request", t.e.userInput ~= ".*espresso.*"), macchiato = @(ca t.id as count : t.e.type == "request", t.e.userInput ~= ".*macchiato.*")
The query generates the following result:
Was this page helpful?