sqlinfluxdbinfluxdb-python

query the last 24h changes in time series


There is multiple symbols and for each there is a time seri with timestamp in a database I used the following query to get almost the last element of each symbol in it's time seri. but I need also to extract how much each symbol has changed over the last 24h and report as a percent number.

query = """
SELECT "askPrice" FROM "bookTicker" where
time >= now() - 10s and 
time <= now() GROUP BY "symbol" limit 1
"""
D = client.query(query)
defaultdict(list,
            {('bookTicker',
              (('symbol',
                '1000SHIBUSDT'),)):                                   askPrice
             2021-07-08 11:54:30.224000+00:00  0.007988,
             ('bookTicker',
              (('symbol',
                '1INCHUSDT'),)):                                   askPrice
             2021-07-08 11:54:29.730000+00:00    2.5377,
             ('bookTicker',
              (('symbol',
                'AAVEUSDT'),)):                                   askPrice
             2021-07-08 11:54:29.605000+00:00    284.03
...

bookTicker measurement has the following tags: ['askPrice', 'askSize', 'bidPrice', 'bidSize'] and the symbol come from other measurements in the database. The database is quit large let me know what else I can provide to make the question clear.

Do you have any idea? I find many simular questions in the Review question part but I couldn't get a final results from them.

EDIT just as an idea:

may be this is a better way to get the last elemtnt of each time serie:

SELECT last("askPrice") FROM "bookTicker" WHERE time > now() - 1m GROUP BY "symbol";

and to get the value of each time series 24h befor we need to find the first element from :

SELECT first("askPrice") FROM "bookTicker" WHERE time > now() - 24h GROUP BY "symbol"; 

seems work but I don't know how to calculate the (a_last-a_first)/a_first *100 ?

SELECT last("askPrice") FROM "bookTicker" WHERE time > now() - 1m  GROUP BY "symbol" MINUS
SELECT first("askPrice") FROM "bookTicker" WHERE time > now() - 24h GROUP BY "symbol";  ???

Solution

  • Try:

    SELECT 
      (LAST("askPrice")-FIRST("askPrice"))/FIRST("askPrice")*100 
    FROM "bookTicker" 
    WHERE time > now() - 24h 
    GROUP BY "symbol"