#c# #postgresql #entity-framework #entity-framework-core #candlestick-chart
#c# #postgresql #entity-framework #сущность-основа-ядро #график свечей
Вопрос:
Модели
Торговля
Trade сущность:
[Index(nameof(TimeStamp))]
[Index(nameof(Price))]
public class Trade
{
public int Id { get; set; }
public int SymbolId { get; set; }
public Symbol Symbol { get; set; } // navigation property
public decimal Price { get; set; }
public decimal Quantity { get; set; }
public DateTime TimeStamp { get; set; }
public OrderSide Side { get; set; }
public OrderTypeMinimal Type { get; set; }
}
Результирующая схема в PostgreSQL:
kraken-trades=# d "Trades"
Table "public.Trades"
Column | Type | Collation | Nullable | Default
----------- -------------------------- ----------- ---------- ----------------------------------
Id | integer | | not null | generated by default as identity
SymbolId | integer | | not null |
Price | numeric | | not null |
Quantity | numeric | | not null |
TimeStamp | timestamp with time zone | | not null |
Side | order_side | | not null |
Type | order_type_minimal | | not null |
Indexes:
"PK_Trades" PRIMARY KEY, btree ("Id")
"IX_Trades_Price" btree ("Price")
"IX_Trades_SymbolId" btree ("SymbolId")
"IX_Trades_TimeStamp" btree ("TimeStamp")
Foreign-key constraints:
"FK_Trades_Symbols_SymbolId" FOREIGN KEY ("SymbolId") REFERENCES "Symbols"("Id") ON DELETE CASCADE
Примерные данные:
kraken-trades=# SELECT * FROM "Trades" LIMIT 10;
Id | SymbolId | Price | Quantity | TimeStamp | Side | Type
---------- ---------- ------------- ------------ ------------------------ ------ -------
70866541 | 50 | 32884.10000 | 0.05000000 | 2021-07-08 00:35:40-07 | buy | limit
70866542 | 50 | 32883.80000 | 0.01480000 | 2021-07-08 00:35:40-07 | buy | limit
70866543 | 50 | 32880.80000 | 0.06907595 | 2021-07-08 00:35:40-07 | buy | limit
70866544 | 50 | 32880.80000 | 0.08388407 | 2021-07-08 00:35:40-07 | buy | limit
70866545 | 50 | 32882.10000 | 0.14950000 | 2021-07-08 00:35:41-07 | buy | limit
70866546 | 50 | 32885.00000 | 0.02347417 | 2021-07-08 00:35:42-07 | buy | limit
70866547 | 50 | 32882.30000 | 0.02347417 | 2021-07-08 00:35:59-07 | buy | limit
70866548 | 50 | 32882.30000 | 0.00268462 | 2021-07-08 00:36:00-07 | buy | limit
70866549 | 50 | 32882.30000 | 0.00674727 | 2021-07-08 00:36:01-07 | buy | limit
70866550 | 50 | 32882.30000 | 0.00034898 | 2021-07-08 00:36:03-07 | buy | limit
(10 rows)
Символ
Модель:
public class Symbol
{
public int Id { get; set; }
public string Name { get; set; }
}
Схема:
kraken-trades=# d "Symbols"
Table "public.Symbols"
Column | Type | Collation | Nullable | Default
-------- --------- ----------- ---------- ----------------------------------
Id | integer | | not null | generated by default as identity
Name | text | | not null |
Indexes:
"PK_Symbols" PRIMARY KEY, btree ("Id")
"IX_Symbols_Name" UNIQUE, btree ("Name")
Referenced by:
TABLE ""Trades"" CONSTRAINT "FK_Trades_Symbols_SymbolId" FOREIGN KEY ("SymbolId") REFERENCES "Symbols"("Id") ON DELETE CASCADE
Пример данных:
kraken-trades=# SELECT * FROM "Symbols" LIMIT 10;
Id | Name
---- ----------
1 | 1INCHEUR
2 | 1INCHUSD
3 | AAVEAUD
4 | AAVEETH
5 | AAVEEUR
6 | AAVEGBP
7 | AAVEUSD
8 | AAVEXBT
9 | ADAAUD
10 | ADAETH
(10 rows)
The goal
Для заданного размера временного интервала (например, месяц, день, час и т. Д.) Я хотел бы найти следующее для всех сделок:
- Самая высокая цена
- Самая низкая цена
- Цена в начале временного интервала
- Цена в конце временного интервала
Другими словами, я хотел бы генерировать данные о свечах из сделок.
Подход 1
Давайте начнем с простого. Это будет генерировать высокие и низкие цены на биткойн по месяцам:
var symbol_id = db.Symbols.First(symbol => symbol.Name == "XBTUSD").Id;
var result =
db.Trades
.Where(trade => trade.SymbolId == symbol_id)
.GroupBy(trade => new { Year = trade.TimeStamp.Year, Month = trade.TimeStamp.Month })
.Select(group =>
new Candle()
{
DateTime = new DateTime(group.Key.Year, group.Key.Month, 1),
High = group.Max(trade => trade.Price),
Low = group.Min(trade => trade.Price)
});
Это работает и быстро! Он обрабатывает 41,695,261 сделки и занимает 12 секунд в моей системе.
Пример вывода (открытие и закрытие отсутствуют, поэтому они есть 0.00 ):
2013-10 0.00 207.30 122.00 0.00
2013-11 0.00 1198.80 201.04 0.00
2013-12 0.00 1120.00 380.00 0.00
2014-01 0.00 1019.00 742.28 0.00
2014-02 0.00 835.00 450.00 0.00
2014-03 0.00 720.00 436.07 0.00
2014-04 0.00 583.75 353.76 0.00
2014-05 0.00 649.97 422.19 0.00
2014-06 0.00 677.91 525.00 0.00
2014-07 0.00 658.87 566.75 0.00
Код: KrakenCandles/Program.cs.
Подход 2
Хорошо, давайте Open Close также заполним свойства и. Просто, не так ли? 🙂
var result =
db.Trades
.Where(trade => trade.SymbolId == symbol_id)
.GroupBy(trade => new { Year = trade.TimeStamp.Year, Month = trade.TimeStamp.Month })
.Select(group =>
new Candle()
{
DateTime = new DateTime(group.Key.Year, group.Key.Month, 1),
High = group.Max(trade => trade.Price),
Low = group.Min(trade => trade.Price),
Open = group.OrderBy(trade => trade.TimeStamp).First().Price,
Close = group.OrderBy(trade => trade.TimeStamp).Last().Price
});
О — о — о:
Ключевое сообщение:
Unhandled exception. Npgsql.PostgresException (0x80004005): 42803: subquery uses ungrouped column "t.TimeStamp" from outer query
Подход 3
Давайте возьмем подход 2 и добавим в ToList :
var result =
db.Trades
.Where(trade => trade.SymbolId == symbol_id).ToList()
.GroupBy(trade => new { Year = trade.TimeStamp.Year, Month = trade.TimeStamp.Month })
.Select(group =>
new Candle()
{
DateTime = new DateTime(group.Key.Year, group.Key.Month, 1),
High = group.Max(trade => trade.Price),
Low = group.Min(trade => trade.Price),
Open = group.OrderBy(trade => trade.TimeStamp).First().Price,
Close = group.OrderBy(trade => trade.TimeStamp).Last().Price
});
Это работает! 😮 Но это медленно. Занимает около 6 минут (по сравнению с 11 секундами для подхода 1!).
Версия SQL
Как работает версия SQL? Ну, во-первых, у меня есть запрос, который, похоже, работает, но он не так лаконичен, как подход 3. Вот оно что:
SELECT
date, trade_count, low, high,
open_price,
close_price
FROM
(
SELECT
date, trade_count, low, high,
trades_open."Price" AS open_price,
trades_close."Price" AS close_price,
ROW_NUMBER() OVER (PARTITION BY date) AS n
FROM
(
SELECT
date_trunc('month', "TimeStamp") AS date,
COUNT(*) AS trade_count,
min( "TimeStamp" ) open_timestamp,
max( "TimeStamp" ) close_timestamp,
min( "Price" ) AS low,
max( "Price" ) AS high
FROM "Trades"
WHERE "SymbolId" = (SELECT "Id" FROM "Symbols" WHERE "Name" = 'XBTUSD')
GROUP BY date_trunc('month', "TimeStamp")
) tbl
JOIN "Trades" trades_open ON tbl.open_timestamp = trades_open."TimeStamp"
JOIN "Trades" trades_close ON tbl.close_timestamp = trades_close."TimeStamp"
WHERE
trades_open."SymbolId" = (SELECT "Id" FROM "Symbols" WHERE "Name" = 'XBTUSD')
AND
trades_close."SymbolId" = (SELECT "Id" FROM "Symbols" WHERE "Name" = 'XBTUSD')
) AS tbl_2
WHERE n = 1
ORDER BY date;
В моей системе это занимает около 2 минут.
Так что, похоже, теоретически, есть место для улучшения версии EF Core. Я даже не уверен, что версия SQL в любом случае является наиболее оптимальной.
Вопрос
Есть ли способ сделать что-то вроде подхода 3 быстрее? Или, возможно, какой-то другой подход полностью?
Примечания
Весь проект доступен здесь: