Интервальные выражения со значением

Если взять два значения даты-времени и от одного из них отнять другое, то получится интервал. Но сложение таких значений друг с другом не имеет смысла, поэтому SQL эту операцию не поддерживает. Если же сложить друг с другом два интервала или вычесть один из другого, то в результате снова получится интервал. Кроме того, интервал можно умножать или делить на числовую константу.

Вспомните, что в SQL имеются два типа интервалов: год-месяц и день-время. Чтобы избежать двусмысленности, необходимо в интервальном выражении со значением указывать, какой из этих типов в нем используется. Например, в следующем выражении вычисляется интервал в годах и месяцах от текущей даты до дня, когда вы достигнете пенсионного возраста (60 лет):

(BIRTHDAY_60 נCURRENT_DATE) YEAR TO MONTH

А это возвращает интервал в 40 дней:

INTERVAL '17' DAY + INTERVAL '23' DAY

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

INTERVAL '9' MONTH * 5

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

Условные выражения со значением

Значение условного выражения со значением зависит от условия. Такие выражения, как CASE, NULLIF и COALESCE, значительно сложнее, чем другие выражения со значением. Эти три вида условных выражений настолько сложны, что заслуживают отдельного рассмотрения. Подробно о них речь пойдет в главе 8.

Функции

Функция – это простая или достаточно сложная операция, которую обычные команды SQL выполнить не могут, но которая тем не менее достаточно часто встречается на практике. В SQL имеются функции, выполняющие работу, которую иначе пришлось бы выполнять приложению, написанному на базовом языке. В языке SQL есть две главные разновидности функций: итоговые функции и функции значения.

Суммирование с помощью итоговых функций

Итоговые функции применяются к наборам строк из таблицы, а не только к ее отдельным строкам. Эти функции в текущем наборе строк "суммируют" некоторые характеристики, т.е. получают по ним определенные итоги. В такой набор могут входить все строки таблицы или только те из них, которые определяются предложением WHERE. (Подробно о предложениях WHERE рассказывается в главе 9.)

Программисты используют название итоговые функции, потому что те берут информацию из целого набора строк, определенным образом ее обрабатывают и выдают результат в виде единичной строки. Кроме того, эти функции еще называются функциями наборов.

Чтобы показать применение итоговых функций, проанализируйте табл. 7.2, в которой представлены питательные компоненты, содержащиеся в 100 граммах некоторых продуктов питания.

Таблица 7.2. Питательные компоненты некоторых продуктов питания (в 100 граммах).

Продукт питания (Food)	Калории (Calories)	Белки (Protein), г	Жиры (Fat), г	Углеводы (Carbogidrate), г
Жареные миндальные орехи	627	18.6	57.7	19.6
Спаржа	20	2.2	0.2	3.6
Сырые бананы	85	1.1	0.2	22.2
Гамбургер с нежирной говядиной	219	27.4	11.3
Нежное мясо цыплят	166	31.6	3.4
Жареный опоссум	221	30.2	10.2
Свиной окорок	394	21.9	33.3
Фасоль лима	111	7.6	0.5	19.8
Кола	39			10.0
Белый хлеб	269	8.7	3.2	50.4
Пшеничный хлеб	243	10.5	3.0	47.7
Брокколи	26	3.1	0.3	4.5
Сливочное масло	716	0.6	81.0	0.4
Шарики из желе	367		0.5	93.1
Хрустящий арахис	421	5.7	10.4	81.0

Информация из табл. 7.2 хранится в таблице FOODS (продукты), находящейся в базе данных. В пустых полях находится значение NULL. Сообщить важные сведения о данных из этой таблицы помогают итоговые функции COUNT, AVG, MAX, MIN и SUM.

COUNT

Функция COUNT (счет) сообщает, сколько строк находится в таблице или сколько строк таблицы удовлетворяют некоторые условия. Вот самое простое применение этой функции:

SELECT COUNT (*)FROM FOODS;

Функция возвращает результат, равный 15, так как она считает все строки таблицы FOODS. Тот же результат получается при выполнении следующего оператора:

SELECT COUNT (CALORIES)FROM FOODS;

Так как значение в столбец CALORIES (калории) было введено в каждой строке, то результат подсчета получается тот же. Правда, если в некоторых строках в этом столбце находятся неопределенные значения, функция такие строки не считает.

Следующий оператор возвращает значение, равное 11, так как в 4 из 15 строк таблицы FOODS в столбце CARBOHYDRATE (углеводы) находится значение NULL:

