Нужны идеи.

← →
Юрий Зотов © (2014-07-14 21:27) [0]

Есть БД, в ней данные о людях. Обязательные поля - ФИО, пол, дата рождения. Необязательные поля (т.е. могут быть пустыми) - серия и номер паспорта. Таких записей - десятки миллионов.

Вводится документ, в нем те же поля. Требуется проверить, есть ли такой человек в БД и в зависимости от результатов проверки отнести вводимый документ к одной из трех групп:
А). найден с полным соответствием полей;
Б). найден с неполным соответствием полей;
В). не найден.

Планирую сделать так. В коде конструируем select по всем заполненным полям документа. Если этот select пустой, то имеем случай B, если дает одного человека, то это случай А, если дает несколько человек, то это случай Б.

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

Решение "в лоб" - делать отдельные select"ы по каждому заполненному полю и на клиенте анализировать совпадение каждого поля. Но это не годится - слишком долго, да и выборки будут огромными.

В общем, нужны идеи. Заранее спасибо.

← →
turbouser © (2014-07-14 21:34) [1]

> В коде конструируем select

Это неправильный подход. К сожалению, СУБД не озвучена, но скорее всего одна из тех, в которых можно реализовать ХП.
т.е. все сводится к созданию процедуры, входные параметры - фио, др, данные паспорта и все остальное. И уже в ней select -ы. Точнее даже EXISTS-ы :)

← →
это все... (2014-07-14 21:36) [2]

на первом шаге sql запрос по мандатори полям оунли.
результат в xml
второй шаг - selectNodes c xpath по вариациям опциональных полей.

← →
это все... (2014-07-14 21:38) [3]

т.е. все сводится к созданию процедуры,

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

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

и процедура здесь ничем не поможет

← →
Inovet © (2014-07-14 21:39) [4]

> [0] Юрий Зотов © (14.07.14 21:27)
> Но при этом не совпало лишь какое-то одно поле (например,
> указан ошибочный номер паспорта)

Г) Указан чужой номер паспорта

← →
turbouser © (2014-07-14 21:42) [5]

> дело то не в этом.
> ввели необязательный параметр (который не совпадает с тем
> что в базе)
> результат чел в базе есть, а мы его не видим.

тут важный момент -
> Но при этом не совпало лишь какое-то одно поле

Набор полей ограничен, и на EXISTS проверить просто

> и процедура здесь ничем не поможет

Очень даже поможет. Вместо того, что бы динамически городить уйму select-ов, все можно сделать в одной ХП.

← →
это все... (2014-07-14 21:45) [6]

Вместо того, что бы динамически городить

а никто кроме тебя городить динамический sql и не будет.

будет sql сделанный в дизайнтайм.
но все условия будут по кейсу.
что введено - повлияет на выборку.
что не введено - никак не повлияет на выборку.

и процедура здесь нафик не нужна.

← →
turbouser © (2014-07-14 21:46) [7]

> это все... (14.07.14 21:38) [3]

> проще простой селект где все условия по кейсу

при условии, что
> Таких записей - десятки миллионов

Надо курить план. Скорее всего кейс будет неэффективен

← →
turbouser © (2014-07-14 21:49) [8]

> это все... (14.07.14 21:45) [6]

> а никто кроме тебя городить динамический sql и не будет.

А как же безопасность? :) А если надо что-то в этом коде срочно, прямо внезапно поменять?

← →
это все... (2014-07-14 21:51) [9]

при условии, что
> Таких записей - десятки миллионов

боюсь-боюсь.

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

← →
jack128_ (2014-07-14 21:52) [10]

Чем такой код не устраивает:
select First 1 ID, FirstName, LastName, .... , (LastName = :LastName) as LastNameFlag, (FirstName= :FirstName) as FirstNameFlag, ..... from People where LastNameFlag or FirstNameFlag or .... (остальные флаги) order by LastNameFlag + FirstNameFlag + (остальные флаги) desc
// так как БД не озвучена, то предпологаю, что там есть поддержка boolean и неявное преобразование из boolean в int (false = 0, true = 1)

Если все поля XXXFlag = True, то это вариант А)
Если одно из полей XXXFlag = False, то это вариант Б) какие именно поля не совпадают можно по значениям флагов понять.
Если выборка пустая, то В)

← →
Омлет © (2014-07-14 21:52) [11]

Как-то так:

