OLAP vs Time-Series Databases: The SQL Perspective

-- QuestDB implements the LATEST ON ... PARTITION BY extension

SELECT * FROM trades
LATEST ON timestamp PARTITION BY symbol, side;

-- DuckDB does not have an extension for this, but we can use a window
-- function and the QUALIFY filter

  SELECT *,
  row_number() OVER(
    PARTITION BY symbol, side
    ORDER BY timestamp DESC
    ) as rownum
  FROM trades
  QUALIFY rownum=1;

-- For this query, there are no extensions in either PostgreSQL, Timescale,
-- or Clickhouse. We can use standard SQL with window functions and a CTE

WITH numbered AS (
  SELECT *,
  row_number() OVER(
    PARTITION BY symbol, side
    ORDER BY timestamp DESC
    ) as rownum
  FROM trades
)
SELECT * from numbered WHERE rownum=1;

-- QuestDB offers the IN extension for time-intervals

SELECT * FROM trades WHERE timestamp in '2023'; -- whole year
SELECT * FROM trades WHERE timestamp in '2023-12'; -- whole month
SELECT * FROM trades WHERE timestamp in '2023-12-20'; -- whole day

-- The whole day, extending 15s into the next day
SELECT * FROM trades WHERE timestamp in '2023-12-20;15s';

-- For the past 7 days, 2 seconds before and after midnight
SELECT * from trades WHERE timestamp in '2023-09-20T23:59:58;4s;-1d;7'

-- PostgreSQL and Timescale can use the EXTRACT function
-- and then filter by parts of a date

-- whole year
SELECT * FROM trades WHERE EXTRACT(YEAR FROM timestamp) = 2023;

-- whole month
SELECT * FROM trades WHERE EXTRACT(YEAR FROM timestamp) = 2023 AND
  EXTRACT(MONTH FROM timestamp) = 12;

-- whole day
SELECT * FROM trades WHERE EXTRACT(YEAR FROM timestamp) = 2023 AND
  EXTRACT(MONTH FROM timestamp) = 12 AND
  EXTRACT(DAY FROM timestamp) = 20;

-- The whole day, extending 15s into the next day
SELECT * FROM trades
WHERE timestamp between '2023-12-20' AND '2023-12-21T00:00:15';

-- For the past 7 days, 2 seconds before and after midnight
SELECT * FROM trades
WHERE timestamp between '2023-12-14' AND '2023-12-21T00:00:02' AND
(
  (
  EXTRACT(HOUR FROM timestamp)=23 AND
  EXTRACT(MINUTE FROM timestamp)=59 AND
  EXTRACT(SECOND FROM timestamp)>=58
  ) OR
  (
  EXTRACT(HOUR FROM timestamp)=0 AND
  EXTRACT(MINUTE FROM timestamp)=0 AND
  EXTRACT(SECOND FROM timestamp)<2
  )
);

-- DuckDB and Clickhouse can use datetime functions
-- and then filter by parts of a date

-- whole year
SELECT * FROM trades WHERE year(timestamp)=2023;

-- whole month
SELECT * FROM trades WHERE year(timestamp)=2023 AND
  month(timestamp)=12;

-- whole day
SELECT * FROM trades WHERE year(timestamp)=2023 AND
  month(timestamp)=12 AND day(timestamp)=20;

-- The whole day, extending 15s into the next day
SELECT * FROM trades
WHERE timestamp between '2023-12-20' AND '2023-12-21T00:00:15';

-- For the past 7 days, 2 seconds before and after midnight
SELECT * FROM trades
WHERE timestamp between '2023-12-14' AND '2023-12-21T00:00:02' AND
(
  (
  hour(timestamp)=23 AND minute(timestamp)=59 AND second(timestamp)>=58
  ) OR
  (
  hour(timestamp)=0 AND minute(timestamp)=0 AND second(timestamp)<2
  )
);

-- QuestDB provides ASOF JOIN. Since table definition includes
-- the designated timestamp column, no time condition is needed

SELECT t.*, n.*
FROM trades ASOF JOIN news ON (symbol);

-- Postgresql and timescale are the only ones with no ASOF JOIN support,
-- but we can use a LEFT JOIN LATERAL and filter by time using DISTINCT

