Блог

Главная - Вопросы по программированию - Условное списание средств с кредитов с использованием SQL

Условное списание средств с кредитов с использованием SQL

#sql #oracle #oracle11g

Вопрос:

У меня есть одна таблица криптотранзакций в приведенной ниже структуре, в которой хранятся транзакции.

ID ТРАНЗАКЦИЯ_ТИПЕИД ИМЯ ТРАНЗАКЦИИ сумма
1 101 биткоин-получено 5
2 102 биткоин-майнинг 20
3 103 биткоин-переведенный -5
4 104 биткойн-потерян -10
5 101 биткоин-получено 55
6 102 биткоин-майнинг 8
7 104 биткойн-потерян -16
8 103 биткоин-переведенный -5

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

Ниже приведен ожидаемый результат

ID ТРАНЗАКЦИЯ_ТИПЕИД ИМЯ ТРАНЗАКЦИИ сумма
1 101 биткоин-получено 0
2 102 биткоин-майнинг 0
5 101 биткоин-получено 49
6 102 биткоин-майнинг 3

Комментарии:

1. Можете ли вы показать случай, когда поведение FIFO четко определено?

2. Отображение ожидаемых результатов для входного набора данных, НЕ указанного в вашем вопросе, крайне бесполезно. Если вашего первого примера недостаточно для демонстрации всех необходимых действий, отредактируйте свой вопрос, чтобы включить расширенный набор данных. Вопрос должен, по крайней мере, соответствовать самому себе.

3. @MatBailie Я изменил вопрос с более подробной информацией.

4. Разница между ожидаемым и фактическим объясняется порядком операций, как отмечено в ответе. Поведение FIFO (ответа) в настоящее время основано на received mined порядке и, а не на lost transferred порядке и, и transferred корректировки выполняются в первую очередь (в received mined порядке и). Когда у меня будет время, я попытаюсь устранить эту разницу.

5. Защищены ли данные от того, чтобы конечное состояние стало отрицательным? Например, могут ли данные когда-либо отображаться start with nothing, mine 10, then immediately transfer 15 ?

Ответ №1:

Вы можете использовать PIPELINED функцию и прочитать таблицу только один раз:

 CREATE FUNCTION process_cryptotransledger
  RETURN cryptotransactionledger_ttype PIPELINED
IS
  transactions cryptotransactionledger_ttype;
  loss_amount INT;
BEGIN
  SELECT cryptotransactionledger_type(
           id,
           transaction_typeid,
           transaction_name,
           amount
         )
  BULK COLLECT INTO transactions
  FROM   cryptotransactionledger
  ORDER BY id;
  
  
  FOR loss IN 1 .. transactions.COUNT
  LOOP
    IF transactions(loss).transaction_name
         IN ('bitcoin-received', 'bitcoin-mined')
    THEN
      CONTINUE;
    END IF;

    loss_amount := transactions(loss).amount;

    FOR gain IN 1 .. transactions.COUNT
    LOOP
      EXIT WHEN loss_amount >= 0;
      
      IF transactions(gain).amount <= 0
      OR (
         transactions(gain).transaction_name <> 'bitcoin-mined'
         AND transactions(loss).transaction_name = 'bitcoin-transferred'
      )
      THEN
        CONTINUE;
      END IF;
      
      IF -loss_amount >= transactions(gain).amount THEN
        loss_amount := loss_amount   transactions(gain).amount;
        transactions(gain).amount := 0;
      ELSE
        transactions(gain).amount := transactions(gain).amount   loss_amount;
        loss_amount := 0;
      END IF;
    END LOOP;
  END LOOP;

  FOR i IN 1 .. transactions.COUNT
  LOOP
    IF transactions(i).transaction_name
         IN ('bitcoin-received', 'bitcoin-mined')
    THEN
      PIPE ROW (transactions(i));
    END IF;
  END LOOP;
END;
/

После определения типов данных:

 CREATE TYPE cryptotransactionledger_type AS OBJECT(
  id                 INT,
  transaction_typeid INT,
  transaction_name   VARCHAR2(30),
  amount             INT
);

