Деякі аспекти використання для користувача функцій в пропозиціях SQL, Мова запитів SQL, Бази даних, статті

Володимир Журавльов, Олег Поль, Visual FoxPro CLUB

"В чому перевага склерозу?. У тому, що весь час дізнаєшся нове"
Вже набридло говорити про те, що зовсім нічого нового в цьому житті не відбувається, а більшість нового – це добре забуте старе. Ось ще приклад. Один автор цієї статті літній, але відносно молодий фоксіст, який почав працювати тільки з FXP2.6 під вінди, а другий-один з найстаріших кліперістов. Тому й прийоми різні. У першого в основному нові методи ФОКС, у другого вічні істини dbase. Виявляється, що старі рецепти часто дають багато кращі рішення, ніж новомодні навороти, знаючи які, дуже важко використовувати старі прийоми, хоч і читав теорію і потенційно їх знаєш. Потім, чорт візьми, вічно потрапляєш на агресивну рекламу Микрософт з описом нових, все більш потужних методів. І часто віриш всьому, що вони говорять. Але от беремо тривіальні завдання, суем їх на нову кухню і чекаємо смачного пирога. А замість нього іноді – одна сморід.
Мова піде здебільшого про SQL select з пропозиціями outer left/right join, union та використанні групових функцій.
В одній з попередніх статей ми вже згадували про те, що генератор запитів і уявлень view дає неправильний код з цими пропозиціями, якщо маємо кілька join.
Приклад такого неправильний код:

LEFT OUTER JOIN dbf_s!valuta;
ON int(pd.valuta_id) == int(Valuta.valuta_id) ;
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ;
ON pd.shpz_id = Sc_shpz.shpz_id ;
ON pd.nc_id = bc_ac.nc_id ;
ON pd.country_id = country.country_id ;
ON pd.um_id = Sc_ed.um_id ;
LEFT OUTER JOIN dbf_s!valuta Valuta_a ;
LEFT OUTER JOIN dbf_s!Sc_shpz;
LEFT OUTER JOIN dbf_s!bc_ac;
LEFT OUTER JOIN dbf_s!Country ;
LEFT OUTER JOIN dbf_s!Sc_ed
А потрібно
LEFT OUTER JOIN dbf_s!valuta;
ON int(pd.valuta_id) == int(Valuta.valuta_id) ;
LEFT OUTER JOIN dbf_s!valuta Valuta_a ;
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ;
LEFT OUTER JOIN dbf_s!Sc_shpz;
ON pd.shpz_id = Sc_shpz.shpz_id ;
LEFT OUTER JOIN dbf_s!bc_ac;
ON pd.nc_id = bc_ac.nc_id ;
LEFT OUTER JOIN dbf_s!Country ;
ON pd.country_id = country.country_id ;
LEFT OUTER JOIN dbf_s!Sc_ed ;
ON pd.um_id = Sc_ed.um_id
Ну це не біда. "Хто попереджений – той озброєний".
Біда в іншому – генератор дозволяє до цього додати пропозицію where, Де можна використовувати будь-які з вже використаних таблиць.
Ось тут-то (особливо якщо умова where в ньому стосується приєднаних таблиць) результат може бути і зовсім невірний. А як таку умову не додати, якщо потрібно взяти товар за певний інтервал часу? Або ціну товару з прайс листа, відповідну потрібного обліковому періоду? Цього в join ніяк не засунеш.
Без outer join, здавалося б, теж не можна – посилання на будь-який довідник може бути порожньою, а рядок товару втрачати небажано. Тому умови з outer join не вийде перенести в where. Чекати, коли Мікрософт виправить помилки – так наші клієнти чекати нас не будуть і швиденько злиняти до більш удачливим фірмам або програмних продуктів. Тому, хочеш не хочеш, а давай правильний результат.
Спочатку ми розбили запит на два-один з outer join , Другий з where. Працює досить непогано і при наявності потрібних індексів досить швидко. Проте, іноді виходить треба писати аж надто багато гілок за різними умовами. Число SQL та їх складність росте і росте. Більше того, доводиться включати вторинні довідники з ще одним outer join на таблицю, яка сама вже висить на outer join. Імовірність помилки в результаті занадто
велика.
Ось тут то на допомогу приходять старі, але від цього, не менш ефективні методи. А саме:
використання для користувача функцій. Ідея полягає в тому, щоб прибрати з пропозиції Select SQL всі таблиці, які прив'язані до зовнішніх об'єднанням, а шукати потрібні посилання в цих таблицях в призначених для користувача функціях. Функція повинна бути така, що при наявності посилання – отримати її значення, а при відсутності повернути "пустушку" потрібного типу. Тобто зробити роботу outer join самим. Що для цього потрібно: ім'я ключа, за яким у довіднику шукається довідка, ім'я довідкової таблиці, поле з довідника, ім'я тега, за яким ведеться пошук.

Деякі хитрощі

