计算加权平均购买价格(重置的交易表)

更新时间：2022-12-31 10:14:41

您可以使用可以使用以下查询获得:

声明@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                 |
+--------+--------------+---------------+-----------------------+

上一篇 : ：应用内购买显示警告在 iTunes 连接中的应用内不可用下一篇 : 尝试测试应用内购买时，不允许创建iTunes帐户

计算加权平均购买价格(重置的交易表)

相关阅读

技术问答最新文章