ТАБЛИЧНІ ВИРАЖЕННЯ SQL

Спочатку розглянемо наведену нижче граматику табличних виразів < table exp&gt

у формі Бекуса-Наура

&lttable exp&gt

::=  &ltwith exp&gt \ &ltnonwith exp&gt

&ltwith exp&gt

: : =  WITH [ RECURSIVE ]

&lttable name&gt [ ( &ltcolumn name commalist&gt ) ] ] AS (

&lttable exp&gt ) &ltnonwith exp&gt

::= &ltjoin table exp&gt | &ltnonjoin table exp&gt

&ltjoin table exp&gt

::=  &lttable ref&gt I NATURAL ] JOIN &lttable ref&gt                                                                                 [ ON &ltbool exp&gt

| USING ( &ltcolumn name conmalist&gt ) ] | &lttable ref&gt CROSS JOIN &lttable ref&gt | ( &ltjoin table exp&gt )

&lttable ref&gt

::=  &lttable name&gt [ [ AS ] &ltrange var name&gt

[ ( &ltcolumn name commalist&gt ) ] ] | ( &ltnonwi th exp&gt ) [ AS ]

&ltrange var name&gt

&ltnonjoin table exp&gt

[ ( &ltcolumn name commalist&gt ) ] | &ltjoin table exp&gt

::=  &ltnonjoin table term&gt

| &ltnonwith exp&gt UNION [ ALL | DISTINCT ]

[ CORRESPONDING [ BY ( &ltcolumn name commalist&gt ) ] ]

&lttable term&gt &ltnonwith exp&gt EXCEPT [ ALL | DISTINCT ] [ CORRESPONDING [ BY ( &ltcolumn name commalist&gt ) ] ]

&lttable term&gt

&ltnonjoin table term&gt

::=  &ltnonjoin table primary&gt

| &lttable term&gt INTERSECT [ ALL | DISTINCT ]

[ CORRESPONDING [ BY ( &ltcolumn name commalist&gt ) ] ] &lttable primary&gt

&lttable term&gt

::=  &ltnonjoin table term&gt \ &ltjoin table exp&gt

&lttable primary&gt

::=  &ltnonjoin table primary&gt \ &ltjoin table exp&gt

&ltnonjoin table primary&gt

::=  TABLE &lttable name&gt | &lttable constructor&gt |

&ltselect exp&gt | ( &ltnonjoin table exp&gt )

&lttable constructor

::=  VALUES &ltrow constructor commalist&gt

&ltrow constructor

::=  &ltscalar exp&gt

| ( &ltscalar exp commalist&gt ) I ( &lttable exp&gt )

&ltselect exp&gt

::=  SELECT [ ALL | DISTINCT ] &ltselect item commalist&gt FROM &lttable ref commalist&gt [ WHERE &ltbool exp&gt ]

[ GROUP BY &ltcolumn name commalist&gt ] [ HAVING &ltbool

exp&gt ]

&ltselect item&gt

::=                                         &ltscalar exp&gt  [   [  AS   ]   &ltcolumn name&gt  ] | [   &ltrange var name&gt.   ]   *

Тепер перейдемо до конкретного випадку виразів вибірки &ltselect exp>, які, безумовно, становлять найбільший інтерес з точки зору практики Вирази при ведено нижче)

2 Якщо не задані ні ключове слово ALL, ні ключове слово DISTINCT, за замовчуванням применя ється ключове слово ALL

3 Приймемо на час припущення, що обчислення виразів в конструкціях FROM, WHERE, GROUP BY і HAVING вже виконано Незалежно від того, які з цих конструкцій определе ни і які опущені, результатом їх обчислення концептуально завжди стає таблиця (можливо, згрупована таблиця, як описано нижче), яка називатиметься табли цею Т1 (хоча цей концептуальний результат фактично є безіменним)

4 Припустимо, що Т2 – таблиця, отримана з таблиці Т1 шляхом обчислення зазначеного елемента вибірки &ltselect  item&gt стосовно до Т1

5 Припустимо, що ТЗ – таблиця, яка отримана з таблиці Т2 шляхом усунення надлишкових дублікатів рядків з Т2, якщо задано ключове слово DISTINCT, або таблиця, ідентична Т2, в іншому випадку

6 Таблиця ТЗ являє собою остаточний результат

Тепер перейдемо до опису елементів вибірки &ltselect item&gt. Для цього необхідно розглянути два випадки, причому другий з них відноситься просто до форми, в якій застосовується скорочений варіант розділеного комами списку &ltselect item&gt з першої форми таким чином, перший випадок дійсно є більш фундаментальним