CREATE TYPE cryptotransactionledger_ttype
  AS TABLE OF cryptotransactionledger_type;

Затем для выборочных данных:

 CREATE TABLE cryptotransactionledger (
  id, transaction_typeid, transaction_name, amount
) AS
  SELECT 1, 101, 'bitcoin-received',      5 FROM DUAL UNION ALL
  SELECT 2, 102, 'bitcoin-mined',        20 FROM DUAL UNION ALL
  SELECT 3, 103, 'bitcoin-transferred',  -5 FROM DUAL UNION ALL
  SELECT 4, 104, 'bitcoin-lost',        -10 FROM DUAL UNION ALL
  SELECT 5, 101, 'bitcoin-received',     55 FROM DUAL UNION ALL
  SELECT 6, 102, 'bitcoin-mined',         8 FROM DUAL UNION ALL
  SELECT 7, 104, 'bitcoin-lost',        -16 FROM DUAL UNION ALL
  SELECT 8, 103, 'bitcoin-transferred',  -5 FROM DUAL;

Запрос:

 SELECT *
FROM   TABLE(process_cryptotransledger());

Выходы:

ID ТРАНЗАКЦИЯ_ТИПЕИД ИМЯ ТРАНЗАКЦИИ сумма
1 101 биткоин-получено 0
2 102 биткоин-майнинг 0
5 101 биткоин-получено 49
6 102 биткоин-майнинг 3

Обновить

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

 CREATE OR REPLACE FUNCTION process_cryptotransledger
  RETURN cryptotransactionledger_ttype PIPELINED
IS
  CURSOR transactions_cur IS
    SELECT cryptotransactionledger_type(
             id,
             transaction_typeid,
             transaction_name,
             amount
           )
    FROM   cryptotransactionledger
    ORDER BY id;

  transactions cryptotransactionledger_ttype;
  loss         cryptotransactionledger_type;
  gains        cryptotransactionledger_ttype := cryptotransactionledger_ttype();
  gain         PLS_INTEGER;
BEGIN
  OPEN transactions_cur;
  
  LOOP
    FETCH transactions_cur
    BULK COLLECT INTO transactions
    LIMIT 1000; -- Set the batch size to an appropriate level.
    
    EXIT WHEN transactions.COUNT = 0;
    
    FOR i IN 1 .. transactions.COUNT
    LOOP
      -- Process each item in the batch.

      IF transactions(i).transaction_name
           IN ('bitcoin-received', 'bitcoin-mined')
      THEN
        -- Store the gains.
        gains.EXTEND();
        gains(gains.LAST) :=  transactions(i);
        CONTINUE;
      END IF;

      -- Process each loss.
      loss := transactions(i);

      gain := gains.FIRST;
      WHILE gain IS NOT NULL AND gains(gain).amount = 0
      LOOP
        -- Pipe the fully processed gain rows
        PIPE ROW( gains(gain) );
        gains.DELETE(gain);
        gain := gains.FIRST;
      END LOOP;
      
      -- Update the first appropriate gain row(s) with the loss amount.
      WHILE gain IS NOT NULL AND loss.amount < 0
      LOOP
        IF gains(gain).amount > 0
        AND (
           gains(gain).transaction_name = 'bitcoin-mined'
           OR loss.transaction_name <> 'bitcoin-transferred'
        )
        THEN
          IF -loss.amount >= gains(gain).amount THEN
            loss.amount := loss.amount   gains(gain).amount;
            gains(gain).amount := 0;
          ELSE
            gains(gain).amount := gains(gain).amount   loss.amount;
            loss.amount := 0;
          END IF;
        END IF;
        gain := gains.NEXT(gain);
      END LOOP;
    END LOOP;
  END LOOP;
  
  CLOSE transactions_cur;

  -- Pipe the remaining gain rows.
  FOR i IN gains.FIRST .. gains.LAST
  LOOP
    PIPE ROW (gains(i));
  END LOOP;