SELECT t.*, n.*
FROM trades t LEFT JOIN LATERAL (
 SELECT DISTINCT ON (news.symbol) *
    FROM news n1
    WHERE n1.symbol = t.symbol
    AND n1.timestamp <= news.timestamp
    ORDER BY n1.symbol, n1.timestamp DESC LIMIT 1) n

-- DuckDB provides the ASOF JOIN extension

SELECT t.*, n.*
FROM trades t ASOF JOIN news n
  ON t.symbol = n.symbol AND t.timestamp >= n.timestamp;

-- Clickhouse provides the ASOF JOIN extension

SELECT t.*, n.*
FROM trades t ASOF JOIN news n
  ON t.symbol = n.symbol AND n.timestamp <= t.timestamp;

-- QuestDB implements the SAMPLE BY extension
-- which accepts granularity from years to microseconds.
-- GROUP BY and ORDER BY are implicit and not needed

SELECT
   timestamp, symbol,
   sum(price) AS price,
   sum(amount) AS volume
FROM trades
SAMPLE BY 15m;

-- Postgresql supports date_bin, allowing a maximum of 1 month
-- time buckets. An alternative should be used for longer intervals
-- then we GROUP BY and ORDER BY

SELECT
  date_bin(
    '15 minutes', timestamp, date_trunc('day',timestamp)
    ) AS time_bucket,
  symbol,
  sum(price) AS price,
  sum(amount) AS volume
FROM trades
GROUP BY time
ORDER BY time_bucket

-- Timescale supports time_bucket, then GROUP BY and ORDER

SELECT time_bucket('15 minute', timestamp) AS bucket,
    symbol,
    sum(price) AS price,
    sum(amount) AS volume
FROM trades
GROUP BY bucket, symbol
ORDER BY bucket ASC;

-- DuckDB supports time_bucket, then GROUP BY and ORDER

SELECT time_bucket(Interval '15 minutes', timestamp) AS bucket,
    symbol,
    sum(price) AS price,
    sum(amount) AS volume
FROM trades
GROUP BY bucket, symbol
ORDER BY bucket ASC;

-- Clickhouse supports toStartOfInterval, then GROUP BY and ORDER

SELECT
    toStartOfInterval(timestamp, INTERVAL 15 MINUTE) AS i,
    symbol,
    sum(price) AS price,
    sum(amount) AS volume
FROM trades
GROUP BY i, symbol
ORDER BY i ASC;

-- QuestDB SAMPLE BY accepts FILL with different strategies,
-- including LINEAR, PREVious row value, NULL, or Literal value

SELECT
timestamp,
sum(price) AS price,
sum(amount) AS volume
FROM trades
SAMPLE BY 1s FILL(LINEAR);

-- Postgresql. No support for interpolation, so we use window functions.
-- We first generate a virtual table with all the intervals we want
-- in the result and LEFT JOIN with our table. Now we need to find
-- previous and next values, so we can finally interpolate

WITH time_series AS (
    SELECT generate_series(
        min(date_trunc('day', timestamp)),
        max(timestamp),
        interval '1 seconds'
    ) AS time_bucket
    FROM trades
),
aggregated_data AS (
    SELECT
        date_bin(
          '1 seconds', timestamp, date_trunc('day', timestamp)
          ) AS time_bucket,
        symbol,
        sum(price) AS price,
        sum(amount) AS volume
    FROM trades
    GROUP BY time_bucket, symbol
)
SELECT
    ts.time_bucket,
    ad.symbol,
    COALESCE(
        ad.price,
        last_value(ad.price) OVER w
        + (next_value(ad.price) OVER w - last_value(ad.price) OVER w)
        * EXTRACT(
          epoch FROM ts.time_bucket - last_value(ts.time_bucket) OVER w
          )
        / NULLIF(
          EXTRACT(
            epoch FROM next_value(ts.time_bucket) OVER w
              - last_value(ts.time_bucket) OVER w
            ), 0
            )
    ) AS interpolated_price,
    COALESCE(
        ad.volume,
        last_value(ad.volume) OVER w
        + (next_value(ad.volume) OVER w - last_value(ad.volume) OVER w)
        * EXTRACT(
          epoch FROM ts.time_bucket - last_value(ts.time_bucket) OVER w
          )
        / NULLIF(
          EXTRACT(
            epoch FROM next_value(ts.time_bucket) OVER w
              - last_value(ts.time_bucket) OVER w
              ), 0
              )
    ) AS interpolated_volume
