Проблема, описанная в этом посте решена, но теперь задача усложнилась и требуется ваша помощь (в этом же топике). Буду благодарен! Заранее всем спасибо! 

Есть таблица, которая содержит информацию о тарифах. 
Структура таблица
id - Порядковый номер записи
idtariff - ID тарифа
date_start - дата начала действия тарифа в UNIX формате
date_finish - дата окончания действия тарифа в UNIX формате
price - Величина тарифа в указанный период

К примеру есть следующие записи



Т.е. с 01.05.2010 по 15.05.2010 величина тарифа с ID = 5 равняется 20 в день, а с 16.05.2010 по 31.05.2010 равняется 25 в день

Допустим, мне необходимо получить информацию о стоимости тарифа с 02.05.2010 по 07.05.2010. 
Здесь всё просто:
SELECT `price` FROM `table` WHERE `date_start` <= '1272744000' AND `date_finish` >= '1273176000'
Полученное значение умножаем на количество суток в нашем случае 5 и все довольны.

А что делать, когда нам необходимо подсчитать стоимость тарифа с 02.05.2010 по 25.05.2010.
Сейчас, я веду эти подсчёты с помощью цикла.
Т.е. беру первую дату 02.05.2010 (1272744000) и ищу стоимость по ней, затем беру следующую дату 03.05.2010 и прибавляю к предыдущей и так далее до даты равной 25.05.2010.
Получается на один подсчёт уходит более 20 запросок к БД. Считаю, что делаю очень неправильно, поэтому, хотелось бы проконсультироваться.

Спасибо.




Это сообщение отредактировал(а) letsgo - 27.7.2010, 13:08

я не совсем понял задачу, у меня подозрение на неправильную архитектуру, но твоя задача (то, что описано) решается просто
date_finish - date_start = разница в секундах
date_finish - date_start / 60 / 60 / 24 = количество суток
в первом случае
(1273176000 - 1272744000) / 60 / 60 / 24 = 5
тоже самое во втором случае.
и к PHP вопрос не имеет никакого отношения.

а вообще есть куча функций для работы с датой и временем (типа date_diff), смотри в manual-ах своей СУБД.

Это сообщение отредактировал(а) azesmcar - 24.3.2010, 10:54

Опишу может чуть подробнее.
В каждой строке таблицы хранится запись о величине тарифа в конкретном периоде. Т.е. в первой строке, указано, что с 1 мая по 15 мая тариф равняется 20 в день, во второй строке, что с 16 мая по 31 мая тариф == 25. Если мне нужно узнать стоимость тарифа со 2 мая по 7 мая. Я знаю, как это сделать одним запросом, потому что использоваться будет только одна строка.
Если же мне нужно узнать величину тарифа со 2 мая по 17 мая, я не могу это сделать одним запросом (  ), потому что величина тарифа с 01 мая по 15 мая хранится в одной строке, а данные для подсчёта тарифа с 16 по 17 уже в другой строке. И сейчас я эту проблему решаю с помощью цикла, что создаёт большую нагрузку на БД, так как запросов делается очень много.

Может с моей стороны будет наглостью,  но хотелось бы получить конкретный вид запроса, т.е.
сколько будет составлять тариф с 02.05.2010 (1272744000) по 17.05.2010 (1274126399). Сейчас я это делаю следующим образом






Да. Извиняюсь. Уже забыл, что здесь есть раздел для подобных вопросов.

Это сообщение отредактировал(а) letsgo - 24.3.2010, 11:44

letsgo

Если там две строки, то и цены могут быть разные, в таком случае нужна итерация по каждой строке..или мне не совсем понятно. Если цена та же самая (тогда зачем нужны две идентичные строки?) - тогда группируй по idtariff.

Это сообщение отредактировал(а) azesmcar - 24.3.2010, 11:43

Я отредактировал сообщение, добавил код, с помощью которого сейчас ведётся подсчет. Т.е., сейчас я выбираю величину тарифа для каждого дня. Если нам нужно подсчитать для 7 дней, делаем 6 запросов, нужно 17 дней делаем 16 запросов. И при каждом запросе увеличиваем переменную $date_start на 86400 секунд (т.е. на 1 сутки)


