Правильная ссылка на эту страницу
http://az-design.ru/Support/DataBase/SQL/CelkoJ/01h007.shtml

Глава 7. Другие выражения

       Для повышения эффективности языка в стандарте SQL-92 появились новые выражения и были расширены правила создания старых. В принципе, все выражения, которые кажутся разумными, скорее всего имеют допустимый синтаксис.

7.1. Выражение CASE

       Выражение CASE, вероятно, является наиболее ценным приобретением SQL-92. Ниже приводится краткое описание работы с ним, остальные приемы представлены в других местах книги. В особенности обратите внимание на раздел 9.3, где рассматриваются операторы UPDATE.
       Выражение CASE позволяет программисту получить значение на основе значения некоторого логического выражения. Идея и синтаксис перешли в стандарт ANSI из языка программирования ADA. Спецификация CASE в форме Бэкуса-Наура выглядит следующим образом:

<case specification  = <simple case> | <searched case>
<simple case>  = CASE <case operand>
                      <simple when clause> . . .
                      [<else clause>]
                 END
<searched case>  = CASE <searched when clause> . . .
                        [<else clause>]
                   END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<case operand> ::= <value expression>
<when operand> ::= <value expression>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>

       Выражение searched CASE является, вероятно, его наиболее часто используемой версией. Предложения WHEN ... THEN... выполняются слева направо. Первое предложение WHEN, дающее ответ TRUE, возвращает то значение, которое приводится в соответствующем ему предложении THEN. Подобные выражения можно вкладывать друг в друга. Если явным образом в выражении CASE предложение ELSE не определено, по умолчанию предполагается предложение ELSE NULL.
       Я рекомендую всегда задавать предложение ELSE, чтобы впоследствии, когда вы найдете то, что потребуется вернуть, можно было соответствующим образом изменить его. Я посоветовал бы в ответ на NULL в выражении CASE также возвращать NULL, поскольку именно так работает большинство функций SQL.
       Конструкция <simple case expression> определена как выражение searched CASE, когда во всех предложениях WHEN производится сравнение на равенство с операндом <case operand>. Например:

CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END

       Это можно написать и так:

CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END

7.1.1. Функции COALESCE() и NULLIF()

       В стандарте SQL-92 в терминах выражений CASE определено несколько других функций, упрощающих и облегчающих реализацию языка. Например, функцию COALESCE() можно определить так:
       1. COALESCE (<val'je exp #1>) эквивалентна (<value exp#1>)
       2. COALESCE (<value exp #1>, < value exp #2>) эквивалентна

CASE WHEN <value exp #1> IS NOT NULL
     THEN <value exp #1> 
     ELSE <value exp #2> END

       Затем можно рекурсивно определить функцию для (n) выражений, где (n>=3):

COALESCE <value exp #1>, rvalue exp #2>, ... , n), 

       Эквивалентна

CASE WHEN <value exp #1> IS NOT NULL
     THEN <value exp #1>
     ELSE COALESCE <value exp #2>, ..., n)
END

       Аналогичным образом, функция NULLIF(<value exp #1>, <value exp#2>) эквивалентна выражению:

CASE WHEN <value exp #1> = <value exp #2>
     THEN NULL
     ELSE <value exp #1>
END

7.1.2. Выражения CASE с GROUP BY

       Очень полезно выражение CASE в составе запросов GROUP BY. Например, чтобы определить число мужчин и женщин в отделе, можно поступить следующим образом:

SElECT dept_nbr,
       SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0) AS males,
       SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0) AS females
  FROM Personnel
 GROUP BY dept_nbr;

или:

SELECT dept_nbr,
       COUNT(CASE WHEN gender = 'M' THEN 1 ELSE NULL) AS males,
       COUNT(CASE WHEN gender = 'F' THEM 1 ELSE NULL) AS females
  FROM Personnel
 GROUP BY dept_nbr;

       Я не уверен, можно ли заранее определить, какая форма будет работать быстрее.
       В этом примере выражение CASE было включено в состав агрегатной функции; можно поступить и наоборот. Например, у вас есть таблица квалификации сотрудников:

CREATE TABLE PersonnelSkills
   (emp_id            CHAR(11) NOT NULL,
    skill_id          CHAR(11) NOT NULL,
    primary_skill_md  CHAR(1) NOT NULL
CONSTRAINT pnmary_skill_given
           CHECK (primary_skill_md IN 'Y', 'N1),
PRIMARY KEY (emp_id, skilled));

       Для каждой квалификации каждого сотрудника в таблице отведена строка. Человек, имеющий несколько квалификаций, характеризуется несколькими строками, причем основная квалификация помечена индикатором 'Y'. Если квалификация только одна (т.е. в таблице для сотрудника имеется лишь одна строка), значение pnmary_skill_ind будет не определено. Задача состоит в том, чтобы вывести список всех сотрудников, показав для них:
       a) единственную квалификацию, если для сотрудника в таблице имеетсятолько одна строка
       или
       b) основную квалификацию, если для сотрудника в таблице имеется несколько строк.

