Обновить строку, используя значение из строки до
я ищу строку обновления, используя значение из предыдущей строки.
У меня есть что-то вроде этого:
| Группа по | Значение1 | Значение2 | Значение2 - ожидаемый результат |
|---|---|---|---|
| 1 | 0 | 20 | 20 |
| 1 | 3 | Икс | 23 |
| 1 | 5 | Икс | 28 |
| 1 | 2 | Икс | 30 |
| 2 | 0 | 30 | 30 |
| 2 | 5 | Икс | 35 |
| 2 | 2 | Икс | 37 |
Значение2 = Значение2 из строки до + значение 1, но столбец «Группировать по» является важным. Если Value2 перед этим находится в другой группе, то: Value2 = Value2
Может ли тот же объяснить, как сделать это заявление об обновлении? Я пытался использовать CTE с функцией LAG, но всегда попадал в бесконечный цикл.
Code for create table:
create table test
(
[GroupBy] int
, [Date] date
, [Value1] int
, [Value2] int
)
Вставка данных:
INSERT INTO test ([GroupBy], [Date] [Value1], [Value2])
VALUES
(1, '2022-01-01', 0, 20),
(1, '2022-01-02', 3, NULL),
(1, '2022-01-03', 5, NULL),
(1, '2022-01-04', 2, NULL),
(2, '2022-01-01', 0, 30),
(2, '2022-01-02', 5, NULL),
(2, '2022-01-03', 2, NULL)
Первичный ключ по: [GroupBy], [Date]
Каков порядок строк, определяющих, что предшествует чему?!? Помните, что таблица представляет собой набор неупорядоченных строк. У вас должны быть столбцы, определяющие порядок в запросе. Без этой информации ваш вопрос не может быть решен, так как любой ответ будет недетерминированным (может возвращать разные SET при каждом выполнении)
я сделал обновления
Отвечает ли это на ваш вопрос? Вычислить промежуточный итог в SQL Server . В частности этот ответ stackoverflow.com/a/10309947/8895292
Я добавил ожидаемый результат в последний столбец. Вы знаете, что это возможно сделать с помощью @variable, но я хочу знать, будет ли использование CTE с LAG более оптимальным для ЦП моего сервера.
(Редактировать) Вам не нужен LAG. Разделенный SUM() OVER(....) должен помочь dbfiddle.uk/… . Голосование за закрытие, так как уже есть множество тем по промежуточным итогам.
Ответы 1
Проверить с помощью LAG-функция
-- Using LAG function
-- https://docs.microsoft.com/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699
;With MyCTE as (
SELECT
t.[date], t.GroupBy,t.Value1,Value2 = ISNULL(t.Value2,0),
PreValue2 = ISNULL(LAG(t.Value2,1,0) OVER (PARTITION BY t.GroupBy ORDER BY [date]),0)
FROM test t
)
SELECT [date], GroupBy, Value1, Value2, PreValue2, [Value1+PreValue2] = Value1+PreValue2
FROM MyCTE
После комментария, возможно, исходный запрос не был хорошо описан, и вам нужно не «Value2 из строки до + значение 1», а «СУММА всех Value2 из всех строк до + значение 1»
В этом случае проверьте это решение
-- Value1 + total of all previous Value2
;With MyCTE as (
SELECT
t.[date], t.GroupBy,t.Value1,Value2 = ISNULL(t.Value2,0)
,TotalPreValue2 = SUM(ISNULL(t.Value2,0))
OVER (PARTITION BY t.GroupBy ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
FROM test t
)
SELECT [date], GroupBy, Value1, Value2, TotalPreValue2, [Value1+TotalPreValue2] = Value1+TotalPreValue2
FROM MyCTE
GO
И если вам нужно что-то еще, например «сумма всех значений 2 из ВСЕХ предыдущих строк до + сумма всех значений 1 из предыдущих строк», проверьте это
-- total of all previous Value1 + total of all previous Value2
;With MyCTE as (
SELECT
t.[date], t.GroupBy,t.Value1,Value2 = ISNULL(t.Value2,0)
,TotalPreValue2 = SUM(ISNULL(t.Value2,0))
OVER (PARTITION BY t.GroupBy ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
,TotalPreValue1 = SUM(ISNULL(t.Value1,0))
OVER (PARTITION BY t.GroupBy ORDER BY [date] ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW)
FROM test t
)
SELECT [date], GroupBy, Value1, Value2, TotalPreValue2, TotalPreValue1, [TotalPreValue1+TotalPreValue2] = TotalPreValue1+TotalPreValue2
FROM MyCTE
Но это не правильный результат. Для строки 3 результат должен быть 28, для строки 4: 30, для строки 7: 37
Почему 28? Пожалуйста, объясните, так как этот результат соответствует вашему описанию в вопросе. Строка 3 получает значение предыдущего значения2, равное 0, плюс значение значения1, равное 5, и 0+5 = 5, так что же не так?!?
Это ваш запрос: Value2 из строки до + значение 1 сгруппировано по «groupBy», и это именно тот результат, который возвращает мой запрос. Пожалуйста, объясните, как вы получаете 28. Возможно, вы имеете в виду другой результат: Value2 от ВСЕ РЯДЫ до + значение 1. это совершенно другой запрос, и все же запрос будет почти таким же - пожалуйста, уточните, что вам нужно лучше
Я добавил еще два варианта для разных требований. Пожалуйста, проверьте, и если они не подходят, вам нужно описать точный ожидаемый набор результатов и описание того, как вы получили эти результаты.
Но я хочу обновить столбец Value2. Я имею в виду, что значение Value2 является рекурсией. Для row2: Value2 = Value2[before] + Value1 = 20 + 3 = 23 --теперь в row2 Value2 = 23 — это значение, используемое для обновления в row3. Для row3: Value2 = Value2[before ] + Value1 = 23 + 5 = 28 -- теперь в row2 Value2 = 28 и т. д.
Проверьте мои два добавления вариантов. Это разные запросы, но решение почти одинаковое. Пожалуйста, проверьте, и если они не подходят, вам нужно описать точный ПОЛНЫЙ ожидаемый набор результатов и описание того, как вы получили эти результаты.
Вариант 2 - то, что мне нужно. Спасибо!
Добро пожаловать :-)
Это могло бы быть быстрее, если бы вы сделали свою часть и предоставили запросы для создания таблицы и вставки демонстрационных данных вместо историй (описаний) о таблице и данных
:-)