Добавьте смещение к строкам, чтобы получить монотонно возрастающие значения
на основе этого потока (Проверить строки на монотонно возрастающие значения) у меня есть дополнительное требование: Столбец значения представляет собой счетчик. В моем приложении по какой-то досадной причине значение счетчика время от времени сбрасывается, т.е. начинается с нуля. Для оценки данных мне нужно накопленное значение всех отсчетов. Моя идея заключалась в том, чтобы создать дополнительный столбец, содержащий накопленное значение. Пока не происходит сброса, значение нового столбца такое же, как и в столбце исходного значения. После сброса значением нового столбца будет последнее накопленное значение + текущее значение счетчика. Данные могут быть сброшены несколько раз. Еще раз, строки с одинаковым «именем» принадлежат одному и тому же измерению и должны обрабатываться с сортировкой по Meas_date.
Это исходные данные:
id name meas_date value
1 name1 2018/01/01 1
2 name1 2018/01/02 2
3 name2 2018/01/04 2
4 name1 2018/01/03 1
5 name1 2018/01/04 5
6 name2 2018/01/05 4
7 name2 2018/01/06 2
8 name1 2018/01/05 2
Желаемый результат будет
id name meas_date value accumulated_value
1 name1 2018/01/01 1 1
2 name1 2018/01/02 2 2
3 name2 2018/01/04 2 2
4 name1 2018/01/03 1 3
5 name1 2018/01/04 5 7
6 name2 2018/01/05 4 4
7 name2 2018/01/06 2 6
8 name1 2018/01/05 2 9
Функция LAG из упомянутого выше потока действительно помогает найти строки, в которых было сброшено значение счетчика. Но теперь я изо всех сил пытаюсь совместить это с накоплением значений, чтобы получить общие значения счетчика.
Большое спасибо,
Христианин
Это может показаться странным вопросом, но что вызывает сброс поля «значение»? Разве не было бы разумным поступить так, чтобы просто исправить эту проблему и использовать ее? (Предполагая, что сброс не является преднамеренным)
Думаю, слово «счетчик» вводило в заблуждение. Мне не нужен счетчик вроде увеличивающегося индекса в строках, поэтому rownum не помогает. Значение счетчика содержит количество событий, которые произошли в течение дня (указанного с помощью параметра Meas_date) на испытательном стенде. Для оценки общего количества событий, среднего количества событий в день, неделю и т. д. На испытательном стенде мне нужно накопленное количество событий с момента начала измерения до каждого дня измерения.
В одних случаях сброс является преднамеренным, в других - нет. Проблема, которая приводит к непреднамеренным случаям, будет устранена, но вовлечено много заинтересованных сторон, и это займет некоторое время. Из прошлого у меня есть много миллионов измерений, с которыми мне нужно работать, поэтому я ищу то, что я описал выше.
@Christian - Как у вас получилось аккумулированное_значение = 3 в строке №4? Должно ли это быть 4 = значение в row1 + row2 + row4? Или оставайтесь на 1, потому что даты тактов разные.
row1 и row2 имеют возрастающие значения (1,2), что означает, что сброса не было. В строке 4 значение меньше, чем в строке 2, поэтому оно было сброшено. Последнее «допустимое» значение (строка2) должно использоваться как смещение. Итак, 3 правильно, так как это 2 (смещение от строки2) + 1 (текущее значение из строки4). Meas_date используется только для сортировки, а не для фильтрации или группировки.
Вы не можете использовать row2> row1 как доказательство того, что сброса не было. Могло быть 1 событие для строки 1, сброс, затем 2 события для строки 2, если я понимаю вашу конструкцию.
Реальная ситуация немного сложнее, я просто упростил ее для этого вопроса. Имея реальные данные, я могу решить, произошел сброс или нет.
@Christian - продолжаю вопросы. А как у вас получилось 7 в ряду 5? Строка 5 является увеличивающейся строкой в соответствии с вашей логикой. Стоит ли оставаться на 5 ?. Мне удалось получить правильные результаты до строки 4 с помощью функции LAG (), просто добавив значение текущей строки и предыдущей строки в случае, если текущая строка меньше, чем значение предыдущей строки. Таким образом, значения в строке 4 равны 1-current val, а значение prev - 2, что дает правильный результат.
@Art - для name1: row1 и row2 просты, накопленное значение равно 1 и 2, поскольку сброса не произошло. row4 имеет сброс, счетчик начинается с нуля. Накопленное значение - это «старое» значение (из строки2) + новое значение => 2 + 1 = 3. В строке 5 нет сброса по сравнению со строкой 4, поэтому накопленное значение - это «старое» значение + новое значение => 2 + 5 = 7 (строка 2 + строка 5). В row9 произошел еще один сброс. Накопленное значение теперь row2 + row5 + row9 = 2 + 5 + 2 = 9
Ответы 2
Это полезно?
select id, name, meas_date, value, sum(value) over(partition by meas_date order by meas_date, value ) from #temp
group by id, name, meas_date, value
order by meas_date, value
К сожалению, при этом не учитывается, что не все значения необходимо накапливать. Вместо этого необходимо применить смещение. Смещение для последовательного набора строк (по отношению к измерению_date) - это сумма всех «последних» значений всех предыдущих наборов строк. Набор здесь означает последовательные строки без сброса значения.
Думаю, я нашел решение, которое требует двух шагов:
-- 1. set flag column = 2 for all rows with values right before an reset
update TEST dst set dst.flag = (
with src as (
SELECT id, name, value,
CASE WHEN value < value_next THEN 0 ELSE 2 END AS flag
FROM (
SELECT id, name, value,
LEAD(value, 1, 0) OVER (PARTITION BY name order by meas_date) AS value_next
FROM TEST
)
)
select src.flag from src where dst.id = src.id
)
-- 2. Use SQL for Modeling to calculate the accumulated values
SELECT name, meas_date, value, offset, value+offset as accumulated_value
FROM TEST
MODEL RETURN UPDATED ROWS
PARTITION BY (name)
DIMENSION BY (meas_date, flag)
MEASURES (value, 0 as offset)
RULES (
offset[meas_date, ANY] ORDER BY meas_date = NVL(sum(NVL(value,0))[meas_date < CV(meas_date), flag=2],0)
);
После шага 1:
id name meas_date value flag
1 name1 01.01.18 1 0
2 name1 02.01.18 2 2
3 name2 04.01.18 2 0
4 name1 03.01.18 1 0
5 name1 04.01.18 5 2
6 name2 05.01.18 4 2
7 name2 06.01.18 2 2
8 name1 05.01.18 2 2
Результат шага 2
name meas_date value offset accumulated_value
name1 01.01.18 1 0 1
name1 02.01.18 2 0 2
name1 03.01.18 1 2 3
name1 04.01.18 5 2 7
name1 05.01.18 2 7 9
name2 04.01.18 2 0 2
name2 05.01.18 4 0 4
name2 06.01.18 2 4 6
Покажите, пожалуйста, результат.
Почему? Если вы просто хотите видеть это на выходе, добавьте rownum к выбранным атрибутам. Слишком много состояний гонки, чтобы быть уверенным, что СУБД будет генерировать монотонно возрастающие числа со счетчика. Если вы счастливы снизить производительность базы данных, вы можете ограничить вставки / обновления процедурой с автономной транзакцией и тяжелой блокировкой.