END;
/

бд<>скрипка <>здесь

Комментарии:

1. Спасибо MT0 за то, что поделился этим подходом.

2.@MT0 — Будет ли это являться оптимизацией? dbfiddle.uk/… (Уменьшение количества проверенных и пропущенных записей, что делает вложенный цикл «менее треугольным»?)

3. @MatBailie Может быть…. это было очень похоже на мое первоначальное решение, а затем я переработал его в один запрос. Это может быть или не быть оптимизацией в зависимости от того, что является ограничивающим фактором: если ограничивающим фактором является время, затраченное на ввод-вывод, то выполнение двух вариантов может снизить производительность; если ограничивающим фактором является время обработки, а ввод-вывод не является существенным фактором, то это может быть быстрее. Я предполагаю, что (особенно для небольших наборов данных) время, затраченное на анализ запроса и ввод-вывод, будет более значительным, чем затраты на окончательную обработку. Вам нужно будет составить профиль обоих и проверить.

4. @MatBailie Я обновил его более оптимизированным (и более сложным) решением.

5. @rocketpicks — Я рекомендую протестировать этот обновленный ответ.

Ответ №2:

Допущения:

  1. Мы не можем добывать или получать меньше 0.
  2. Мы не можем переводить или терять суммы, которых у нас нет.

Было неясно, что включает в себя поведение FIFO. Здесь мог бы помочь лучший тестовый случай.

Вот обновленный тестовый пример с приведенными выше данными, а затем немного больший набор данных, плюс решение, которое пытается внедрить логику FIFO:

Обновленный тестовый случай с большим количеством данных и логикой FIFO

В следующем решении для выполнения этой работы используется несколько вычислений.

В cte1 мы выводим:

  1. run_mined — текущая сумма (в порядке идентификаторов) типа = 102 (добытые суммы)
  2. tot_xfer — общая сумма типа = 103 (переведенные суммы)
  3. tot_lost — общая сумма по типу = 104 (потерянные суммы)

Затем, поскольку переведенные суммы могут быть вычтены только из добытых сумм , мы делаем это следующим cte2 шагом, корректируя суммы добытых строк.

Если общая сумма перевода больше текущей текущей суммы добытой строки, эта сумма уменьшается до 0. Мы перевели всю эту сумму.

Если общая сумма перевода не превышает текущую текущую сумму добытых данных, мы вычитаем переведенную сумму, не превышающую текущую добытую сумму для этой строки.

Любая последующая добытая строка не затрагивается, так как дальнейших переводов не происходит.

В cte1b , мы вычисляем run2_in , что является обновленной текущей суммой обоих mined и received сумм. Обратите внимание, что mined суммы были скорректированы cte2 .

cte3 теперь выполняет расчет , аналогичный cte2 , но на этот раз корректирует оба типа received и mined (101 и 102) в порядке FIFO, исходя из общей оставшейся lost суммы.

Наконец, мы выбираем только полностью настроенные received и mined строки для отображения, а также соответствующие id , чтобы указать порядок, в котором выполнялись операции FIFO.