FROM time_series ts
LEFT JOIN aggregated_data ad ON ts.time_bucket = ad.time_bucket
WINDOW w AS (
    PARTITION BY ad.symbol
    ORDER BY ts.time_bucket
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
ORDER BY ts.time_bucket, ad.symbol;

-- Timescale offers the time_bucket_gapfill function which,
-- together with interpolate, allows for different gap filling
-- strategies

SELECT time_bucket_gapfill('1 second', timestamp) AS bucket,
    interpolate(sum(price)) AS price,
    interpolate(sum(amount)) AS volume,
FROM trades
GROUP BY bucket, symbol
ORDER BY bucket ASC;

-- Duckdb. No support for interpolation, so we need to use window functions.
-- We first generate a virtual table with all the intervals we want
-- in the result and LEFT JOIN with our table. Now we need to find
-- previous and next values, so we can finally interpolate

WITH time_series AS (
    SELECT unnest(generate_series(
        min(date_trunc('second',timestamp)),
        max(timestamp),
        interval '1 seconds'
    )) AS time_bucket
    FROM trades
),
aggregated_data AS (
    SELECT time_bucket(Interval '1 seconds', timestamp) AS bucket,
    symbol,
    sum(price) AS price,
    sum(amount) AS volume
    FROM trades
    GROUP BY bucket, symbol
    ORDER BY bucket ASC
)
SELECT
    ts.time_bucket,
    ad.symbol,
    price,
    volume,
    COALESCE(
        ad.price,
        last_value(ad.price) OVER w
        + (first_value(ad.price) OVER w - last_value(ad.price) OVER w)
        * EXTRACT(
          epoch FROM ts.time_bucket - last_value(ts.time_bucket) OVER w
          )
        / NULLIF(
          EXTRACT(
            epoch FROM first_value(ts.time_bucket) OVER w
              - last_value(ts.time_bucket) OVER w
            ), 0
            )
    ) AS interpolated_price,
    COALESCE(
        ad.volume,
        last_value(ad.volume) OVER w
        + (first_value(ad.volume) OVER w - last_value(ad.volume) OVER w)
        * EXTRACT(
          epoch FROM ts.time_bucket - last_value(ts.time_bucket) OVER w
          )
        / NULLIF(
          EXTRACT(
            epoch FROM first_value(ts.time_bucket) OVER w
              - last_value(ts.time_bucket) OVER w
            ), 0
            )
    ) AS interpolated_volume
FROM time_series ts
LEFT JOIN aggregated_data ad ON ts.time_bucket = ad.bucket
WINDOW w AS (
    PARTITION BY symbol
    ORDER BY time_bucket
)
ORDER BY ts.time_bucket, ad.symbol;

-- Clickhouse. It supports the WITH FILL STEP extension, but
-- interpolation is based on last row value only, so no
-- easy method for linear interpolation.
-- We first fill rows, then use window functions to get results similar
-- to linear interpolation. We could make the query more complex to get
-- better results, but this is probably enough for this post comparison

WITH interpolated AS (
    SELECT
    toStartOfInterval(timestamp, INTERVAL 15 SECOND) AS i,
    symbol,
    sum(price) AS price,
    sum(amount) AS volume
FROM trades
GROUP BY i, symbol
ORDER BY i ASC
WITH FILL STEP Interval 1 SECOND INTERPOLATE(symbol)
), prev_and_next AS (
  SELECT i, symbol, price, volume,
  last_value(volume) OVER prev_w as prev_vol,
  first_value(volume) OVER next_w as next_vol,
  last_value(price) OVER prev_w as prev_price,
  first_value(price) OVER next_w as next_price
  FROM interpolated
  WINDOW prev_w AS (
    PARTITION BY symbol ORDER BY i
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ),
    next_w AS (
    PARTITION BY symbol ORDER BY i
    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    )
)
SELECT i, symbol,
      coalesce(price, (prev_price + next_price)/2) as price,
      coalesce(volume,(prev_vol+next_vol)/2) as volume
FROM prev_and_next;