Накопительный чистый поток в соответствии с последовательностью транзакций Датавремя для каждого клиента в SQL Server

ProgramBox

Накопительный чистый поток в соответствии с последовательностью транзакций Датавремя для каждого клиента в SQL Server

Post author:admin
Запись опубликована:21 августа, 2022
Post category:Вопросы по программированию

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

Я пытаюсь получить чистые потоки для каждого клиента в соответствии с последовательностью возникновения транзакций (DateTime ASC). Для этой цели я создал циклический запрос, но, к сожалению, его обработка занимает много времени. 20 тысяч записей заняли 8 минут, в то время как я должен был запустить его на 5 миллионах записей.

Объяснение: Например, клиент 923xxxxx307 выполнил транзакцию 4, вот объяснение того, как это будет работать

В таблице примеров данных первая транзакция — это ПОТОК ВЫВОДА, где он использовал 26 рупий со счета, поэтому в таблице требуемых выходных данных ЧИСТЫЙ ПОТОК ВЫВОДА в первой строке равен 26
В таблице примеров данных выполняется вторая транзакция, в ходе которой он внес 60 рупий на свой счет, эти 60 рупий должны быть припаркованы в его собственном отдельном ведре, и это не должно отражаться в таблице обязательных исходов
В таблице примеров данных третья транзакция — это ПОТОК ВЫХОДА, в котором он использовал 62 рупии из своего скрипта учетной записи, за вычетом этой суммы 62 из его корзины, поддерживаемой в пункте 2, поэтому в требуемой таблице выходных данных столбец ЧИСТОГО ПОТОКА должен показывать 2, т.Е. (62-60 = 2). Кроме того, его корзина должна стать 0, поскольку она была полностью использована в 3-й транзакции
В таблице Sample Data четвертая транзакция снова находится В потоке, где он внес 360 рупий на свой счет, поэтому его корзина снова должна показывать 360 рупий, не влияя на требуемую таблицу вывода. и так далее.

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

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

Пожалуйста, помогите мне с каким-нибудь эффективным решением для этого. Я прилагаю скриншот ввода и вывода вместе с моим кодом.

 -- Creating SAMPLE DATA Table ---
DROP TABLE IF EXISTS #TestData
CREATE TABLE #TestData
(
    [FINANCIAL ID]  BIGINT,
    [DATE]          DATETIME,
    [TRXN DATETIME] DATETIME,
    AMOUNT          FLOAT,
    [CUSTOMER NO]   VARCHAR(20),
    [PRODUCT NAME]  VARCHAR(10)
)
GO

-- Inserting Sample Date in above table ---
INSERT INTO #TestData
VALUES (9442001596,'2020-11-01','2020-11-01 00:05:18',26,'923xxxxx307','OUT FLOW')

INSERT INTO #TestData
VALUES (9442094863,'2020-11-01','2020-11-01 00:15:01',60,'923xxxxx307','IN FLOW')

INSERT INTO #TestData
VALUES (9442106611,'2020-11-01','2020-11-01 00:16:26',62,'923xxxxx307','OUT FLOW')

INSERT INTO #TestData
VALUES (9442198611,'2020-11-01','2020-11-01 00:30:35',360,'923xxxxx307','IN FLOW')

INSERT INTO #TestData
VALUES (9442227548,'2020-11-01','2020-11-01 00:36:07',15000,'923xxxxx864','IN FLOW')

INSERT INTO #TestData
VALUES (9442264685,'2020-11-01','2020-11-01 00:44:03',1660,'923xxxxx864','IN FLOW')

INSERT INTO #TestData
VALUES (9442266137,'2020-11-01','2020-11-01 00:44:24',4540,'923xxxxx864','OUT FLOW')

INSERT INTO #TestData
VALUES (9442358832,'2020-11-01','2020-11-01 01:08:06',200,'923xxxxx864','OUT FLOW')

INSERT INTO #TestData
VALUES (9442434263,'2020-11-01','2020-11-01 01:34:05',190,'923xxxxx864','OUT FLOW')

INSERT INTO #TestData
VALUES (9442517054,'2020-11-01','2020-11-01 02:14:48',5000,'923xxxxx562','IN FLOW')

INSERT INTO #TestData
VALUES (9442525893,'2020-11-01','2020-11-01 02:20:18',5000,'923xxxxx562','IN FLOW')

INSERT INTO #TestData
VALUES (9442533823,'2020-11-01','2020-11-01 02:25:14',10000,'923xxxxx562','IN FLOW')

INSERT INTO #TestData
VALUES (9442541534,'2020-11-01','2020-11-01 02:30:25',10000,'923xxxxx562','IN FLOW')

