mardi 8 mars 2016

SQLite time series: One table vs multiple tables

Idea

I am analyzing the market of a MMORPG. With my raspberry pi, I am downloading the market prices every five minutes.

There are almost 17.000 items which may be interesting, but I will focus in 1.200 of them. So the problem is how to store them.

The DB now looks like this:

Given 1.200 differente tables, for table/item 1038:

Time            BuyQuantity  BuyPrice  SellQuantity  SellPrice

"1454792048"      64         35848          30          65948
"1454792410"      64         35848          30          65948
"1454792711"      64         35848          30          65948
"1454793010"      64         35848          30          65948
"1454793316"      64         35848          30          65948
"1454793611"      64         35848          32          65947
"1454793910"      64         35848          32          65947
"1454794217"      64         35848          32          65947
"1454794512"      67         35849          32          65947

#Length: 6800 rows

Afterwards I analyze the data with pandas to calculate daily tendencies and market depth.

Questions

  1. Second time working with db, would be SQLite useful for analyzing time series?
  2. I am using one table per item, and I estimate I would need around 1.200 tables. The reason is that I am using UNIX timestamp as the primary key value. I have read it makes more efficient the posterior search. This is the proper approach or would be more convenient a one table DB?
  3. Would you recommend to use pytables of pandas itself for storing the data? One argument to use SQL is to make it more "agnostic" soy I could later use scikit learn or any library to "analyze" the data.

I have read some posts, for example #1 and #2 ask something similar, but it seems that the answer depends on the purpose and the values that you are storing.

Regargind this, I want to be able to perform different analysis in the future, from comparing different "families" of items to look more the "most sold" one. Maybe some Machine Learning in the future!

Thank you in advance!!

Aucun commentaire:

Enregistrer un commentaire