Запрос на отображение отработанных кредитов из таблицы транзакций

Я работаю с таблицей, содержащей кредитные транзакции, где я хочу показать, какие кредиты были потрачены при продаже.

В таблице:

Credits добавляются сущностью, использующей уникальный код сущности (записанный в столбце GivenByUserCode)
У кредитных добавок всегда есть такой код.
Кредиты, которые были потрачены, всегда будут иметь отрицательное значение.
Пропущенные кредиты не будут иметь код сущности (значение GivenByUserCode равно null).

Используя приведенные выше данные в качестве примера, если пользователь совершил покупку на 2018-01-02 отчет должен показать все эти кредиты, полученные из BM01. Что добавляет сложности, так это то, что покупка может быть разделена на несколько дополнений, см. 2018-02-03 на 2018-02-03 которая делится на 3 дополнения.

Я думаю, что решение будет иметь какое-то отношение к использованию cte и более, но у меня нет опыта их использования. Я нашел аналогичную (не такую же) проблему на SqlServerCentral.

Любая помощь/направление будут оценены наиболее высоко.

Вход и DDL

DECLARE @CreditLogs TABLE(CreditLogId int not null identity(1,1), Credits INT NOT NULL, OccurredOn DATETIME2(7) NOT NULL, GivenByUserCode VARCHAR(100) NULL)

INSERT INTO @CreditLogs (Credits, OccurredOn, GivenByUserCode) VALUES
  (10,  '2018-01-01', 'BM01')
, (10,  '2018-01-01', 'BM01')
, (-10, '2018-01-02', NULL)
, (-5,  '2018-01-04', NULL)
, (5,   '2018-02-01', 'SP99')
, (40,  '2018-02-02', 'BM02')
, (-40, '2018-02-03', NULL)
, (-4,  '2018-03-05', NULL)

Ввод в виде таблицы

CreditLogId | Credits | OccurredOn | GivenByUserCode
------------+---------+------------+----------------
          1 |      10 | 2018-01-01 |            BM01
          2 |      10 | 2018-01-01 |            BM01
          3 |     -10 | 2018-01-02 |            NULL
          4 |      -5 | 2018-01-04 |            NULL
          5 |       5 | 2018-02-01 |            SP99
          6 |      40 | 2018-02-02 |            BM02
          7 |     -40 | 2018-02-03 |            NULL
          8 |      -4 | 2018-03-05 |            NULL

Ожидаемый результат

SELECT *
FROM (VALUES
     (3, '2018-01-02', 10, 'BM01')
    ,(4, '2018-01-04', 5, 'BM01')
    ,(7, '2018-02-03', 5, 'BM01')
    ,(7, '2018-02-03', 5, 'SP99')
    ,(7, '2018-02-03', 30, 'BM02')
    ,(8, '2018-03-05', 4, 'BM02')
) expectedOut (CreditLogId, OccurredOn, Credits, GivenByUserCode)

Производит выход

CreditLogId | Occurred on | Credits | GivenByUserCode
------------+-------------+---------+----------------
          3 |  2018-01-02 |      10 |            BM01
          4 |  2018-01-04 |       5 |            BM01
          7 |  2018-02-03 |       5 |            BM01
          7 |  2018-02-03 |       5 |            SP99
          7 |  2018-02-03 |      30 |            BM02
          8 |  2018-03-05 |       4 |            BM02

Код до сих пор

Это не так много, и я не уверен, куда идти отсюда.

WITH totals AS (
    SELECT CreditLogId, OccurredOn, credits, sum(credits) OVER(ORDER BY OccurredOn) AS TotalSpent
    FROM @CreditLogs
    WHERE Credits < 0
)
SELECT *
FROM totals

Дополнительное разъяснение

Ожидаемый результат - для каждой потраченной суммы кредита, в которой были получены эти кредиты. Кредиты расходуются на основе первого в первом (FIFO). Здесь объяснение каждого значения в образце выводится в надежде, что это прояснит желаемый результат.

Для расходования 10 кредитов (идентификатор кредитного журнала 3) можно проследить до добавления из кредитного лога 1
Для расходования 5 кредитов (идентификатор 4 кредитного журнала) можно проследить до добавления из идентификатора кредитного лога 2 (поскольку идентификатор кредитного журнала 1 был "использован")
Для расходования 40 кредитов в кредитном журнале идентификатор 7 можно проследить до
- Остаток от кредитного журнала id 2, 5 кредитов
- ID кредитного журнала 5 (добавление 5)
- Кредитный лог файл 6 (добавление 40 и 10 оставшихся)
Для расходования 4 кредитов в кредитном журнале 8 используется бланк кредитного лога 6

Обратите внимание, что общий остаток в 6 кредитов остается, баланс не должен быть нулевым, но никогда не будет отрицательным, поскольку пользователи могут тратить только то, что у них есть.

Ответ 1

попробуй это:

WITH Credits_added AS (
    SELECT CreditLogId, OccurredOn, credits
    , SUM(credits) OVER (ORDER BY CreditLogId) - credits AS b --before
    , SUM(credits) OVER (ORDER BY CreditLogId) AS a --after
    , GivenByUserCode
    FROM @CreditLogs
    WHERE Credits > 0)
, Credits_spent AS (
    SELECT CreditLogId, OccurredOn, credits
    , SUM(credits) OVER (ORDER BY CreditLogId) * -1 + credits AS b
    , SUM(credits) OVER (ORDER BY CreditLogId) * -1 AS a
    FROM @CreditLogs
    WHERE Credits < 0)
SELECT s.CreditLogId, s.OccurredOn
, CASE WHEN a.a > s.a THEN s.a ELSE a.a END - CASE WHEN a.b > s.b THEN a.b ELSE s.b END AS Credits 
, a.GivenByUserCode
FROM Credits_added AS a
INNER JOIN Credits_spent AS s ON a.a > s.b AND s.a > a.b

Ответ 2

поскольку вы упомянули, что в год будет от 4 до 5 миллионов записей, даже если это может быть сделано по запросу, это будет медленный запрос.

Я бы предложил иметь другую таблицу, такую как creditSpent которая содержит (PurchaseCreditLogId, additionCreditLogId, Amount)

И во время вставки покупок найдите все записи, подсчитайте сумму, которая должна быть уменьшена от каждого, и сохраните эту информацию в этой таблице

Затем, когда вы запускаете отчет, вы можете сделать простой запрос в этой таблице