我需要一些帮助或指针与一个单一的查询，这可以建立一个利润/亏损为每个单独的股票交易基于先进先出(先进先出)的原则，在下面的数据集上。应该兼容SQL Server 2016+和Azure SQL。
示例数据如下:
| StockName | TransactionDate | TranCode | Quantity | PriceUSD | TotalAmountUSD | StockABC | 2017-12-11 11:16:11.000 | 买 | 2364年,444444年 | 0, 114323 | 270, 310382 |
|---|---|---|---|---|---|
| StockXYZ | 2017-12-11 11:16:11.000 | 买 | 2364年,444444年 | 0, 114323 | 270, 310382 |
| StockABC | 2017-12-14 14:16:24.000 | 销售 | 1000 | 0, 158849 | 158849 |
| StockXYZ | 2017-12-14 14:16:24.000 | 销售 | 1000 | 0, 158849 | 158849 |
| StockABC | 2017-12-14 19:38:46.000 | 销售 | 700 | 0, 198934 | 139, 2538 |
| StockXYZ | 2017-12-14 19:38:46.000 | 销售 | 700 | 0, 198934 | 139, 2538 |
| StockABC | 2017-12-15 09:38:09.000 | 销售 | 664, 4444444 | 0, 207171 | 137, 65362 |
这里有一个简单的方法(sort of)，它使用一个称为dbo.fnTally的计数函数为每个项目或"共享"生成1行。的数量。'buy_cte'和'sell_cte'都使用CROSS APPLYdbo。(1, s.Quantity)来展开每个quantity项的行。此外，两个CTE分配一个有序的row_number称为"trans_rn"。通过在trans_rn上连接两个CTE,FIFO的盈利能力可以通过简单的价格求和来计算。像这样
dbo.fnTally
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8;
;with
buy_cte(Currency, TransactionDate, Price, trans_rn) as (
select Currency, TransactionDate, Price,
row_number() over (partition by Currency order by TransactionDate)
from #Stock s
cross apply dbo.fnTally(1, s.Quantity) fn
where TranCode='BUY'),
sell_cte(Currency, TransactionDate, Price, trans_rn) as (
select Currency, TransactionDate, Price,
row_number() over (partition by Currency order by TransactionDate)
from #Stock s
cross apply dbo.fnTally(1, s.Quantity) fn
where TranCode='SELL')
select s.Currency, s.TransactionDate,
cast(sum(b.price) as decimal(14,2)) buy_sum,
cast(sum(s.price) as decimal(14,2)) sell_sum,
cast(sum(s.price-b.price) as decimal(14,2)) profit_sum,
count(*) q_sold
from buy_cte b
join sell_cte s on b.Currency=s.Currency
and b.trans_rn=s.trans_rn
and b.TransactionDate<s.TransactionDate
group by s.Currency, s.TransactionDate
order by TransactionDate;
Currency TransactionDate buy_sum sell_sum profit_sum q_sold
StockABC 2017-12-14 14:16:00.000 114.32 158.85 44.53 1000
StockABC 2017-12-14 19:38:00.000 80.03 139.25 59.23 700
StockABC 2017-12-15 09:38:00.000 75.91 137.56 61.65 664
StockABC 2017-12-21 21:02:00.000 163.50 158.24 -5.26 334
StockABC 2017-12-26 10:45:00.000 195.32 174.92 -20.40 399
StockABC 2017-12-30 11:34:00.000 244.76 316.88 72.12 500
StockABC 2018-01-03 17:45:00.000 9.79 21.13 11.34 20
StockABC 2018-03-21 15:42:00.000 633.45 319.92 -313.53 1472
StockABC 2018-04-16 07:53:00.000 73.92 105.97 32.05 450
StockABC 2018-04-24 20:15:00.000 75.44 136.96 61.52 460
StockABC 2018-07-22 17:08:00.000 66.53 87.15 20.62 500
StockABC 2018-08-05 17:34:00.000 133.00 127.40 -5.60 1000
感谢您的反馈，让我做一些修改。
SELECT Currency,TransactionDate,
SUM(CASE WHEN TranCode = 'SELL' THEN CostBasis * (-1) ELSE CostBasis END )OVER(PARTITION BY Currency ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL
FROM #Stock
GROUP BY Currency, TransactionDate, CostBasis, TranCode