Арифметическое вычисление с использованием предыдущей записи для двух столбцов, которые зависят друг от друга в sql

#sql #sql-server #tsql #sql-server-2012 #recursive-query

#sql #sql-сервер #tsql #sql-сервер-2012 #рекурсивный запрос #sql-server-2012

Вопрос:

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

Я делюсь образцом таблицы для вашей справки

 A   | B   | C   | D
500 | 400 | 400 | 0
300 | 400 | 300 | 100
200 | 500 | 200 | 400
700 | 200 | 600 | 0
900 | 800 | 800 | 0

В приведенной выше таблице я должен вычислить столбцы C и D. Формула Excel равна:

1-я строка C — это IF(A2> B2, B2,A2)
2-я строка до последней строки C — IF(A3> B3 D2, B3 D2, A3)
1-я строка D равна IF(A2-B2<=0,-1*(A2-B2),0)
2-я строка до последней строки D равна IF(A3-(B3 D2)<=0,-1*(A3-(B3 D2)),0)

Как получить то же самое в sql

 CREATE TABLE sampl (A int, B int);
INSERT INTO SAMPL VALUES (500, 400);
INSERT INTO SAMPL VALUES (300, 400);
INSERT INTO SAMPL VALUES (200, 500);
INSERT INTO SAMPL VALUES (700, 200);
INSERT INTO SAMPL VALUES (900, 800);
SELECT * FROM sampl
SELECT *, CASE WHEN A>B THEN B ELSE A END C, CASE WHEN A-B<=0 THEN -1*(A-B) ELSE 0 END D FROM sampl

Как получить несколько записей

 rn  Tab a   b   c   d    
1   AA  500 400 400 0    
2   AA  300 400 300 100    
3   AA  200 500 200 400    
4   AA  700 200 600 0    
5   AA  900 800 800 0    
1   BB  500 400 400 0    
2   BB  300 400 300 100    
3   BB  200 500 200 400   
4   BB  700 200 600 0

1. SQL — это не Excel; не относитесь к нему подобным образом. Вы не можете ссылаться на другие строки по значению индекса столбца и строки.

2. Вам нужно посмотреть на функцию задержки

3. Есть ли у вас какой-либо столбец, который мы можем использовать для определения порядка этих строк; ни A , ни B не находятся в порядке возрастания. В SQL Server нет «Порядка вставки», данные хранятся в неупорядоченной куче. Если вам нужно иметь возможность ссылаться на следующую / предыдущую строку, вы можете сделать это с помощью LEAD / LAG , однако для этого у вас должно быть ORDER BY предложение. ORDER BY sampl.A не сохранит порядок строк при их вставке.

4. Это можно сделать только с помощью рекурсии, потому что d зависит от … d.

5. Похоже, что rCTE будет одним из методов тогда. Тем не менее, нам нужен способ определить порядок данных с помощью ORDER BY .

Ответ №1:

Я перевел формулу в рекурсивный CTE:

 DECLARE @tbl TABLE (tab VARCHAR(100), rn INT, a INT, b INT);
INSERT INTO @tbl VALUES
('aa', 1, 500, 400),
('aa', 2, 300, 400),
('aa', 3, 200, 500),
('aa', 4, 700, 200),
('aa', 5, 900, 800),
('bb', 1, 500, 400),
('bb', 2, 300, 400),
('bb', 3, 200, 500),
('bb', 4, 700, 200);

WITH rcte AS (
    SELECT tab
         , rn
         , a
         , b
         , IIF(a > b, b, a) AS c
         , IIF(a - b <= 0, -1 * (a - b), 0) AS d
    FROM @tbl
    WHERE rn = 1

    UNION ALL

    SELECT curr.tab
         , curr.rn
         , curr.a
         , curr.b
         , IIF(curr.a > curr.b   prev.d, curr.b   prev.d, curr.a)
         , IIF(curr.a - (curr.b   prev.d) <= 0, -1 * (curr.a - (curr.b   prev.d)), 0)
    FROM @tbl AS curr
    JOIN rcte AS prev ON curr.tab = prev.tab AND curr.rn = prev.rn   1
)
SELECT *
FROM rcte
ORDER BY tab, rn

Обратите внимание, что я добавил rn столбец к данным. Вы могли бы использовать ROW_NUMBER() OVER (PARTITION BY tab ORDER BY foo) для создания этого столбца, если необходимо. Также обратите внимание, что a - b <= 0 может быть записано как a <= b .

Демонстрация в db<>fiddle

1. Это работает. Как получить то же самое для нескольких записей

2. Смотрите пересмотренный ответ.

3. @SalmanA . . . Я почти уверен, что для этого необходим рекурсивный CTE из-за if() .