Блог

Для расчета скорректированной базы затрат (ACB) это текущая сумма либо Цены * Количества Комиссии, либо Предыдущего ACB/Доли * Количества, в зависимости от того, является ли это продажей или покупкой.

У меня есть следующая таблица с именем transaction_t :

Для расчета текущей «скорректированной базы затрат» у меня есть следующий запрос:

 SELECT 
  SUM(CASE 
        WHEN T.Action in ("buy", "reinvest") THEN T.Quantity
        ​WHEN Action = "sell" THEN -T.Quantity
      END
  ) OVER (ORDER BY T.Date) AS quantity_balance,
  SUM(CASE 
        WHEN T.Action in ("buy", "reinvest") THEN T.Quantity * T.Price   T.Commission
        WHEN T.Action = "sell" THEN T.Quantity * ***(previous_total_acb / previous_quantity_balance)***[1]
      END
  ) OVER (ORDER BY T.Date) AS total_acb
  FROM transaction_t AS T;
 

Этот запрос не работает. Потому что псевдокод previous_total_acb / previous_quantity_balance ссылается на столбец, который определяется в этой оконной функции.

Как заставить это работать в SQLite?

Примечание [1]: Здесь previous_total_acb это псевдонимный код, я намерен ссылаться на сам столбец, the total_acb . Но sqlite, похоже, не поддерживает такую обратную ссылку. Это previous_quantity_balance относится к столбцу «брат», который также создается оконной функцией, the quantity_balance . Похоже, это тоже не работает.

Вы могли бы использовать CTE (или подзапрос) для подготовки своих обратных вычислений, а затем ссылаться на них в своем окончательном запросе. Я не понимаю ваш настоящий sql. Вот упрощенная версия, которая может не иметь смысла, но просто демонстрирует структуру:

Этот первый CTE предназначен только для того, чтобы дать некоторые значения, которые в вашем случае взяты из вашей таблицы:

 WITH TBL(d, a, q, p)
AS
(
   SELECT * FROM (
              VALUES (20210102, 'buy',  150, 110.21),
                     (20210121, 'buy',  380, 106.87),
                     (20210321, 'sell', 360, 104.33)
                 )
),
 

так что на самом деле вы бы начали здесь с WITH

 SRC AS
(
    SELECT d,
           a,
           q,
           p,
           q*p AS cost,
           lag(q*p) OVER (ORDER BY d) AS prev_cost
      FROM TBL
)
 

Что приводит к этому:

  -------- ---- --- ------ ------- --------- 
|d       |a   |q  |p     |cost   |prev_cost|
 -------- ---- --- ------ ------- --------- 
|20210102|buy |150|110.21|16531.5|NULL     |
|20210121|buy |380|106.87|40610.6|16531.5  |
|20210321|sell|360|104.33|37558.8|40610.6  |
 -------- ---- --- ------ ------- --------- 
 

и оттуда вы можете использовать текущие и предыдущие значения для создания вычисляемого поля

 SELECT d, cost, cost-prev_cost AS diff FROM SRC ORDER BY d;
 

что (как я уже сказал, просто для демонстрации и не имеет здесь никакого реального значения) дает

  -------- ------- ---------- 
|d       |cost   |diff      |
 -------- ------- ---------- 
|20210102|16531.5|NULL      |
|20210121|40610.6|24079.1   |
|20210321|37558.8|-3051.79  |
 -------- ------- ---------- 
 

Таким образом, если собрать это вместе и предположить, что ваши данные хранятся в TBL нем, это будет выглядеть так

 WITH SRC AS
(
    SELECT d,
           a,
           q,
           p,
           q*p AS cost,
           lag(q*p) OVER (ORDER BY d) AS prev_cost
      FROM TBL
)
SELECT d, cost, cost-prev_cost AS diff FROM SRC ORDER BY d;
 

У вас может быть столько CTE, сколько необходимо для подготовки ваших данных «обратной связи». Не уверен, но, похоже, вам может понадобиться, по крайней мере, еще один средний предварительный CTE в вашем случае для расчета previous_total_acb , прежде чем вы сможете использовать его в своем окончательном запросе.