#sql-server #tsql #recursive-query
#sql-server #tsql #рекурсивный-запрос
Вопрос:
У меня есть приведенные ниже данные для одного магазина и одного продукта, и мне нужно вычислить столбец на основе другого столбца.
Начальный набор данных:
store product tran_date audit_date audit_bal inv_value
10001 323232 2020-01-01 null null 5
10001 323232 2020-01-02 2020-01-02 20 31
10001 323232 2020-01-03 null null 13
10001 323232 2020-01-04 null null 6
10001 323232 2020-01-05 null null 21
10001 323232 2020-01-06 null null 17
10001 323232 2020-01-07 null null 6
10001 323232 2020-01-08 null null 34
10001 323232 2020-01-09 null null 35
10001 323232 2020-01-10 2020-01-10 60 17
10001 323232 2020-01-12 null null 6
10001 323232 2020-01-13 null null 9
10001 323232 2020-01-14 null null 5
10001 323232 2020-01-15 null null 29
Логика:
start_stock для следующего за днем audit_date дня должно быть значение audit_bal audit_date
для оставшихся дней, это должны быть предыдущие дни end_stock
end_stock для следующего за днем audit_date должно быть audit_bal audit_date inv_value для оставшихся дней, это должно быть start_stock (предыдущие дни end_stock ) inv_value
Конечный набор данных должен быть
store product tran_date audit_date audit_bal inv_value start_stock end_stock
10001 323232 2020-01-01 null null 5 6 11
10001 323232 2020-01-02 2020-01-02 20 31 11 42
10001 323232 2020-01-03 null null 13 20 33
10001 323232 2020-01-04 null null 6 33 39
10001 323232 2020-01-05 null null 21 39 60
10001 323232 2020-01-06 null null 17 60 77
10001 323232 2020-01-07 null null 6 77 83
10001 323232 2020-01-08 null null 34 83 117
10001 323232 2020-01-09 null null 35 117 152
10001 323232 2020-01-10 2020-01-10 120 17 152 169
10001 323232 2020-01-12 null null 6 120 126
10001 323232 2020-01-13 null null 9 126 135
10001 323232 2020-01-14 null null 5 135 140
10001 323232 2020-01-15 null null 29 140 169
Я использовал приведенный ниже запрос, но не получил правильных результатов
WITH Inv AS (
SELECT *,case when tran_date = date_add(lag_audit_date,1) then lag_audit_bal inv_value
when date_add(lag_audit_date,1) != tran_date then lag_audit_bal inv_value
else SUM(inv_value) OVER (partition by store,product ORDER BY tran_date ASC ROWS UNBOUNDED PRECEDING) end as end_stock
FROM
basedata
)
SELECT tran_date,audit_date,audit_bal,lag_audit_date,lag_audit_bal,
case when tran_date = date_add(lag_audit_date,1) then lag_audit_bal
when date_add(lag_audit_date,1) != tran_date then lag_audit_bal
else LAG(end_stock,1,0) OVER (partition by store,product ORDER BY transaction_date ASC) end as start_stock,
inv_val,
end_stock
FROM Inv;
Может кто-нибудь, пожалуйста, помочь мне.
Комментарии:
1. Несколько сценариев крайнего случая, которые следует учитывать при разработке логики для такой проблемы — чтобы убедиться, что ваша логика надлежащим образом обрабатывает необходимые крайние случаи: 1) Что делать, если это приложение устанавливается в первый раз, и нет предыдущей строки — существуют ли какие-либо бизнес-правила для особых случаев/ логика, которая должна быть реализована для обработки этого? 2) Что делать, если предыдущая найденная строка находится не точно (N-1) дней от текущей строки, для которой необходимо выполнить вычисление (это возможный / обычный сценарий — или приложение должно обрабатывать это как фатальное исключение?
2. Некоторые дополнительные крайние случаи, которые следует учитывать: 1) Следует ли выполнять вычисления только для вставки для строки (N)? 2) Что делать, если строка (N) обновляется? 3) Что, если строка (N-1) впоследствии обновляется после выполнения вычисления для строки (N)? 4) Что делать, если строка (N-1) будет удалена?
3. В первый день мы ставим 0
Ответ №1:
.. ваш запрос, слегка скорректированный..
declare @t table (store int, product int, tran_date date, audit_date date, audit_bal int, inv_value int);
insert into @t
values
--store product tran_date audit_date audit_bal inv_value
(10001, 323232, '20200101', null, null, 5),
(10001, 323232, '20200102', '20200102', 20, 31),
(10001, 323232, '20200103', null, null, 13),
(10001, 323232, '20200104', null, null, 6),
(10001, 323232, '20200105', null, null, 21),
(10001, 323232, '20200106', null, null, 17),
(10001, 323232, '20200107', null, null, 6),
(10001, 323232, '20200108', null, null, 34),
(10001, 323232, '20200109', null, null, 35),
(10001, 323232, '20200110', '20200110', 120, 17),
(10001, 323232, '20200112', null, null, 6),
(10001, 323232, '20200113', null, null, 9),
(10001, 323232, '20200114', null, null, 5),
(10001, 323232, '20200115', null, null, 29);
select
store, product, tran_date, audit_date, audit_bal, inv_value,
--start_stock = end_stock-inv_value
end_stock-inv_value as start_stock, end_stock
from
(
--calculate end_stock
select *,
max(grp_audit_bal) over(partition by store, product, grp_audit_date order by tran_date)
sum(inv_value) over(partition by store, product, grp_audit_date order by tran_date) as end_stock
from
(
--groups are defined by latest audit_date
--also get the audit_bal per group (audit_bal is assigned only to the first member of the group, lag() is used)
select *,
max(audit_date) over(partition by store, product order by tran_date ROWS between UNBOUNDED PRECEDING and 1 PRECEDING) as grp_audit_date,
lag(audit_bal) over(partition by store, product order by tran_date) as grp_audit_bal
from @t
) as xyz
) as src;
Комментарии:
1. @lptr-Извините за мой предыдущий комментарий. Пожалуйста, не обращайте внимания. Я добавил несколько операторов case в запрос, который вы предоставили, и результаты были перепутаны. Я удалил его и попробовал выполнить точный запрос, как указано выше, он отлично работает. Большое вам спасибо за ваш ответ.
2. …@satya … приятно и спасибо за поддержку
Ответ №2:
В этом вопросе администратора базы данных была предложена следующая логика:
SELECT
s.stmnt_date, s.debit, s.credit,
SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS balance
FROM
statements AS s
ORDER BY
stmnt_date ;
Также обратите внимание на дополнительное обсуждение, касающееся a (все еще возможно?) Ограничение аналитической функции MySQL (если это применимо в вашем случае).
Эта публикация также предложила возможное (менее эффективное) решение, если ваша база данных не поддерживает приведенный выше синтаксис (примечание: я еще не тестировал ни одно из этих решений):
SELECT
s.stmnt_date, s.debit, s.credit,
@b := @b s.debit - s.credit AS balance
FROM
(SELECT @b := 0.0) AS dummy
CROSS JOIN
statements AS s
ORDER BY
stmnt_date ;
Если вы работаете с MySQL, вы также можете найти интересующую функцию LAG()
Обратите внимание на пример, проиллюстрированный в этом руководстве:
https://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag
SELECT
t, val,
LAG(val) OVER w AS 'lag',
LEAD(val) OVER w AS 'lead',
val - LAG(val) OVER w AS 'lag diff',
val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);
ЗАДЕРЖКА (выражение [, N[, по умолчанию]]) [null_treatment] over_clause
Возвращает значение выражения из строки, которая отстает (предшествует) текущей строке на N строк в пределах ее раздела. Если такой строки нет, возвращаемое значение по умолчанию. Например, если N равно 3, возвращаемое значение является значением по умолчанию для первых двух строк. Если N или default отсутствуют, значения по умолчанию равны 1 и NULL соответственно.
N должно быть буквальным неотрицательным целым числом. Если N равно 0, выражение вычисляется для текущей строки.
Начиная с MySQL 8.0.22, N не может быть нулевым. Кроме того, теперь это должно быть целое число в диапазоне от 1 до 263 включительно в любой из следующих форм:
an unsigned integer constant literal
a positional parameter marker (?)
a user-defined variable
a local variable in a stored routine
Комментарии:
1. @satya вам нужно предоставить более подробную информацию «не работает», нам точно ничего не говорит. Вместо этого объясните, как результаты, которые он генерирует, отличаются от ожидаемых.