Блог

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

 select * into #balances from [IDAT_AR_BALANCES] where amount > 0
select * into #credits from [IDAT_AR_BALANCES] where amount < 0

create index ba_ID on #balances (CLIENT_ID)
create index cr_ID on #credits (CLIENT_ID)

declare credit_cursor cursor for
select [CLIENT_ID], amount, cvtGUID from #credits

open credit_cursor
declare @client_id varchar(11)
declare @credit money
declare @balance money
declare @cvtGuidBalance uniqueidentifier
declare @cvtGuidCredit uniqueidentifier
fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
while @@fetch_status = 0
begin
      while(@credit < 0 and (select count(*) from #balances where @client_id = CLIENT_ID and amount <> 0) > 0)
      begin
            select top 1  @balance = amount, @cvtGuidBalance = cvtGuid from #balances where @client_id = CLIENT_ID and amount <> 0 order by AGING_DATE
            set @credit = @balance   @credit
            if(@credit > 0)
            begin
                  update #balances set amount = @credit where cvtGuid = @cvtGuidBalance
                  set @credit = 0
            end
            else
            begin
                  update #balances set amount = 0 where cvtGuid = @cvtGuidBalance
            end
      end
      update #credits set amount = @credit where cvtGuid = @cvtGuidCredit
      fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
end

close credit_cursor
deallocate credit_cursor

delete #balances where AMOUNT = 0
delete #credits where AMOUNT = 0

truncate table [IDAT_AR_BALANCES]

insert [IDAT_AR_BALANCES] select * from #balances
insert [IDAT_AR_BALANCES] select * from #credits

drop table #balances
drop table #credits
  

В моих тестовых примерах для 10000 записей и 1000 клиентов выполнение занимает 26 секунд, добавив два индекса в CLIENT_ID, я смог сократить это число до 14 секунд. Однако это все еще слишком медленно для того, что мне нужно, конечный результат может содержать до 10000 клиентов и более 4 000 000 записей, поэтому время выполнения может легко исчисляться двузначными числами минут.

Буду признателен за любые рекомендации о том, как я могу реструктурировать это, чтобы удалить курсор.

Пример (обновлен, чтобы показать, что у вас может быть несколько кредитов после его запуска):

 before
cvtGuid      client_id      ammount     AGING_DATE
xxxxxx       1              20.00       1/1/2011
xxxxxx       1              30.00       1/2/2011
xxxxxx       1              -10.00      1/3/2011
xxxxxx       1              5.00        1/4/2011
xxxxxx       2              20.00       1/1/2011
xxxxxx       2              15.00       1/2/2011
xxxxxx       2              -40.00      1/3/2011
xxxxxx       2              5.00        1/4/2011
xxxxxx       3              10.00       1/1/2011
xxxxxx       3              -20.00      1/2/2011
xxxxxx       3              5.00        1/3/2011
xxxxxx       3              -8.00       1/4/2011

after
cvtGuid      client_id      ammount     AGING_DATE
xxxxxx       1              10.00       1/1/2011
xxxxxx       1              30.00       1/2/2011
xxxxxx       1              5.00        1/4/2011
xxxxxx       3              -5.00       1/2/2011
xxxxxx       3              -8.00       1/4/2011
  

таким образом, отрицательный кредит будет применен к самому старому положительному балансу (клиент 1 в примере), если после этого не останется положительных остатков, останется оставшийся отрицательный (клиент 3), если они полностью компенсируются (это имеет место в 90% случаев с реальными данными), запись будет полностью удалена (клиент 2).

Это можно решить с помощью рекурсивного CTE.

Основная идея заключается в следующем:

1. Получите итоговые значения положительных и отрицательных значений отдельно для каждой учетной записи ( client_id ).

2. Выполните итерацию по каждой учетной записи и «отщепите» сумму от одного из двух итогов, в зависимости от amount знака и абсолютного значения (т. е. никогда не «отщепляйте» от соответствующей суммы больше, чем ее текущее значение). То же значение должно быть добавлено к / вычтено из amount .

3. После обновления удалите те строки, в которых amount значение стало равным 0.

Для своего решения я позаимствовал определение табличной переменной Ливена (спасибо!), добавив один столбец ( cvtGuid , объявленный как int для демонстрации) и одну строку (последнюю из исходного примера, которая отсутствовала в скрипте Ливена).

 /* preparing the demonstration data */
DECLARE @IDAT_AR_BALANCES TABLE (
  cvtGuid int IDENTITY,
  client_id INTEGER
  , amount FLOAT
  , date DATE
);
INSERT INTO @IDAT_AR_BALANCES
  SELECT 1, 20.00, '1/1/2011'
  UNION ALL SELECT 1, 30.00, '1/2/2011'
  UNION ALL SELECT 1, -10.00, '1/3/2011'
  UNION ALL SELECT 1, 5.00, '1/4/2011'
  UNION ALL SELECT 2, 20.00, '1/1/2011'
  UNION ALL SELECT 2, 15.00, '1/2/2011'
  UNION ALL SELECT 2, -40.00, '1/3/2011'
  UNION ALL SELECT 2, 5.00, '1/4/2011'
  UNION ALL SELECT 3, 10.00, '1/1/2011'
  UNION ALL SELECT 3, -20.00, '1/2/2011'
  UNION ALL SELECT 3, 5.00, '1/3/2011'
  UNION ALL SELECT 3, -8.00, '1/4/2011';

/* checking the original contents */
SELECT * FROM @IDAT_AR_BALANCES;

/* getting on with the job: */
WITH totals AS (
  SELECT
    /* 1) preparing the totals */
    client_id,
    total_pos = SUM(CASE WHEN amount > 0 THEN amount END),
    total_neg = SUM(CASE WHEN amount < 0 THEN amount END)
  FROM @IDAT_AR_BALANCES
  GROUP BY client_id
),
refined AS (
  /* 2) refining the original data with auxiliary columns:
     * rownum - row numbers (unique within accounts);
     * amount_to_discard_pos - the amount to discard `amount` completely if it's negative;
     * amount_to_discard_neg - the amount to discard `amount` completely if it's positive
  */
  SELECT
    *,
    rownum = ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date),
    amount_to_discard_pos = CAST(CASE WHEN amount < 0 THEN -amount ELSE 0 END AS float),
    amount_to_discard_neg = CAST(CASE WHEN amount > 0 THEN -amount ELSE 0 END AS float)
  FROM @IDAT_AR_BALANCES
),
prepared AS (
  /* 3) preparing the final table (using a recursive CTE) */
  SELECT
    cvtGuid = CAST(NULL AS int),
    client_id,
    amount = CAST(NULL AS float),
    date = CAST(NULL AS date),
    amount_update = CAST(NULL AS float),
    running_balance_pos = total_pos,
    running_balance_neg = total_neg,
    rownum = CAST(0 AS bigint)
  FROM totals
  UNION ALL
  SELECT
    n.cvtGuid,
    n.client_id,
    n.amount,
    n.date,
    amount_update = CAST(
      CASE
        WHEN n.amount_to_discard_pos < p.running_balance_pos
        THEN n.amount_to_discard_pos
        ELSE p.running_balance_pos
      END
       
      CASE
        WHEN n.amount_to_discard_neg > p.running_balance_neg
        THEN n.amount_to_discard_neg
        ELSE p.running_balance_neg
      END
    AS float),
    running_balance_pos = CAST(p.running_balance_pos -
      CASE
        WHEN n.amount_to_discard_pos < p.running_balance_pos
        THEN n.amount_to_discard_pos
        ELSE p.running_balance_pos
      END
    AS float),
    running_balance_neg = CAST(p.running_balance_neg -
      CASE
        WHEN n.amount_to_discard_neg > p.running_balance_neg
        THEN n.amount_to_discard_neg
        ELSE p.running_balance_neg
      END
    AS float),
    n.rownum
  FROM refined n
    INNER JOIN prepared p ON n.client_id = p.client_id AND n.rownum = p.rownum   1
)
/*                  -- some junk that I've forgotten to clean up,
SELECT *            -- which you might actually want to use
FROM prepared       -- to view the final prepared result set
WHERE rownum > 0    -- before actually running the update
ORDER BY client_id, rownum
*/
/* performing the update */
UPDATE t
SET amount = t.amount   u.amount_update
FROM @IDAT_AR_BALANCES t INNER JOIN prepared u ON t.cvtGuid = u.cvtGuid
OPTION (MAXRECURSION 0);

/* checking the contents after UPDATE */
SELECT * FROM @IDAT_AR_BALANCES;

/* deleting the eliminated amounts */
DELETE FROM @IDAT_AR_BALANCES WHERE amount = 0;

/* checking the contents after DELETE */
SELECT * FROM @IDAT_AR_BALANCES;
  

Обновить

Как правильно предположил Ливен (еще раз спасибо!), вы можете сначала удалить все строки из учетных записей, в которых amount сумма равна 0, затем обновите остальные строки. Это повысит общую производительность, поскольку, как вы говорите, сумма большинства данных равна 0.

Вот вариант решения Ливена для удаления «нулевых учетных записей»:

 DELETE FROM @IDAT_AR_BALANCES
WHERE client_id IN (
  SELECT client_id
  FROM @IDAT_AR_BALANCES
  GROUP BY client_id
  HAVING SUM(amount) = 0
)
  

Однако имейте в виду, что DELETE после обновления все равно потребуется обновление, поскольку обновление может сбросить некоторые из amount значений на 0. На вашем месте я мог бы подумать о создании триггера ДЛЯ ОБНОВЛЕНИЯ, который автоматически удалял бы строки, где amount = 0 . Такое решение не всегда приемлемо, но иногда подходит. Это зависит от того, что еще вы можете сделать со своими данными. Это также может зависеть от того, является ли это исключительно вашим проектом или есть и другие сопровождающие (которым не нравятся строки, которые «волшебным» образом и неожиданно исчезают).

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

Вы можете занести свои кредиты в таблицу с серийным номером для каждого клиента:

 CREATE TABLE #CreditsInSequence
  (
  Client_ID INT NOT NULL,
  Sequence  INT NOT NULL,
  PRIMARY KEY (ClientID, Sequence),
  Date      DATE NOT NULL,
  Amount    DECIMAL NOT NULL
  )
INSERT INTO #CreditsInSequence (Client_ID, Sequence, Date, Amount)
  SELECT
    client_id, ROW_NUMBER (PARTITION BY client_id, ORDER BY date) AS Sequence, date, amount
  FROM
    #credits
  

Если у клиента только один кредит, у него будет одна строка в таблице с Sequence = 1. Если у другого клиента есть три кредита, у него будет три строки с порядковыми номерами 1, 2 и 3. Теперь вы можете выполнять цикл по этой временной таблице, и вам потребуется всего лишь количество итераций, равное наибольшему количеству кредитов, имеющихся у любого отдельного клиента.

 DECLARE @MaxSeq INT = (SELECT MAX(Sequence) FROM #Credits)
DECLARE @Seq    INT = 1
WHILE @Seq <= @MaxSeq
  BEGIN
  -- Do something with this set of credits
  SELECT
    Client_ID, Date, Amount
  FROM
    #CreditsInSequence
  WHERE
    Sequence = @Seq

  SET @Seq  = 1  -- Don't forget to increment the loop!
  END
  

Как и в случае с вашим курсором, это позволяет вам работать последовательно, полностью обрабатывая первый кредит для каждого клиента, прежде чем переходить ко второму. В качестве бонуса, по моему опыту, такого рода «притворный цикл FOR» часто выполняется быстрее, чем курсор.

Чтобы определить правильный баланс, к которому следует применить каждый кредит, я бы начал с чего-то вроде этого:

 SELECT
  B.client_id,
  MIN(B.date) AS Date,
  B.amount - COALESCE(AC.Amount, 0.00) AS MaxAmountCreditable
FROM
  #balances AS B
  LEFT JOIN #AllocatedCredits AS AC ON B.BalanceID = AC.BalanceID
WHERE
  B.amount   COALESCE(AC.Amount, 0.00) > 0.00
GROUP BY
  B.client_id
  

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

Удачи, и не стесняйтесь обращаться к SO, если вам нужна какая-либо помощь!

Вам нужно будет проверить, будет ли это быстрее, но это делается с помощью (в основном) операций на основе набора, а не на основе курсора.

Тестовые данные

 DECLARE @IDAT_AR_BALANCES TABLE (
  client_id INTEGER
  , amount FLOAT
  , date DATE
) 

INSERT INTO @IDAT_AR_BALANCES
  SELECT 1, 20.00, '1/1/2011'
  UNION ALL SELECT 1, 30.00, '1/2/2011'
  UNION ALL SELECT 1, -10.00, '1/3/2011'
  UNION ALL SELECT 1, 5.00, '1/4/2011'
  UNION ALL SELECT 2, 20.00, '1/1/2011'
  UNION ALL SELECT 2, 15.00, '1/2/2011'
  UNION ALL SELECT 2, -40.00, '1/3/2011'
  UNION ALL SELECT 2, 5.00, '1/4/2011'
  UNION ALL SELECT 3, 10.00, '1/1/2011'
  UNION ALL SELECT 3, -20.00, '1/2/2011'
  UNION ALL SELECT 3, 5.00, '1/3/2011' 
  

Удалите все, что в сумме равно 0 (90% данных)

   DELETE FROM @IDAT_AR_BALANCES
  FROM @IDAT_AR_BALANCES b
       INNER JOIN (
         SELECT client_id
         FROM   @IDAT_AR_BALANCES
         GROUP BY 
                client_id
         HAVING SUM(amount) = 0
       ) bd ON bd.client_id = b.client_id
  

Оставшиеся записи

 DECLARE @Oldest TABLE (
  client_id INTEGER PRIMARY KEY CLUSTERED
  , date DATE
)

DECLARE @Negative TABLE (
  client_id INTEGER PRIMARY KEY CLUSTERED
  , amount FLOAT
)  

WHILE EXISTS (  SELECT  b.client_id
                        , MIN(b.amount) 
                FROM    @IDAT_AR_BALANCES b
                        INNER JOIN (
                          SELECT  client_id
                          FROM    @IDAT_AR_BALANCES
                          GROUP BY
                                  client_id
                          HAVING  COUNT(*) > 1
                        ) r ON r.client_id = b.client_id                
                WHERE   b.amount < 0 
                GROUP BY 
                        b.client_id 
                HAVING COUNT(*) > 0
             )
BEGIN

  DELETE FROM @Oldest
  DELETE FROM @Negative

  INSERT INTO @Oldest
    SELECT  client_id
            , date = MIN(date)
    FROM    @IDAT_AR_BALANCES 
    WHERE   amount > 0
    GROUP BY
            client_id

  INSERT INTO @Negative
    SELECT  b.client_id
            , amount = SUM(amount)
    FROM    @IDAT_AR_BALANCES b
            LEFT OUTER JOIN @Oldest o ON o.client_id = b.client_id AND o.date = b.date
    WHERE   amount < 0
            AND o.client_id IS NULL
    GROUP BY
            b.client_id

  UPDATE  @IDAT_AR_BALANCES
  SET     b.amount = b.amount   n.amount
  FROM    @IDAT_AR_BALANCES b
          INNER JOIN @Oldest o ON o.client_id = b.client_id AND o.date = b.date
          INNER JOIN @Negative n ON n.client_id = b.client_id

  DELETE FROM @IDAT_AR_BALANCES
  FROM    @IDAT_AR_BALANCES b
          LEFT OUTER JOIN @Oldest o ON o.client_id = b.client_id AND o.date = b.date
          INNER JOIN (
            SELECT  client_id
            FROM    @IDAT_AR_BALANCES
            GROUP BY
                    client_id
            HAVING  COUNT(*) > 1
          ) r ON r.client_id = b.client_id
  WHERE   amount < 0
          AND o.client_id IS NULL

END  

DELETE  FROM @IDAT_AR_BALANCES
WHERE   amount = 0          

SELECT  *
FROM    @IDAT_AR_BALANCES
  

Во-первых, как вы заявляете, вы должны иметь дело только с теми клиентами, у которых есть остатки.
Во-вторых, вы можете имитировать функциональность курсоров с помощью цикла WHILE ..

вот изменения в коде. Я оставил outer основную часть вычислений, поскольку они не являются проблемой… если вы хотите, чтобы я завершил код, дайте мне знать

 --first, only deal with those clients with balances
select CLIENT_ID into #ToDoList 
from [IDAT_AR_BALANCES]
group by CLIENT_ID
having sum(amount)!=0

--next, get the temp debit and credit tables just for the clients you are working on
select * into #balances from [IDAT_AR_BALANCES] where amount > 0 and CLIENT_ID IN (SELECT CLIENT_ID FROM #ToDoList)
select * into #credits from [IDAT_AR_BALANCES] where amount < 0 and CLIENT_ID IN (SELECT CLIENT_ID FROM #ToDoList)

--fine
create index ba_ID on #balances (CLIENT_ID)
create index cr_ID on #credits (CLIENT_ID)

--simulate a cursor... but much less resource intensive

declare @client_id varchar(11)

-- now loop through each client and perform their aging
while exists (select * from #ToDoList)
begin
    select top 1 @client_id = CLIENT_ID from #ToDoList 

    --perform your debit to credit matching and account aging here, per client

    delete from #TodoList where Client_ID=@client_ID
end

--clean up.. drop temp tables, etc
  

Последняя мысль… На самом деле я написал этот самый код для большой CRM для борьбы с вредителями, которую я разработал несколько лет назад … и я обнаружил, что наиболее эффективным решением этой проблемы была …. процедура, хранимая в .NET CLR.

Хотя я обычно избегаю CLR-процедур любой ценой.. бывают случаи, когда они превосходят SQL. В этом случае процедурные (строка за строкой) запросы с математическими вычислениями могут выполняться намного быстрее в CLR proc.

В моем случае это было значительно быстрее, чем SQL.

К вашему сведению