Вычитание значения из предыдущей строки sql по группе
У меня есть следующий набор данных:
DATA_FIM CENTRO C M ESTOQUE
2018-02-01 HD01 CD 70 539.000
2018-03-01 HD01 CD 70 511.000
2018-04-01 HD01 CD 70 468.000
2018-05-01 HD01 CD 70 447.000
2018-06-01 HD01 CD 70 382.000
2018-07-01 HD01 CD 70 348.000
2018-08-01 HD01 CD 70 285.000
2018-09-01 HD01 CD 70 245.000
2018-10-01 HD01 CD 70 221.000
2018-11-01 HD01 CD 70 207.000
2018-12-01 HD01 CD 70 122.000
2018-12-21 HD01 CD 70 101.000
2018-02-01 HD01 CD 71 164.000
2018-03-01 HD01 CD 71 147.000
2018-04-01 HD01 CD 71 124.000
2018-05-01 HD01 CD 71 107.000
2018-06-01 HD01 CD 71 78.000
1- Рассматривая 2-й, 3-й, 4-й столбцы как группу, я хочу вычесть значения 5-го столбца из предыдущей строки того же столбца.
Для создания новой строки.
[![Данные][2]][2]
Это очень похоже на эту проблему: [Выпуск: 13196190][3], однако в моем случае у меня несколько индексов, и я не знаю, как решить.
ОБНОВЛЕНО
Я использую предложенные коды
select
ZBI_FAT_PRODUCT.DATA_FIM,
ZBI_DIM_EMPRESA.CENTRO,
ZBI_DIM_EMPRESA.CANAL,
ZBI_DIM_PRODUCT.MATERIAL,
ZBI_DIM_PRODUCT.TITULO,
ZBI_FAT_PRODUCT.ESTOQUE_VENDA,
LAG(ZBI_FAT_PRODUCT.ESTOQUE_VENDA, 1, ZBI_FAT_PRODUCT.ESTOQUE_VENDA) OVER (PARTITION BY ZBI_DIM_EMPRESA.CENTRO, ZBI_DIM_PRODUCT.MATERIAL, ZBI_DIM_PRODUCT.TITULO ORDER BY ZBI_FAT_PRODUCT.DATA_FIM) - ZBI_FAT_PRODUCT.ESTOQUE_VENDA AS NEW
FROM ZBI_DIM_EMPRESA INNER JOIN (ZBI_DIM_PRODUCT INNER JOIN ZBI_FAT_PRODUCT ON ZBI_DIM_PRODUCT.BK = ZBI_FAT_PRODUCT.BK_MATERIAL) ON ZBI_DIM_EMPRESA.BK = ZBI_FAT_PRODUCT.BK_EMPRESA;
Хорошо, я вижу, вам всегда нужна разница, кроме первой строки, где она равна 0.
Да вы правы.
Вы пробовали использовать оконную функцию LAG? Он доступен только с версии 2012+, но здесь он подходит как влитой.
@ Цезарь Я удалил свой ответ, потому что при проверке ответ Гордона оказался правильным. На самом деле вам не нужно COALESCE, это будет работать только с функцией задержки как таковой: LAG(ZBI_FAT_PRODUCT.ESTOQUE_VENDA, 1, ZBI_FAT_PRODUCT.ESTOQUE_VENDA) OVER (PARTITION BY ZBI_DIM_EMPRESA.CENTRO, ZBI_DIM_PRODUCT.MATERIAL, ZBI_DIM_PRODUCT.TITULO ORDER BY ZBI_FAT_PRODUCT.DATA_FIM) - ZBI_FAT_PRODUCT.ESTOQUE_VENDA AS NEW
Большое спасибо @BarryPiccinni
Ответы 2
Вы, кажется, просто хотите lag():
select el.*,
(lag(estoque, 0, estoque) over (partition by centro, c, m order by date_mif) -
estoque
) as diff
from energylog el;
Обновлено:
Для отредактированного вопроса только два столбца составляют группу
select el.*,
(lag(estoque, 0, value) over (partition by centro, canal, order by date_mif) -
estoque
) as diff
from energylog el;
LAG — это решение, но, вероятно, здесь больше подходит COALESCE(LAG(ESTOQUE) OVER (PARTITION BY CENTRO, C, M ORDER BY DATA_FIM) - ESTOQUE, 0) AS [New]. +1. Спасибо.
Я пытаюсь использовать оба предложенных метода, однако большинство строк из «estoque» и «[New]» имеют значение 0. (раньше это было не так).
@Жоров. . . coalesce() в этом случае не нужен; аргумент по умолчанию для lag() обрабатывает первую строку в каждой группе.
@Цезарь. . . Ваши образцы данных и данные в вашем редактировании отличаются. Кажется, вы хотите определить группы только двумя столбцами, а не тремя.
Что я должен использовать для внутренней функции задержки? Когда я сохраняю аргумент «Значение», он возвращает сообщение 207, уровень 16, состояние 1, строка 9. Недопустимое имя столбца «значение».
Я снова обновил код, см. выше. Он работает только без аргумента «Значение»
@GordonLinoff Да, без COALESCE должно быть LAG(ESTOQUE, 1, ESTOQUE) OVER (PARTITION BY CENTRO, C, M ORDER BY DATA_FIM) - ESTOQUE AS [New],
Для краткости и извлечения решения из комментариев -
Ответ заключается в использовании функции LAG():
SELECT *,
LAG(ESTOQUE, 1, ESTOQUE) OVER (PARTITION BY CENTRO, C, M ORDER BY DATA_FIM) - ESTOQUE AS New
FROM EnergyLog
Вот пример такой работы: SQLFiddle
Большое спасибо @BarryPiccinni
«Я хочу вычесть значения 5-го столбца из предыдущей строки» - из того же столбца предыдущей строки? А для 3-й строки вы хотите вычесть ее из необработанного значения 2-й строки или из уменьшенного значения?