Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Непонятки в LAG / 5 сообщений из 5, страница 1 из 1
29.11.2023, 23:00
    #40138246
dab2
dab2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Непонятки в LAG
Всем привет! Задача расчёта финансового результата(дохода/убытка) по методу 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
| Ответить | Цитировать | Написать | Ответы  
30.11.2023, 06:25
    #40138247
Silver
Silver
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Непонятки в LAG
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
| Ответить | Цитировать | Написать | Ответы  
01.12.2023, 11:00
    #40138248
ShIgor
ShIgor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Непонятки в LAG
Silver [игнорируется] 

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

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

у вас не с LAGом проблемы, а с математикой и пониманием FIFO
но задача очень интересная, не уверен, что ее вообще можно решить в одном запросе.
...
Изменено: 08.12.2023, 13:24 - ShIgor
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Непонятки в LAG / 5 сообщений из 5, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 120ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=2186881]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 22ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 2ms
others: 12ms
total:  120ms
x
x
Закрыть


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