MySQL Рассчитать минимальную стоимость услуги с разным временем запуска
Предположим, у меня есть такая таблица:
Цены
-----------------------------------------------------------
| service_id | starting_time | ending_time | price_per_hour |
-----------------------------------------------------------
| 1 | 08:00:00 | 10:00:00 | 90 |
-----------------------------------------------------------
| 1 | 10:00:00 | 11:00:00 | 50 |
-----------------------------------------------------------
| 1 | 11:00:00 | 15:00:00 | 80 |
-----------------------------------------------------------
Теперь пользователь моей веб-страницы хочет найти поставщиков услуг по самой низкой цене. Они говорят мне время начала службы, продолжительность и (здесь самая сложная часть) допуск для времени начала. Скажем, служба должна начинаться в 10 утра, должна длиться 2 часа, но они готовы прийти на час раньше или позже (+/- 1 час до времени начала).
Я не могу придумать идею одного запроса, чтобы получить минимально возможную цену, в зависимости от того, начинается ли сервис в 9, 10 или 11 утра. Время начала допуска может варьироваться. Это просто, если нет допусков, вы просто выбираете все строки из цен, которые относятся ко времени обслуживания, вычисляете их долю в цене, а затем суммируете подзапрос. Но как мне ввести переменный начальный фактор времени в это?
Это упрощенный запрос для расчета цены, зная точную отправную точку услуги. :service_start а также service_end заполнители.
SELECT SUM(t1.price) FROM
(SELECT price_per_hour * TIME_TO_SEC( TIMEDIFF( IF(ending_time < :service_end,
ending_time, :service_end), IF(starting_time < :service_start, :service_start,
starting_time) ) ) / 3600 AS price FROM prices WHERE service_id = 1 AND NOT
(starting_time >= :service_end OR ending_time <= :service_start)) AS t1
И теперь я понятия не имею, куда идти дальше. Как ввести изменчивость времени запуска сервиса.
РЕДАКТИРОВАТЬ:
Хорошо, я думаю, что мне было лень думать об этом раньше. Я считаю, что это не может быть сделано с помощью MySQL. Я разбил его на математические уравнения, которые могли бы его решить, и я считаю, что это не то, что вы можете реализовать в MySQL (два уравнения с большим количеством переменных). Я думаю, что для того, чтобы получить то, что я хочу, понадобятся некоторые грубые вычисления на стороне PHP.
В этом случае я хотел бы минимизировать x * 90 + y * 50 + z * 80, где x, y, z - количество часов, взятых из каждого ценового диапазона, с учетом некоторых условий, таких как x,y,z%0,5 = 0 (полчаса), x+y+z = 2 и т. Д.
РЕДАКТИРОВАТЬ 2:
Я опишу проблему более подробно, так как кажется, что не достаточно конкретен:
У меня есть три таблицы, которые имеют отношение к этой проблеме:
Сервисы
-----------------------------------------------------------------------------------------------
| service_id | service_type_id | provider_id | name | starting_time | ending_time |
-----------------------------------------------------------------------------------------------
| 2 | 1 | 3 | 2014-02-07 08:00:00 | 2014-02-07 23:00:00 |
-----------------------------------------------------------------------------------------------
цены (больше прайс-лист)
------------------------------------------------------------------------------
| service_type_id | provider_id | starting_time | ending_time | price_per_hour |
------------------------------------------------------------------------------
| 1 | 3 | 08:00:00 | 10:00:00 | 90 |
------------------------------------------------------------------------------
| 1 | 3 | 10:00:00 | 11:00:00 | 50 |
------------------------------------------------------------------------------
| 1 | 3 | 11:00:00 | 15:00:00 | 80 |
------------------------------------------------------------------------------
| 1 | 3 | 15:00:00 | 23:00:00 | 70 |
------------------------------------------------------------------------------
service_slots
-----------------------------------------------------------------------------------
| service_slot_id | service_id | starting_time | ending_time | booked |
-----------------------------------------------------------------------------------
| 1 | 2 | 2014-02-07 08:00:00 | 2014-02-07 09:00:00 | 1 |
| 2 | 2 | 2014-02-07 09:00:00 | 2014-02-07 17:00:00 | 0 |
| 3 | 2 | 2014-02-07 17:00:00 | 2014-02-07 19:00:00 | 1 |
| 4 | 2 | 2014-02-07 19:00:00 | 2014-02-07 21:00:00 | 1 |
| 5 | 2 | 2014-02-07 21:00:00 | 2014-02-07 23:00:00 | 0 |
-----------------------------------------------------------------------------------
Первая таблица содержит график обслуживания провайдера. Провайдер говорит, что предлагает услугу с 8:00 до 23:00. Вы можете забронировать столько времени от этой услуги, сколько захотите (подумайте о бронировании теннисного корта).
У них также есть цены на эти услуги. Эта цена говорит о том, что каждый день (в течение срока действия прайс-листа) услуга стоит то и другое между этим и тем часом. Вы можете забронировать услугу в разных ценовых диапазонах. То есть, если в прайс-листе указано, что услуга стоит 90 с 8:00 до 10:00 и с 50:00 с 10:00 до 11:00, то вы можете бронировать с 9 до 11 часов. Тогда к вашему бронированию применимы два ценовых диапазона.
Третья таблица содержит информацию о том, какой чанк (слот) определенной услуги был зарезервирован.
Сначала я запрашиваю service_slots для доступных чанков, а затем мне нужно получить цену за всю длительность чанка, зная, что цена может измениться в середине продолжительности чанка.
Надеюсь, я с ума схожу, более ясно на этот раз
1 ответ
Тяжело отредактировано, так старая почта ушла!
Сначала проверьте мои коды.
Мой тестовый стол:
CREATE TABLE IF NOT EXISTS price(
id INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
pid INT(20) NOT NULL,
staring_time DATETIME,
ending_time DATETIME,
price INT(20) NOT NULL
);
И запросы:
SET @start_pref = '2014-02-07 02:00:00';
SET @end_pref = '2014-02-07 04:00:00';
SELECT
*,
(
SELECT id
FROM price
WHERE
pid = p.pid AND
starting_time <= @start_pref AND
ending_time > @start_pref
) AS id_first,
(
SELECT id
FROM price
WHERE
pid = p.pid AND
ending_time >= @end_pref AND
starting_time < @end_pref
) AS id_last,
(
SELECT IF(
id_first = id_last,
(
SELECT HOUR(TIMEDIFF(@end_pref, @start_pref)*price)
FROM price
WHERE id = id_first AND pid = p.pid
),
(
(
SELECT HOUR(TIMEDIFF(ending_time, @start_pref)*price)
FROM price
WHERE id = id_first AND pid = p.pid
) + (
SELECT HOUR(TIMEDIFF(@end_pref, starting_time)*price)
FROM price
WHERE id = id_last AND pid = p.pid
)
)
) FROM price WHERE pid = p.pid AND id = id_first
) AS total
FROM price p
WHERE
starting_time <= @start_pref AND @start_pref < ending_time
ORDER BY total ASC;
Это работает только тогда, когда @start_pref и @end_pref покрывают 1 - 2 последовательных временных графика.
Тем не менее, это не работает, когда привилегированное время охватывает более 2 записей.
Пример:
Starting pref time: 1am
Ending pref time: 5am
For one specific provider
1am - 2am - $10
2am - 3am - $20
4am - 5am - $30
Это потому, что я не думал об этом заранее, когда начал и закончил свой запрос.
Теперь, если это два или более, вам нужно больше подзапросов, чтобы найти эти идентификаторы, а затем добавить их на мой SELECT IF( подзапрос.
Также обратите внимание, как я добавил / добавил столбец id? Так что я могу легко сказать, если это id принадлежит к тому же pid на текущий запрос.
PID - это идентификатор провайдера.
Весь скрипт sql.