SELECT COALESCE( SELECT "full" WHERE EXISTS(SELECT * FROM t WHERE fio=? AND sex=? AND birthDate=? and seria=? and number=?), SELECT "partially" WHERE EXISTS(SELECT * FROM t WHERE (fio=? and birthDate=?) or (seria = ? and number=?)), "nothing" )

← →
это все... (2014-07-14 21:53) [12]

А как же безопасность? :)

в смысле безопасность?

← →
turbouser © (2014-07-14 21:54) [13]

> это все... (14.07.14 21:53) [12]
>
> А как же безопасность? :)
>
> в смысле безопасность?

SQL injection например

← →
это все... (2014-07-14 21:56) [14]

а дофига ли ты въинъектишь небезопасности в параметры?

← →
turbouser © (2014-07-14 21:58) [15]

> это все... (14.07.14 21:56) [14]
>
> а дофига ли ты въинъектишь небезопасности в параметры?

%

← →
turbouser © (2014-07-14 21:59) [16]

И вообще, если весь SQL генерируется динамически - просто сказка

← →
это все... (2014-07-14 22:00) [17]

процент вдруг стал инъекцией?

чего только не узнаешь в потрепаться

← →
turbouser © (2014-07-14 22:02) [18]

> это все... (14.07.14 22:00) [17]
>
> процент вдруг стал инъекцией?
>
> чего только не узнаешь в потрепаться

Ну сольют у тебя всю базу с фио и паспортами, ничего, не инъекция ни разу:)

← →
это все... (2014-07-14 22:02) [19]

И вообще, если весь SQL генерируется динамически - просто сказка

знаешь, серверу как то пофик на то что ему сгенерили sql за полсекунды до отправки запроса.

он твоих восторгов не разделяэ. ему по барабану.

← →
это все... (2014-07-14 22:03) [20]

Ну сольют у тебя всю базу с фио и паспортами, ничего, не инъекция ни разу:)

даже если сольют, то при чем здесь иньекция

слово нравится?

← →
Омлет © (2014-07-14 22:04) [21]

> turbouser ©

Как связаны динамическое формирование sql и безопасность?
Не пори чушь.

← →
turbouser © (2014-07-14 22:04) [22]

> это все...

> даже если сольют, то при чем здесь иньекция
>
> слово нравится?

Хм. Все понятно.

← →
это все... (2014-07-14 22:06) [23]

Хм. Все понятно.

Вот тут ты прав. Все понятно стало уже постов десять как.

← →
turbouser © (2014-07-14 22:06) [24]

> Омлет © (14.07.14 22:04) [21]
>
> > turbouser ©
>
> Как связаны динамическое формирование sql и безопасность?
>
> Не пори чушь.

В чем именно чушь?

← →
turbouser © (2014-07-14 22:17) [25]

> Омлет © (14.07.14 22:04) [21]

Ты считаешь, что если можно узнать имя таблиц и сделать из них SELECT * это в принципе ерунда, не стоит внимания, и к безопасности отношения никакого не имеет?

> это все... (14.07.14 22:06) [23]
>
> Хм. Все понятно.
>
> Вот тут ты прав. Все понятно стало уже постов десять как.
>

Удачи :)

← →
это все... (2014-07-14 22:19) [26]

Ты считаешь, что если можно узнать имя таблиц и сделать из них SELECT *

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

← →
Омлет © (2014-07-14 22:21) [27]

> turbouser © (14.07.14 22:17) [25]

Хватит позориться.
Иди уже почитай про подстановку параметров в запросы.

← →
turbouser © (2014-07-14 23:00) [28]

> Омлет © (14.07.14 22:21) [27]
>
> > turbouser © (14.07.14 22:17) [25]
>
> Хватит позориться.
> Иди уже почитай про подстановку параметров в запросы.

> это все... (14.07.14 22:19) [26]
>
> Ты считаешь, что если можно узнать имя таблиц и сделать
> из них SELECT *
>
> я тебе все имена всех таблиц расскажу.
> а ты через параметры моего запроса сделай из них селект.
>
> и пока не сделаешь не приходи.

Речь не про подстановку в параметры. Так что не в кассу.

← →
й (2014-07-14 23:19) [29]

