Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Поиск по неполным датам / 10 сообщений из 10, страница 1 из 1
07.06.2010, 12:19
    #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:35
    #36672853
lLocust
lLocust
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск по неполным датам
Kosto,

А что за СУБД?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.06.2010, 12:39
    #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, 13:14
    #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:26
    #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:38
    #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, 14:04
    #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
| Ответить | Цитировать | Написать  
08.06.2010, 11:53
    #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, 17:31
    #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, 18:53
    #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
| Ответить | Цитировать | Написать  
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Поиск по неполным датам / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 497ms
Закрыть
start [/forum/topic.php?fid=32&mobile=1&tid=1542673]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 155ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 242ms
total:  497ms
x
x
Закрыть


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