Technical Details on Writing QQL Queries
In QQL Background we already explained more on how the ad-hoc database your QQL queries will be executed on is put together at run-time. This article focusses on the technical details and what feature set you can expect when writing your queries.
As a database we use Sqlite 3 (https://www.sqlite.org/). You can find detailed information on how queries work for Sqlite 3 on their website . Especially helpful for writing QQL queries is the documentation on SELECT queries. Please do note that we only support SELECT queries in our stack, as you are not meant to change any data using INSERT, UPDATE or even DELETE queries. Also all other statements except for SELECT are turned off by our system. There are also great references on available SQL functions, aggregate functions and date and time functions on their website.
Sqlite 3 also has full support for JOIN statements. These can be really powerful when it comes to joining multiple data sources within a single metric. Imagine joining data across multiple networks, e.g. comparing your engagement performance on Facebook with the same on Twitter.
Enhanced SQL and aggregate functions
Beside the default feature set of Sqlite 3, we have enhanced it by custom SQL and aggregate functions to make your life a little easier.
...does return 0 even if value is NULL. A good example could be a formula for measuring engagement on Facebook where you want to divide by the number of posts in the given time period, e.g. (Likes+Comments+Shares)/Posts. Now if there are no posts at this day, meaning Posts will be 0, the division will be undefined, which equals NULL within Sqlite. Still, if there is no post, you probably want to define your formula to evaluate to 0 instead of NULL. Just to mention, NULL within our tool will be interpreted as “not available” and thus will also not be shown in any of the charts. Tables will show “n/a”.
Example: SELECT profileId AS dim1, time AS dim2, ZEROIFNULL((ownPostsLikes*1.0+ownPostsComments+ownPostsShares)/ownPosts) FROM facebook
… exactly matches what PRINTF does ( check here and search for PRINTF), but NULL remains NULL, which will be interpreted as “not available” within our tool.
… returns “base” raised to the power of “exponent” (be).
… is an addition to the existing aggregate functions of Sqlite useful in combination with a GROUP BY statement. FIRST(...) returns the first item within the grouped item set, LAST(...) returns the last one.
Next to enhanced SQL and aggregate functions, within QQL there are certain predefined variables which will be replaced at run-time. These can be very handy when it comes to more complex queries referencing e.g. to the selected time period. Here is a full list:
- modifyStartTime (string, e.g. "+3 days", takes input accepted by http://php.net/manual/de/datetime.formats.php)
- modifyEndTime (string, e.g. "-7 days", takes input accepted by http://php.net/manual/de/datetime.formats.php)
- overwriteStartTimeRelativeToEndTime (string, e.g. "-7 days" from end time, takes input accepted by http://php.net/manual/de/datetime.formats.php)
- setProfileOrderAndVisibilityByInspectingQueryResult (boolean, e.g. true)