Форум MySQL

Собственно, в продолжение тем:
а) http://softtime.ru/forum/read.php?id_forum=3&id_theme=77952
б) http://softtime.ru/forum/read.php?id_forum=1&id_theme=64873

О чём речь: есть услуга, стоимость которой колеблется в разное время года (в разные периоды). Нам нужно выяснить сколько будет итоговая стоимость услуги за пользование в обозначенный период времени.

Есть год, описанный периодами (unix time):
||------- A --------||---------- B --------||---------- C ----------||----------- D ---------||
............................................ {Ua------------ user select --------------Ub} ...........

Каждый период описан двумя значениям a и b.
Конец периода равен началу следующего, то есть Ab = Ba.

В БД периоды описываем след. образом:
id | serviceID | dateFrom | dateTo
то есть, на каждую услугу мы можем иметь несколько периодов, которые в конечном итогда должны охватывать весь год.

Формат даты. Я выбрал Unix timespamp. Важно учесть, что в течении года бывают переводы стрелок, и что бы избежать лишних проверок и корректировок в БД я пишу дату по Гринвичу, т.е. с нулевым смещением временной зоны. Соответственно, при дальшейших запросах переданные в функцию даты перед запросом в БД привожу к Гринвичу.

Отмечу, что время в timestamp я задаю как 00:00, впрочем, думаю можно любое, зависит от конкретной задачи; главное, что бы выполнялось условие Ab = Ba, о чём было сказано выше.

Реализация
Нам нужно узнать, допустим, сколько стоит прокат машины с 15 мая по 27 ноября.
На рисунке этот промежуток обозначен как Ua-Ub.

Делам запрос в БД, что бы получить все периоды, которые задел наш промежуток Ua-Ub.

... where Ua <  dateTo AND Ub > dateFrom

|------- A --------| |---------- B --------| |---------- C ----------| |----------- D ---------|
............................................ {Ua------------ user select --------------Ub} ...........

Дальше в цикле смотрим каждый сезон и считаем кол-во дней:

если в периоде
if (Ua >= dateFrom && Ub <= dateTo) days = Ub - Ua;
если за рамками, или равен
if (Ua <= dateFrom && Ub >= dateTo) days = dateTo- dateFrom ;
если вышли за правый край
if (Ua >= dateFrom && Ub > dateTo) days = dateTo- Ua;
если вышли за левый край
if (Ua < dateFrom && Ub <= dateTo) days = Ub- dateFrom ;

days = bcdiv(days ,86400);

Ну вот, после всех проверок мы имеем в переменной days количество дней в периоде на текущей интерации. Вернее, не дней - а ночей.

ps Остался ещё один момент: если конец заданного пользователем отрезка в следующий год попадает, или находится целиком там. Есть идеи, но о них позже.

Критика и советы принимаются.

что, совсем нечего добавить, товарищи? :) не верю в идеализм предложенного решения.

где таблицы? :))

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

Вот, набросал то, как должна решаться задача в моем понимании.

CREATE TABLE `tariffs` (   `id` int(11) NOT NULL auto_increment,   `t_name` varchar(50) default NULL,   `a` int(10) unsigned default NULL,   `b` int(10) unsigned default NULL,   `rate` int(10) unsigned default NULL,   PRIMARY KEY (`id`),   KEY `t_name` (`t_name`) ) ;

INSERT INTO tariffs (t_name, a, b, rate)  VALUES ('Winter', UNIX_TIMESTAMP('2010-12-01 00:00:00'), UNIX_TIMESTAMP('2011-03-01 00:00:00'), 40), ('Spring', UNIX_TIMESTAMP('2011-03-01 00:00:00'), UNIX_TIMESTAMP('2011-06-01 00:00:00'), 30), ('Summer', UNIX_TIMESTAMP('2011-06-01 00:00:00'), UNIX_TIMESTAMP('2011-09-01 00:00:00'), 10), ('Autumn', UNIX_TIMESTAMP('2011-09-01 00:00:00'), UNIX_TIMESTAMP('2011-12-01 00:00:00'), 20);

CREATE TABLE orders  (   id INT(11) NOT NULL DEFAULT NULL PRIMARY KEY AUTO_INCREMENT ,   `ord_name` VARCHAR(50) NULL,    `a`  INT(11) NOT NULL,   `b`  INT(11) NOT NULL );

INSERT INTO orders (ord_name, a, b)  VALUES ('Vetal',  UNIX_TIMESTAMP('2010-12-23 00:00:00'), UNIX_TIMESTAMP('2011-08-25 00:00:00')), ('John',   UNIX_TIMESTAMP('2011-02-18 00:00:00'), UNIX_TIMESTAMP('2011-07-12 00:00:00')), ('Lucie',  UNIX_TIMESTAMP('2011-04-05 00:00:00'), UNIX_TIMESTAMP('2011-09-30 00:00:00')), ('Juditt', UNIX_TIMESTAMP('2011-10-02 00:00:00'), UNIX_TIMESTAMP('2011-11-04 00:00:00'));

SELECT ord_name             , DATE(FROM_UNIXTIME(o.a)) AS oa               , DATE(FROM_UNIXTIME(o.b)) AS ob               , t_name             ,SUM(rate/86400 *                  (                       case when o.b < t.b then o.b else t.b end                   -                    case when o.a  > t.a then o.a else t.a end                  )                ) as total   FROM orders o      LEFT JOIN tariffs t        ON case when o.a  > t.a then o.a else t.a end             <                case when o.b < t.b then o.b else t.b end    GROUP BY o.id, o.ord_name, o.a, o.b;

+----------+------------+------------+-------+  | ord_name |      oa    |      ob    | total |  +----------+------------+------------+-------+  |  Vetal   | 2010-12-23 | 2011-08-25 | 6330  |  |  John    | 2011-02-18 | 2011-07-12 | 3610  |  |  Lucie   | 2011-04-05 | 2011-09-30 | 3210  |  |  Juditt  | 2011-10-02 | 2011-11-04 |  660  |  +----------+------------+------------+-------+

Красиво. Эх, век живи, век учись.

Ну хорошо, а если начало в этом году, конец в следующем? Или весь заданный отрезок вышел за рамки заданых в БД периодов?

В коде можно этот момент отловить и обработать. А тут как?

>Ну хорошо, а если начало в этом году, конец в следующем?

Никакой привязки к границе года в модели нет. Запрос работает для любых интервалов.


>Или весь заданный отрезок вышел за рамки заданых в БД периодов?

Значит заданные периоды нетарифицированы,
Значит посчитаны будут только тарифицированные отрезки.
Значит надо найти того, кто отвечает за тарификацию периодов очередного года и набить ему рожу попросить внести в БД (модель) соответствующие отрезки с тарифами.
Вот, собственно, и всё.

>В коде можно этот момент отловить и обработать. А тут как?

Никто не мешает всё это обработать кодом на прикладном уровне.
Запрос будет не аналитический, а просто выбирающий все пересеченные отрезки.
А уж код сделает что напишите.