ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Поиск по неполным датам
10 сообщений из 10, страница 1 из 1
Поиск по неполным датам
    #36672790
Kosto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kosto
Участник
У сущности "Субъект" есть несколько дат, например, дата рождения.
По требованиям оператор может указать дату в одном из 4-х форматов :
1. D полный формат : день + месяц + год (DD.MM.YYYY)
2. M неполный формат без дня : месяц + год (MM.YYYY)
3. Y неполный формат без дня и без месяца : год (YYYY)
4. T произвольный текст до 100 символов.
Требование к поиску по дате :
диапазон дат в фильтре поиска всегда указывается в полном формате D .

если у сущности дата указана в формате D , при поиске она должна находится если дата попадает в указанный диапазон.

если у сущности дата указана в формате M (или Y ), при поиске она должна находится если в фильтре поиска присутствует тот же год и месяц (или только год), что и в дате.

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

Пример поиска, задан фильтр по дате: с 03.06.2005 по 14.07.2005
Код: plaintext
1.
2.
3.
07.06.2005 - будет найдена
   06.2005 - будет найдена
   07.2005 - будет найдена
      2005 - будет найдена

Сейчас хранение, обработка и поиск реализованы следующим образом.
В таблице для хранения даты используется 4-е поля:

DT_BIRTH_TYPE NUMBER тип даты, один из четырех вариантов

DT_BIRTH VARCHAR(100) дата как она введена оператором (соблюдение формата гарантировано)

DT_BIRTH_BEGIN DATE начало диапазона даты, вычисляется ( D - результат совпадает с датой; M - первый день месяца; Y - первый день года; T - не вычисляется)

DT_BIRTH_END DATE конец диапазона даты, вычисляется ( D - результат совпадает с датой; M - последний день месяца; Y - последний день года; T - не вычисляется)

Для хранения информации такая структура подходит.
Для поиска используется следующее условие:
Код: plaintext
1.
AND p_dt_birth_begin <= DT_BIRTH_END
AND p_dt_birth_end >= DT_BIRTH_BEGIN
Поиск выполняется по индексу, но неэффективно, т.к. сначала просматриваются все записи до p_dt_birth_begin и лишь затем отсекаются даты по p_dt_birth_end или наоборот. В результате при поиске, в худшем случае, просматривается половина индекса.

Вопрос : как хранить дату и как реализовать поиск по дате для того чтобы поиск был эффективнее текущей реализации и выполнялось соответствие требованиям?
...
Рейтинг: 0 / 0
07.06.2010, 12:19
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36672853
Фотография lLocust
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
lLocust
Участник
Kosto,

А что за СУБД?
...
Рейтинг: 0 / 0
07.06.2010, 12:35
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36672868
Kosto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kosto
Участник
lLocustKosto,

А что за СУБД?
СУБД Оракл
Код: plaintext
1.
2.
3.
4.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
...
Рейтинг: 0 / 0
07.06.2010, 12:39
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36672963
Фотография krvsa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
krvsa
Участник
Kosto , а если сохранять дату в такие поля

Название поляДеньМесяцГодПроизвольный текст

Тогда запрос на интервал будет типа таким...

Код: plaintext
1.
2.
3.
4.
5.
(day between BegDay and EndDay or day is null)
and
(month between BegMonth and EndMonth or month is null)
and
year between BegYear and EndYear
...
Рейтинг: 0 / 0
07.06.2010, 13:14
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36672989
Kosto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kosto
Участник
krvsa Kosto , а если сохранять дату в такие поля

Название поляДеньМесяцГодПроизвольный текст

Тогда запрос на интервал будет типа таким...

Код: plaintext
1.
2.
3.
4.
5.
(day between BegDay and EndDay or day is null)
and
(month between BegMonth and EndMonth or month is null)
and
year between BegYear and EndYear

Этот алгоритм не работает.
Пример: параметр поиска с 20.12.2005 по 12.01.2006
30.12.2005 не будет найдено, хотя по требованиям должно находится.
Код: plaintext
1.
2.
3.
( 30  /*day*/ BETWEEN  20  /*BegDay*/ AND  12  /*EndDay*/ OR  30  /*day*/ IS NULL) AND
( 12  /*month*/ BETWEEN  12  /*BegMonth*/ AND  1  /*EndMonth*/ OR  12  /*month*/ IS NULL) AND
 2005  /*year*/ BETWEEN  2005  /*BegYear*/ AND  2006  /*EndYear*/
