Нарастающий итог с условиями
Итак, у меня есть таблица с 2 столбцами - Товар и количество. Колонка [элемент] уникальна, [количество] может иметь такое же значение. Задача: Из первой таблицы составить еще одну таблицу, содержащую дополнительный столбец - cumulative_total. Этот столбец добавляет максимальные значения столбца [количество], но когда cumulative_total достигает 160, вы должны last_value[cumulative_total] = 160, а last_value [количество] = 160 - last_value[количество]. Когда это условие выполнено, задача завершена.
Стол
Ожидаемый результат:
При необходимости скачайте ссылку .csv файл (https://anonfiles.com/1855h9Rby5/task_csv)
Истинный результат должен выглядеть так я пытаюсь сделать условие для cumulative_total.
Set @total:= 0;
Select item,
sum(quantity) over (ORDER BY quantity DESC) as quantity,
if (@total:= @total + `quantity` > 160,
@total:=160,
@total:= @total + `quantity`
) as cumulative_total
FROM task
Отредактированное сообщение: добавить ожидаемую таблицу
MySQL и Oracle - очень разные СУБД по (слишком) многим аспектам. Какой из них вы используете?
MySQL, мой плохой, в тегах удален oracle
Ваш вывод сделан только двумя строками или это просто пример?
Это просто пример, он может содержать больше
Рассмотрите возможность сокращения выборочных данных до 7–8 строк, но с полным ожидаемым выходом. Трудно понять, что происходит с записями, которые следуют за закрытой (cumulative_total = 160).
Когда мы достигаем [cumulative_total] = 160, то мы должны Last_value [количество], чтобы попасть в финальную таблицу, 160 - last_value [cumulative_total]. В примере - мы получаем больше 160 [cumulative_total], поэтому мы делаем его 160, и last_value[quantity](94) -> make 160 - last_value[cumulative_total](126) = 34
Не знаю, что вы подразумеваете под last_value в своем комментарии или в вопросе. покажите в виде текста набор данных и ожидаемые результаты для всего набора данных. это очень поможет.
О, вы говорите, что строки вывода останавливаются, как только вы достигаете 160, и количество (а не cumulative_total) последней строки может быть уменьшено?
Ответы 1
Если я правильно понимаю, вам просто нужно скорректировать каждое количество на сумму, на которую совокупный итог превысил 160, если таковые имеются. И удалите строки после того, как они достигли 160 во внешнем запросе, ища количества, которые стали отрицательными.
select *
from (
select
item,
quantity - greatest(0, sum(quantity) over (ORDER BY quantity DESC) - 160) as quantity,
least(sum(quantity) over (ORDER BY quantity DESC),160) as cumulative_total
from task
) cumulative
where quantity > 0
Рабочий пример
Хорошо, последний вопрос, пожалуйста, если мне нужно сделать тот же запрос, но количество из таблицы должно быть от 0 до 9. Как я могу ограничить это
Дополнение к предыдущему комментарию, нарастающим итогом, для цифр 0 - 9, должно быть не 160, а 40
Звучит так, как будто все, что вы хотите, это (sum(quantity) over (order by quantity desc)-1) % 160 + 1? предоставьте данные в текстовой форме с ожидаемыми результатами для ваших данных.