SELECT emp_id,
       CASE WHEN COUNT(*) = 1 
            THEN MAX(skill_id)
            ELSE MAX(CASE WHEN primary_skill_md = 'Y'
                          THEN skill_id
                          ELSE NULL END)
       END AS mam_skill
  FROM PersonnelSkills
 GROUP BY emp_id;

       На первый взгляд такое решение может показаться нарушением правила SQL, запрещающего вложение агрегатных функций, но это не так. Агрегатные функции находятся внутри выражений CASE, а последние вложены в другие агрегатные функции. Подобная конструкция работает, поскольку каждое выражение CASE приводится к агрегатной функции, МАХ() или NULL.

7.1.3. Конструкции CASE, CHECK() и логическая импликация

       В выражения CASE можно включать сложные логические предикаты, возвращающие 1 (TRUE) или 0 (FALSE):

CONSTRAINT implication_example
   CHECK (CASE WHEN dept_nbr = 'DT'
               THEN CASE WHEN salary < 44000.00
                         THEN 1 ELSE 0 END
               ELSE 1 END = 1)

       Данная конструкция представляет собой операцию логической импликации (logical implication). Обычно ее обозначают стрелкой с двойным хвостом, направленной в одну сторону (=>), и определяют как утверждение "истинная предпосылка не может привести к ложному заключению", или "если А, то В".
       В переводе на обычный язык приведенный выше пример означает: "Если сотрудник работает в отделе D1, его зарплата меньше 44000 фунтов". Это утверждение отличается от ограничения (dept_nbг = 'D1' AND salary < 44000.00).

7.1.4 Функция DECODE() СУБД Oracle

       В Oracle реализована функция DEC0DEO, представляющая собой упрощенный вариант выражения CASE:

DECODE (<control vanable>, 
        <value1>, <value2>, 
        <value3>, <value4>,
   ... [<last value>])

       Она эквивалентна следующему:

CASE <control vanable> 
     WHEN <value1> THEN <value2> 
     WHEN <value3> THEN <value4>
     ELSE <last value>
END

       Задача программиста — правильно соотнести пары значений после первоначальной переменной <control vanable>; значение <last value> является необязательным.

7.2. Подзапросы и константы

       Подзапросы представляют собой операторы SELECT в скобках. Они бывают четырех типов — табличные, колоночные, строчные и скалярные. Как следует из названия, табличный подзапрос возвращает в результате таблицу, поэтому он используется там, где в SQL-92 есть таблица; обычно это предложение FROM.
       Колоночный подзапрос возвращает таблицу с одним столбцом (колонкой). В первоначальных стандартах SQL-86 и SQL-89 этот тип был важен, поскольку предикаты IN, <comp op> ALL и <comp op> ANY основывались на способности языка конвертировать столбцы в список сравнений, соединенных операторами AND или OR.
       Строчный подзапрос возвращает одну строку. Он может использоваться там, где применяются строки. Такой запрос является основой одноэлементного оператора SELECT, применяемого во встроенном SQL. В настоящее время он используется не слишком часто, но с расширением операций theta, обрабатывающих сравнения строк, он может приобрести большую популярность.
       Скалярный подзапрос возвращает одно скалярное значение. Он применяется везде, где используются такие значения; обычно это предложения SELECT и WHERE.
       Скалярный подзапрос можно рассматривать как SQL-версию подпрограммы или встроенной (in-line) функции. Он включается в код на базовом языке и доступен в программе. Применение скалярных подзапросов будет частично рассмотрено в следующих разделах при описании приемов и методов программирования. Пока я лишь отмечу, что производительность работы такого подзапроса очень сильно зависит от архитектуры оборудования, на котором реализован ваш вариант языка SQL. Компьютеры с возможностью параллельного вычисления могут для каждого скалярного подзапроса выделить свой процессор, что заметно повысит производительность.
       С помощью выражений VALUES() можно сконструировать таблицы любой формы. Подробно это описано в разделе 9.2 при рассмотрении оператора INSERT INTO, основная же идея состоит в том, что вы можете создать строку в виде разделенного запятыми списка скалярных выражений, а таблицу — в виде разделенного запятыми списка строковых конструкторов.

7.3. Характеристические функции Розенштейна

       Характеристическая функция преобразует логическое выражение в единицу, если оно истинно, и в нуль, если ложно. Это можно сделать также в уже описанных выражениях CASE, но другим способом. В качестве символа операции в литературе принято использовать маленькую букву дельта (δ) или заглавную букву кси (Χ). С помощью оператора можно определить множество, задав правило для его элементов.
       Давид Розенштейн нашел в языке Sybase T-SQL способы реализации характеристических функций с помощью применяемых к числовым столбцам алгебраических выражений (Rozenshtein, 1995) еще до того, как компания Sybase добавила в свой продукт выражение CASE. He углубляясь в детали, я просто воспользуюсь нотацией Розенштейна и приведу основные формулы преобразования числовых выражений в вычисляемые характеристические функции:

δ(a = b) Становится (1 - ABS(SIGN(a - b)))
δ(а <> b) Становится (ABS(SIGN(a - b)))
δ(а < b) Становится (1 - SIGN(1 + SIGN(a - b)))
δ(a <= b) Становится (SIGN(1 - SIGN(a - b)))
δ(а > b) Становится (1 - SIGN(1 - SIGN(a - b)))
δ(а >= b) Становится (SIGN(1 + SIGN(a - b)))

       Основные логические операции также можно перевести в такие функции:

NOT δ (а) Становится (1-<δ(a))
(δ(a) AND δ(b)) Становится (δ(а) * δ(b))
(δ(a) OR δ(b)) Становится (δ(a) + δ(b))

       Это напоминает оригинальную нотацию Джорджа Буля для его алгебры. Однако имейте в виду, если а или b равны NULL, результат также будет NULL, а не один или нуль, — Буль о таком варианте даже не помышлял.
       Символьные строки можно обработать с помощью функции POSITION.

δ(a = s) становится POSITION(a IN s)
δ(a <> s) становится SIGN(1 - POSITION(a IN s))

       В книге Розенштейна приводятся и другие приемы, но многие из них работают только в реализации T-SQL компании Sybase и непереносимы на остальные продукты. Кроме того, этот код может слишком усложниться, что затруднит чтение другим программистам.
       В стандарте SQL-92 выражения VALUES были расширены; теперь вы можете объявить больше одной строки.




<<< Пред. Оглавление
 
След. >>>

Дата последнего изменения:
Thursday, 21-Aug-2014 09:10:55 MSK


Постоянный адрес статьи:
http://az-design.ru/Support/DataBase/SQL/CelkoJ/01h007.shtml