INSERT INTO #TestData
VALUES (9442545883,'2020-11-01','2020-11-01 02:33:04',25500,'923xxxxx562','OUT FLOW')

INSERT INTO #TestData
VALUES (9442552698,'2020-11-01','2020-11-01 02:37:08',5000,'923xxxxx562','OUT FLOW')

INSERT INTO #TestData
VALUES (9443455472,'2020-11-01','2020-11-01 07:45:10',180,'923xxxxx074','OUT FLOW')

INSERT INTO #TestData
VALUES (9443529884,'2020-11-01','2020-11-01 07:54:41',280,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443657359,'2020-11-01','2020-11-01 08:10:09',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443670261,'2020-11-01','2020-11-01 08:11:34',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443682756,'2020-11-01','2020-11-01 08:12:59',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443683147,'2020-11-01','2020-11-01 08:13:01',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443872236,'2020-11-01','2020-11-01 08:33:04',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443872617,'2020-11-01','2020-11-01 08:33:06',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9443886681,'2020-11-01','2020-11-01 08:34:31',100,'923xxxxx074','IN FLOW')

INSERT INTO #TestData
VALUES (9444185688,'2020-11-01','2020-11-01 09:02:11',300,'923xxxxx074','IN FLOW')

-- Ranked table with ROW NUMBERS --- 
DROP TABLE IF EXISTS #TestData_Ranked
SELECT *, ROWNUMBER = ROW_NUMBER() OVER (ORDER BY td.[FINANCIAL ID])
INTO #TestData_Ranked
FROM #TestData td
GO

CREATE UNIQUE CLUSTERED INDEX [CI_ROWNUMBER] ON #TestData_Ranked (ROWNUMBER ASC)
GO