Есть и другие случаи, когда данный алгоритм не будет работать.
...
Рейтинг: 0 / 0
07.06.2010, 13:26
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36673016
Фотография krvsa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
krvsa
Участник
Kosto , жаль я форматы не указал... Они у нас не такие как в Оракл...

Тип поля ФорматДень У нас это некое число дней с некой даты... Для тебя "обычная дата".Месяц ГГГГ.ММГод ГГГГ

Даже в твоём понимании мой "месяц" и "год" сильно смахивают на некое число. Что не должно усложнить поиск.

Т.о. "мой поиск" в "твоей" интерпретации будут выглядеть так

Код: plaintext
1.
2.
3.
4.
( 30 . 12 . 2005  /*day*/ BETWEEN  20 . 12 . 2005  /*BegDay*/ AND  12 . 01 . 2006  /*EndDay*/ OR  30 . 12 . 2005  /*day*/ IS NULL) 
AND
( 2005 . 12  /*month*/ BETWEEN  2005 . 12  /*BegMonth*/ AND  2006 . 01  /*EndMonth*/ OR  2005 . 12  /*month*/ IS NULL)
AND
 2005  /*year*/ BETWEEN  2005  /*BegYear*/ AND  2006  /*EndYear*/
...
Рейтинг: 0 / 0
07.06.2010, 13:38
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36673099
Kosto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kosto
Участник
krvsa Kosto , жаль я форматы не указал... Они у нас не такие как в Оракл...

Тип поля ФорматДень У нас это некое число дней с некой даты... Для тебя "обычная дата".Месяц ГГГГ.ММГод ГГГГ

Даже в твоём понимании мой "месяц" и "год" сильно смахивают на некое число. Что не должно усложнить поиск.

Т.о. "мой поиск" в "твоей" интерпретации будут выглядеть так

Код: plaintext
1.
2.
3.
4.
( 30 . 12 . 2005  /*day*/ BETWEEN  20 . 12 . 2005  /*BegDay*/ AND  12 . 01 . 2006  /*EndDay*/ OR  30 . 12 . 2005  /*day*/ IS NULL) 
AND
( 2005 . 12  /*month*/ BETWEEN  2005 . 12  /*BegMonth*/ AND  2006 . 01  /*EndMonth*/ OR  2005 . 12  /*month*/ IS NULL)
AND
 2005  /*year*/ BETWEEN  2005  /*BegYear*/ AND  2006  /*EndYear*/

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

Если есть другие варианты - предлагайте, буду проверять.
...
Рейтинг: 0 / 0
07.06.2010, 14:04
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36675015
Guest2010
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guest2010
Гость
KostoУ сущности "Субъект" есть несколько дат, например, дата рождения.
По требованиям оператор может указать дату в одном из 4-х форматов :
1. D полный формат : день + месяц + год (DD.MM.YYYY)
2. M неполный формат без дня : месяц + год (MM.YYYY)
3. Y неполный формат без дня и без месяца : год (YYYY)
4. T произвольный текст до 100 символов.

...

Вопрос : как хранить дату и как реализовать поиск по дате для того чтобы поиск был эффективнее текущей реализации и выполнялось соответствие требованиям?

Предлагается следующий вариант.
Хранить дату в поле с типом DATE:
1. D полный формат : 07.06.2005
2. M неполный формат без дня : 01.06.2005 (первый день месяца)
3. Y неполный формат без дня и без месяца : 01.01.2005 (первый день года)
4. T произвольный текст хранить в другом поле.

На тип даты и саму дату создать индекс.

Тогда можно делать выборку:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
WITH tbl AS (
SELECT 'D' type_date, to_date('07.06.2005', 'DD.MM.YYYY') birth_date FROM dual UNION ALL
SELECT 'M' type_date, to_date('01.06.2005', 'DD.MM.YYYY') birth_date FROM dual UNION ALL
SELECT 'M' type_date, to_date('01.06.2005', 'DD.MM.YYYY') birth_date FROM dual UNION ALL
SELECT 'Y' type_date, to_date('01.01.2005', 'DD.MM.YYYY') birth_date FROM dual --UNION ALL
)