SELECT COUNT (CARBOHYDRATE)FROM FOODS;

Совет:
В поле таблицы базы данных неопределенное значение может находиться по разным причинам. Самыми распространенными являются следующие: значение вообще не известно или пока не известно. Или значение, возможно, известно, но пока еще не введено. Иногда, если значение, предназначенное для какого-либо поля, равно нулю, оператор, вводящий данные, это поле обходит стороной и, таким образом, оставляет в нем значение NULL. Так поступать не надо, потому что нуль все же является определенным значением и его можно учитывать при подсчетах. A NULL определенным значением не является, и в SQL неопределенные значения не учитываются при расчетах.

Кроме того, чтобы узнать, сколько в столбце имеется различных значений, можно использовать функцию COUNT в сочетании с ключевым словом DISTINCT (различный). Проанализируйте следующий оператор:

SELECT COUNT (DISTINCT Fat)FROM FOODS;

Он возвращает значение, равное 12. Как видно в таблице, в 100-граммовой порции спаржи имеется столько жиров (0.2 грамма), сколько и в 100 граммах бананов, а в 100-граммовой порции фасоли лима – ровно столько жиров (0.5 грамма), сколько в 100 граммах желе. Таким образом, в таблице находится всего 12 разных значений, имеющих отношение к содержанию жиров.

AVG

Функция AVG (среднее) подсчитывает и возвращает среднее арифметическое всех значений, находящихся в определенном столбце. Конечно, эту функцию можно применять только к столбцам с числовыми данными, как в следующем примере:

SELECT AVG (Fat)FROM FOODS;

В результате получается среднее содержание жиров, равное 15.37. Это число достаточно высокое. Дело в том, что весь подсчет портит информация по сливочному маслу. Возможно, вы зададите себе вопрос, а каким было бы среднее содержание жиров, если бы не учитывалось масло. Чтобы ответить на него, в оператор можно поместить выражение WHERE:

SELECT AVG (Fat)FROM FOODSWHERE FOOD <> 'Butter';

В этом случае содержание жиров в 100 граммах пищевых продуктов в среднем падает до 10.32 грамма.

МАХ

Функция МАХ (максимум) возвращает максимальное значение, найденное в указанном столбце. Следующий оператор возвращает значение, равное 81 (количество жиров в 100 граммах сливочного масла):

SELECT MAX (Fat)FROM FOODS;

MIN

Функция MIN (минимум) возвращает минимальное значение, обнаруженное в указанном столбце. Следующий оператор возвращает значение, равное 0.4, потому что функция не учитывает неопределенные значения:

SELECT MIN (Carbohydrate)FROM FOODS;SUM

Функция SUM (сумма) возвращает сумму всех значений, обнаруженных в указанном столбце. Следующий оператор возвращает число 3924, которое является общим количеством калорий во всех 15 продуктах:

SELECT SUM (Calories)FROM FOODS;

Функции значения

Некоторые операции применяются для самых разных целей. Так как эти операции приходится использовать достаточно часто, то было бы более чем оправданным включить их в SQL в виде функций значения. Конечно, если сравнивать с такими системами управления базами данных, как Access или dBASE, то в SQL этих функций довольно-таки мало, но те немногие, что есть, являются, вероятно, функциями, которые нужны вам чаще всего. В SQL используются три вида таких функций:

· строковые функции;

· числовые функции;

· функции даты-времени.

Строковые функции

Строковые функции принимают значение одной символьной или битовой строки и возвращают другую символьную или битовую строку. В SQL имеется шесть таких функций:

· SUBSTRING;

· UPPER;

· LOWER;

· TRIM;

· TRANSLATE;

· CONVERT.

· SUBSTRING

· Функция SUBSTRING (подстрока) используется для того, чтобы из исходной строки выделить подстроку. Выделенная функцией подстрока имеет тот же тип, что и исходная. Например, если исходная строка является символьной, то и подстрока также является символьной. Вот синтаксис функции SUBSTRING:

· SUBSTRING (строковое_значение FROM начало [FOR длина]).

· Предложение в квадратных скобках ([]) не является обязательным. Подстрока, которую следует выделить из строкового_значения, начинается с символа, порядковый номер которого, если считать с самого первого символа, представлен значением начало. Кроме того, подстрока состоит из определенного количества символов, представленного значением длина. Если предложение FOR отсутствует, то подстрока выделяется, начиная от символа, соответствующего значению начало, до самого конца строки. Проанализируйте следующий пример:

