ALTER TRIGGER [dbo].[trigger] ON [dbo].[trades]
AFTER INSERT
AS
BEGIN
DECLARE @Clusters TABLE (
id INT NOT NULL,
period SMALLDATETIME NOT NULL,
price DECIMAL(18,6) NOT NULL,
quantity BIGINT NOT NULL,
buyquantity BIGINT NOT NULL,
[count] INT NOT NULL,
maxtrade INT NOT NULL
);

DECLARE @DayClusters TABLE (
id INT NOT NULL,
period SMALLDATETIME NOT NULL,
price DECIMAL(18,6) NOT NULL,
quantity BIGINT NOT NULL,
buyquantity BIGINT NOT NULL,
[count] INT NOT NULL,
maxtrade INT NOT NULL
);

DECLARE @Candles TABLE (
Id INT NOT NULL,
Period SMALLDATETIME NOT NULL,
OpnPrice DECIMAL(18,6) NOT NULL,
ClsPrice DECIMAL(18,6) NOT NULL,
MinPrice DECIMAL(18,6) NOT NULL,
MaxPrice DECIMAL(18,6) NOT NULL,
Quantity BIGINT NOT NULL,
Volume DECIMAL(18,6) NOT NULL,
BuyQuantity BIGINT NOT NULL,
BuyVolume DECIMAL(18,6) NOT NULL,
OI INT NOT NULL
);

DECLARE @DayCandles TABLE (
Id INT NOT NULL,
Period SMALLDATETIME NOT NULL,
OpnPrice DECIMAL(18,6) NOT NULL,
ClsPrice DECIMAL(18,6) NOT NULL,
MinPrice DECIMAL(18,6) NOT NULL,
MaxPrice DECIMAL(18,6) NOT NULL,
Quantity BIGINT NOT NULL,
Volume DECIMAL(18,6) NOT NULL,
BuyQuantity BIGINT NOT NULL,
BuyVolume DECIMAL(18,6) NOT NULL,
OI INT NOT NULL
);

DECLARE @table1 TABLE (
ID INT NOT NULL,
[number] BIGINT NOT NULL,
TradeDate DATETIME NOT NULL,
Price DECIMAL(18, 6) NOT NULL,
Quantity INT NOT NULL,
Volume DECIMAL(18, 6) NOT NULL,
OI INT NOT NULL,
Direction TINYINT NOT NULL,
INDEX ix_b NONCLUSTERED (id, TradeDate),
INDEX ix_c NONCLUSTERED (id, TradeDate, price)
);

DECLARE @max TABLE (
id INT NOT NULL,
MaxNumber BIGINT NOT NULL,
[date] DATETIME NOT NULL
);

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

/****** Скрипт для команды SelectTopNRows из среды SSMS ******/
INSERT INTO @table1
SELECT ID,
[number],
CONVERT(SMALLDATETIME, ROUND(CONVERT(FLOAT, tradedate) * 1440.0, 0, 1) / 1440.0) AS TradeDate,
Price,
Quantity,
Volume,
OI,
Direction
FROM inserted where TradeDate > '20240812';

INSERT INTO @max
SELECT id, MAX([number]), MAX(tradedate)
FROM @table1
GROUP BY id;

UPDATE MaxTrades
SET MaxNumber = x.MaxNumber,
[MaxTime] = x.[date]
FROM @max AS x
WHERE x.id = MaxTrades.id AND x.MaxNumber > MaxTrades.MaxNumber;

INSERT INTO MaxTrades (id, MaxNumber, [MaxTime])
SELECT id, MaxNumber, [date]
FROM @max AS s
WHERE NOT EXISTS (
SELECT *
FROM MaxTrades
WHERE MaxTrades.id = s.id
);

INSERT INTO @Candles
SELECT id,
t.rounddate,
OpnPrice,
ClsPrice,
MinPrice,
MaxPrice,
quantity,
m.Value AS Value,
buyquantity,
m.BuyValue AS BuyValue,
OI
FROM (
SELECT id, tradedate AS rounddate
FROM @table1
GROUP BY id, tradedate
) AS t
CROSS APPLY (
SELECT TOP 1 price AS clsprice
FROM @table1 AS d
WHERE id = t.ID AND tradedate = t.rounddate
ORDER BY [number] DESC
) AS cls
CROSS APPLY (
SELECT TOP 1 price AS opnprice
FROM @table1 AS d
WHERE id = t.ID AND tradedate = t.rounddate
ORDER BY [number] ASC
) AS o
CROSS APPLY (
SELECT MIN(price) AS minprice,
MAX(price) AS maxprice,
SUM(Volume) AS value,
SUM(Volume * Direction) AS buyvalue,
SUM(quantity) AS quantity,
SUM(quantity * Direction) AS buyquantity,
MAX(OI) AS OI
FROM @table1 AS dd
WHERE id = t.ID AND tradedate = t.rounddate
) AS m;