SELECT *
  FROM tbl
  WHERE type_date = 'D'
    AND birth_date BETWEEN to_date('03.06.2005', 'DD.MM.YYYY') AND to_date('14.07.2005', 'DD.MM.YYYY')
UNION ALL
  SELECT *
  FROM tbl
  WHERE type_date = 'M'
    AND birth_date BETWEEN TRUNC(to_date('03.06.2005', 'DD.MM.YYYY'), 'MM') AND TRUNC(to_date('14.07.2005', 'DD.MM.YYYY'), 'MM')
UNION ALL
  SELECT *
  FROM tbl
  WHERE type_date = 'Y'
    AND birth_date BETWEEN TRUNC(to_date('03.06.2005', 'DD.MM.YYYY'), 'YYYY') AND TRUNC(to_date('14.07.2005', 'DD.MM.YYYY'), 'YYYY')

Немного громоздко, но кажется работает.
...
Рейтинг: 0 / 0
08.06.2010, 11:53
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36676122
Kosto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kosto
Участник
Проверил быстродействие предложенных вариантов.
Проверял с помощью трассировки время и реальный план выполнения поискового запроса на разных наборах диапазонов дат.
По таблице и индексам статистика была собрана.

Вариант, предложенный krvsa не обладает достаточным быстродействием. Время поиска, в среднем, не быстрее чем в текущей реализации, описанной в начальном посте. А порой и просто медленнее. Допускаю что не смог создать "правильный" индекс.
Название поля Тип ОписаниеDT_BIRTH VARCHAR дата как она была внесена в систему операторомDT_BIRTH_DAY DATE день "обычная дата"DT_BIRTH_MONTH NUMBER месяц "ГГГГ.ММ"DT_BIRTH_YEAR NUMBER год "ГГГГ"
Созданные индексы
Название Список полейTEST_I1 DT_BIRTH_DAY + DT_BIRTH_MONTH + DT_BIRTH_YEARTEST_I2 DT_BIRTH_YEAR + DT_BIRTH_MONTH + DT_BIRTH_DAYTEST_I3 DT_BIRTH_DAYTEST_I4 DT_BIRTH_MONTHTEST_I5 DT_BIRTH_YEAR
Без хинтования запрос использовал INDEX RANGE SCAN TEST_I5 и показал время поиска сопоставимое с TABLE ACCESS FULL
C хинтованием стало выполняться значительно дольше.
Возможно, я что-то упускаю.


Вариант, предложенный Guest2010 показал результаты по времени лучше чем текущая реализация. Запрос выполняется быстрее. В "лучших" прогонах стало быстрее на 20% по сравнению с текущей реализацией. Но при этом выполняется 3-и обращения к таблице ( это логично; если написать запрос без UNION ALL через OR результат аналогичный ). Так как поиск по дате это часть расширенного поиска сущности и в запросе участвует больше одной таблицы, буду смотреть на функционирование в целом.
...
Рейтинг: 0 / 0
08.06.2010, 17:31
| Ответить | Цитировать | Написать  
Поиск по неполным датам
    #36676358
Фотография krvsa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
krvsa
Участник
KostoДопускаю что не смог создать "правильный" индекс.
Скорее всего "правильный" запрос... Т.к. вариант Guest2010 аналогичен моему. А вот запрос лучше взять его но вот в таком варианте...

Код: plaintext
1.
2.
3.
4.
day between BegDay and EndDay
UNION ALL
month between BegMonth and EndMonth
UNION ALL
year between BegYear and EndYear

Не дословно конечно...
...
Рейтинг: 0 / 0
08.06.2010, 18:53
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Поиск по неполным датам
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=32&msg=36676358&tid=1542673]:
 0ms
get settings:
 9ms
get forum list:
 20ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 167ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 68ms
get tp. blocked users:
 1ms
others: 207ms
total:  493ms
созд. / загр.: 493ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]