Возможно ли в UPDATE «видеть» результат обновления предыдущих строк?

Question

[Alexeytur]

Добрый день.

Имеется такая таблица:

CREATE TABLE PDIndicatorsHistory 
(
	RecId int PRIMARY KEY IDENTITY(1,1),
	ItemId int,
	ReportDate datetime,
	PD int
)

Такая функция:

FUNCTION GetPD 
(
    @ReportDate datetime,
    @ItemId int
)
RETURNS int
AS
-- сложная логика пропущена, интересует этот запрос

    SELECT  TOP 1 
	   @Rating = rih.Rating * 2
            FROM PDIndicatorsHistory AS rih 
            WHERE
		rih.ItemId= @itemId
                AND rih.ReportDate < @ReportDate
            ORDER BY rih.ReportDate DESC

    RETURN @Rating
END

Заполняю тестовыми данными:

INSERT INTO PDIndicatorsHistory (ItemId,ReportDate,PD) VALUES
(1, '2025-07-06', 2), (1, '2025-07-07', NULL), (1, '2025-07-08', NULL)

Хочу обновить все три строки одним UPDATE:

UPDATE PDIndicatorsHistory
		SET
			PD = GetPD (ReportDate, ItemId)
		WHERE
			ItemId= @ItemId
			AND ReportDate >= '2025-07-06'

Нужно одним оператором обновить все три строки, но т.к. GetPD сама использует внутри PDIndicatorsHistory,
нужно чтобы в операторе UPDATE функция GetPD вызывалась в порядке ReportDate ASC и могла "видеть" результат обновления предыдущей строки.

Нужно чтобы получился такой результат:

ItemId ReportDate PD
1 '2025-07-06' 2
1 '2025-07-07' 4
1 '2025-07-08' 8

Возможно ли такое сделать?

Comments

[alexalexes]

Вопрос. Вы хотите чтобы GetPD считал рейтинг с учетом уже обновленных данных при текущем проходе update, или на основе тех данных, которые были до начала транзакции?

[Alexeytur]

alexalexes,
При текущем проходе

[Akina]

Но обычный UPDATE при обновлении нескольких строк не дает определенного порядка выполнения.

Ну сделайте же нормально. В рекурсивном CTE (он гарантирует порядок обработки) получите данные для обновления (PrimaryID, New_PDRating) и по полученным значениям обновите вторую копию таблицы.

А ещё - я не вижу инициализации "предыдущего" обновлённого значения для первой записи набора, которая не имеет предыдущей записи.

[Alexeytur]

Akina,
Функция сделана так, что ей требуется для вычисления текущего значения, наличие предыдущего уже записанного в таблицу. Рекурсивное CTE поможет с этой проблемой?

[Akina]

Alexeytur,

Рекурсивное CTE поможет с этой проблемой?

При использовании критерия сортировки, обеспечивающего уникальность - несомненно.

[Alexeytur]

Akina,
Спасибо, попробую

[Alexeytur]

Akina,
Добрый день, переписал на рекурсивный CTE, но все равно функция "видит" только одно значение

DECLARE @DateFrom date = '2025-06-06'

;with cte as (
	SELECT TOP 100 PERCENT
		rih.recId,
		@DateFrom AS ReportDate,
		GetPD(rih.ScheduleId, rih.ReportDate, rih.CompanyId) AS PD
		FROM PDIndicatorsHistory  rih
		WHERE 
			rih.ItemId= 111407
			and rih.reportdate = @DateFrom
	ORDER BY reportdate, RecId
	UNION ALL
	SELECT TOP 100 PERCENT
		rih.recId,
		rih.ReportDate,
		GetPD(rih.ScheduleId, rih.ReportDate, rih.CompanyId) AS PD
		FROM PDIndicatorsHistory  rih
		JOIN cte ON rih.ReportDate = DATEADD(DAY, 1, cte.ReportDate)
		WHERE
			rih.ItemId= 111407
			AND rih.ReportDate <= GETDATE()
		ORDER BY reportdate, RecId

)
UPDATE rih
	SET
		PD = cte.pd
	FROM PDIndicatorsHistory  rih
	JOIN cte on cte.recid = rih.recid

Может, я неправильно написал CTE?
Я слышал, что такое можно сделать на основе MERGE. Вы знаете про этот способ?

[Akina]

Я думаю, что будет быстрее и проще, если вы сделаете модельный пример (и добавите его в текст вопроса). Упрощённый CREATE TABLE, наполнение начальными данными (4-5 записей должно хватить), упрощённая процедура (типа значение в следующей строке = значение в предыдущей умножить на два), и требуемое финальное состояние данных. Тогда можно будет прямо сразу делать запрос, а не крутить в воздухе пальцами.

[Alexeytur]

Akina,
Здравствуйте, добавил пример заполнения и желаемого результата

[сергей кузьмин]

вам нужно найти три строки и заменить в каждой столбец PD на значение вычислякмое из оконной функции ROW_NUMBER() да или нет ? при чем тут "предыдущие" ?