Да. одна строка = один период = одна цена, следующая строка уже другой период и другая цена соответственно

Это сообщение отредактировал(а) letsgo - 24.3.2010, 11:50

letsgo

ок. кажется понял.

Добавлено через 7 минут и 9 секунд
дай структуру таблицы и тестовые данные

Это сообщение отредактировал(а) azesmcar - 24.3.2010, 11:59

Это сообщение отредактировал(а) letsgo - 24.3.2010, 12:21

а данные где?

Извиняюсь. Задержка вышла.
7 периодов по тарифу с ID=1, начиная с 01.05.2010. Все по 15 дней, но это случайно.
Период идёт в следующем виде:
НачалоПериода (01.05.2010 00:00) - КонецПериода (15.05.2010 23:59)


Это сообщение отредактировал(а) letsgo - 24.3.2010, 13:01

letsgo

Сейчас времени нет долго ковыряться, написать можно с помощью SQL, предлагаю обойтись банальной оптимизацией цикла, т.е. сперва получить все тарифы для 16 дней а потом делать итерация по ним а не каждый раз запрашивать данные на 1 день.

Так чему же будет равен тариф, если до 15го было 20, а с 16го стало 25?

По какой формуле считается-то?

насколько я смог понять будет считаться с 15 по 16 по цене 20 а потом по цене 25,

Тогда считается не тариф, а сумма оплаты.
А это элементарно



Все. В итоге у нас цена и число дней когда эта цена действовала. Можно там же в запросе их перемножить и просуммировать.

Извиняюсь, а можно поподробнее, что это за "остальные 3 случая".
Буду благодарен

А логику применить?
Есть четыре варианта.
Период может попасть в промежуток дат 

•  целиком
•  концом
•  началом
• быть больше промежутка (т.е. промежуток сам находится в периоде).

Соответственно число дней, которые будут взяты из этого периода, будет разным.
Вариант когда период не попадает в промежуток дат отсеивается еще в WHERE (кстати там ошибка, его надо дополнить, чтобы брались данные и для 4го случая)

Отлично. Первый вариант и последний я реализовал. А вот как быть со 2 и 3. 
Если в первом и последнем варианте возвращается стоимость тарифа и количество дней (секунд), то я просто перемножал и получал нужный мне результат.
А что во 2 и 3 варианте должно возвращаться? Две и более строки?

А почитать мой пример?
Там как раз 3й вариант приведен в качестве образца подсчета числа дней.

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

Не посчитайте за наглость, но...
А как описать ситуацию, когда в запросе задаются параметры, скажем так несуществующего периода?
Допустим, у нас в базе содержится информация о стоимости тарифа в сутки для периода с 15.05.2010 по 31.05.2010, стоимость тарифа в июне не дана.
Но пользователь всё-таки запрашивает стоимость тарифа с 25.05.2010 по 03.06.2010, допустим. Как лучше оформить ограничение в запросе, когда $date_finish (в нашем случае 03.06.2010) в базе не существует? Бывает, что пользователь запрашивает стоимость тарифа на период, к примеру, с 15.05.2010 по 10.06.2010, а в базе по каким-то причинам, есть периоды 15.05.2010 - 05.06.2010 и 08.06.2010 - 31.06.2010
Т.е. случае разные бывают, когда нет нескольких дней или естьь величина тарифа до какого-то числа, а дальше нет. Это всё нужно предусмотреть, чтобы пользователю не выдавался результат меньше. В этих случаях должен нуль возвращаться.

Сейчас, я, к сожалению, вижу только одно решение, если возвращаемое количество days меньше, чем оно должно быть, то возвращать 0.  
Но плохо, что в этом случае, придётся обходить все строки, полученные при запросе.
Насколько это правильно? Или это лучше сделать в самом запросе? 

Это сообщение отредактировал(а) letsgo - 25.3.2010, 11:50

Основная причина - это количество запросов к БД.


