Главная Вход в систему Регистрация
Задать вопрос Консультации Пользователи Форум
О системе Помощь
Задать вопрос экспертам
Консультация № 186052
16.05.2012, 07:37
79.10 руб.
0 7 0
Компьютеры Программное обеспечение Базы данных
Здравствуйте! Прошу помощи в следующем вопросе:

Имеется три таблицы: Объект с первичным ключом ID_object, Арендатор с первичным ключом ID_arendator и Контракт с первичным ключом ID_contract, атрибутами Дата_заключения_договора, Дата_начала_аренды, Дата_окончания_аренды, внешними ключами ID_object и ID_contract. Объект связан с Контрактом связью один-ко-многим, Арендатор связан с Контрактом один-ко-многим.

Требуется написать запрос, который бы отобразил статистику за год (год считается от даты выполнения запроса минус год) по месяцам сданных объектов к отношению всех объектов. То есть если в одном месяце объект сдаётся, то он должен попасть в список сдаваемых. Если объектов нет, то отображается 0%.

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

select count(*), count(*)/(SELECT count(*) FROM object)*100
From contract
Where DATE_FORMAT(`Дата_начала_аренды`,'%Y.%m')<= '2012.06' and DATE_FORMAT(`Дата_окончания аренды`,'%Y.%m') >= '2012.06';


Можете помочь написать запрос по статистике за весь год по месяцам? То есть табличку вида:
01.05.11 - 5%
01.06.11 - 0%
01.07.11 - 20%
и т.д. вплоть до 01.05.12

Обсуждение

давно
Асмик Гаряка
Профессор
230118
3054
16.05.2012, 09:52
общий
Можно ввести вычислимый столбец месяц как DATE_FORMAT(`Дата_окончания аренды`,'%m') и сделать группировку.
Неизвестный
16.05.2012, 10:56
общий
Адресаты:
не получится. если сейчас 6.2012, то группировка нужна с 7.2011 до 6.2012 именно в такой очередности. лучше сделать группировку по год+месяц. не знаю как в MySQL, а в MS SQL это можно сделать.

но наверное лучше сделать дополнительную таблицу месяцев Mes
id_dat
01.01.2011
01.02.2011
01.03.2011
....
01.04.2012
01.05.2012
01.06.2012
и т.д.

select m.id_dat,count(*), count(*)/(SELECT count(*) FROM object)*100
From contract c inner join mes m on ( m.id_dat>=c.[Дата_начала_аренды] and m.id_dat<=c.[Дата_окончания_аренды] )
Where m.id_dat>datediff('m',-13,now)
group by m.id_dat

но если в результате выполнения запроса SELECT count(*) FROM object ответом будет NULL или 0, то выдаст ошибку ( как раз в случае если объектов нет )
Неизвестный
17.05.2012, 01:20
общий
Спасибо! Тогда на днях проверю, возможно ли такое. Пока просто нет возможности проверить. Без ввода дополнительной информации видимо никак нельзя обойтись, да?
Неизвестный
17.05.2012, 10:37
общий
с дополнительной таблицей будет гораздо проще. я не представляю, как сделать выделение каждого месяца для периода с 1.6.2011 по 31.5.2012 если аренда указана с 5.9.2011 по 25.8.2012. т.е. общий отчет за этот период ( часть срока аренды, которая попадает в отчет ) можно сделать, а вот разбить его по месяцам проблематично.

и по поводу ошибки при выполнении расчета (SELECT count(*) FROM object)
если в таблице object еще нет записей, то выдаст ошибку "деление на 0" . после добавления записей будет работать нормально. поэтому есть смысл обрабатывать % программно, а не из запроса.

и еще по %
в данной записи выдается отношение сданных в аренду от общего количества объектов. этим показывается эффективность работы службы договоров. но может получиться, что % будет больше 100. если один объект сдается первому арендатору по 15.4.2012, а другому с 16.4.2012. тогда эффективность для единственного объекта покажет 200% ( т.к. будет учитываться два контракта для одного объекта ) , что неверно.
Неизвестный
19.05.2012, 23:49
общий
Спасибо большое за ответ! Думаю его можно вполне считать за ответ на вопрос. Запрос правда переделал для mysql, просто не работает функци mydiff там так просто. И также решил эту проблему двойственности объекта (если два контракта на один объект в пределах месяца), используя DISTINCT по первичному ключу объекта в счётчике. Вот получился такой запрос:

select DATE_FORMAT(`cur_month`,'%Y.%m') as "Год, месяц",count(DISTINCT c.cadastral_number) as "Количество сдаваемых объектов", count(DISTINCT c.cadastral_number)/(SELECT count(*) FROM object)*100 as "% от общего количества объектов"
From contract c right join mes m on ( DATE_FORMAT(`cur_month`,'%Y.%m')>=DATE_FORMAT(`starting_date`,'%Y.%m') and DATE_FORMAT(`cur_month`,'%Y.%m')<=DATE_FORMAT(`expiring_date`,'%Y.%m'))
Where (DATE_FORMAT(`cur_month`,'%Y.%m')>(SELECT DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 12 MONTH),'%Y.%m')) AND DATE_FORMAT(`cur_month`,'%Y.%m')<=DATE_FORMAT(curdate(), '%Y.%m'))
group by DATE_FORMAT(`cur_month`,'%Y.%m')
Неизвестный
21.05.2012, 11:22
общий
Главное чтобы работало.

Я тогда подготовлю ответ. Заодно больше распишу теорию. Только без Вашей переделки запроса под MySQL - я не знаю его специфики.
Неизвестный
21.05.2012, 23:38
общий
У вас просто два недочёта - вывод данных годичной давности по... будущее (в зависимости от того, сколько годов-месяцев мы занесли в таблицу месяцев - если занесли до конца года, то будет выведено до конца года). Ну и про DISTINCT - не знаю, получится ли в MS SQL, но тут это отсеяло ненужные договора в пределах месяца по одному объекту.
Форма ответа