Skip to content

Latest commit

 

History

History
103 lines (77 loc) · 3.07 KB

sql-extensions.md

File metadata and controls

103 lines (77 loc) · 3.07 KB
title description
SQL extensions
QuestDB attempts to implement standard ANSI SQL with time-based extensions for convenience. This document describes SQL extensions in QuestDB and how users can benefit from them.

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.

SQL extensions

We have extended SQL to support our data storage model and simplify semantics of time series analytics.

LATEST ON

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 a SELECT statement.

SELECT * FROM balances
WHERE balance > 800
LATEST ON ts PARTITION BY customer_id, currency;

SAMPLE BY

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.

SELECT avg(balance) FROM accounts SAMPLE BY 1M

Timestamp search

Timestamp search can be performed with regular operators, e.g >, <= etc. However, QuestDB provides a native notation which is faster and less verbose.

SELECT * FROM scores WHERE ts IN '2018';

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.

my_table;
-- equivalent to:
SELECT * FROM my_table;

GROUP BY is optional

The 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:

SELECT a, b, c, d, sum(e) FROM tab GROUP BY a, b, c, d;

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 written as:

SELECT a, b, c, d, sum(e) FROM tab;

Implicit HAVING

Let's look at another more complex example using HAVING in standard SQL:

SELECT a, b, c, d, sum(e)
FROM tab
GROUP BY a, b, c, d
HAVING sum(e) > 100;

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:

(SELECT a, b, c, d, sum(e) s FROM tab) WHERE s > 100;