ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Непонятки в LAG
5 сообщений из 5, страница 1 из 1
Непонятки в LAG
    #40138246
Фотография dab2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dab2
Участник
Всем привет! Задача расчёта финансового результата(дохода/убытка) по методу FIFO после каждой сделки.
Финансовый результат возникает в момент совершения сделки, обратной текущей накопленной позиции, например клиент совершил 3 покупки на 300 бумаг, потом 1 продажу на 100 бумаг, потом опять покупку на 100 бумаг, потом ещё продажу на 200 бумаг.
В данном случае финансовый результат будет возникать при каждой продаже - прибыль или убыток. При покупках финансового результата нет.

Задачу надо решить задачу без циклов и курсоров.

Кто подскажет, почему в операторе [previous_cumulative_quantity] = LAG(cumulative_quantity) OVER... возникает ошибка Недопустимое имя столбца "cumulative_quantity".
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
CREATE TABLE deal ( [date_oper] datetime, [client] varchar(64), [share] VARCHAR(20), [quantity] decimal(19,7), price decimal(19,7))
INSERT INTO deal
VALUES
( '2022-01-13T17:36:32', '016084', 'VTBR', 15000000, 0.006013),
( '2022-01-13T17:36:37', '016084', 'VTBR', 10000000, 0.006014),
( '2022-01-13T17:36:39', '016084', 'VTBR', 10000000, 0.006015),
( '2022-01-13T17:36:40', '016084', 'VTBR', 7000000, 0.006012),
( '2022-01-13T21:23:07', '016084', 'VTBR', -40000000, 0.006020),
( '2022-01-13T21:23:10', '016084', 'VTBR', 1000000, 0.006016),
( '2022-01-13T21:23:12', '016084', 'VTBR', 10000000, 0.006018),
( '2022-01-13T21:23:18', '016084', 'VTBR', -1500000, 0.006012),
( '2022-01-13T21:23:19', '016084', 'VTBR', -1000000, 0.006013),
( '2022-01-13T21:23:26', '016084', 'VTBR', -500000, 0.006010),
( '2022-01-13T21:23:28', '016084', 'VTBR', -2000000, 0.006025),
( '2022-01-13T21:23:33', '016084', 'VTBR', -2000000, 0.006030),
( '2022-01-13T21:23:34', '016084', 'VTBR', -6000000, 0.006030),
('2022-01-14T10:10:34', '016085', 'ADT', 0.5367, 80.15),
('2022-01-14T11:15:18', '016085', 'ADT', 0.483, 81.15),
('2022-01-14T11:15:19', '016085', 'ADT', 0.283, 81.15),
('2022-01-14T12:15:20', '016085', 'ADT', -1.0889, 82.25),
('2022-01-14T12:15:21', '016085', 'ADT', -0.2138, 82.28);

--суммарный доход по VTBR = -361
--по ADT = -1.98

WITH CTE AS (
  -- Расчет накопленной позиции по каждой бумаге
  SELECT
    date_oper,
    client,
    share,
    quantity,
    price,
    cumulative_quantity = SUM(quantity) OVER (PARTITION BY client, share ORDER BY date_oper),
    cumulative_cost = SUM(quantity * [price]) OVER (PARTITION BY [client], [share] ORDER BY [date_oper]),
    [previous_cumulative_quantity] = LAG(cumulative_quantity) OVER (PARTITION BY [client], [share] ORDER BY [date_oper])
  FROM
    deal
)
SELECT
  [date_oper],
  [client],
  [share],
  [quantity],
  [price],
  CASE
    WHEN [quantity] < 0 THEN
      CASE
        WHEN [previous_cumulative_quantity] IS NULL OR [previous_cumulative_quantity] >= ABS([quantity]) THEN [quantity] * price - LAG(cumulative_cost) OVER (PARTITION BY client, share ORDER BY date_oper)
        ELSE NULL
      END
    ELSE NULL
  END AS financial_result
FROM
  CTE
ORDER BY
  date_oper;

--В этом решении добавлено оконное выражение previous_cumulative_quantity, которое содержит накопленное количество для предыдущей операции. Затем используется вложенное условие CASE для проверки, есть ли соответствующая продажа для текущей покупки. Если предыдущее накопленное количество больше или равно абсолютному значению текущего количества, то рассчитывается финансовый результат. В противном случае результат остается пустым.
--Обратите внимание, что в этом решении предполагается, что покупки и продажи будут адекватно сопоставлены во времени. Если такая соответствующая продажа не найдена после определенного количества покупок, результаты могут быть некорректными.
...
Рейтинг: 0 / 0
29.11.2023, 23:00
| Ответить | Цитировать | Написать | Ответы  
Непонятки в LAG
    #40138247
Silver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Silver
Участник
dab2 [игнорируется] 

Он вычисляется в соседнем поле и еще не существует.
Примерно так, но логику не проверял, только добавил уровень
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
--суммарный доход по VTBR = -361
--по ADT = -1.98
WITH CTE
AS (
  -- Расчет накопленной позиции по каждой бумаге
  SELECT date_oper
    ,client
    ,share
    ,quantity
    ,price
    ,cumulative_quantity = SUM(quantity) OVER (
      PARTITION BY client
      ,share ORDER BY date_oper
      )
    ,cumulative_cost = SUM(quantity * [price]) OVER (
      PARTITION BY [client]
      ,[share] ORDER BY [date_oper]
      ) --,
    --[previous_cumulative_quantity] = LAG(cumulative_quantity) OVER (PARTITION BY [client], [share] ORDER BY [date_oper])
  FROM deal
  )
SELECT [date_oper]
  ,[client]
  ,[share]
  ,[quantity]
  ,[price]
  ,CASE
    WHEN [quantity] < 0
      THEN CASE
          WHEN [previous_cumulative_quantity] IS NULL
            OR [previous_cumulative_quantity] >= ABS([quantity])
            THEN [quantity] * price - LAG(cumulative_cost) OVER (
                PARTITION BY client
                ,share ORDER BY date_oper
                )
          ELSE NULL
          END
    ELSE NULL
    END AS financial_result
FROM (
  SELECT *
  FROM (
    SELECT *
      ,[previous_cumulative_quantity] = LAG(cumulative_quantity) OVER (
        PARTITION BY [client]
        ,[share] ORDER BY [date_oper]
        )
    FROM CTE
    ) t
  ) tt
ORDER BY date_oper;
...
Рейтинг: 0 / 0
30.11.2023, 06:25
| Ответить | Цитировать | Написать | Ответы  
Непонятки в LAG
    #40138248
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
Silver [игнорируется] 

надо просто вместо LAG использовать ту же SUM, только с ROWS BETWEEN UNBOUND PRECEEDING AND 1 PRECEEDING
...
Рейтинг: 0 / 0
01.12.2023, 11:00
| Ответить | Цитировать | Написать | Ответы  
Непонятки в LAG
    #40138257
Фотография dab2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dab2
Участник
ShIgor [игнорируется] 

Пока результат не совпадает с целевым:
--суммарный доход по VTBR = -361
--по ADT = -1.98
...
Рейтинг: 0 / 0
03.12.2023, 21:00
| Ответить | Цитировать | Написать | Ответы  
Непонятки в LAG
    #40138266
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
dab2 [игнорируется] 

у вас не с LAGом проблемы, а с математикой и пониманием FIFO
но задача очень интересная, не уверен, что ее вообще можно решить в одном запросе.
...
Изменено: 08.12.2023, 13:24 - ShIgor
Рейтинг: 0 / 0
08.12.2023, 13:21
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Непонятки в LAG
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]