QuestDB attempts to implement standard ANSI SQL. We also try to be compatible with PostgreSQL, although parts of this are a work in progress. This page presents the main extensions we bring to SQL and the main differences that one might find in SQL but not in QuestDB's dialect.
We have extended SQL to support our data storage model and simplify semantics of time series analytics.
LATEST ON is a clause introduced to help find
the latest entry by timestamp for a given key or combination of keys as part of
SAMPLE BY is used for time-based aggregations with an efficient syntax. The short query below will return the simple average balance from a list of accounts by one month buckets.
Timestamp search can be performed with regular operators, e.g
However, QuestDB provides a
native notation which is faster
and less verbose.
#Differences from standard SQL
#SELECT * FROM is optional
In QuestDB, using
SELECT * FROM is optional, so
SELECT * FROM my_table; will
return the same result as
my_table;. While adding
SELECT * FROM makes SQL
look more complete, there are examples where omitting these keywords makes
queries a lot easier to read.
#GROUP BY is optional
GROUP BY clause is optional and can be omitted as the QuestDB optimizer
derives group-by implementation from the
SELECT clause. In standard SQL, users
might write a query like the following:
However, enumerating a subset of
SELECT columns in the
GROUP BY clause is
redundant and therefore unnecessary. The same SQL in QuestDB SQL-dialect can be
Let's look at another more complex example using
HAVING in standard SQL:
In QuestDB's dialect, featherweight sub-queries come to the rescue to create a
smaller, more readable query, without unnecessary repetitive aggregations.
HAVING functionality can be obtained implicitly as follows: