Percentage based scan on a timeseries data

Question

I have 15 GB csv file. It has two columns (trade_time and price).

This is how the data looks like.

trade_time,price
2020-01-01 00:00:01.481,7189.42
2020-01-01 00:00:01.708,7189.42
2020-01-01 00:00:06.290,7189.5
2020-01-01 00:00:06.291,7190.52
2020-01-01 00:00:07.161,7188.97
2020-01-01 00:00:08.274,7189.93
2020-01-01 00:00:09.277,7190.47
2020-01-01 00:00:09.384,7190.47
2020-01-01 00:00:09.630,7190.11
2020-01-01 00:00:09.848,7189.74
2020-01-01 00:00:10.098,7189.46
2020-01-01 00:00:10.197,7189.16
2020-01-01 00:00:10.351,7189.1

I would like to check whether price is up or down by 0.5%. If the price hits +0.5% first, the result is 1. If the price hits -0.5% first, then the result is 0.

At the moment, I'm using this python solution. If database can perform better for my use case, then I would like to move to the database solution.

1. Since my task involves timeseries data, which database is better for my use case? SQL or NoSQL?
2. Is it really possible to do percentage based comparison in databases? e.g. +0.5%
3. How long the indexing process usually take for 1 billion rows?

Edit: dbfiddle can be found here.

CREATE TABLE Trades (
    trade_time datetime(3)  NOT NULL PRIMARY KEY,
    price      NUMERIC(7,2) NOT NULL
);

INSERT INTO Trades(trade_time,price) VALUES
 ('2020-01-01 00:00:01.481',7189.42)
,('2020-01-01 00:00:01.708',7189.42)
,('2020-01-01 00:00:06.290',7189.5)
,('2020-01-01 00:00:06.291',7190.52)
,('2020-01-01 00:00:07.161',7188.97)
,('2020-01-01 00:00:08.274',7189.93)
,('2020-01-01 00:00:09.277',7190.47)
,('2020-01-01 00:00:09.384',7190.47)
,('2020-01-01 00:00:09.630',7190.11)
,('2020-01-01 00:00:09.848',7189.74)

CSV Import to MySQL

LOAD DATA INFILE 'trades.csv' INTO TABLE Trades
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(trade_time, price);

Answer 1

(Discussing a implementation using MySQL)

CREATE TABLE Trades (
    trade_time DATETIME(3) NOT NULL, -- millisecond resolution
    price DECIMAL(8,2) NOT NULL,     -- or possibly something else
    PRIMARY KEY(trade_time)          -- UNIQUE and an INDEX
) ENGINE=InnoDB

You seem to be concerned about only one stock, correct? (If not then PRIMARY KEY (stock_id, trade_time) in that order.)

($start and $start_ts come from your app that is building and running the query. They need to hold the beginning price and time.)

SELECT ts, price
     FROM Trades
     WHERE trade_time > $start_ts,
       AND ABS(price - $start) / $start > 0.005  -- up or down .5%
     ORDER BY trade_time ASC

to find the next time when the price has moved by 0.5%. Similarly, use < and DESC to find the previous time.

Because of the indexing and clustering, this query will take [typically] less than 1ms. (Of course, if the price takes a month to move by .5%, the query will take much longer.)

A 5-minute candle (with just lo, avg, hi) is probably done via:

SELECT  MIN(trade_time) as lo,
        AVG(trade_time) as avg,
        MAX(trade_time) as hi
    FROM Trades
    WHERE trade_time >= $start_ts
      AND trade_time  < $start_ts + INTERVAL 5 MINUTE

The candle query should be run once every 5 minutes and saved in another table with 4 columns: trade_time, lo, avg, hi. It will take time to catch up with the data you already have, but future data should be computed on a 5-minute EVENT or cron.

It will take time to get the PRIMARY KEY in place. But once in place, new data will arrive just as fast as before. It may take hour(s) to add the PK.