SQL:

 WITH cte1 AS (
         SELECT a.*
              , SUM(CASE WHEN (transaction_typeid = 102) THEN 1 ELSE 0 END * amount) OVER (ORDER BY id) AS run_mined   
              , SUM(CASE WHEN (transaction_typeid = 103) THEN 1 ELSE 0 END * amount) OVER ()            AS tot_xfer    
              , SUM(CASE WHEN (transaction_typeid = 104) THEN 1 ELSE 0 END * amount) OVER ()            AS tot_lost    
           FROM cryptotransactionledger a
          ORDER BY id
     )
   , cte2 AS (
         SELECT a.id, a.transaction_typeid,  a.transaction_name
              , CASE WHEN transaction_typeid <> 102        THEN amount
                     WHEN run_mined  <= ABS(tot_xfer )     THEN 0
                     WHEN run_mined    tot_xfer  >= amount THEN amount
                                                           ELSE run_mined    tot_xfer 
                 END AS amount
              , run_mined 
              , tot_xfer 
              , tot_lost 
              , amount AS amount1
           FROM cte1 a
     )
   , cte1b AS (
         SELECT a.*
              , SUM(CASE WHEN (transaction_typeid IN (101, 102)) THEN 1 ELSE 0 END * amount) OVER (ORDER BY id) AS run2_in     
           FROM cte2 a
     )
   , cte3 AS (
         SELECT a.id, a.transaction_typeid,  a.transaction_name
              , CASE WHEN transaction_typeid NOT IN (101, 102) THEN amount
                     WHEN run2_in    <= ABS(tot_lost )         THEN 0
                     WHEN run2_in      tot_lost  >= amount     THEN amount
                                                               ELSE run2_in      tot_lost 
                 END AS amount
              , run_mined 
              , tot_xfer 
              , tot_lost 
              , run2_in
              , amount1
              , amount AS amount2
           FROM cte1b a
     )
SELECT id, transaction_name, amount
  FROM cte3
 WHERE transaction_typeid IN (101, 102)
 ORDER BY id
;

Результат с использованием данных из исходного вопроса (тривиальный случай):

  ---- ------------------ -------- 
| id | transaction_name | amount |
 ---- ------------------ -------- 
|  1 | bitcoin-received |      0 |
|  2 | bitcoin-mined    |     10 |
 ---- ------------------ --------

В обновленной скрипке приведен пример с большим количеством данных:

Новые данные:

 create table cryptotransactionledger as
    select  1 as id, 101 as transaction_typeid, 'bitcoin-received'    as transaction_name,   5 as amount from dual union all
    select  2 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,  20 as amount from dual union all
    select  3 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select  4 as id, 104 as transaction_typeid, 'bitcoin-lost'        as transaction_name, -10 as amount from dual union all
    select  5 as id, 101 as transaction_typeid, 'bitcoin-received'    as transaction_name,  55 as amount from dual union all
    select 15 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,   8 as amount from dual union all
    select 16 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,  20 as amount from dual union all
    select 17 as id, 102 as transaction_typeid, 'bitcoin-mined'       as transaction_name,  30 as amount from dual union all
    select 18 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 19 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 20 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 30 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual union all
    select 31 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -4 as amount from dual union all
    select 40 as id, 104 as transaction_typeid, 'bitcoin-lost'        as transaction_name, -16 as amount from dual union all
    select 99 as id, 103 as transaction_typeid, 'bitcoin-transferred' as transaction_name,  -5 as amount from dual WHERE 1 = 0
;

Результат:

  ---- ------------------ -------- 
| id | transaction_name | amount |
 ---- ------------------ -------- 
|  1 | bitcoin-received |      0 |
|  2 | bitcoin-mined    |      0 |
|  5 | bitcoin-received |     34 |
| 15 | bitcoin-mined    |      0 |
| 16 | bitcoin-mined    |     19 |
| 17 | bitcoin-mined    |     30 |
 ---- ------------------ --------

Комментарии:

1. Спасибо Джону за этот подробный ответ. Это потрясающе, и это решение почти соответствует моему ожидаемому результату. Разница лишь в том, что в конечном результате Сумма для 3-й строки, т. е. ID 5, должна была быть 39 вместо 34, а сумма для 5-й строки, т. е. ID 16, должна была быть 14 вместо 19. Я думаю, что один из биткойн-переводов суммы 5 неправильно вычитается из полученных биткойнов (идентификатор 5) вместо добытых биткойнов (идентификатор 16). Биткоин-перевод разрешается списывать только с биткоин-майнинга. В данных основной таблицы после идентификатора 5 только 16 очков теряются в биткоинах, поэтому, когда мы вычитаем 55-16, мы получим 39.

