What are the implementation strategies for K-line data filling?

Community
,
1

Hello, everyone. I am researching QuestDB for my company, focusing on handling various query scenarios and storing K-line data. I encountered a problem and would like to ask for help on how to implement it:

The problem is: Within a specific time window, if no trades occur, a K-line should still be generated with the open, high, low, and close prices being the last trade price before that time window. For example, my table new_trades:

CREATE TABLE new_trades (
    symbol symbol,
    side symbol,
    mid long,
    tradeId long,
    amount_unit double,
    price DOUBLE,
    amount DOUBLE,
    ts TIMESTAMP
) TIMESTAMP(ts)
PARTITION BY DAY;

There are two test data entries：

image
image2178×272 36.2 KB

The requirement is to be able to query:
2024-12-03T09:12:00.000000Z
2024-12-03T09:11:00.000000Z
2024-12-03T09:10:00.000000Z
…
2024-12-03T09:03:00.000000Z

The requirement is to obtain the 10 K-line data, where there were no transactions from 2024-12-03T00:12:46.000000Z to 2024-12-03T09:12:00.000000Z. Therefore, the expected result is that the open, high, low, and close values for the period from 2024-12-03T09:03 to 2024-12-03T09:12 are filled with price=2068.94, and the other amount values are filled with 0. How can this be achieved?

This is the SQL I tried to write, but I did not get the expected result. Please guide me, and I would greatly appreciate it. :pray:

SELECT
    first(price) AS open,     
    max(price) AS high,       
    min(price) AS low,       
    last(price) AS close,    
    sum(amount) AS total_volume, 
    count(amount) AS total_count,
    ts AS kline_time      
FROM
    new_trades
WHERE
    symbol = 'ETH-USDT'
    AND ts <= '2024-12-03T09:12:31.612000Z' 
SAMPLE BY 1m 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC
LIMIT 10;
2

what results are you getting? On the demo instance (https://demo.questdb.io) I see results as you described with your query

SELECT
    first(price) AS open,     
    max(price) AS high,       
    min(price) AS low,       
    last(price) AS close,    
    sum(amount) AS total_volume, 
    count(amount) AS total_count,
    timestamp AS kline_time      
FROM
    trades
WHERE
    symbol = 'ETH-USDT'
    AND timestamp > dateadd('m', -10, now()) 
SAMPLE BY 1s 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC;

image
image1103×1147 143 KB

3

@javier thank you for response

It seems that on the demo instance, all trading pairs have trades every second, so there is no data matching my query scenario, making it difficult to reproduce. Therefore, I created a new_trades table myself, which only contains two rows of data (as shown above).

What I want to verify is that within a specific time window, even if there are no trades, K-lines (candlesticks) should still be generated. The open, high, low, and close prices for these K-lines should be filled with the prices from the last trade before the time window. For example, as shown in the new_trades table above, the “ETS-USDT” pair only has two trades at 2024-12-03T00:12:46.000000Z and 2024-12-02T15:12:46.000000Z.

Now, I want to query for the 10 K-lines between 2024-12-03T09:03:00.000000Z and 2024-12-03T09:12:00.000000Z. Since there were no trades during this time, the most recent trade occurred at 2024-12-03T00:12:46.000000Z. Therefore, the expected result is that the open, high, low, and close prices for the 10 K-lines between 2024-12-03T09:03:00.000000Z and 2024-12-03T09:12:00.000000Z should be filled with the data from the trade at 2024-12-03T00:12:46.000000Z. However, the SQL query I wrote did not return the expected results:

SELECTfirst(price) AS open,     
    max(price) AS high,       
    min(price) AS low,       
    last(price) AS close,     
    sum(amount) AS total_volume, 
    count(amount) AS total_count,
    ts AS kline_time    
FROM
    new_trades
WHERE
    symbol = 'ETH-USDT'AND ts <= '2024-12-03T09:12:00.000000Z'  and ts >= '2024-12-03T09:03:00.000000Z' 
SAMPLE BY 1m 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC
LIMIT 10;

(Sorry, I’m limited in the number of images I can upload.) The query result is empty.

Alternatively, using another query method also did not return the expected results.

SELECT
    first(price) AS open,  
    max(price) AS high,     
    min(price) AS low,        
    last(price) AS close,      
    sum(amount) AS total_volume,
    count(amount) AS total_count,
    ts AS kline_time    
FROM
    new_trades
WHERE
    symbol = 'ETH-USDT'
    AND ts <= '2024-12-03T09:12:00.000000Z' 
SAMPLE BY 1m 
FILL(PREV,PREV,PREV,PREV,0,0)
ORDER BY kline_time DESC
LIMIT 10;

2112×500 62.7 KB

The query results (which are in reverse order) start from 2024-12-03T00:12:00.000000Z, but they should start from 2024-12-03T09:12:00.000000Z. The complete query results should display data from 2024-12-03T09:12:00.000000Z to 2024-12-03T09:03:00.000000Z.