· SUBSTRING ('Полностью пшеничный хлеб' FROM 11 FOR 11)

· Выделенной подстрокой является 'пшеничный х'. Она начинается с одиннадцатого символа исходной строки и имеет длину в одиннадцать символов. С первого взгляда SUBSTRING не представляется такой уж ценной функцией. Для литерала 'Полностью пшеничный хлеб' не требуется функция нахождения подстроки. Впрочем, функция SUBSTRING действительно представляет ценность, потому что строковое значение не обязательно должно быть литералом. Это значение может быть любым выражением, в результате выполнения которого получается символьная строка. Например, это может быть переменная fooditem, которая каждый раз может принимать разные значения. Следующее выражение может извлекать нужную подстроку независимо от того, какую символьную строку представляет переменная fooditem:

· SUBSTRING (:fooditem FROM 11 FOR 11).

· Все функции значения объединяет то, что они могут оперировать как со значениями, так и с выражениями, после выполнения которых получаются значения требуемого типа.

· Внимание:
При использовании функции SUBSTRING не следует забывать о следующем. Выбираемая вами подстрока действительно должна быть частью исходной строки. Если вам нужна подстрока, которая начинается с одиннадцатого символа, а в исходной строке всего только четыре символа, то вы получите значение NULL. Поэтому необходимо иметь некоторое представление о структуре своих данных, перед тем как задавать значения для функции SUBSTRING. Кроме того, нельзя указывать отрицательную длину подстроки.

· Если у столбца тип данных VARCHAR, ширина поля конкретной строки не будет известна. Если вы укажете слишком большую длину подстроки, при которой та выйдет за правый край поля, функция SUBSTRING возвратит конец исходной строки и не будет сообщать об ошибке.

· Скажем, у вас имеется оператор:

· SELECT * FROM FOODS

· WHERE SUBSTRING (Food FROM 7 FOR 7) = 'хлеб';

· И даже если значение, находящееся в столбце FOOD таблицы FOODS, имеет длину меньше 14 символов, этот оператор все равно возвращает табличную строку с данными, относящимися к белому хлебу.

· Совет:
Если какой-либо оператор функции SUBSTRING имеет значение NULL, то эта функция возвращает результат NULL.

· UPPER

· Другая функция, UPPER (верхний регистр), преобразует все символы символьной строки в верхний регистр, как показано в следующей таблице, в примерах со строками 'e.e.cummings' и Isaac Newton, Ph.D.'.

Выражение	Результат
UPPER ('е.е.cummings')	'E.E.CUMMINGS'
UPPER ('Isaac Newton, Ph.D.')	'ISAAC NEWTON, PH.D.'

· Функция UPPER не оказывает воздействия на строку, все символы которой уже находятся в верхнем регистре.

· LOWER

· Другая функция, LOWER (нижний регистр), преобразует все символы символьной строки в нижний регистр, как показано в следующей таблице, в примерах со строками 'TAXES' и 'E.E.Cummings'.

Выражение	Результат
LOWER ('TAXES')	'taxes'
LOWER ('Е. Е. Cummings')	'е. е.cummings'

· Функция LOWER не оказывает воздействия на строку, все символы которой уже находятся в нижнем регистре.

· TRIM

· Чтобы из символьной строки удалить ведущие, замыкающие или одновременно и те и другие пробелы (и не только пробелы), используйте функцию TRIM (обрезать). Следующие примеры показывают, как ее использовать, например, применительно к строкам, где находится слово treat.

Выражение	Результат
TRIM (LEADING ' ' FROM ' treat ')	'treat '
TRIM (TRAILING ' ' FROM ' treat ')	' treat'
TRIM (BOTH ' ' FROM ' treat ')	'treat'
TRIM (BOTH 't' FROM 'treat')	'rea'

· Символом по умолчанию для этой функции является пробел, поэтому следующий синтаксис также правильный:

· TRIM (BOTH FROM ' treat ').

· В этом случае получается тот же результат, что и в третьем примере из таблицы, а именно 'treat'.

· TRANSLATE и CONVERT

· Функции TRANSLATE (перевести) и CONVERT (преобразовать) выбирают исходную строку, составленную из символов одного набора, и переводят ее в строку, составленную из символов другого набора. Примерами могут быть переводы символов из английского набора в армянский или символов иврита во французский. Функции преобразования, выполняющие эти действия, зависят от реализации SQL. Подробности можно узнать в документации по имеющейся у вас реализации.

