Как я могу эффективно рассчитать продажи с n-го числа месяца?

Question

У меня установлена ​​актуальная база данных mysql, и мне нужна функция для расчета продаж между n-м числом прошлого месяца и сегодняшним днем. Функция будет вызываться несколько раз в день, потому что я ищу точку, в которой кумулятивные продажи с предыдущего числа месяца пересекают порог.

Фоном является то, что я разрабатываю сайт подписки. Клиенты будут регистрироваться в специальные дни года и совершать покупки в течение всего года. Я хочу, чтобы иметь возможность рассчитывать продажи на ежемесячной основе.

Если клиент подписывается на n-е число месяца, мне нужно рассчитать объем продаж между предыдущим n-м месяцем и сегодняшним днем.

Если клиент подписывается 28-го числа, а сегодня - 30-го, то можно подумать, что продажи были только 2 дня, но если сегодня 30 марта, то прошло 30 дней. В другом примере: если клиент зарегистрируется 31 октября, как это будет происходить 28 февраля или 30 апреля?

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

Спасибо в ожидании

Answer 1

Я мог бы порекомендовать TIMESTAMP и INTERVAL. Это просто и эффективно без необходимости делать что-то достаточно тривиальное

Итак, рассмотрим следующую схему и запрос

DROP TABLE IF EXISTS `example_sales`;
CREATE TABLE `example_sales`(
  `id` INT(11) UNSIGNED AUTO_INCREMENT,
  `id_customer` MEDIUMINT(8) UNSIGNED NOT NULL,
  `profits` DECIMAL(16,2) NOT NULL DEFAULT 0,
  `ts` TIMESTAMP NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- add some values, it doesn't matter the order in the example
INSERT INTO `example_sales`( `id_customer`, `profits`, `ts` ) VALUES
  ( 1, 10.00, NOW( ) - INTERVAL 12 DAY ),
  ( 1, 14.00, NOW( ) - INTERVAL 1 WEEK ),
  ( 1, 110.00, NOW( ) - INTERVAL 30 DAY ),
  ( 1, 153.00, NOW( ) - INTERVAL 8 DAY ),
  ( 1, 5.00, NOW( ) - INTERVAL 2 DAY ),
  ( 1, 97.00, NOW( ) - INTERVAL 13 DAY ),
  ( 1, 1.00, '2018-02-28 13:00:00' ),
  ( 1, 2.00, '2018-03-28 13:00:00' ),
  ( 1, 3.00, '2018-01-30 13:00:00' ),
  ( 1, 4.00, '2018-03-30 13:00:00' ),
  ( 1, 42.00, NOW( ) - INTERVAL 42 DAY );

Обновлено, так как я изначально не до конца понял вопрос. Я сохранил временную метку и внес некоторые исправления, которых раньше не заметил.

-- '2018-03-28' or '2018-03-29' instead of NOW( ) or anything you like
SET @last  := DATE( NOW( ) ); 
SET @first := LEAST( DATE_SUB( @last, INTERVAL 1 MONTH ), LAST_DAY( DATE_SUB( @last, INTERVAL 1 MONTH ) ) );

-- change last and first test different sets
SELECT `profits`, DATE( `ts` ) AS `date`
  FROM `example_sales`   
  WHERE `id_customer` = 1
 HAVING `date` BETWEEN @first AND @last 
 ORDER BY `date`;

И когда вы достаточно уверены, что это сделает работу

SELECT SUM( `profits` ), DATE( `ts` ) AS `date`
  FROM `example_sales`   
  WHERE `id_customer` = 1
 HAVING `date` BETWEEN @first AND @last;

Надеюсь, что на этот раз все получится.

Answer 2

Вы можете искать что-то вроде:

DATE_ADD(
    LAST_DAY(DATE_SUB(CURDATE(), interval 2 MONTH)), 
    INTERVAL 15 DAY
)

Учитывая количество дней N, он возвращает Nдень прошлого месяца.

На сегодняшний день, если дать 15 дней, это дает '2018-12-15' (15 числа прошлого месяца, например, 15 декабря 2018 года).

Если вы хотите, чтобы возвращаемая дата никогда не превышала последний день прошлого месяца (например, текущий месяц - март и N = 31), ты можешь использовать:

LEAST(
   DATE_ADD(
       LAST_DAY(DATE_SUB(CURDATE(), interval 2 MONTH)), 
       INTERVAL 15 DAY),
   LAST_DAY(DATE_SUB('CURDATE(), interval 1 MONTH))
)

Как правило, это:

SELECT LEAST(
   DATE_ADD(
       LAST_DAY(DATE_SUB('2018-03-28', interval 2 MONTH)), 
       INTERVAL 31 DAY),
   LAST_DAY(DATE_SUB('2018-03-28', interval 1 MONTH))
)

Yieds: '2018-02-28' - (последний день февраля).

Answer 3

Спасибо вам обоим за ваш вклад. Я уже потратил несколько часов на изучение этой проблемы, и некоторые из наиболее многообещающих ответов пришли из stackru, поэтому я решил опубликовать свой вопрос здесь.

Я согласен с тем, что вопрос плохо сформулирован, потому что потратил немного больше времени, пытаясь определить проблему. Как следствие, я собираюсь изменить проблему на что-то, что может быть легко решено.

Вместо того, чтобы полагаться на дату подписки, я собираюсь работать на основе календарного месяца. Эти силы меняются в других местах, но я думаю, что могу жить с ними.

еще раз спасибо