-- Main Query to get OUTPUT DATA table ---
DECLARE @start BIGINT   = (SELECT MIN(iios.ROWNUMBER) FROM #TestData_Ranked iios)
DECLARE @end BIGINT     = (SELECT MAX(iios.ROWNUMBER) FROM #TestData_Ranked iios)

DROP TABLE IF EXISTS #ibft_in
CREATE TABLE #ibft_in ([CUSTOMER NO] VARCHAR(20), [TRXN DATETIME] DATETIME, AMOUNT FLOAT)

DROP TABLE IF EXISTS #ibft_out
SELECT TOP(0) iios.[FINANCIAL ID], iios.DATE, iios.[TRXN DATETIME], iios.AMOUNT, iios.[CUSTOMER NO], iios.[PRODUCT NAME], 0 AS [NET OUT FLOW]
INTO #ibft_out
FROM #TestData_Ranked iios


WHILE (@start <= @end)
BEGIN

    DECLARE @cur_FinancialId BIGINT = (SELECT [FINANCIAL ID] FROM #TestData_Ranked WHERE ROWNUMBER = @start)
    DECLARE @cur_TrxnDateTime DATETIME = (SELECT [TRXN DATETIME] FROM #TestData_Ranked WHERE ROWNUMBER = @start)
    DECLARE @cur_Date DATE = (SELECT DATE FROM #TestData_Ranked WHERE ROWNUMBER = @start)
    DECLARE @cur_Amount FLOAT = (SELECT AMOUNT FROM #TestData_Ranked WHERE ROWNUMBER = @start)
    DECLARE @cur_Customer_No VARCHAR(20) = (SELECT [CUSTOMER NO] FROM #TestData_Ranked WHERE ROWNUMBER = @start)
    DECLARE @cur_ProductName VARCHAR(10) = (SELECT [PRODUCT NAME] FROM #TestData_Ranked WHERE ROWNUMBER = @start)

    DECLARE @IN_IfExists INT    = (SELECT COUNT(*) FROM #ibft_in ii WHERE ii.[CUSTOMER NO] = @cur_Customer_No)
    DECLARE @IN_Amount FLOAT    = ISNULL((SELECT ISNULL(ii.AMOUNT, 0) FROM #ibft_in ii WHERE ii.[CUSTOMER NO] = @cur_Customer_No), 0)

    DECLARE @Remaining_Amount FLOAT = ISNULL(CASE WHEN (@cur_Amount - @IN_Amount)<0 THEN 0 ELSE (@cur_Amount - @IN_Amount) END, 0)

    PRINT @start
    PRINT @cur_Amount
    PRINT @IN_Amount

    IF (@cur_ProductName = 'IN FLOW' AND @IN_IfExists = 0)
    BEGIN
        INSERT INTO #ibft_in
        VALUES (@cur_Customer_No, @cur_TrxnDateTime, @cur_Amount)
    END
    ELSE
    BEGIN
        IF (@cur_ProductName = 'IN FLOW' AND @IN_IfExists > 0)
        BEGIN
            UPDATE #ibft_in
            SET 
                [TRXN DATETIME] = @cur_TrxnDateTime,
                AMOUNT          = @IN_Amount   @cur_Amount
            WHERE 
                [CUSTOMER NO]           = @cur_Customer_No
        END
        ELSE
        BEGIN
            IF (@cur_ProductName = 'OUT FLOW')
            BEGIN
                INSERT INTO #ibft_out
                VALUES (@cur_FinancialId, @cur_Date, @cur_TrxnDateTime, @cur_Amount, @cur_Customer_No, @cur_ProductName, @Remaining_Amount)

                UPDATE #ibft_in
                SET 
                    AMOUNT = CASE WHEN (@IN_Amount - @cur_Amount)<0 THEN 0 ELSE (@IN_Amount - @cur_Amount) END
                WHERE
                    [CUSTOMER NO] = @cur_Customer_No
            END
        END
    END

SET @start = @start   1
END
--------------------------------------------

ПРИМЕРНЫЕ ДАННЫЕ

ТРЕБУЕМЫЙ ВЫВОД

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

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

3. … Я вижу (едва читаемые) изображения. Однако вы не объяснили, чего вы хотите достичь, кроме как добавить в вопрос массу кода.

Ответ №1:

Редактировать:

Технически это все еще запрос RBAR, только что переписанный. У меня недостаточно выборочных данных, чтобы узнать, какова производительность, поэтому, очевидно, пожалуйста, запустите это с вашим набором данных 20k для сравнения скорости, но при этом вместо КУРСОРА используется CTE. Обратите внимание, что это начинается прямо с вашей -- Ranked table with ROW NUMBERS --- строки:

 -- Ranked table with ROW NUMBERS --- 
DROP TABLE IF EXISTS #TestData_Ranked
SELECT *, 
    --ROWNUMBER = ROW_NUMBER() OVER (ORDER BY td.[FINANCIAL ID]), 
    TrxTypeSEQ = ROW_NUMBER() over (PARTITION BY [customer no] ORDER BY [trxn datetime])
INTO #TestData_Ranked
FROM #TestData td
GO

-- not unique but still an index
CREATE CLUSTERED INDEX [CI_ROWNUMBER] ON #TestData_Ranked (TrxTypeSEQ ASC)
GO


DROP TABLE IF EXISTS #ibft_out;

WiTH SortedTrx AS (
  SELECT 
    [Financial ID],
    [Date],
    [Trxn Datetime],
    [Amount],
    [Customer No],
    [Product Name],
    TrxTypeSEQ,
    BUCKET = case when [Product Name] = 'IN FLOW' then Amount ELSE 0 END,
    NETOUT = case when [Product Name] = 'OUT FLOW' then Amount ELSE 0 END
  FROM #TestData_Ranked  
  WHERE TrxTypeSEQ = 1

  UNION ALL 

  SELECT 
    AllTrx.[Financial ID],
    AllTrx.[Date],
    AllTrx.[Trxn Datetime],
    AllTrx.[Amount],
    AllTrx.[Customer No],
    AllTrx.[Product Name],
    AllTrx.TrxTypeSEQ,
    BUCKET = case when AllTrx.[Product Name] = 'IN FLOW' then SortedTrx.Bucket   AllTrx.Amount 
                    ELSE 
                        case when AllTrx.[Amount] > SortedTrx.Bucket then 0 else SortedTrx.Bucket - AllTrx.[Amount] end
                    END,
    NETOUT = case when AllTrx.[Product Name] = 'IN FLOW' then 0 
                    else 
                        case when AllTrx.[Amount] < SortedTrx.Bucket then 0 else AllTrx.[Amount] - SortedTrx.Bucket end
                    END
  FROM #TestData_Ranked  AllTrx 
  INNER JOIN SortedTrx ON AllTrx.[customer no] = SortedTrx.[customer no] AND SortedTrx.TrxTypeSEQ   1 = AllTrx.TrxTypeSEQ
)
SELECT 
    [Financial ID],
    [Date],
    [Trxn Datetime],
    [Amount],
    [Customer No],
    [Product Name],
    NETOUT
INTO #ibft_out
FROM SortedTrx 
WHERE [Product Name] = 'OUT FLOW'
ORDER BY [Trxn Datetime]

SELECT * FROM #ibft_out

ОРИГИНАЛ:

Переписывание вашего WHILE цикла в a CURSOR сократит количество запросов, которые вы запрашиваете #TestData_Ranked , с 6 до 1; вместо того, чтобы запрашивать #ibft_in 3 раза каждый раз, вы будете запрашивать его только один или два раза, в зависимости от типа транзакции. Мне интересно узнать, какую производительность он вам дает; выборка данных выполнялась за 7 секунд с анализом плана выполнения по сравнению с более чем 40 секундами с использованием вашего кода.

Замена всего после вашей -- Main Query to get OUTPUT DATA table --- строки на:

 -- Create our useful tables for IN amp; OUT calculations 
DROP TABLE IF EXISTS #ibft_in
CREATE TABLE #ibft_in ([CUSTOMER NO] VARCHAR(20), [TRXN DATETIME] DATETIME, AMOUNT FLOAT)

DROP TABLE IF EXISTS #ibft_out
SELECT TOP(0) iios.[FINANCIAL ID], iios.DATE, iios.[TRXN DATETIME], iios.AMOUNT, iios.[CUSTOMER NO], iios.[PRODUCT NAME], 0 AS [NET OUT FLOW]
INTO #ibft_out
FROM #TestData_Ranked iios

-- Declare these once, outside a loop, to cut down on wasted work
DECLARE @currRow INT = 0,
        @currFinancialID BIGINT = 0,
        @currDateTime DATETIME,
        @currAmt FLOAT,
        @currCustomer VARCHAR(20),
        @currTrxType VARCHAR(10),
        @fundsAvailable FLOAT;
        
-- We want a one-way cursor as fast as we can
DECLARE trx_cursor CURSOR LOCAL FAST_FORWARD FOR
   SELECT 
      ROWNUMBER,
      [Financial ID],
      [Trxn Datetime],
      [Amount],
      [Customer No],
      [Product Name]
    FROM #TestData_Ranked
    ORDER BY ROWNUMBER ASC

OPEN trx_cursor
FETCH NEXT FROM trx_cursor INTO @currRow, 
                                @currFinancialID,
                                @currDateTime,
                                @currAmt,
                                @currCustomer,
                                @currTrxType
WHILE @@FETCH_STATUS = 0
BEGIN
    -- If this is an IN transaction, INSERT or UPDATE our bucket of rupees
    IF @currTrxType = 'IN FLOW' 
        MERGE #ibft_in as Tgt
        USING (select @currCustomer as cno, @currDateTime as dt, @currAmt as amt) as Src
        ON tgt.[CUSTOMER NO] = cno
        WHEN NOT MATCHED BY TARGET THEN INSERT 
            VALUES (cno, dt, amt) 
        WHEN MATCHED THEN UPDATE
            SET Tgt.[TRXN DATETIME] = dt,
                Tgt.AMOUNT = tgt.amount   amt;

    -- OTHERWISE, calculate our NET OUT
    ELSE 
    BEGIN
        SELECT @fundsAvailable = ISNULL((SELECT ISNULL(ii.AMOUNT, 0) FROM #ibft_in ii WHERE ii.[CUSTOMER NO] = @currCustomer), 0)

        INSERT INTO #ibft_out 
        VALUES ( 
            @currFinancialID, 
            CAST(@currDateTime as DATE), 
            @currDateTime, 
            @currAmt, 
            @currCustomer, 
            @currTrxType, 
            ISNULL(CASE WHEN (@currAmt - @fundsAvailable)<0 THEN 0 ELSE (@currAmt - @fundsAvailable) END, 0)
        )

        UPDATE #ibft_in
        SET AMOUNT = CASE WHEN (@fundsAvailable - @currAmt)<0 THEN 0 ELSE (@fundsAvailable - @currAmt) END
        WHERE [CUSTOMER NO] = @currCustomer
    END
        

FETCH NEXT FROM trx_cursor INTO @currRow, 
                                @currFinancialID,
                                @currDateTime,
                                @currAmt,
                                @currCustomer,
                                @currTrxType
END
CLOSE trx_cursor
DEALLOCATE trx_cursor

select * from #ibft_in
select * from #ibft_out

1. Отличная работа, я обработал 20000 записей всего за 30 секунд, с предыдущим кодом этот объем данных занимал 7 минут, плюс я научился использовать КУРСОР. Спасибо.

2. это сработало, но все равно заняло слишком много времени. Я запустил его для данных за 5 дней, прошло 11 часов, и он все еще работает. Не могли бы вы помочь.

3. @AnwaarEMustafa Если для выполнения 20 тыс. строк потребовалось 30 секунд, 5 тыс. строк должны были занять всего 2-3 часа. Я бы выполнил проверку, чтобы проверить, есть ли заблокированный поток, препятствующий его работе по назначению (вы используете временные таблицы / промежуточные таблицы, а не таблицу prod, используемую другими процессами, верно?). Я добавил рекурсивный запрос, но они не гарантированно будут работать быстрее, чем cursor . То, что вы хотите, требует перехода от строки к строке (RBAR), и это всегда будет дерьмовой производительностью.

Метки: Накопительный чистый поток в соответствии с последовательностью транзакций Датавремя для каждого клиента в SQL Server