英文:
Create a column and use previous value of created column in SQL
问题
我有一个表格,我想要计算已购资产的平均价格,数值如下:
行号 | 价格 | 数量 | 前净数量 |
---|---|---|---|
1 | 100 | 100 | 0 |
2 | 200 | 100 | 100 |
3 | 100 | 100 | 200 |
4 | 100 | -100 | 300 |
5 | 200 | 100 | 200 |
6 | 100 | -200 | 300 |
7 | 300 | 100 | 100 |
价格和数量是当前购买的数据,prevNetVolume是前面所有行的买入和卖出数量之和。我想要计算每行的平均价格。这是平均价格的计算公式:
if (数量 > 0)
{
// 这是一笔买入交易
平均价格 = (价格 * 数量 + prevNetVolume * prevAveragePrice) / (prevNetVolume + 数量)
}
else
{
// 这是一笔卖出交易
平均价格 = prevAveragePrice;
}
prevAveragePrice 是前一行的 averagePrice,但由于我还没有这一列,我不知道如何同时创建和访问其前一个值。最终,我们需要一个表格如下:
行号 | 价格 | 数量 | 前净数量 | 平均价格 |
---|---|---|---|---|
1 | 100 | 100 | 0 | 100 |
2 | 200 | 100 | 100 | 150 |
3 | 100 | 100 | 200 | 133.33 |
4 | 100 | -100 | 300 | 133.33 |
5 | 200 | 100 | 200 | 155.55 |
6 | 100 | -200 | 300 | 155.55 |
7 | 300 | 100 | 100 | 227.77 |
如何在SQL中创建这一列呢?
我尝试使用临时表、while循环和LAG函数来解决这个问题,首先将averagePrice设置为0:
IF OBJECT_ID('tempdb..#Transaction2') is not null
DROP TABLE #Transaction2
SELECT
*
, 0 AS averagePrice
,ISNULL((SELECT SUM(b.Volume) FROM Table1 b WHERE Table1.RowNumber > b.RowNumber ),0) AS prevNetVolume
INTO #Transaction2
FROM Table1
DECLARE @count INT = (SELECT COUNT(*) FROM #Transaction2)
DECLARE @counter INT = 1;
WHILE @counter < @count
BEGIN
UPDATE #Transaction2
SET averagePrice = (SELECT ((Volume*price)+(ISNULL(LAG(averagePrice) OVER (ORDER BY RowNumber),0)*prevNetVolume))/(prevNetVolume + Volume) )
WHERE @counter = RowNumber
SET @counter += 1
END
SELECT * FROM #Transaction2
这里的问题是在更新中的LAG函数无法正确读取前一行的averagePrice的更新值。有没有一种方法可以获取已更新的值?这是fiddle的链接:fiddle
英文:
I have a table where I want to calculate average price of bought assets, values is like this:
RowNumber | price | volume | prevNetVolume |
---|---|---|---|
1 | 100 | 100 | 0 |
2 | 200 | 100 | 100 |
3 | 100 | 100 | 200 |
4 | 100 | -100 | 300 |
5 | 200 | 100 | 200 |
6 | 100 | -200 | 300 |
7 | 300 | 100 | 100 |
price and volume is data of current purchase and prevNetVolume is sum of all buy and sell volumes of previous rows. I want to calculate average price for each row. This is the average price formula:
if (volume > 0)
{
// it is a buy transaction
averagePrice = (price * volume) + (prevNetVolume * prevAveragePrice))/(prevNetVolume + volme)
}
else
{
// it is a sell transaction
averagePrice = prevAveragePrice;
}
and prevAveragePrice is averagePrice of previous row, but as I don't have that column yet, I don't know how to create and access its previous value at the same time. At the end we must have a table like this:
RowNumber | price | volume | prevNetVolume | averagePrice |
---|---|---|---|---|
1 | 100 | 100 | 0 | 100 |
2 | 200 | 100 | 100 | 150 |
3 | 100 | 100 | 200 | 133.33 |
4 | 100 | -100 | 300 | 133.33 |
5 | 200 | 100 | 200 | 155.55 |
6 | 100 | -200 | 300 | 155.55 |
7 | 300 | 100 | 100 | 227.77 |
How can I create this column in SQL?
I tried to solve the problem using a temp table, a while loop and lag function, first I added averagePrice as 0:
IF OBJECT_ID('tempdb..#Transaction2') is not null
DROP TABLE #Transaction2
SELECT
*
, 0 AS averagePrice
,ISNULL((SELECT SUM(b.Volume) FROM Table1 b WHERE Table1.RowNumber > b.RowNumber ),0) AS prevNetVolume
INTO #Transaction2
FROM Table1
DECLARE @count INT = (SELECT COUNT(*) FROM #Transaction2)
DECLARE @counter INT =1;
WHILE @counter <@count
BEGIN
UPDATE #Transaction2
SET averagePrice = (SELECT ((Volume*price)+(ISNULL(LAG(averagePrice) OVER (ORDER BY RowNumber),0)*prevNetVolume))/(prevNetVolume + Volume) )
WHERE @counter = RowNumber
SET @counter += 1
END
SELECT * FROM #Transaction2
the problem here is the LAG inside the update, does not correctly read the updated value of averagePrice of previous row. is there a way to get the updated value? here is the fiddle: fiddle
答案1
得分: 0
以下是代码部分的中文翻译:
你可以使用 `CASE WHEN` 子句
CASE WHEN (volume > 0) THEN
(price * volume) + (prevNetVolume * prevAveragePrice))/(prevNetVolume + volme)
else
prevAveragePrice
END averagePrice
正如您所要求的仅获取值的方式,您可以使用窗口函数来获取SUM和AVG
以及提到的CTE来获取之前计算的值
由于您没有指定您使用的数据库系统,我选择了SQL Server,但几乎所有数据库系统都支持CTE和窗口函数
WITH CTE AS (SELECT
[RowNumber], [price], [volume],
SUM([volume]) OVER( ORDER BY [RowNumber]) NetVolume
,AVG([price]) OVER( ORDER BY [RowNumber]) avg_price
FROM table1)
SELECT
[RowNumber], [price], [volume],
CASE WHEN LAG(NetVolume) OVER (ORDER BY [RowNumber]) IS NULL THEN 0
ELSE LAG(NetVolume) OVER (ORDER BY [RowNumber]) END prevNetVolume,
avg_price
FROM CTE
| RowNumber | price | volume | prevNetVolume | avg\_price |
| ---------:|-----:|------:|-------------:|---------:|
| 1 | 100.00 | 100 | 0 | 100.000000 |
| 2 | 200.00 | 100 | 100 | 150.000000 |
| 3 | 100.00 | 100 | 200 | 133.333333 |
| 4 | 100.00 | -100 | 300 | 125.000000 |
| 5 | 200.00 | 100 | 200 | 140.000000 |
| 6 | 100.00 | -200 | 300 | 133.333333 |
| 7 | 300.00 | 100 | 100 | 157.142857 |
[fiddle](https://dbfiddle.uk/vWtIZPeb)
添加您的公式后,数字将如下:
CREATE TABLE Table1
([RowNumber] int, [price] DECIMAL (10,2), [volume] int)
;
INSERT INTO Table1
([RowNumber], [price], [volume])
VALUES
(1, 100, 100),
(2, 200, 100),
(3, 100, 100),
(4, 100, -100),
(5, 200, 100),
(6, 100, -200),
(7, 300, 100)
;
status
7 rows affected
WITH CTE AS (SELECT
[RowNumber], [price], [volume],
SUM([volume]) OVER( ORDER BY [RowNumber]) NetVolume
,AVG([price]) OVER( ORDER BY [RowNumber]) avg_price
FROM table1),
cte2 as
(SELECT
[RowNumber], [price], [volume],
CASE WHEN LAG(NetVolume) OVER (ORDER BY [RowNumber]) IS NULL THEN 0
ELSE LAG(NetVolume) OVER (ORDER BY [RowNumber]) END prevNetVolume,
CASE WHEN LAG(avg_price) OVER (ORDER BY [RowNumber]) IS NULL THEN 0
ELSE LAG(avg_price) OVER (ORDER BY [RowNumber]) END prevAveragePrice
FROM CTE)
SELECT
[RowNumber], [price], [volume],
prevNetVolume,
CASE WHEN (volume > 0) THEN
((price * volume) + (prevNetVolume * prevAveragePrice))/(prevNetVolume + volume)
else
prevAveragePrice
END averagePrice
FROM CTE2
| RowNumber | price | volume | prevNetVolume | averagePrice |
| ---------:|-----:|------:|-------------:|------------:|
| 1 | 100.00 | 100 | 0 | 100.000000 |
| 2 | 200.00 | 100 | 100 | 150.000000 |
| 3 | 100.00 | 100 | 200 | 133.333333 |
| 4 | 100.00 | -100 | 300 | 133.333333 |
| 5 | 200.00 | 100 | 200 | 150.000000 |
| 6 | 100.00 | -200 | 300 | 140.000000 |
| 7 | 300.00 | 100 | 100 | 216.666666 |
[fiddle](https://dbfiddle.uk/6oylO5Si)
英文:
You can use a CASE WHEN
clause
CASE WHEN (volume > 0) THEN
(price * volume) + (prevNetVolume * prevAveragePrice))/(prevNetVolume + volme)
else
prevAveragePrice
END averagePrice
As you asked only for how to get the values, you can use window funcz´tion to get the SUM and AVG
AND the mentioned CTE to get the previous calculated values
As you ddidn't specify the databse system you are using i choose SQL server, but alomos all databases systems suport CTE and window functions
WITH CTE AS (SELECT
[RowNumber], [price], [volume],
SUM([volume]) OVER( ORDER BY [RowNumber]) NetVolume
,AVG([price]) OVER( ORDER BY [RowNumber]) avg_price
FROM table1)
SELECT
[RowNumber], [price], [volume],
CASE WHEN LAG(NetVolume) OVER (ORDER BY [RowNumber]) IS NULL THEN 0
ELSE LAG(NetVolume) OVER (ORDER BY [RowNumber]) END prevNetVolume,
avg_price
FROM CTE
RowNumber | price | volume | prevNetVolume | avg_price |
---|---|---|---|---|
1 | 100.00 | 100 | 0 | 100.000000 |
2 | 200.00 | 100 | 100 | 150.000000 |
3 | 100.00 | 100 | 200 | 133.333333 |
4 | 100.00 | -100 | 300 | 125.000000 |
5 | 200.00 | 100 | 200 | 140.000000 |
6 | 100.00 | -200 | 300 | 133.333333 |
7 | 300.00 | 100 | 100 | 157.142857 |
adding your formula, the numbers will end up
CREATE TABLE Table1
([RowNumber] int, [price] DECIMAL (10,2), [volume] int)
;
INSERT INTO Table1
([RowNumber], [price], [volume])
VALUES
(1, 100, 100),
(2, 200, 100),
(3, 100, 100),
(4, 100, -100),
(5, 200, 100),
(6, 100, -200),
(7, 300, 100)
;
7 rows affected
WITH CTE AS (SELECT
[RowNumber], [price], [volume],
SUM([volume]) OVER( ORDER BY [RowNumber]) NetVolume
,AVG([price]) OVER( ORDER BY [RowNumber]) avg_price
FROM table1),
cte2 as
(SELECT
[RowNumber], [price], [volume],
CASE WHEN LAG(NetVolume) OVER (ORDER BY [RowNumber]) IS NULL THEN 0
ELSE LAG(NetVolume) OVER (ORDER BY [RowNumber]) END prevNetVolume,
CASE WHEN LAG(avg_price) OVER (ORDER BY [RowNumber]) IS NULL THEN 0
ELSE LAG(avg_price) OVER (ORDER BY [RowNumber]) END prevAveragePrice
FROM CTE)
SELECT
[RowNumber], [price], [volume],
prevNetVolume,
CASE WHEN (volume > 0) THEN
((price * volume) + (prevNetVolume * prevAveragePrice))/(prevNetVolume + volume)
else
prevAveragePrice
END averagePrice
FROM CTE2
RowNumber | price | volume | prevNetVolume | averagePrice |
---|---|---|---|---|
1 | 100.00 | 100 | 0 | 100.000000 |
2 | 200.00 | 100 | 100 | 150.000000 |
3 | 100.00 | 100 | 200 | 133.333333 |
4 | 100.00 | -100 | 300 | 133.333333 |
5 | 200.00 | 100 | 200 | 150.000000 |
6 | 100.00 | -200 | 300 | 140.000000 |
7 | 300.00 | 100 | 100 | 216.666666 |
答案2
得分: 0
以下是 SQL 查询的翻译部分,不包括表格内容和附加问题:
SELECT m1.*,
COALESCE(LAG(volume) OVER (ORDER BY RowNumber), 0) as PerviousVolume,
volume * price as TotalPriceForThisLine,
SUM(volume * price) OVER (ORDER BY RowNumber) as CumulativeTotalPrice,
SUM(volume) OVER (ORDER BY RowNumber) as CumulativeVolume,
SUM(volume * price) OVER (ORDER BY RowNumber) /
SUM(volume) OVER (ORDER BY RowNumber) as AveragePrice
FROM mytable m1
请注意,您提到您的平均价格与我的不同。要查找错误,您可能需要检查数据或查询逻辑以确定问题的原因。
英文:
SELECT m1.*,
COALESCE(LAG(volume) OVER (ORDER BY RowNumber),0) as PerviousVolume,
volume * price as TotalPriceForThisLine,
SUM(volume * price) OVER (order by RowNumber) as CumulativeTotalPrice,
SUM(volume) OVER (order by RowNumber) as CumulatieveVolume,
SUM(volume * price) OVER (order by RowNumber) /
SUM(volume) OVER (order by RowNumber) as AveragePrice
FROM mytable m1
RowNumber | price | volume | prevNetVolume | PerviousVolume | TotalPriceForThisLine | CumulativeTotalPrice | CumulatieveVolume | AveragePrice |
---|---|---|---|---|---|---|---|---|
1 | 100 | 100 | 0 | 0 | 10000 | 10000 | 100 | 100.0000 |
2 | 200 | 100 | 100 | 100 | 20000 | 30000 | 200 | 150.0000 |
3 | 100 | 100 | 200 | 100 | 10000 | 40000 | 300 | 133.3333 |
4 | 100 | -100 | 300 | 100 | -10000 | 30000 | 200 | 150.0000 |
5 | 200 | 100 | 200 | -100 | 20000 | 50000 | 300 | 166.6667 |
6 | 100 | -200 | 300 | 100 | -20000 | 30000 | 100 | 300.0000 |
7 | 300 | 100 | 100 | -200 | 30000 | 60000 | 200 | 300.0000 |
NOTE: My Average price is different than yours, can you find the error that I, oy you, made 😁😉 ?
see: DBFIDDLE
答案3
得分: 0
我找到了解决方案:
DECLARE @count INT = (SELECT COUNT(*) FROM my_table)
DECLARE @counter INT = 1;
DECLARE @prevAvgPrice FLOAT = 0;
WHILE @counter <= @count
BEGIN
DECLARE @price FLOAT = (SELECT price FROM my_table WHERE RowNumber = @counter)
DECLARE @volume FLOAT = (SELECT volume FROM my_table WHERE RowNumber = @counter)
DECLARE @prevNetVolume FLOAT = (SELECT prevNetVolume FROM my_table WHERE RowNumber = @counter)
DECLARE @averagePrice FLOAT;
IF @volume > 0
BEGIN
-- 这是一笔买入交易
SET @averagePrice = ((@price * @volume) + (@prevNetVolume * @prevAvgPrice)) / (@prevNetVolume + @volume)
END
ELSE
BEGIN
-- 这是一笔卖出交易
SET @averagePrice = @prevAvgPrice;
END
-- 用计算出的平均价格更新当前行
UPDATE my_table SET averagePrice = @averagePrice WHERE RowNumber = @counter;
-- 为下一次迭代更新前一次的平均价格
SET @prevAvgPrice = @averagePrice;
SET @counter += 1;
END
SELECT * FROM my_table;
这里是示例链接:示例链接
英文:
I found the solution:
DECLARE @count INT = (SELECT COUNT(*) FROM my_table)
DECLARE @counter INT = 1;
DECLARE @prevAvgPrice FLOAT = 0;
WHILE @counter <= @count
BEGIN
DECLARE @price FLOAT = (SELECT price FROM my_table WHERE RowNumber = @counter)
DECLARE @volume FLOAT = (SELECT volume FROM my_table WHERE RowNumber = @counter)
DECLARE @prevNetVolume FLOAT = (SELECT prevNetVolume FROM my_table WHERE RowNumber = @counter)
DECLARE @averagePrice FLOAT;
IF @volume > 0
BEGIN
-- it is a buy transaction
SET @averagePrice = ((@price * @volume) + (@prevNetVolume * @prevAvgPrice)) / (@prevNetVolume + @volume)
END
ELSE
BEGIN
-- it is a sell transaction
SET @averagePrice = @prevAvgPrice;
END
-- update the current row with the calculated average price
UPDATE my_table SET averagePrice = @averagePrice WHERE RowNumber = @counter;
-- update the previous average price for the next iteration
SET @prevAvgPrice = @averagePrice;
SET @counter += 1;
END
SELECT * FROM my_table;
here is the fiddle: fiddle
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论