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"; ???
Try:
SELECT
(LAST("askPrice")-FIRST("askPrice"))/FIRST("askPrice")*100
FROM "bookTicker"
WHERE time > now() - 24h
GROUP BY "symbol"