ItemId ReportDate PD ROW_NUMBER()
1 '2025-07-06' 2         1
1 '2025-07-07' 4         2
1 '2025-07-08' 8         3

оконные функции для идентификации одинаковых записей. это вроде элементарная задача

Answer 1

[Akina]

Пример возможной реализации. Демонстрация логики.

1. Создание тестовой таблицы

CREATE TABLE PDIndicatorsHistory 
(
	RecId int PRIMARY KEY IDENTITY(1,1),
	ItemId int,
	ReportDate datetime,
	PD int
);
  
INSERT INTO PDIndicatorsHistory (ItemId,ReportDate,PD) VALUES
(1, '2025-07-06', 2), (1, '2025-07-07', NULL), (1, '2025-07-08', NULL);

SELECT * FROM PDIndicatorsHistory;

2. Создание функции

CREATE FUNCTION dbo.GetPD 
(
    @prev_RecId INT,  -- идентификатор предыдущей записи, её исходное состояние
    @next_RecId int,  -- идентификатор текущей записи, её исходное состояние
    @prev_PD INT      -- значения отдельных полей обновлённой предыдущей записи
)
RETURNS int
AS

BEGIN
-- требуемая сложная логика, опирающаяся на идентификаторы текущей и предыдущей записей
-- что позволяет получить значения полей записей ДО запуска SELECT или там UPDATE,
-- а также на значения полей модифицированного состояния предыдущей записи, переданных по отдельности
    RETURN @prev_PD * 2;
END;

3. Обновление

WITH
-- первый CTE, просто нумерует записи по возрастанию даты для каждого ItemId отдельно
cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY ReportDate ASC) rn
    FROM PDIndicatorsHistory
    ),
-- рекурсивно обрабатываем записи в порядке возрастания номера
cte2 AS (
    SELECT RecId, ItemId, ReportDate, PD, rn
    FROM cte1
    WHERE rn = 1 -- начинаем с первой
    UNION ALL
    SELECT cte1.RecId,
           cte1.ItemId,
           cte1.ReportDate,
           dbo.GetPD(cte2.RecId, cte1.RecId, cte2.PD),
           cte1.rn
    FROM cte1
    JOIN cte2 ON cte1.ItemId = cte2.ItemId
             AND cte1.rn = cte2.rn + 1 -- а потом вторая, третья... пока не кончатся
    )
-- посчитав значения, которые нужно использовать при обновлении, выполняем само обновление
UPDATE PDIndicatorsHistory
SET PD = cte2.PD
FROM cte2
WHERE PDIndicatorsHistory.RecID = cte2.RecId;

fiddle

PS. Почему так сложно? Такова логика UPDATE (транзакции, роллбэки - по-другому никак). Сначала подготовить новое состояние для ВСЕХ модифицируемых записей, а потом выполнить их фактическое обновление. И соответственно SELECT к таблице внутри функции обращается к исходному состоянию таблицы, ему пофиг, что запись, к которой ты обращаешься, ПОДГОТОВЛЕНА к модификации. Она ещё не модифицирована.

Comments

[Alexeytur]

Здравствуйте, спасибо за помощь!

Есть пара моментов

1. Существующую сигнатуру GetPD менять возможности нет, она зависит от ItemId и ReportDate, а то что она иногда получает предыдущее значение из PDIndicatorsHistory - редкая ситуация.

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

Возможно ли как-нибудь это по-другому сделать, не прибегая к циклам?

[Akina]

Alexeytur,

Существующую сигнатуру GetPD менять возможности нет, она зависит от ItemId и ReportDate, а то что она иногда получает предыдущее значение из PDIndicatorsHistory - редкая ситуация.

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

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

Ну добавьте в запрос OPTION (MAXRECURSION 0).

[Alexeytur]

Akina,
Добрый день.

Изменил реализацию GetPD, чтобы она зависела от PDIndicatorsHistory, и запрос перестал видеть предыдущие строки.

CREATE FUNCTION dbo.GetPD 
(
    @ReportDate datetime,
    @ItemId int
)
RETURNS int
AS

BEGIN
    RETURN ISNULL((
        SELECT TOP 1
            PD * 2
            FROM TMP_PDIndicatorsHistory
            WHERE
                ItemId = @ItemId
                AND ReportDate < @ReportDate
			ORDER BY RecId DESC
  
  ),0)
END;

Fiddle https://dbfiddle.uk/ER_9PaV-

P.S. не знаете, почему фиддл выдает ошибку запуска, и на вашем не измененном примере и на моем доработанном?

[Akina]

Alexeytur, исправьте адрес ссылки на fiddle. Линк не должен заканчиваться символом тире.

и запрос перестал видеть предыдущие строки.

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

[Alexeytur]

Akina,
Но можно как-нибудь так сделать, чтобы функция видела именно состояние TMP_PDIndicatorsHistory на текущий момент полуобработанной таблицы?

С dbfiddle проблема, выдает ошибку даже на select 1; Не могу создать ссылку

[Akina]

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

[Alexeytur]

Akina,
Спасибо за помощь