Покладемо, що для одного ключа з довідника має бути знайдено кілька різних полів. Не хочеться перевантажувати роботу пошуку вдруге. Якщо потрібна запис в довіднику знайдена – для іншого поля ні до чого заново робити пошук.
Друге.
SQL select володіє однією неприємною властивістю: довжина результуючого поля оцінюється під час ініціалізації. Тому, якщо для першого запису довідка відсутня, а для наступної довідка є, то і для порожньої записи потрібно повернути поле потрібної довжини. Інакше записи з довідника будуть урізані по одній букві.
Ось приклад такої функції
Lparameters pr_id,mydat
Local retcr

If order(‘val_course’)!=’datcur’
Set order to datcur in val_course
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),’val_course’)
retcr=course
Endif
Else
If set(‘NEAR’)=’OFF’
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),’val_course’)
If pr_id=val_course.valuta_id
retcr=val_course.course
Endif
Set near off
Endif
Return retcr:

Function getref
Lparameters fld,pr_id,tb,tg

Local fl,tpt,rt,rtt,lnn, fl,fll

fl=tb+’.’+alltrim(fld)
fll=tb+’.’+tg
*** Якщо збігся ключ, можна не шукати, інакше – шукаємо
If not empty(pr_id) and ;
(eval(fll)=pr_id or seek(pr_id,tb,tg))
rtt=eval(fl)
Return rtt
Else
rt=eval(fl)
tpt=type(‘rt’)
Do case
Case tpt=’N’
rtt=0
Case tpt=’C’
Lnn=len(eval(fl))
rtt=pad(”,lnn)
Case tpt=’D’
rtt=ctod(‘..’)
Case tpt=’L’
rtt=.f.
Endcase
Return rtt
Endif

Lparameters pr_id,mydat

Local retcr

If order(‘val_course’)!=’datcur’
Set order to datcur in val_course
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),’val_course’)
retcr=course
Endif
Else
If set(‘NEAR’)=’OFF’
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),’val_course’)
If pr_id=val_course.valuta_id
retcr=val_course.course
Endif
Set near off
Endif
Return retcr

Приклад використання. Нехай у нас був запит SQL такого вигляду:

Select doc.num,val from doc LEFT OUTER JOIN valuta;
ON doc.valuta_id= Valuta.valuta_id

Тепер пишемо

Select doc.num getref ('val', 'doc.valuta_id', 'valuta', 'valuta_id) as val from doc

Тут звичайно вийшло більш громіздко, ніж у вихідному прикладі, зате можна додавати ще купу полів з довідників і купу довідників і не бояться при цьому використовувати пропозицію where в цьому ж sql

Другий приклад складніше.
Є таблиця валют і таблиця курсів валют з ключами валюти, датою і курсом. Причому в таблицю курсів валют курси можуть записуватися не в порядку часу, а довільно. Деякі валюти в таблиці курсів можуть взагалі відсутні. Потрібно, проте, показати всі валюти з останнім їх курсом.

Рішення перше (неправильне):

Select val, max (dat), cour from valuta, vcour where valuta.valuta_id = vcour.valuta_id union;
Select val, ctod ('..'), 0 as cour from valuta where valuta_id not in (select valuta_id from vcour)
Біда в тому, що функція max, працюючи по всьому діапазону записів, дату-то дає правильну, зате курс – з останньої за рахунком запису. Якщо курс вводився не в хронологічному порядку – курс виходить не останньою дати, а останній запис, хоча сама дата у вибірці – остання.
Рішення в стилі Microsoft – правильне, але не оптимальне.

Select max(dat)as dt,valuta_id from ;
vcour group by valuta_id into cursor qr
Select distinct val,cour,dat from ;
valuta,vcour,qr ;
where valuta.valuta_id=vcour.valuta_id ;
and qr.dt=vcour.dat and ;
qr.valuta_id=valuta.valuta_id ;
union ;
Select distinct val, 1 as cour, ctod ('..') as dat from valuta where;
valuta.valuta_id not in (select distinct valuta_id from vcour);
into cursor vcou

Рішення за дідівськими заповітами. Найшвидше.

Створимо в таблиці курсу валют композитний індекс datcur з виразом
STR(valuta_id,3)+DTOS(dat)
Зробимо функцію Getvcr
Lparameters pr_id,mydat

Local retcr

If order(‘vcour’)!=’datcur’
Set order to datcur in vcour
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),’vcour’)
retcr=vcour.cour
Endif
Else
If set(‘NEAR’)=’OFF’
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),’vcour’)
If pr_id=vcour.valuta_id
retcr=vcour.cour
Endif
Set near off
Endif
Return retcr

Тепер достатньо записати Select val, getvcr (valuta.valuta_id) as cour from valuta – і все.
А хочете курс на конкретну дату – вкажіть її у другому параметрі.
Так і згадується анекдот про двох бичків, старого і молодого. Молодий все норовив швидко побігти до корівок. Нам же до вподоби принцип старого бичка-підемо повільно повільно, але поімеем все, що хотіли і може навіть обженемо кого і молодші.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*