MySQL: рассчитать оставшиеся дни продажи в месяце

Question

У меня есть расчет ежедневного целевого дохода, в котором указано, какой доход мне нужно зарабатывать ежедневно для достижения цели ежемесячного дохода:

'Revenue_Goal' / 'Selling_Days'

(Примечание: "Selling_Days" - это целое уже в наборе данных.)

То, что я пытаюсь достичь, - это получение скорректированной ежедневной цели, основанной на том, сколько дней прошло. Я ЧАСТИЧНО достиг этого с:

('Revenue_Goal' - SUM('Revenue')) / DATEDIFF(MAX('Date'),NOW())

Однако, часть, которую я не могу понять, это только расчет дней продажи в скорректированной формуле. Мне нужно использовать формулу выше (или что-то подобное), которая считает дни только когда:

• С понедельника по пятницу = 1 день продажи
• Суббота = 0,5 день продажи
• Воскресенье = 0

Итак, если сегодня 25.03.16, мой расчет DATEDIFF вернул бы 6, но моя цель - получить 4.5.

Любая помощь приветствуется!

Answer 1

Имеет смысл написать это как хранимую функцию. Я могу вспомнить три метода, каждый из которых улучшается по сравнению с предыдущим (хотя метод 2, вероятно, достаточно хорош).

Способ 1: начать с первой даты, увеличивать до второй даты. Для каждой даты звоните DAYOFWEEK(). Если воскресенье, то добавить 0, если суббота добавить 0,5, в противном случае добавить 1.

DROP FUNCTION IF EXISTS `count_days_goal`;

DELIMITER $$
CREATE FUNCTION `count_days_goal`(start_date DATE, end_date DATE) RETURNS FLOAT
    DETERMINISTIC
BEGIN
    DECLARE sell_days FLOAT;
    DECLARE dow INT;

    SET sell_days = 0.0;
    WHILE start_date <= end_date DO
        SET dow = DAYOFWEEK(start_date);
        IF (dow > 1) THEN 
            IF (dow = 7) THEN SET sell_days = sell_days + 0.5;
            ELSE SET sell_days = sell_days + 1.0;
            END IF;
        END IF;
        SET start_date = start_date + INTERVAL 1 DAY;
    END WHILE;

    RETURN (sell_days);
END$$
DELIMITER ;

Пара тестов (обратите внимание, что моя реализация включает последний день в подсчитанных днях):

select count_days_goal(DATE('2016-03-21'), DATE('2016-03-27'));
select count_days_goal(DATE('2016-03-01'), DATE('2016-03-27')); 

Это просто, но неэффективно. Требуется больше времени, чтобы пробежать дальше друг от друга дни. Имейте в виду, один вызов все равно будет быстрее, чем зрительная связь, но это будет немного оскорбительно для большинства разработчиков. К счастью, мы можем сделать лучше.

Способ 2: каждая полная неделя составляет 5,5 дней продажи. Итак, разделите количество дней между датами на 7, на минуту не обращайте внимания на остаток и умножьте на 5,5.

Пример: 32 дня (4 недели + 4 дня), что составляет 22 дня продажи плюс 4 остатка.

Это остаток, где все становится сложнее. Эти 4 дня могут включать субботу или нет. Самое простое решение - использовать метод 1 в течение последних нескольких дней. Это никогда не будет повторяться более 6 дней, поэтому это быстро.

Итак, теперь код выглядит так:

DROP FUNCTION IF EXISTS `count_days_goal`;
DELIMITER $$
CREATE FUNCTION `count_days_goal`(start_date DATE, end_date DATE) RETURNS FLOAT
    DETERMINISTIC
BEGIN
    DECLARE sell_days FLOAT;
    DECLARE dow INT;
    DECLARE whole_weeks INT;

    SET whole_weeks = FLOOR(DATEDIFF(end_date, start_date)/7);
    SET start_date = start_date + (7 * whole_weeks);
    SET sell_days = whole_weeks * 5.5;

    WHILE start_date <= end_date DO
        SET dow = DAYOFWEEK(start_date);
        IF (dow > 1) THEN 
            IF (dow = 7) THEN SET sell_days = sell_days + 0.5;
            ELSE SET sell_days = sell_days + 1.0;
            END IF;
        END IF;
        SET start_date = start_date + INTERVAL 1 DAY;
    END WHILE;

    RETURN (sell_days);
END$$
DELIMITER ;

Метод 3: Вы также можете использовать метод 2, но полностью избавиться от цикла. Чтобы увидеть, как это можно сделать, полезно иметь перед вами календарь. (Я не удосужился реализовать это; метод 2 довольно хорош.)

Вместо зацикливания звоните DAYOFWEEK в первое и последнее свидание Давайте назовем тех dow_start а также dow_end,

Если dow_start < dow_end тогда все эти досадные оставшиеся дни лежат внутри недели, они не переходят с субботы на воскресенье. Так что все, что вам нужно сделать, это:

SET answer = DATEDIFF(dow_end, dow_start) + 1;
IF dow_start = 1 (i.e. Sunday) THEN subtract 1 from answer
IF dow_end is 7 (i.e. Saturday) THEN subtract 0.5 from answer

Если dow_start > dow_end затем эти досадные оставшиеся дни оборачиваются. Я верю, что в этом случае вы можете поменяться dow_start а также dow_end (возвращая нас к предыдущему делу). Вычислите его, как и раньше, но затем вычтите ответ из 5.5 и добавьте 1. (Объяснение: посчитайте дни недели, которые НЕ находятся между day_start а также day_end, а затем вычтите их из общей суммы за неделю.)

Как я уже сказал, я не писал и не проверял этот, так что это может быть не совсем правильно.

Как обрабатывать банковские праздники: если у вас есть их в таблице, вы можете просто COUNT количество строк между двумя датами. Вычтите это из своего ответа. Легко! (Предполагая, что праздничные дни никогда не бывают выходными.) Существуют веб-сайты, на которых перечисляются банковские праздники для разных стран, даже один, который генерирует список дат для вас в далеком будущем, что облегчает создание таблицы выходных.

Answer 2

Это один из n способов вычислить это

SELECT
  SUM( 
    ELT( DAYOFWEEK(CONCAT( DATE_FORMAT(now(), '%Y-%m-'),nr)),
    '0','1','1','1','1','1','0.5') ) calc_days
FROM (
SELECT d2.a*10+d1.a  AS nr FROM (
  SELECT 0  a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 
  5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8  UNION ALL SELECT 9) AS d1
  CROSS JOIN (
  SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  ) AS d2
) AS x
WHERE nr BETWEEN  DAY(NOW()) AND DAY(LAST_DAY(NOW()))
ORDER BY nr;