INSERT INTO @DayCandles
SELECT id,
t.period,
OpnPrice,
ClsPrice,
MinPrice,
MaxPrice,
quantity,
m.volume AS Volume,
buyquantity,
m.BuyVolume AS BuyVolume,
OI
FROM (
SELECT id, CAST(period AS DATE) AS period
FROM @Candles
GROUP BY id, CAST(period AS DATE)
) AS t
CROSS APPLY (
SELECT TOP 1 clsprice AS clsprice
FROM @Candles AS d
WHERE id = t.ID AND CAST(period AS DATE) = t.period
ORDER BY period DESC
) AS cls
CROSS APPLY (
SELECT TOP 1 opnprice AS opnprice
FROM @Candles AS d
WHERE id = t.ID AND CAST(period AS DATE) = t.period
ORDER BY period ASC
) AS o
CROSS APPLY (
SELECT MIN(minprice) AS minprice,
MAX(maxprice) AS maxprice,
SUM(volume) AS volume,
SUM(buyVolume) AS buyVolume,
SUM(quantity) AS quantity,
SUM(buyquantity) AS buyquantity,
MAX(OI) AS OI
FROM @Candles AS dd
WHERE id = t.ID AND CAST(period AS DATE) = t.period
) AS m
OPTION (RECOMPILE);

INSERT INTO @Clusters
SELECT id,
TradeDate,
Price,
SUM(quantity),
SUM(quantity * direction),
COUNT(quantity),
CASE
WHEN -MIN(quantity * (direction * 2 — 1)) > MAX(quantity * direction) THEN MIN(quantity * (direction * 2 — 1))
ELSE MAX(quantity * direction)
END
FROM @table1
GROUP BY id, TradeDate, price;

INSERT INTO @DayClusters
SELECT id,
CAST(period AS DATE),
Price,
SUM(quantity),
SUM(buyquantity),
SUM([count]),
CASE
WHEN -MIN(maxtrade) > MAX(maxtrade) THEN MIN(maxtrade)
ELSE MAX(maxtrade)
END
FROM @Clusters
GROUP BY id, CAST(period AS DATE), price;

MERGE INTO Candles WITH (ROWLOCK, UPDLOCK) AS target
USING @Candles AS source
ON target.id = source.id AND target.period = source.period
WHEN MATCHED THEN
UPDATE SET
MaxPrice = CASE WHEN source.maxprice > target.MaxPrice THEN source.maxprice ELSE target.MaxPrice END,
MinPrice = CASE WHEN source.minprice < target.MinPrice THEN source.minprice ELSE target.MinPrice END,
ClsPrice = source.clsprice,
Volume = target.Volume + source.volume,
BuyVolume = target.BuyVolume + source.BuyVolume,
Quantity = target.Quantity + source.Quantity,
BuyQuantity = target.BuyQuantity + source.BuyQuantity,
OI = source.OI
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Period, OpnPrice, ClsPrice, MinPrice, MaxPrice, Quantity, Volume, BuyQuantity, BuyVolume, OI)
VALUES (source.Id, source.Period, source.OpnPrice, source.ClsPrice, source.MinPrice, source.MaxPrice, source.Quantity, source.Volume, source.BuyQuantity, source.BuyVolume, source.OI);

MERGE INTO DayCandles WITH (ROWLOCK, UPDLOCK) AS target
USING @DayCandles AS source
ON target.id = source.id AND target.period = source.period
WHEN MATCHED THEN
UPDATE SET
MaxPrice = CASE WHEN source.maxprice > target.MaxPrice THEN source.maxprice ELSE target.MaxPrice END,
MinPrice = CASE WHEN source.minprice < target.MinPrice THEN source.minprice ELSE target.MinPrice END,
ClsPrice = source.clsprice,
Volume = target.Volume + source.volume,
BuyVolume = target.BuyVolume + source.BuyVolume,
Quantity = target.Quantity + source.Quantity,
BuyQuantity = target.BuyQuantity + source.BuyQuantity,
OI = source.OI
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Period, OpnPrice, ClsPrice, MinPrice, MaxPrice, Quantity, Volume, BuyQuantity, BuyVolume, OI)
VALUES (source.Id, source.Period, source.OpnPrice, source.ClsPrice, source.MinPrice, source.MaxPrice, source.Quantity, source.Volume, source.BuyQuantity, source.BuyVolume, source.OI);

MERGE INTO Clusters WITH (ROWLOCK, UPDLOCK) AS target
USING @Clusters AS source
ON target.id = source.id AND target.period = source.period AND target.price = source.price
WHEN MATCHED THEN
UPDATE SET
quantity = target.quantity + source.quantity,
buyquantity = target.buyquantity + source.buyquantity,
[count] = target.[count] + source.[count],
maxtrade = CASE
WHEN abs( source.maxtrade) > abs(target.maxtrade) THEN source.maxtrade
ELSE target.maxtrade
END
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, period, price, quantity, buyquantity, [count], maxtrade)
VALUES (source.id, source.period, source.price, source.quantity, source.buyquantity, source.[count], source.maxtrade);

MERGE INTO DayClusters WITH (ROWLOCK, UPDLOCK) AS target
USING @DayClusters AS source
ON target.id = source.id AND target.period = source.period AND target.price = source.price
WHEN MATCHED THEN
UPDATE SET
quantity = target.quantity + source.quantity,
buyquantity = target.buyquantity + source.buyquantity,
[count] = target.[count] + source.[count],
maxtrade = CASE
WHEN abs(source.maxtrade) > abs(target.maxtrade) THEN source.maxtrade
ELSE target.maxtrade
END
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, period, price, quantity, buyquantity, [count], maxtrade)
VALUES (source.id, source.period, source.price, source.quantity, source.buyquantity, source.[count], source.maxtrade);

END;

 

Ибо нет ничего дороже здоровья и времени — как базового ресурса.