2. @rocketpicks Это было сделано намеренно, так transferred как суммы были выполнены первыми и удалены из mined строк. Все -5 суммы были связаны transfers и должны были быть взяты из mined строк. Я не уверен, как какая-либо из этих строк повлияла на received строку. Я проверю. Это не должно быть трудно найти. Результаты такие, как я и ожидал. Может быть, вы хотели немного другого порядка операций. Нам нужно будет найти эту разницу.

3. @rocketpicks, Чтобы было ясно, это было не совсем FIFO, так как я обработал все transfers сначала, независимо от lost случаев. Если мы хотим выполнить их все сразу, это выполнимо, но это также может привести к lost тому, что случаи будут вычтены из mined строк, не оставив возможности обрабатывать некоторые transfer случаи (в порядке FIFO). Если вы предполагаете, что все это всегда безопасно, мы можем настроить все это так, чтобы оно обрабатывалось в течение одного и того же CTE срока в строгом порядке FIFO. Однако общий подход должен быть применим.

4. В строке № 5 таблицы у нас received 55. После этого остается только одна lost сумма -16 , а остальные все есть transferred . Итак, если мы взяли все transfers из mined строк, то строка № 5 не станет 34 правильной? received — lost то 55 есть — 16 = 39 . Я не уверен, как из этого вычитались дополнительные 5, делая это 34 .

5. @rocketpicks, я полагаю, что осталось 5 при id = 1, а не только 55 при id = 5. Проверьте результат cte1b и проверьте текущий amount столбец и run2_in столбец. SELECT * FROM cte1b ORDER BY id; … dbfiddle.uk/… (последняя панель)

Ответ №3:

Следующий ответ использует рекурсию для реализации цикла.

  • Вероятно, лучше написать реальный цикл…

Это связано с тем, что правила FIFO означают, что невозможно заранее знать, какие mined / received записи будут уменьшены на lost запись. Поэтому каждая lost / transferred запись должна быть полностью обработана, прежде чем может начаться выделение для следующей lost / transferred записи.

Затем я использовал следующую логику…

  • income Запись — это когда transaction_typeid есть 101 или 102 .
  • outgoing Запись — это когда transaction_typeid есть 103 или 104 .
  • Если outgoing это тип 104 / lost , он может быть применен к любому income типу. В противном случае outgoing должен быть тип 103 / transferred и может быть применен только к income типу 102 / mined .

Тогда…

  • Создайте набор записей из всех income записей
  • Присоединяйте outgoing записи к этому набору по одной за раз (сначала самая низкая id ).
  • Максимум, что может быть выделено для первой income записи, — это LEAST(in.amount, out.amount)
  • Для 2-й записи это становится LEAST(in.amount, out.amount - <amount allocated to row1>)

Используя оконные функции, это становится (псевдокодом)…

 LEAST(
  in.amount,
  GREATEST(
    0,
    out.amount - SUM(in.amount) OVER (<all-preceding-rows>)
  )
)
WHERE out.transcation_type_id = 104
   OR  in.transaction_type_id = 102

Итак, последний (довольно длинный) запрос таков…

 WITH
  income
AS
(
  SELECT
    c.id,
    c.transaction_typeid,
    c.amount
  FROM
    cryptotransactionledger  c
  WHERE
    c.transaction_typeid IN (101, 102)
),
  outgoing
AS
(
  SELECT
    o.*,
    ROW_NUMBER() OVER (ORDER BY o.id)  AS seq_num
  FROM
    cryptotransactionledger  o
  WHERE
    o.transaction_typeid IN (103, 104)
),
  fifo(
    depth, id, transaction_typeid, amount
  )
AS
(
  SELECT 0, i.* FROM income i
  ---------
  UNION ALL
  ---------
  SELECT
    f.depth   1,
    f.id,
    f.transaction_typeid,
    f.amount
    -
    LEAST(
      -- everything remaining
      f.amount,
      -- the remaining available deductions
      GREATEST(
        0,
        CASE WHEN o.transaction_typeid = 104 THEN -o.amount
             WHEN f.transaction_typeid = 102 THEN -o.amount
                                             ELSE 0         END
        -
        -- the total amount from all preceding income rows
        COALESCE(
          SUM(CASE WHEN o.transaction_typeid = 104 THEN f.amount
                   WHEN f.transaction_typeid = 102 THEN f.amount
                                                   ELSE 0         END
          )
          OVER (ORDER BY f.id
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND         1 PRECEDING
          ),
          0
        )
      )
    )
  FROM
    fifo     f
  INNER JOIN
    outgoing o
      ON o.seq_num = f.depth   1
)
SELECT
  f.*