Випадок 1 Опція &ltselect i ten» приймає наступну форму

[[AS   ]   &ltcolumn name&gt   ]

Скалярний вираз &ltscalar exp&gt звичайно (але не обовязково) включає один або декілька стовпців таблиці Т1 (див п 3) Для кожного рядка Т1 обчислення виразу&ltscalar  exp&gt призводить

1 Фактично, всі прості списки і розділені комами списки, що розглядаються в даному при положенні, повинні бути непорожніми

2 Іншими словами, в контексті оператора SELECT застосовуваним за замовчуванням значенням є ALL На відміну від цього, в контексті UNION, INTERSECT або EXCEPT застосовуваним за замовчуванням зна чением є DISTINCT

до отримання скалярного результату Розділений комами список таких результатів (відповідний обчисленню всіх елементів вибірки &ltselect item&gt в конструкції SELECT стосовно окремому рядку Т1) становить один рядок таблиці Т2 (див п 4) Якщо елемент &ltselect item&gt включає конструкцію AS, то неуточнене імя стовпця з цієї конструкції призначається як імя відповідного стовпця Т2 (необовязкове ключове слово AS передбачено просто для зручності і може бути опущено в результаті цього зміст конструкції не зміниться) Якщо елемент &ltselect i ten» не включає конструкцію AS, можуть розглядатися наступні два варіанти: по-перше, якщо цей елемент складається просто з імені стовпця (Можливо, уточненого), то імя присвоюється в якості імені відповідного стовпцю таблиці Т2 по-друге, в іншому випадку відповідний стовпець Т2, по суті, залишається безіменним (але фактично цим стовпцем присвоюється внутрішнє імя, залежне від реалізації) З цього випливають наведені нижче висновки

■ Оскільки імя, введене за допомогою конструкції AS, безумовно, відноситься до колонку таблиці Т2, а не таблиці Т1, то це імя не може використовуватися в конструкціях WHERE, GROUP BY і HAVING, що безпосередньо беруть участь у формуванні таблиці Т1 (якщо вони є) Однак на нього можна посилатися у відповідній конструкції ORDER BY, якщо вона є, і також у зовнішньому вираженні &lttable   exp>, яке містить вкладене в нього розглядається вираз &ltselect exp&gt.

■ Якщо елемент &ltselect   item&gt включає виклик агрегує оператора, а вираз

&ltselect  exp&gt не включає конструкцію GROUP BY (див нижче), то жоден елемент &ltselect i tem&gt в конструкції SELECT не може включати будь-яких посилань на стовпець Т1, якщо тільки

ця посилання на стовпець не є фактичним параметром (або не входить до складу фактичного параметра) у виклику агрегує оператора

Випадок 2 Опція &ltselect i tem&gt приймає наступну форму

[   &ltrange var name&gt.   ]   *

Якщо специфікатор опущений (тобто опція включає конструкцію GROUP BY, то кожен елемент вибірки&ltselect  item> в конструкції SELECT (включаючи всі ті елементи, які стають наслідком застосування скороченого позначення у вигляді зірочки), повинен бути однозначним у розрахунку на кожну групу

Конструкція HAVING

Конструкція HAVING приймає наступну форму

HAVING  <'оо1 Ехр>

Припустимо, що G – згрупована таблиця, отримана в результаті обчислення безпосередньо передують конструкцій: конструкції FROM, конструкції WHERE (якщо вона є) і конструкції GROUP BY (якщо вона є) Якщо конструкція GROUP BY відсутній, то таблиця G розглядається як результат обчислення одних тільки конструкцій FROM і WHERE, тому вважається згрупованої таблицею, що містить одну і тільки одну групу 3 іншими словами, в даному випадку застосовується неявна концептуальна конструкція GROUP BY, яка взагалі не задає жодного групують шпальти Результатом застосування конструкції HAVING є згрупована таблиця, отримана з таблиці G шляхом видалення всіх груп, для яких логічне вираження не приймає значення TRUE З цього випливають наведені нижче висновки

■ Якщо конструкція HAVING опущена, а конструкція GROUP BY включена, то результатом обчислення конструкції HAVING є просто таблиця G Якщо опущені обидві конструкції, і HAVING, і GROUP BY, то результатом стає просто справжня (Тобто не GROUP) таблиця Т, отримана в результаті застосування конструкцій FROM і WHERE

■ Будь-яке скалярний вираз &ltscalar exp&gt в конструкції HAVING має бути однозначним у розрахунку на кожну групу (як і вирази &ltscalar  exp&gt в конструкції SELECT, якщо є конструкція GROUP BY, як було описано в попередньому підрозділі)

3 Саме це зазначено в стандарті, хоча було б точніше застосувати тут формулювання «не більше однієї групи (Оскільки фупп не повинно бути взагалі, якщо застосування конструкцій FROM і WHERE призводить до отримання порожній таблиці)

Всеохоплюючий приклад

На завершення цього опису виразів вибірки&ltselect  exp&gt  наведемо досить складний приклад, який ілюструє кілька особливостей цих виразів, описаних а попередніх підрозділах (але, зрозуміло, не все) Відповідний запит наведено нижче

Для всіх деталей червоного і синього кольору, таких що загальний обсяг їх поставки перевищує 350 (але з підсумків виключені всі поставки, для яких це кількість менша або дорівнює 200), отримати номери деталей, вага в грамах, колір і максимальне поставляється кількість цих деталей

Нижче наведена можливе формулювання цього запиту на мові SQL SELECT P P #,

‘Weight in grains = AS TEXT1, PWEIGHT * 454 AS GMWT, PCOLOR,

‘Max quantity = AS TEXT2, MAX ( SPQTY ) AS MXQTY FROM  P, SP WHERE PP# = SPP#

AND ( PCOLOR = COLOR (Red) OR PCOLOR = COLOR (Blue) ) AND SPQTY &gt QTY ( 200 ) GROUP BY PP#, PWEIGHT, PCOLOR HAVING SUM ( SPQTY ) &gt QTY ( 350 )

ПоясненняНасамперед необхідно відзначити, що (як зазначено в попередніх підрозділах) конструкції вираження exp&gt  концептуально обчислюються в тому порядку, в якому вони записані Єдиним винятком з цього правила є сама конструкція SELECT, яка обчислюється в останню чергу Тому в даному прикладі можна припустити, що результат формується, як описано нижче

1 Конструкція FROM У наведеному вище реченні конструкція FROM обчислюється для по одержанні нової таблиці, яка представляє собою декартовій твір таблиць Р і SP

2 Конструкція WHERE До результату, отриманого в кроці 1, застосовується операція скорочення, виконувана шляхом видалення всіх рядків, які не задовольняють умові конструкції WHERE Тому в даному прикладі видаляються рядки, які не відповідають наступного логічного виразу <Ьоо1 ехр>

Р Р # = SPP #

AND ( PCOLOR = COLOR (Red) OR PCOLOR = COLOR (Blue) ) AND SPQTY &gt QTY ( 200 )

3 Конструкція GROUP BY Результат, отриманий в кроці 2, групується за значеннями в стовпці (стовпцях), названому в конструкції GROUP BY У даному прикладі такими стовпцями є Р Р #, Р WEIGHT І Р COLOR

4 Конструкція HAVING З результату, отриманого в кроці 3, видаляються групи, які не задовольняють ряющіе наступного логічного виразу &ltbool exp&gt: SUM  (  SPQTY )    &gt QTY  ( 350 )

5 Конструкція SELECT Кожна група в результаті кроку 4 застосовується для вироблення одного рядка остаточного результату наступним чином По-перше, з групи витягуються дан ні про номер деталі, вазі, кольорі і максимальній кількості По-друге, вага перетворюється в грами По-третє, у відповідні місця даної рядки вставляються дві символьні рядки Weight in grams = і Max quantity =. До речі, слід зазначити, що тут використовує ся той факт, що в мові SQL стовпці таблиць мають впорядкування зліва направо (про що говорить фраза відповідні місця даної рядка) якби текстові написи зявлялися не в цих відповідних місцях, то вони мали б мало сенсу

Остаточний результат виглядає наступним чином

Читач повинен враховувати, що наведене вище опис алгоритму виконання запиту призначено винятково в якості концептуального пояснення того, як відбувається обчислення виразу&ltselect  exp&gt.  Цей алгоритм, безумовно, є правильним в тому сенсі, що він гарантує отримання правильного результату Але якби він дійсно виконувався відповідно до цим описом, то опинився б досить неефективним Наприклад, навряд чи було б доцільно фактично формувати декартовій твір в кроці 1 Саме такі міркування і є тією причиною, по якій в реляційних системах потрібно оптимізатор, як було описано в розділі 18 Безумовно, завдання оптимізатора в системі SQL можна охарактеризувати як пошук процедури реалізації, що дозволяє отримати точно такий же результат, як при використанні тільки що (схематично) описаного концептуального алгоритму, але яка є більш ефективною, ніж цей алгоритм

Джерело: Дейт К Дж, Введення в системи баз даних, 8-е видання: Пер з англ – М: Видавничий дім «Вільямс», 2005 – 1328 с: Ил – Парал тит англ

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


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

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

Ваш отзыв

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

*

*