# Percentage based scan on a timeseries data

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
);

('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
``````
• @Vérace-СлаваУкраїні I don't have a database yet. I have only the csv file. So yes, I can go for version 8. I'm not sure what you mean by `3 servers`. Could you elaborate?
– John
Jun 12 at 6:41
• @Vérace-СлаваУкраїні I have updated my question with sample data. Thanks
– John
Jun 12 at 9:22
• @Vérace-СлаваУкраїні I hope you are asking me how I'm loading the csv data into mysql. I use `LOAD DATA INFILE`. I have edited my question to add details. Please do understand that I'm a beginner. So if `LOAD DATA INFILE` is not a better solution, feel free to recommend the better solution. Thanks
– John
Jun 13 at 4:32
• @Vérace-СлаваУкраїні My CSV file has billions of rows. So I'm worried, a disk based solution will be slow. I'm not seeking persistence here. I'm only looking for a way to finish my task faster. After finishing the task, I don't need the database. So even an in-memory solution is okay for use case. Thanks
– John
Jun 13 at 4:36
• You're assuming that a disk-based solution will be slow! WIth partitioning and a decent ELT strategy, that needn't be the case. You have to look at the `LAG()` and `LEAD()` window functions. Also, you appear to be searching for a suitable database - may I suggest that you take a look at PostgreSQL - there are two systems (extensions) which use it and are specifically geared towards Time Series - TimescaleDB and Citus Data - you might want to take a look. After your `LOAD DATA...`, you can run an `UPDATE` using LAG/LEAD to track differences (absolute or %) between adjacent records! HTH... Jun 13 at 5:05

(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
AND ABS(price - \$start) / \$start > 0.005  -- up or down .5%
``````

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,
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.

• Thanks very much for the answer. I'm unable to upvote it due to my low reputation. I have a question though. You are using the variable `\$start`. But I don't see anywhere it is being declared. Could you tell me how that variable works?
– John
yesterday
• You are right. At the moment, I'm focusing only one stock. Thanks
– John
yesterday
• @John - I annotated my Answer with mention of \$start and \$start_ts. yesterday
• Got it. Thanks.
– John
20 hours ago