Mysql - Расчет прибыли от продаж между датами
У меня есть таблица, которая выглядит так:
+--------+---------------------+-------+--------+-----------+
| PartNo | Date | Inv | Retail | Wholesale |
+--------+---------------------+-------+--------+-----------+
| 1 | 2018-05-12 00:00:00 | 15 | $100 | $90 |
| 2 | 2018-05-12 00:00:00 | 20 | $200 | $150 |
| 3 | 2018-05-12 00:00:00 | 25 | $300 | $200 |
| 1 | 2018-05-13 00:00:00 | 10 | $95 | $90 |
| 2 | 2018-05-14 00:00:00 | 15 | $200 | $150 |
| 3 | 2018-05-14 00:00:00 | 20 | $300 | $200 |
+--------+---------------------+-------+--------+-----------+
И я хочу, чтобы это выглядело так с запросом Mysql:
+--------+------+--------+
| PartNo | Sold | Profit |
+--------+------+--------+
| 1 | 5 | $25 |
| 2 | 5 | $250 |
| 3 | 5 | $500 |
+--------+------+--------+
Мне нужно сгруппировать по PartNo при расчете разницы между итоговыми значениями и прибылью за диапазон дат.
Прибыль единицы должна быть рассчитана путем вычитания оптовой продажи из розничной в последний день (или запись) диапазона дат.
Я чувствую, что это должно быть легко, но различия в диапазонах дат сбивают меня с толку, и обработка записей в диапазоне дат, которые не начинаются и не заканчиваются точно на вводе диапазона дат, теряет меня.
Любая помощь будет очень признательна.
Спасибо.
Почему результат 5 Продан для арт. № 123456?
При наличии выборочных данных гораздо проще читать такие значения, как 1, 2 и 3, вместо 123456, 456789 и 147258.
Я думаю, что в конце моего выступления мне нужно что-то в этом роде. ГДЕ Дата> = '2018-05-10' И Дата <'2018-05-14' + ИНТЕРВАЛ 1 ДНЯ ГРУППА ПО PartNo; Получение сумм прибыли и общих сумм меня бросает.
Как получить 25 прибыли за 123456? насколько я понимаю (100 - 90) + (95 - 90) = 15
Извини, ярл, я думал, что приукрашиваю это, но я понимаю твою точку зрения. Настоящие партно - 16 символов.
Прочтите Пункт GROUP BY, чтобы создать группу, а затем используйте некоторые вычисления в своем запросе для получения прибыли.
У PN 123456 изменилась розничная цена на последнюю дату, поэтому прибыль увеличивается до 25 долларов после продажи 5 и использования маржи последних дней. Возможно, мне следует использовать маржу предыдущего дня, я полагаю, все сводится к запуску этого утром или COB.
Кстати, итоговые суммы - это не проданные единицы, это единицы на полке или в инвентаре.
Благодаря MBijen, Group by будет простой, если говорить о количестве проданных единиц, но поскольку мне приходится рассчитывать проданные единицы на основе разницы между днями и днями, включение арифметики меня сбивает.
Просто сделал PN проще
Я неправильно понял вопрос. Я думал, что в каждой строке 1 заказ.
Совет: не храните "$"
Ответы 2
Вы можете просмотреть ситуацию в начале и в конце периода. Если начальная ситуация не обнаружена, предполагайте, что запасы отсутствуют. Если конечной ситуации не обнаружено, это означает отсутствие продаж в течение периода.
Например, для периода, начинающегося с 2018-05-13 и заканчивающегося 2018-05-14:
select parts.PartNo
, coalesce(FirstSale.Total, 0) - coalesce(LastSale.Total, FirstSale.Total, 0) as Sold
, (coalesce(FirstSale.Total, 0) - coalesce(LastSale.Total, FirstSale.Total, 0)) *
coalesce(LastSale.Retail - LastSale.Wholesale, 0) as Profit
from (
select PartNo
, max(case when Date < '2018-05-13' then Date end) as FirstEntry
, max(case when Date <= '2018-05-14' then Date end) as LastEntry
from Sales
group by
PartNo
) parts
left join
Sales FirstSale
on FirstSale.PartNo = parts.PartNo
and FirstSale.Date = parts.FirstEntry
left join
Sales LastSale
on LastSale.PartNo = parts.PartNo
and LastSale.Date = parts.LastEntry
Andomar, отличное простое решение. Прекрасно работал на моем примере и небольшом столе. Я думаю, что что-то в подвыборке «частей» и двух левых соединениях сильно сказывается на производительности входной таблицы с 60 тыс. Строк. Приветствуются любые рекомендации по производительности. Большое спасибо за это!
Ничего страшного, если отчетный запрос выполняется какое-то время? Если скорость важна, вы можете попробовать индекс на PartNo, Date, Total, Retail, Wholesale. Решение на стороне клиента также может ускорить процесс (например, считывание соответствующих строк в скрипте Python).
SELECT c.partno as partno,MAX(c.inv)-MIN(c.inv) as sold,SUM(CASE WHEN c.date = c.last_date THEN profit else 0 END)*(MAX(c.inv)-MIN(c.inv)) as profit
FROM (SELECT partno,date,inv,retail-wholesale as profit,MAX(date) OVER (partition by partno) AS last_date FROM test1)c
GROUP BY c.partno
ORDER BY c.partno;
Используя оконная функция, сначала добавьте новый столбец, чтобы отслеживать максимальную дату для каждого номера детали. Таким образом, внутренний запрос внутри ИЗ будет создавать такие строки с одним столбцом, добавленным к исходному набору данных,
| 1 | 2018-05-12 00:00:00 | 15 | $100 | $90 | **2018-05-13 00:00:00** |
Выделенное поле - это поле, добавленное в набор данных, которое является последней датой в диапазоне дат для этого номера детали!
Теперь из этого результата мы можем получить прибыль, проверив строку, в которой столбец даты равен новому столбцу, который мы добавили, что по сути вычисляет прибыль за последнюю дату путем вычитания оптовой продажи из розничной и умножения на проданные товары.
PS: Логика для проданных товаров - это группировка по частям и вычитание MIN (Inv) из MAX (Inv).
Мне нравится простота этого решения, но оно использует OVER (раздел), которого нет в моей версии mysql. Большое спасибо.
что ты уже испробовал?