FROM
  fifo  f
WHERE
  f.depth = (SELECT MAX(depth) FROM fifo)
ORDER BY
  f.id
;

Вот демонстрационная версия, основанная на том, о чем вы спрашиваете.

Комментарии:

1. Спасибо, Матбайли, но я думаю, что этот запрос также дает тот же результат 0,0,44,8. Решение Джона также дает мне этот результат, но мой ожидаемый результат составляет 0,0,49,3.

2. @rocketpicks В моем CASE выражении была опечатка, 101 должно было быть 102 (это вычиталось transfered из received , а не из mined ) Я обновил ответ и теперь играю на скрипке.

3. @rocketpicks Я был бы не в курсе, с какой настройкой вы это профилировали. Я ожидал, что ответ MT0 будет наиболее эффективным для больших наборов данных.

Ответ №4:

Здесь та же логика, что и в моем рекурсивном CTE, но написана как чистый цикл.

  • Рекурсивный CTE завершится ошибкой после 2000 outgoing записей.

Создайте временную таблицу для хранения обрабатываемых значений…

 CREATE GLOBAL TEMPORARY TABLE temp_cryptotransactionledger (
  id                  INT,
  transaction_typeid  INT,
  transaction_name    VARCHAR2(32),
  amount              INT
);

Просмотрите каждую outgoing запись и примените логику FIFO…

 DECLARE
  CURSOR cur_outgoing IS
    SELECT id, transaction_typeid, amount
      FROM cryptotransactionledger
     WHERE transaction_typeid IN (103, 104)
  ORDER BY id;
BEGIN
  INSERT INTO temp_cryptotransactionledger
    SELECT c.*
      FROM cryptotransactionledger c
     WHERE c.transaction_typeid IN (101, 102);

  FOR o
  IN cur_outgoing
  LOOP
    MERGE INTO
      temp_cryptotransactionledger  t
    USING
    (
      SELECT
        i.id,
        LEAST(
          i.amount,
          GREATEST(
            0,
            i.amount - o.amount - SUM(i.amount) OVER (ORDER BY i.id)
          )
        )
          AS amount
      FROM
        temp_cryptotransactionledger  i
      WHERE
            i.id     < o.id
        AND i.amount > 0
        AND (
             o.transaction_typeid = 104
          OR i.transaction_typeid = 102
        )
    )
      f
        ON  (t.id = f.id)
    WHEN MATCHED THEN UPDATE SET
      t.amount = t.amount - f.amount
    ;
  END LOOP;
END;
/

Выберите результаты…

 SELECT * FROM temp_cryptotransactionledger;

ДЕМОНСТРАЦИЯ…

Комментарии:

1. Спасибо, Матбайли. Мне интересно, почему он потерпит неудачу после 2000 исходящих записей. Не могли бы вы, пожалуйста, поделиться более подробной информацией об этом.

2. Рекурсивные запросы ограничены глубиной рекурсии 2000. Они не предназначены для использования в неопределенных циклах.

3. @MatBailie, Пожалуйста, добавьте цитату для этого, так как на первый взгляд это не похоже на истинную бд<>скрипка<>

4. @MT0 — Я исправляюсь. Я не знаю, где я «выучил» лимит 2000, но ваша скрипка объективно опровергает его. Спасибо.

5. @rocketpicks Согласно комментарию MT0. Мой рекурсивный CTE не ограничен тем, как я думал. Подходит ли вам CTE или цикл лучше всего, будет зависеть от ваших потребностей, попробуйте как с реальными данными, так и посмотрите, как это работает.

Метки: