创建一个列并在 SQL 中使用先前创建的列的值。

huangapple go评论53阅读模式
英文:

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 &gt; 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(&#39;tempdb..#Transaction2&#39;) is not null
DROP TABLE #Transaction2
SELECT
	*
	, 0 AS averagePrice
	,ISNULL((SELECT SUM(b.Volume) FROM Table1 b WHERE Table1.RowNumber &gt; b.RowNumber ),0) AS prevNetVolume
	
 INTO #Transaction2
FROM Table1

DECLARE @count INT = (SELECT COUNT(*) FROM #Transaction2)
DECLARE @counter INT =1;
WHILE @counter &lt;@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 &gt; 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 &gt; 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 &gt; 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

fiddle

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 &gt; 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

答案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 &lt;= @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 &gt; 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

huangapple
  • 本文由 发表于 2023年3月4日 01:26:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75630159.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定