2023年3月4日 01:26:46go评论62阅读模式

英文:

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

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

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

问题

答案1

答案2

答案3

pandas read_sql。如何使用日期字段的where子句查询

如何在PostgreSQL中正确创建JSON？

FormatException在从Flutter应用程序发送数据到PHP后端时发生。

ORA-00933: SQL命令未正确结束。我在JAVA中执行此查询。

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论