· Помни:
Если бы перевод с одного языка на другой был таким легким, как вызов в SQL функции TRANSLATE, то это было бы прекрасно. К сожалению, такая задача – не из легких. Все, что осуществляет TRANSLATE, – это перевод символа из первого символьного набора в соответствующий символ из второго набора. Она может, например, перевести 'ELLAS' (Греция) в 'Ellas'. Однако функция TRANSLATE не может перевести 'ELLAS' в 'Greece' (Греция).

Числовые функции

Числовые функции значения могут принимать данные разных типов, но возвращают всегда числовое значение. В SQL имеется тринадцать таких функций.

· Положение (POSITION).

· Извлечение (EXTRACT).

· Длина (CHAR_LENGHT, CHARACTER_LENGHT, OCTET_LENGHT).

· Кардинальные числа (CARDINALITY).

· Абсолютное значение (ABS).

· Остаток от деления нацело (MOD).

· Натуральный логарифм (LN).

· Экспонента (ЕХР).

· Возведение в степень (POWER).

· Квадратный корень (SQRT).

· Округление "вниз" (FLOOR).

· Округление "вверх" (CEIL, CEILING).

· Интервальный номер (WIDTH_BUCKET).

POSITION

Функция POSITION (положение) ищет указанную целевую строку внутри указанной исходной и возвращает положение в ней начального символа целевой строки. Эта функция имеет такой синтаксис:

POSITION (целевая_строка IN исходная_строка)

В следующей таблице приведено несколько примеров использования POSITION для исходной строки 'Полностью пшеничный хлеб'.

Выражение	Результат
POSITION ('П' IN 'Полностью пшеничный хлеб')	1
POSITION ('Пол' IN 'Полностью пшеничный хлеб')	1
POSITION ('пш' IN 'Полностью пшеничный хлеб')	11
POSITION ('пшо' IN 'Полностью пшеничный хлеб')	0
POSITION ('' IN 'Полностью пшеничный хлеб')	1

Если эта функция не находит целевую строку, то возвращает неопределенное значение. А если у целевой строки нулевая длина (как в последнем примере), то функция POSITION всегда возвращает единицу. Если любой из операндов этой функции имеет значение NULL, то в результате ее выполнения получится NULL.

EXTRACT

Функция EXTRACT (извлечь) извлекает одиночное поле из значения типа даты-времени или интервала. Например, следующее выражение возвращает 08:

EXTRACT (MONTH FROM DATE '2000-08-20').

CHARACTER_LENGTH

Функция CHARACTER_LENGTH (длина в символах) возвращает количество символов, находящихся в символьной строке. Например, следующее выражение возвращает 15:

CHARACTER_LENGTH (' Жареный опоссум')

Помни:
То, что уже говорилось в этой главе по поводу функции SUBSTRING, относится и к CHARACTER_LENGTH – эта функция не особенно полезна, если ее аргументами являются литералы, например, такие как 'Жареный опоссум'. Вместо выражения CHARACTER_LENGTH ('Жареный опоссум') можно написать число 15. Действительно, написать '15' проще. Функция SUBSTRING становится более полезной, если ее аргумент является не литеральным значением, а выражением.

OCTET_LENGTH

Что касается мира музыки, то в нем вокальный ансамбль, состоящий из восьми певцов, называется октетом. В этом ансамбле обычно есть первое и второе сопрано, первый и второй альт, первый и второй тенор, а также первый и второй бас. А что касается мира информатики, то в нем ансамбль, состоящий из восьми битов данных, называется байтом. Слово байт ясно показывает, что оно имеет отношение к биту, но при этом подразумевается нечто большее. Прекрасная игра слов, но, к сожалению, в слове "байт" ничто не напоминает о "восьмеричности". А если позаимствовать музыкальный термин, то тогда набор из восьми битов будет иметь более подходящее и более описательное название.

Практически во всех современных компьютерах для представления одного алфавитно-цифрового символа используется восемь битов. А в более сложных наборах символов (таких, например, как китайский) для этого требуется уже 16 битов. Функция OCTET_LENGTH (длина в октетах) подсчитывает и возвращает количество октетов (байтов), находящихся в строке. Если строка является битовой, то эта функция возвращает такое количество октетов, чтобы вместить находящееся в строке количество битов. А если строка состоит из символов англоязычного набора (с одним октетом на символ), то OCTET_LENGTH возвращает количество символов, имеющихся в строке. Если же строка состоит из символов китайского набора, то тогда число, возвращаемое функцией, в два раза превышает количество китайских символов. Например:

OCTET_LENGTH ('Beans, Lima')

Эта функция возвращает 11, потому что каждый символ помещается в октете.

В некоторых наборах символов для разных символов используется разное количество октетов. В частности, в тех из них, которые поддерживают смешанное использование символов канджи и латиницы, для перехода из одного набора символов в другой используются управляющие последовательности. Например, для строки, состоящий из 30 латинских символов, потребуется 30 октетов. А если все ее 30 символов взяты из канджи, то для этой строки нужно 62 октета (60 октетов плюс ведущий и замыкающий символы переключения). И наконец, если в этой строке символы латиницы и канджи попеременно чередуются друг с другом, то тогда для нее требуется 150 октетов. (Тогда для каждого символа канджи требуется два октета, а также по одному октету для ведущего и замыкающего символа переключения.) Функция OCTET_LENGTH возвращает то количество октетов, которое нужно, чтобы поместить в них имеющуюся строку.

CARDINALITY

Эта функция работает с коллекциями элементов, такими как массивы или мультимножества, где каждый элемент является значением определенного типа данных. Кардинальное число коллекции – это количество содержащихся в ней элементов. Рассмотрим один из примеров использования функции CARDINALITY:

CARDINALITY (TeamRoster)

Например, если в списке членов команды значится двенадцать человек, то эта функция возвращает значение 12. Столбец TeamRoster таблицы TEAM может быть как массивом, так и мультимножеством. В свою очередь, массив – это упорядоченная коллекция элементов, а мультимножество – неупорядоченная коллекция. Для списка команды, который может изменяться достаточно часто, разумней использовать мультимножество.

ABS

Функция ABS возвращает абсолютное значение числового выражения.

ABS (-273)

Функция возвращает 273.

MOD

Функция MOD возвращает остаток от деления нацело первого числового выражения на второе числовое выражение.

MOD (3.2)

Данная функция возвращает числовое значение 1 – остаток, получаемый при делении нацело числа 3 на число 2.

LN

Функция LN возвращает натуральный логарифм от числового.

LN (9)

Возвращаемое значение будет приближенно равно 2.197224577. Количество знаков числа после запятой зависит от вашей реализации.

ЕХР

Данная функция возводит основание натурального логарифма е в степень, указанную числовым выражением.

ЕХР (2)

Функция возвращает значение, которое приближенно равно 7.389056. Количество знаков числа после запятой зависит от вашей реализации.

POWER

Функция POWER возводит первое числовое выражение в степень, указанную вторым числовым выражением.

POWER (2.8)

В этом примере функция возвращает значение 256, т.е. два в восьмой степени.

SQRT

Эта функция возвращает квадратный корень числового выражения.

SQRT (4)

Функция возвращает значение 2 – квадратный корень из четырех.

FLOOR

Функция FLOOR округляет числовое выражение до наибольшего целого числа, не превышающего данное выражение.

FLOOR (3.141592)

Функция возвращает значение 3.0.

CEIL, или CEILING

Данная функция округляет числовое выражение до наименьшего целого числа, которое не меньше, чем данное выражение.

CEIL (3.141592)

Функция возвращает значение 4.0.

WIDTH_BUCKET

Функция WTDTH_BUCKET используется при выполнении процессов в режиме реального времени (online application processing, OLAP). Эта функция имеет четыре аргумента и возвращает целое число между 0 (нулем) и значением последнего аргумента плюс 1 (один). Для первого аргумента она назначает область в разделенном на равновеликие части диапазоне чисел между вторым и третьим аргументами функции. Для значений, находящихся за пределами заданного диапазона, функция возвращает значение 0 (нуль) либо значение последнего аргумента плюс 1 (один).

Например:

WIDTH_BUCKET (PI, 0, 9, 5)

Предположим, что PI– числовое выражение со значением– это 3.141592. Интервал значений между нулем и девятью (0 и 9 – второй и третий аргументы функции соответственно) нужно разделить на пять равных отрезков (5 – четвертый аргумент функции), каждый шириной в две единицы. В этом случае функция возвращает значение 2, поскольку число 3.141592 находится во втором отрезке, который является диапазоном значений от двух до четырех.