计算加权平均购买价格(重置的交易表) [英] Calculate weighted average purchase price (trades table with reset)
本文介绍了计算加权平均购买价格(重置的交易表)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试使用 Microsoft SQL Server 2016 计算股票的加权平均价格.与我所问的不同之处在于，当所有股票都已卖出时，应重新设置加权价格
问题
NewPrice 在以下交易表中不正确
预期产出
Row NewPrice1 186.40002 186.40003 183.08004 183.08005 183.08006 183.08007 183.08008 183.08007 183.0800查询
SELECT *,PriceRunningTotalFinal =总和(案例当 QuantityRunningTotal = 0 THEN -1 * PriceRunningTotal当单位<0 THEN 0 ELSE 购买价格 * 单位 END) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序),当结果 1.QuantityRunningTotal <= 0 然后 0 时的情况别的总和(案例当 QuantityRunningTotal <= 0 THEN -1 * PriceRunningTotal当单位<0 THEN 0 ELSE 购买价格 * 单位 END) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序)/结果1.QuantityRunningTotal以新价格结束从(SELECT Id、UserCompetitionId、StockId、类型、单位、PurchasePrice、CreatedOn、QuantityRunningTotal = SUM(Units) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序),PriceRunningTotal = SUM(CASE WHEN Units <= 0 THEN 0 ELSE PurchasePrice * Units END) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序)来自贸易) AS 结果1WHERE UserCompetitionId =@UserCompetitionId AND StockId = 122我遇到的问题是 newPrice.
从第 4 行开始，价格应该是 183.08000
这是因为之前的所有股票都已售出(QuantityRunningTotal 为 0).因此加权购买价格的计算需要重新开始，不考虑前几行
更新
查看 Andrei Odegov 的答案，该解决方案有效.但是我没有提到的一件事是我想获得每一行的调整后的加权价格如下图所示:
加权平均价格我试图获得平均价格，它有点用，但我不确定这是正确的方法吗?
声明@Trade TABLE(ID INT 身份，UserCompetitionId INT DEFAULT(92170),StockId INT 默认(122)，输入 INT,单位 INT,购买价格 NUMERIC(8, 4));INSERT @Trade(类型、单位、购买价格)值 (10, 42, 186.4),(20, -42, 183.08),(10, 40, 183.08),(20, -5, 183.92),(20, -1, 181.68),(20, -1, 181.68),(20, -1, 181.68),(20, 17, 181.68),(20, -10, 181.68);和一个 AS(选择*,QuantityRunningTotal = SUM(Units) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序)来自@Trade),BAS (选择*,Grp = SUM(IIF(QuantityRunningTotal - Units <= 0, 1, 0)) 超过(按 UserCompetitionId、StockId 划分按 Id 行无界排序)从 A)SELECT *, TotalAmount/数量从 (选择*,单位 * PurchasePrice AS PurchaseAmount,NewPrice = FIRST_VALUE(PurchasePrice) OVER(按 UserCompetitionId、StockId、Grp 划分按 Id 行无界排序),row_number() over (partition by UserCompetitionId, StockId order by Id desc) as Seq,TotalAmount = SUM(CASE WHEN Units <0 THEN 0 ELSe Units * PurchasePrice END) OVER(按 UserCompetitionId、StockId、Grp 划分按 Id 行无界排序),数量 = SUM(CASE WHEN Units <0 THEN 0 ELSe Units END) OVER(按 UserCompetitionId、StockId、Grp 划分按 Id 行无界排序)从 B)结果WHERE Result.Seq = 1 AND QuantityRunningTotal >0按 StockId 订购；UPDATE2(获取总购买价格 50.65 美元)
声明@Trade TABLE(ID INT 身份，UserCompetitionId INT DEFAULT(92170),StockId INT 默认(122)，单位 INT,购买价格 NUMERIC(8, 4));INSERT @Trade(Units, PurchasePrice)值 (100, 97.8774),(200, 97.89),(-300, 97.8858),(600, 48.9429),(100, 60.9),(-600, 60.395);和一个 AS(选择*,金额 = SUM(IIF(Units > 0, Units * PurchasePrice, 0)) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序),数量 = SUM(IIF(Units > 0, Units, 0)) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序),Qty2 = SUM(Units) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序),TotalAmount = SUM(Units * PurchasePrice) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序)来自@Trade)选择单位为金额，CAST(PurchasePrice AS NUMERIC(6,2)) AS [购买价格],CAST(ABS(Units * PurchasePrice) AS NUMERIC(12,2)) AS [采购金额],CAST(Amount/Qty AS NUMERIC(6, 2)) AS调整后的购买价格"，数量 2,总金额，IIF(Qty2 = 0, 0,TotalAmount/Qty2) 作为平均值从 A按 ID 排序；任何想法将不胜感激
解决方案
您可以使用 可以使用以下查询获得:
声明@Trade TABLE(ID INT 身份，UserCompetitionId INT DEFAULT(92170),StockId INT 默认(122)，单位 INT,购买价格 NUMERIC(8, 4));INSERT @Trade(Units, PurchasePrice)值 (100, 97.8774),(200, 97.89),(-300, 97.8858),(600, 48.9429),(100, 60.9),(-600, 60.395);和一个 AS(选择*,金额 = SUM(IIF(Units > 0, Units * PurchasePrice, 0)) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序),数量 = SUM(IIF(Units > 0, Units, 0)) OVER(按 UserCompetitionId、StockId 划分按 Id 行无界排序)来自@Trade)选择单位为金额，CAST(PurchasePrice AS NUMERIC(6,2)) AS [购买价格],CAST(ABS(Units * PurchasePrice) AS NUMERIC(12,2)) AS [采购金额],CAST(Amount/Qty AS NUMERIC(6, 2)) AS调整后的购买价格"从 A按 ID 排序；输出:
+--------+--------------+---------------+------------------------+|金额|购买价格|购买金额|调整后的购买价格|+--------+--------------+--------------+-----------------------+|100 |97,88 |9787,74 |97,88 ||200 |97,89 |19578,00 |97,89 ||-300 |97,89 |29365,74 |97,89 ||600 |48,94 |29365,74 |65,26 ||100 |60,90 |6090,00 |64,82 ||-600 |60,40 |36237,00 |64,82 |+--------+--------------+--------------+-----------------------+I am trying to calculate the weighted average price for stocks using Microsoft SQL Server 2016. The difference with what I'm asking is that when all stocks have been sold the weighted price should be reset
Issue
NewPrice is incorrect in the following trades table
Expected Output
Row NewPrice
1 186.4000
2 186.4000
3 183.0800
4 183.0800
5 183.0800
6 183.0800
7 183.0800
8 183.0800
7 183.0800
Query
SELECT *,
PriceRunningTotalFinal =
SUM(CASE
WHEN QuantityRunningTotal = 0 THEN -1 * PriceRunningTotal
WHEN Units < 0 THEN 0 ELSE PurchasePrice * Units END) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
CASE WHEN Results1.QuantityRunningTotal <= 0 then 0
else
SUM(CASE
WHEN QuantityRunningTotal <= 0 THEN -1 * PriceRunningTotal
WHEN Units < 0 THEN 0 ELSE PurchasePrice * Units END) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
) / Results1.QuantityRunningTotal
end as NewPrice
FROM
(
SELECT Id, UserCompetitionId, StockId, Type, Units, PurchasePrice, CreatedOn,
QuantityRunningTotal = SUM(Units) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
PriceRunningTotal = SUM(CASE WHEN Units <= 0 THEN 0 ELSE PurchasePrice * Units END) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM Trade
) AS Results1
WHERE UserCompetitionId =@UserCompetitionId AND StockId = 122
The problem I'm having is with the newPrice.
From Row 4 onwards the price should be 183.08000
This is because all previous stocks have been sold (QuantityRunningTotal is 0). Therefore the weighted purchase price calculation needs to start again and not take into account the previous rows
UPDATE
Looking at Andrei Odegov answer, the solution works. But one thing I failed to mention is that I would like to get the adjusted weighted prices for each row
As can be seen in this screenshot:
Weighted average price I tried to get the average price, it sort of works but I'm not sure it's the right way to go about it?
DECLARE @Trade TABLE(
Id INT IDENTITY,
UserCompetitionId INT DEFAULT(92170),
StockId INT DEFAULT(122),
Type INT,
Units INT,
PurchasePrice NUMERIC(8, 4)
);
INSERT @Trade(Type, Units, PurchasePrice)
VALUES (10, 42, 186.4),
(20, -42, 183.08),
(10, 40, 183.08),
(20, -5, 183.92),
(20, -1, 181.68),
(20, -1, 181.68),
(20, -1, 181.68),
(20, 17, 181.68),
(20, -10, 181.68);
WITH
A AS(
SELECT
*,
QuantityRunningTotal = SUM(Units) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM @Trade
),
B AS (
SELECT
*,
Grp = SUM(IIF(QuantityRunningTotal - Units <= 0, 1, 0)) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM A
)
SELECT *, TotalAmount / Qty
FROM (
SELECT
*,
Units * PurchasePrice AS PurchaseAmount,
NewPrice = FIRST_VALUE(PurchasePrice) OVER
(
PARTITION BY UserCompetitionId, StockId, Grp
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
row_number() over (partition by UserCompetitionId, StockId order by Id desc) as Seq,
TotalAmount = SUM(CASE WHEN Units < 0 THEN 0 ELSe Units * PurchasePrice END) OVER
(
PARTITION BY UserCompetitionId, StockId, Grp
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
Qty = SUM(CASE WHEN Units < 0 THEN 0 ELSe Units END) OVER
(
PARTITION BY UserCompetitionId, StockId, Grp
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM B
)AS Result
WHERE Result.Seq = 1 AND QuantityRunningTotal > 0
ORDER BY StockId;
UPDATE2 (Gets the overall purchase price $50.65)
DECLARE @Trade TABLE(
Id INT IDENTITY,
UserCompetitionId INT DEFAULT(92170),
StockId INT DEFAULT(122),
Units INT,
PurchasePrice NUMERIC(8, 4)
);
INSERT @Trade(Units, PurchasePrice)
VALUES (100, 97.8774),
(200, 97.89),
(-300, 97.8858),
(600, 48.9429),
(100, 60.9),
(-600, 60.395);
WITH
A AS(
SELECT
*,
Amount = SUM(IIF(Units > 0, Units * PurchasePrice, 0)) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
Qty = SUM(IIF(Units > 0, Units, 0)) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
Qty2 = SUM(Units) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
TotalAmount = SUM(Units * PurchasePrice) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM @Trade
)
SELECT
Units AS Amount,
CAST(PurchasePrice AS NUMERIC(6,2)) AS [Purchase Price],
CAST(ABS(Units * PurchasePrice) AS NUMERIC(12,2)) AS [Purchase Amount],
CAST(Amount / Qty AS NUMERIC(6, 2)) AS "Adjusted Purchase Price",
Qty2,
TotalAmount,
IIF(Qty2 = 0, 0,TotalAmount/ Qty2) AS Average
FROM A
ORDER BY Id;
Any ideas would be greatly appreciated
解决方案
You can get the desired result using the FIRST_VALUE function.
WITH
A AS(
SELECT
*,
QuantityRunningTotal = SUM(Units) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM @Trade
),
B AS (
SELECT
*,
Grp = SUM(IIF(QuantityRunningTotal - Units <= 0, 1, 0)) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM A
)
SELECT
*,
NewPrice = FIRST_VALUE(PurchasePrice) OVER
(
PARTITION BY UserCompetitionId, StockId, Grp
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM B
ORDER BY Id;
Output:
+----+-------------------+---------+------+-------+---------------+----------------------+-----+----------+
| Id | UserCompetitionId | StockId | Type | Units | PurchasePrice | QuantityRunningTotal | Grp | NewPrice |
+----+-------------------+---------+------+-------+---------------+----------------------+-----+----------+
| 1 | 92170 | 122 | 10 | 42 | 186,4000 | 42 | 1 | 186,4000 |
| 2 | 92170 | 122 | 20 | -42 | 183,0800 | 0 | 1 | 186,4000 |
| 3 | 92170 | 122 | 10 | 40 | 183,0800 | 40 | 2 | 183,0800 |
| 4 | 92170 | 122 | 20 | -5 | 183,9200 | 35 | 2 | 183,0800 |
| 5 | 92170 | 122 | 20 | -1 | 181,6800 | 34 | 2 | 183,0800 |
| 6 | 92170 | 122 | 20 | -1 | 181,6800 | 33 | 2 | 183,0800 |
| 7 | 92170 | 122 | 20 | -1 | 181,6800 | 32 | 2 | 183,0800 |
| 8 | 92170 | 122 | 20 | -2 | 181,6800 | 30 | 2 | 183,0800 |
| 9 | 92170 | 122 | 20 | -30 | 181,6800 | 0 | 2 | 183,0800 |
| 10 | 92170 | 122 | 20 | 17 | 181,6800 | 17 | 3 | 181,6800 |
| 11 | 92170 | 122 | 20 | -10 | 181,6800 | 7 | 3 | 181,6800 |
+----+-------------------+---------+------+-------+---------------+----------------------+-----+----------+
Demo:
https://rextester.com/IMN29774.
Update:
The figures of this your screenshot can be obtained using the following query:
DECLARE @Trade TABLE(
Id INT IDENTITY,
UserCompetitionId INT DEFAULT(92170),
StockId INT DEFAULT(122),
Units INT,
PurchasePrice NUMERIC(8, 4)
);
INSERT @Trade(Units, PurchasePrice)
VALUES (100, 97.8774),
(200, 97.89),
(-300, 97.8858),
(600, 48.9429),
(100, 60.9),
(-600, 60.395);
WITH
A AS(
SELECT
*,
Amount = SUM(IIF(Units > 0, Units * PurchasePrice, 0)) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
),
Qty = SUM(IIF(Units > 0, Units, 0)) OVER
(
PARTITION BY UserCompetitionId, StockId
ORDER BY Id ROWS UNBOUNDED PRECEDING
)
FROM @Trade
)
SELECT
Units AS Amount,
CAST(PurchasePrice AS NUMERIC(6,2)) AS [Purchase Price],
CAST(ABS(Units * PurchasePrice) AS NUMERIC(12,2)) AS [Purchase Amount],
CAST(Amount / Qty AS NUMERIC(6, 2)) AS "Adjusted Purchase Price"
FROM A
ORDER BY Id;
Output:
+--------+--------------+---------------+-----------------------+
| Amount |Purchase Price|Purchase Amount|Adjusted Purchase Price|
+--------+--------------+---------------+-----------------------+
| 100 | 97,88 | 9787,74 | 97,88 |
| 200 | 97,89 | 19578,00 | 97,89 |
| -300 | 97,89 | 29365,74 | 97,89 |
| 600 | 48,94 | 29365,74 | 65,26 |
| 100 | 60,90 | 6090,00 | 64,82 |
| -600 | 60,40 | 36237,00 | 64,82 |
+--------+--------------+---------------+-----------------------+
这篇关于计算加权平均购买价格(重置的交易表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