ничего "хитрого" не придумывается
тупо: 1) селект по всем (N) заполненным полям (на полное совпадение)
кстати, если он вдруг дает несколько человек, то это вроде как не "Б) найден с неполным соответствием полей", а вовсе даже заполнено слишком мало полей, чтобы определить однозначное соответствие, то есть любое совпадение по этому набору полей с большой вероятностью будет ложным!
2) N селектов по N-1 полю (по очереди исключаем каждое) - на неполное совпадение по всем полям кроме одного
3) дофига селектов по N-2 полям, возможно, еще N-3 и т.д. (нужны явно не все возможные комбинации, например, только ФИО, ФИ+дата, ИО+дата скорее нужны, только имя+дата+пол - скорее нет)
Что смущает: нет критерия, что такое "найден с неполным соответствием полей" - если ФИО совпало, а остальное нет - это как, "найден"?
а если только серия и номер? серия, номер и имя? номер и фамилия?
да и критерий "полное совпадение" неясен - это все 7 полей? только 5 обязательных? все заполненные именно во 2 документе поля (от 5 до 7)?

хотя можно еще сделать процедуру, подсчитывающую для каждой записи число полей, совпадающих в ней с образцами-параметрами (f,i,o,sex,date,ser,num) и выдающую те записи, для которых это число больше определенного значения (и ORDER DESC по нему же) - но это только полный проход по таблице, а селекты будут индексы использовать

← →
это все... (2014-07-14 23:30) [30]

Речь не про подстановку в параметры. Так что не в кассу.

Деточка, тебе уже сказали, что кроме параметров в запросе ничего не меняется. А сам запрос в дизайне определен.
А ты все боянишь про свой "сказочный динамический эскуэль"

> это все... (14.07.14 23:30) [30]
>
> Речь не про подстановку в параметры. Так что не в кассу.
>
>
> Деточка, тебе уже сказали, что кроме параметров в запросе
> ничего не меняется. А сам запрос в дизайне определен.
> А ты все боянишь про свой "сказочный динамический эскуэль"

То есть, добавить к твоему в дизайне определенному запросу свои команды это из разряда фантастики?

← →
это все... (2014-07-15 00:15) [32]

Удалено модератором
Примечание: Отдохни немного

← →
OOO (2014-07-15 06:19) [33]

А никого не смутило, что есть база людей с ФИО, паспортными данными и другой информацией на несколько десятков миллионов человек (140?)?

с первой страницы любой запрос по not null и null полям. Главное, имхо, план курить и индексы при возможности правильные навесить.

> никого не смутило, что есть база людей с ФИО, паспортными
> данными и другой информацией

нет. По двум причинам:
1. сколько таковых прошло через руки совершенно легально..
2. ЮЗ ерундой не занимается

плохо что паспотр nullable
может, сделать его по дефолту to_number(GUID) и объявить уникальным ключом, задающим порядок хранения(mssql:кластерный)? По идее, паспорт обязан быть уникальным + второй запрос по №паспорта будет гораздо быстрее.

И с него начинать.

Омлет © (14.07.14 21:52) [11]
Как-то так:
SELECT COALESCE(
SELECT "full" WHERE EXISTS(SELECT * FROM t WHERE (seria + number=?)) --п ервым делом
..

← →
имя (2014-07-15 09:29) [36]

Удалено модератором

Б). найден с неполным соответствием полей;

Тут хорошо бы уточнить что это за "неполное соответствие", какие именно поля могут не совпасть, чтобы выдать такой диагноз, а не вариант В)
Маша Игоревна Петрова и Маша Сигизмундовна Петрова - это неполное соответствие? не понятно.
А Маша Сегизмундовна Питрова - это что?

> OOO (15.07.14 06:19) [33]

> А никого не смутило, что есть база людей с ФИО, паспортными данными и
> другой информацией на несколько десятков миллионов человек?

Ну да, такая база есть. А Вы разве об этом не догадывались? Или Вы думаете, что документы по всяким там пенсионным делам обрабатываются вручную?

> Пит (15.07.14 09:29) [36]

> дядя Юра за нами следит

Конечно. Причем очень внимательно. Читаю и прикидываю "боевую" реализацию. Задать вопрос и забыть о нем - это было бы свинством.

← →
имя (2014-07-15 10:09) [39]

Удалено модератором

> KSergey © (15.07.14 09:55) [37]

> хорошо бы уточнить что это за "неполное соответствие", какие именно поля
> могут не совпасть, чтобы выдать такой диагноз, а не вариант В)

Именно. Первоначально ответ на этот вопрос должен дать человек. Чем сейчас и занимаюсь.

> А Маша Сегизмундовна Питрова - это что?

Либо "не найден", либо "частично совпал". Я уже думал о том, чтобы прикрутить SOUNDEX или что-то в этом духе. Но и этот ответ сначала должен дать человек.

Нужны идеи. Найти похожие ветки