不知能满足不
select securityid, least(abs(c), abs(d)), 长仓, 短仓
from (select securityid, sum(a) c, sum(b) d, 长仓, 短仓
from (select securityid,
case
when qty0 then
sum(qty) over(partition by securityid, qty)
else
0
end b,
case
when sum(qty) over(partition by securityid) > 0 then
sum(qty) over(partition by securityid)
else
0
end 长仓,
case
when sum(qty) over(partition by securityid) 0 Then BuyQty+SellQty
Else 0 End BuyQty,
Case When BuyQty+SellQty>0 Then 0
Else BuyQty+SellQty End SellQty
From
(Select SecurityID,sum(BuyQty) BuyQty,sum(SellQty) SellQty From
(select SecurityID,
case when Qty > 0 Thenqty Else 0 End BuyQty ,
Case When qty =abs(SellQty) Then abs(SellQty)
Else abs(BuyQty) End SellQty
From
(Select SecurityID,sum(BuyQty) BuyQty,sum(SellQty) SellQty From
(select SecurityID,
case when Qty > 0 Thenqty Else 0 End BuyQty ,
Case When qty < 0 Thenqty Else 0 End SellQty
From orders )
Group BySecurityID)
|