Если обратили внимание, то запрос посчитает только дни из периодов существующих в базе.

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


Ноль за весь промежуток? или только за отсутствующие дни?
Если второе, то запрос это и делает (точнее он просто не считает отсутствующие дни).
Если первое, то необходимо пересмотреть сам запрос.

Игнорировать нельзя. Если стоимости тарифа за какой-то день в базе нет, но в периоде запроса пользователя этот день присутствует, мы должны вернуть однозначный нуль. Даже, если период заданный пользователем равен 365 дням, а в базе нет данных только об одном дне, всё равно возвращать нуль.


именно за весь промежуток. 
Если у нас с 01.05.2010 по 15.05.2010 в базе есть данные о стоимости тарифа, а с 16.05.2010 и далее уже нет, то на запрос пользователя о стоимости тарифа с 01.05.2010 по 17.05.2010 мы должны вернуть именно нуль, а не стоимость тарифа с 01.05.2010 по 15.05.2010

Это сообщение отредактировал(а) letsgo - 25.3.2010, 18:10

Ну это несколько грустно.
А в чем логика такого поведения? Не поделитесь?

Что же касается решения задачи, то придется сделать еще один запрос для проверки непрерывности сетки тарифов для указанного периода.
В случае если сетка будем иметь дырки, запрос для суммирования можно просто не выполнять, а отдать пользователю 0.

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


А как считаете, что будет быстрее?
Делать еще один запрос или же проверять разницу: "количество дней, которое возвращает запрос" - "количество дней, заданное пользователем"? Если количество дней не совпадает, следовательно возвращаем нуль.

Именно так, я сейчас эту "дырку" в алгоритме и заткнул, но минус в том, что всё равно приходится совершать лишние манипуляции при обработке возвращаемого результата. Если подскажите, как лучше организовать подобный запрос, с удовольствием попробую на скорость.

Это сообщение отредактировал(а) letsgo - 25.3.2010, 19:05

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


Взять и проверить оба варианта на конкретных данных. Я все же не сервер и в уме не умею делать профайлинг 




Предусмотренное поведение не является дырой.

С момента решения данной проблемы прошло уже большое количество времени и ситуация несколько усложнилась.
Помогите решить новую проблему с подсчётом тарифа с учетом новых изменений.

В существующую таблицу, которая указана в первом посте добавляются два столбца (`valid_from`, `valid_to`), которые содержат в себе информацию о периодах, в которые действует данный тариф.
Объясню на примере.
Раньше у нас в таблице были следующие записи (для упрощения время в UNIX формате запишу в обычном виде)


Чтобы рассчитать стоимость тарифа в указанной выше таблице, нам необходимо воспользоваться запросом, который мне подсказали в данном топике. Он прекрасно работает, к нему нареканий нет, но с учётом добавления двух новых столбцов, о которых я говорил выше, данный запрос становится ошибочным. Что мы имеем сейчас?



Представим, что сейчас на дворе 1 июля 2010 и мы хотим узнать тарифа с idtariff=5 c 28.07.2010 по 03.08.2010
Тогда нам необходимо взять в расчёт следующие периоды:
Строка с ID=1 (14.07.2010      31.07.2010): Мы берем 4 дня. 25*4=100
Строка с ID=3 ( 01.08.2010      22.08.2010): Мы берем 3 дня. 23*3=69
Получается стоимость тарифа = 169

Если мы хотим узнать стоимость тарифа, который будет действовать с 28.07.2010 по 03.08.2010, 
в промежуток времени между 06.07.2010 и по 22.08.2010, то нам необходимо взять следующие строки:
Строка с ID=1 (14.07.2010      31.07.2010): Мы берем 1 день. 25*1=25
Строка с ID=2 (14.07.2010      31.07.2010): Мы берем 3 дня. 21*3=63
Строка с ID=4 (06.07.2010       22.08.2010): Мы берем 3 дня. 20*3=60
Получается стоимость тарифа = 148

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

Это сообщение отредактировал(а) letsgo - 27.7.2010, 13:11