Функции ms sql: Создание определяемых пользователем функций (компонент Database Engine) — SQL Server

Содержание

3.3. Хранимые функции Transact-SQL — Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

С SQL Server вы можете создавать ваши собственные функции, добавляющие и расширяющие функции, предоставляемые системой. Функции могут получать 0 или более параметров и возвращать скалярное значение или таблицу. Входные параметры могут быть любого типа, исключая timestamp, cursor, table.

Сервер SQL поддерживает три типа функций определенных пользователем:

Скалярные функции – похожи на встроенные функции;

Функция, возвращающая таблицу — возвращает результат единичного оператора SELECT. Он похож на объект просмотра, но имеет большую эластичность благодаря использованию параметров, и расширяет возможности индексированного объекта просмотра;

Многооператорная функция — возвращает таблицу созданную одним или несколькими операторами Transact-SQL, чем напоминает хранимые процедуры. В отличие от процедур, на такие функции можно ссылаться в WHERE как на объект просмотра.

3.3.1. Создание хранимой функции

Создание функций очень похоже на создание процедур и объектов просмотра. Недаром мы рассматриваем все эти темы в одной главе. Для создания функции используется оператор CREATE FUNCTION. В зависимости от типа, Объявление будет отличаться. Рассмотрим все три типа объявления.

Скалярная функция:


CREATE  FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } 
      [ ,...n ] ] ) 

RETURNS scalar_return_data_type

[ WITH  [ [,] ...n] ] 

[ AS ]

BEGIN 
    function_body 
    RETURN scalar_expression
END

Функция, возвращающая таблицу:


CREATE FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } 
     [ ,...n ] ] ) 

RETURNS TABLE 

[ WITH  [ [,] ...n ] ] 

[ AS ] 

RETURN [ ( ] select-stmt [ ) ]

Многооператорные функции:


CREATE FUNCTION [ owner_name.  ] function_name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } 
     [ ,...n ] ] ) 

RETURNS @return_variable TABLE  

[ WITH  [ [,] ...n ] ] 

[ AS ] 

BEGIN 
    function_body 
    RETURN
END

 ::= 
    { ENCRYPTION | SCHEMABINDING }

 :: = 
    ( { column_definition | table_constraint } [ ,...n ] )

3.3.2. Скалярные функции в Transact-SQL

Давайте для примера создадим функцию, которая будет возвращать скалярное значение. Например, результат перемножение цены на количество указанного товара. Товар будет идентифицироваться по названию и дате, ведь мы договорились, что сочетание этих полей дает уникальность. Но будьте осторожны, при тестировании запроса, если в разделе 3.2.8 вы выполнили запрос на изменение данных и создали дубликаты покупок за 1.1.2005-го года.

Итак, посмотрим сначала на код создание скалярной функции:


CREATE FUNCTION GetSumm
 (@name varchar(50), @date datetime)
RETURNS numeric(10,2)
 BEGIN
  DECLARE @Summ numeric(10,2)
  SELECT @Summ = Цена*Количество
  FROM Товары
  WHERE [Название товара]=@name
    AND Дата=@date;
  RETURN @Summ
 END

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

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

Код, который должна выполнять функция пишется между ключевыми словами BEGIN (начало) и END (конец). В коде можно использовать любые операторы Transact-SQL, которые мы изучали ранее. Итак, объявление нашей функции в упрощенном виде можно описать следующим образом:


CREATE FUNCTION GetSumm
 (@name varchar(50), @date datetime)
RETURNS numeric(10,2) 
 BEGIN
  -- Код функции
 END

Между ключевыми словами BEGIN и END у нас выполняется следующий код:


-- Объявление переменной
DECLARE @Summ numeric(10,2)

-- Выполнение запроса на выборку суммы
SELECT @Summ = Цена*Количество
FROM Товары
WHERE [Название товара]=@name
  AND Дата=@date;

-- Возврат результата
RETURN @Summ

В первой строке объявляется переменная @Summ. Она нужна для хранения промежуточного результата расчетов. Далее выполняется запрос SELECT, в котором происходит поиск строки по дате и названию товара в таблице товаров. В найденной строке перемножаются поля цены и количества, и результат записывается в переменную @Summ.

Обратите внимание, что в конце запроса стоит знак точки с запятой. Каждый запрос должен заканчиваться этим символом, но в большинстве примеров мы этим пренебрегали, но в функции отсутствие символа «;» может привести к ошибке.

В последней строке возвращаем результат. Для этого нужно написать ключевое слово RETURN, после которого пишется возвращаемое значение или переменная. В данном случае, возвращаться будет содержимое переменной @Summ.

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

3.3.3. Использование функций

Как выполнить такую функцию? Да также, как и многие другие системные функции (например, GETDATE()). Например, следующий пример использует функцию в операторе SELECT:


SELECT dbo.GetSumm('Картофель', '03.03.2005')

В этом примере, оператор SELECT возвращает результат выполнения функции GetSumm. Функция принадлежит пользователю dbo, поэтому перед именем я указал владельца. После имени в скобках должны быть перечислены параметры в том же порядке, что и при объявлении функции. В данном примере я запрашиваю затраты на картофель, купленный 3.3.2005.

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


SELECT Цена*Количество
FROM Товары
WHERE [Название товара]='Картофель'
  AND Дата='03.03.2005'

Функции можно использовать не только в операторе SELECT, но и напрямую, присваивая значение переменной. Например:


DECLARE @Summ numeric(10,2)
SET @Summ=dbo.GetSumm('Картофель', '03.03.2005')
PRINT @Summ

В этом примере мы объявили переменную @Summ типа numeric(10,2). Именно такой тип возвращает функция. В следующей строке переменной присваивается результат выполнения Summ, с помощью SET.

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

Итак, в моей таблице есть две покупки хлеба 1.1.2005-го числа. Попробую запросить у функцию сумму:


SELECT dbo.GetSumm('Хлеб', '01.01.2005')

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

3.3.4. Функция, возвращающая таблицу

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


CREATE FUNCTION GetPrice()
RETURNS TABLE
AS
 RETURN 
 (
  SELECT Дата, [Название товара], Цена, 
    Количество, Цена*Количество AS Сумма
  FROM Товары
 )

Начало функции такое же, как у скалярной – указываем оператор CREATE FUNCTION и имя функции. Я специально создал эту функцию без параметров, чтобы вы увидели, как это делается. Не смотря на то, что параметров нет, после имени должны идти круглые скобки, в которых не надо ничего писать. Если не указать скобок, то сервер вернет ошибку и функция не будет создана.

Разница есть и в секции RETURNS, после которой указывается тип TABLE, что говорит о необходимости вернуть таблицу. После этого идет ключевое слово AS и RETURN, после которого должно идти возвращаемое значение. Для функции данного типа в секции RETURN нужно в скобках указать запрос, результат которого и будет возвращаться функцией.

Когда пишете запрос, то все его поля должны содержать имена. Если одно из полей не имеет имени, то результатом выполнения оператора CREATE FUNCTION будет ошибка. В нашем примере последнее поле является результатом перемножения полей «Цена» и «Количество», а такие поля не имеют имени, поэтому мы его задаем с помощью ключевого слова AS.

Посмотрим, как можно использовать такую функцию с помощью оператора SELECT:


SELECT * 
FROM GetPrice()

Так как мы используем простой оператор SELECT, то мы можем и ограничивать вывод определенными строками, с помощью ограничений в секции WHERE. Например, в следующем примере выбираем из результата функции только те строки, в которых поле «Количество» содержит значение 1:


SELECT * FROM GetPrice()
WHERE Количество=1

Функция возвращает в качестве результата таблице, которую вы можете использовать как любую другую таблицу базы данных. Давайте создадим пример в котором можно будет увидеть использование функции в связи с таблицами. Для начала создадим функцию, которая будет возвращать идентификатор работников таблицы tbPeoples и объединенные в одно поле ФИО:


CREATE FUNCTION GetPeoples()
RETURNS TABLE
AS
 RETURN 
 (
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO
  FROM tbPeoples
 )

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


SELECT * 
FROM GetPeoples() p, tbPhoneNumbers pn
WHERE p.idPeoples=pn.idPeoples

Как видите, функции, возвращающие таблицы очень удобны. Они больше, чем процедуры похожи на объекты просмотра, но при этом позволяют принимать параметры. Таким образом, можно сделать так, чтобы сама функция возвращала нам только то, что нужно. Вьюшки такого не могут делать по определению. Чтобы получить нужные данные, вьюшка должна выполнить свой SELECT запрос, а потом уже во внешнем запросе мы пишем еще один оператор SELECT, с помощью которого ограничивается вывод до необходимого. Таким образом, выполняется два запроса SELECT, что для большой таблицы достаточно накладно. Функция же может сразу вернуть только то, что нужно.

Рассмотрим пример, функция GetPeoples у нас возвращает все строки таблицы. Чтобы получить только нужную фамилию, нужно писать запрос типа:


SELECT * 
FROM GetPeoples()
WHERE FIO LIKE 'ПОЧЕЧКИН%'

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


CREATE FUNCTION GetPeoples1(@Famil varchar(50))
RETURNS TABLE
AS
 RETURN 
 (
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO
  FROM tbPeoples
  WHERE vcFamil=@Famil
 )

3.

3.5. Много операторная функция возвращающая таблицу

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

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


CREATE FUNCTION имя (параметры)
RETURNS  имя_переменной TABLE 
  (описание вида таблицы, 
   в которой будет представлен результат)
AS
 BEGIN
  Выполнение любого количества операций
  RETURN
 END

Это упрощенный вид создания процедуры. Более полный вид мы рассматривали в начале главы, а сейчас я упростил объявление, чтобы проще было его разбирать.

Объявление больше похоже на создание скалярных функций. Первая строка без изменений. В секции RETURNS объявляется переменная, которая имеет тип TABLE. После этого, в скобках нужно описать поля результирующей таблицы. После ключевого слова AS идtт пара операторов BEGIN и END, между которыми может выполняться какое угодно количество операций. Выполнение операций заканчивается ключевым словом RETURN.

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

Теперь посмотрим на пример создания функции:


CREATE FUNCTION getFIO ()
RETURNS  @ret TABLE 
  (idPeoples int primary key,
   vcFIO varchar(100))
AS
 BEGIN
  INSERT @ret
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName
  FROM tbPeoples;

  RETURN
 END

В данном примере в качестве результата объявлена переменная @ret, которая является таблицей из двух полей «idPeoples» типа int и «vcFIO» типа varchar длинной в 50 символов. В теле функции в эту таблицу записываются значения из таблицы tbPeoples и выполняется оператор RETURN, завершающий выполнение функции.

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


SELECT * 
FROM GetFIO()

3.3.6. Опции функций

При создании функций могут использоваться следующие опции SCHEMABINDING (привязать к схеме) и/или ENCRYPTION (шифровать текст функции). Если вторая опция нам уже известна по вьюшкам и процедурам (позволяет шифровать исходный код функции в системных таблицах), то вторая встречается впервые, но при этом предоставляет удобное средство защиты данных.

Если функция создана с опцией SCHEMABINDING, то объекты базы данных, на которые ссылается функция, не могут быть изменены (с использованием оператора ALTER) или удалены (с помощью оператора DROP). Например, следующая функция использует таблицу tbPeoples и при этом используется опция SCHEMABINDING:


CREATE FUNCTION GetPeoples2(@Famil varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN 
 (
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO
  FROM dbo. tbPeoples
  WHERE vcFamil=@Famil
 )

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

все функции объявленные пользователем и просмотрщики на которые ссылается функция, также связаны со схемой с помощью опции SCHEMABINDING;

объекты, на которые ссылается функция, должны использовать имя из двух частей именования: owner.objectname. При создании функции GetPeoples2 ссылка на таблицу указана именно в таком формате – dbo.tbPeoples;

Функция и объекты должны быть расположены в одной базе данных;

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

Создайте функцию и попробуйте после этого удалить таблицу tbPeoples.


DROP TABLE tbPeoples

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

Чтобы увидеть сообщение без удаления ключа, давайте добавим к таблице колонку, а потом попробуем ее удалить:


-- Добавим колонку
ALTER TABLE dbo.tbPeoples
ADD 
 vcTemp VARCHAR(30) NOT NULL default ''

-- Попробуем ее удалить
ALTER TABLE dbo.tbPeoples
DROP COLUMN vcTemp

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

3.3.7. Изменение функций

Вы можете изменять функцию с помощью оператора ALTER FUNCTION. Общий вид для каждого варианта функции отличается. Давайте рассмотрим каждый из них.

1. Общий вид команды изменения скалярной функции:


ALTER FUNCTION [ owner_name.  ] function_name 
    ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 

RETURNS scalar_return_data_type

[ WITH  [,...n] ] 

[ AS ]

BEGIN 
    function_body 
    RETURN scalar_expression
END

2. Общий вид изменения функции, возвращающей таблицу:


ALTER FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 

RETURNS TABLE 

[ WITH  [ ,...n ] ] 

[ AS ] 

RETURN [ ( ] select-stmt [ ) ]

3. Общий вид команды изменения функции с множеством операторов, возвращающей таблицу.


ALTER FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 

RETURNS @return_variable TABLE  

[ WITH  [ ,...n ] ] 

[ AS ] 

BEGIN 
    function_body 
    RETURN
END

 ::= 
    { ENCRYPTION | SCHEMABINDING }

 :: = 
    ( { column_definition | table_constraint } [ ,. ..n ] )

Следующий пример показывает упрощенный вариант команды, изменяющей функцию:


ALTER FUNCTION dbo.tbPeoples
AS
 -- Новое тело функции

3.3.8. Удаления функций

Если вы внимательно читали об объектах просмотра и функциях, то не трудно догадаться, как можно удалить функцию. Конечно же для этого используется оператор DROP FUNCTION:


DROP FUNCTION dbo.GetPeoples2

Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT

Навигация по уроку

Функция SQL SUM
Функция SQL MIN
Функция SQL MAX
Функция SQL AVG
Функция SQL COUNT
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Разрешены ли агрегатные функции от агрегатных функций?

Связанные темы

Оператор SELECT
GROUP BY — группировка в запросах

Назад

Содержание

Вперёд>>>

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц
базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения
единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX,
AVG и COUNT. Следует различать
два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают
одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть
с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных
функций без группировки.

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только
к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы
начинаются так:

SELECT SUM(ИМЯ_СТОЛБЦА) …

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть
задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что
учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого
можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД
не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Сначала работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными —
в файле по этой ссылке.

Пример 1. Есть база данных фирмы с данными о её подразделениях
и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка
из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT SUM(Salary)
FROM Staff

Этот запрос вернёт значение 287664,63.

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

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками
с должностью Clerk.

Правильное решение и ответ.

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает
минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица —
те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42.
Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT MIN(Salary)
FROM Staff WHERE Dept=42

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых
других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о
подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные
о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе,
расположенном в Бостоне.

Правильное решение и ответ.

Страница 2 (Разрешены ли агрегатные функции от агрегатных функций?)

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

Пример 5. База данных и таблица —
те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42.
Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT MAX(Salary)
FROM Staff WHERE Dept=42

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами — Staff и Org.
Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе,
относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).

Правильное решение и ответ.

Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG.
Эта функция возвращает среднее значение среди всех значений столбца.

Пример 7. База данных и таблица —
те же, что и в предыдущих примерах.

Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42.
Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT AVG(Years)
FROM Staff WHERE Dept=42

Результатом будет значение 6,33

В следующем упражнении для самостоятельного решения помимо
агрегатной функции требуется использовать также предикат BETWEEN.

Пример 8. Работаем с одной таблицей — Staff.
Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.

Правильное решение и ответ.

Страница 2 (Разрешены ли агрегатные функции от агрегатных функций?)

Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать
SELECT COUNT(ИМЯ_СТОЛБЦА) …, то результатом будет количество записей без учёта тех записей,
в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента
звёздочку и начать запрос SELECT COUNT(*) . .., то результатом будет количество всех записей (строк)
таблицы.

Пример 9. База данных и таблица —
те же, что и в предыдущих примерах.

Требуется узнать число всех сотрудников, которые получают комиссионные. Число
сотрудников, у которых значения столбца Comm — не NULL, вернёт следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT COUNT(Comm)
FROM Staff

Результатом будет значение 11.

Пример 10. База данных и таблица —
те же, что и в предыдущих примерах.

Если требуется узнать общее количество записей в таблице, то
применяем запрос со звёздочкой в качестве аргумента функции COUNT (на MS SQL Server — с предваряющей конструкцией USE company1;):

SELECT COUNT(*)
FROM Staff

Результатом будет значение 17.

В следующем упражнении для самостоятельного решения потребуется
использовать подзапрос.

Пример 11. Работаем с одной таблицей — Staff.
Вывести число сотрудников в отделе планирования (Plains).

Правильное решение и ответ.

Страница 2 (Разрешены ли агрегатные функции от агрегатных функций?)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY
служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок,
посвящённый отдельно этому оператору.

Работать будем с базой данных «Портал объявлений 1». Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных —
в файле по этой ссылке.

Пример 12. Итак, есть база данных портала объявлений. В ней есть
таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит
данные о больших категориях объявлений (например, Недвижимость), а столбец Parts — о более мелких
частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость).
Столбец Units содержит данные о количестве поданных объявлений, а столбец Money — о денежных суммах,
вырученных за подачу объявлений.

Category	Part	Units	Money
Транспорт	Автомашины	110	17600
Недвижимость	Квартиры	89	18690
Недвижимость	Дачи	57	11970
Транспорт	Мотоциклы	131	20960
Стройматериалы	Доски	68	7140
Электротехника	Телевизоры	127	8255
Электротехника	Холодильники	137	8905
Стройматериалы	Регипс	112	11760
Досуг	Книги	96	6240
Недвижимость	Дома	47	9870
Досуг	Музыка	117	7605
Досуг	Игры	41	2665

Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу
объявлений в каждой категории. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):

SELECT Category, SUM(Money) AS Money
FROM ADS GROUP BY Category

Результатом будет следующая таблица:

Category	Money
Досуг	16510
Недвижимость	40530
Стройматериалы	18900
Транспорт	38560
Электротехника	17160

Пример 13. База данных и таблица —
та же, что в предыдущем примере.

Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было
подано наибольшее число объявлений. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):

SELECT Category, Part, MAX(Units) AS Maximum
FROM ADS GROUP BY Category

Результатом будет следующая таблица:

Category	Part	Maximum
Досуг	Музыка	117
Недвижимость	Квартиры	89
Стройматериалы	Регипс	112
Транспорт	Мотоциклы	131
Электротехника	Холодильники	137

Итоговые и индивидуальные значения в одной таблице можно получить объединением
результатов запросов с помощью оператора UNION.

Страница 2 (Разрешены ли агрегатные функции от агрегатных функций?)

Поделиться с друзьями

Назад

Содержание

Вперёд>>>

List of Transact SQL Functions on SQL Server 2019

004 Certrivate

Aggregate Function

APPROX_COUNT_DISTINCT

Aggregate Function

AVG

Aggregate Function

CHECKSUM_AGG

Агрегатная функция

COUNT

Функция подсчета SQL с разделением по пункту

Aggregate Function

COUNT_BIG

Aggregate Function

GROUPING

Aggregate Function

GROUPING_ID

Aggregate Function

MAX

МАКС.

Агрегированная функция

МИН.

Агрегированная функция

STDEV

Aggregate Function

STDEVP

Aggregate Function

STRING_AGG

String Concatenation in SQL Server with String_Agg Function

Aggregate Function

SUM

Агрегированная функция

VAR

Агрегированная функция

VARP

Analytic Function

CUME_DIST

Analytic Function

FIRST_VALUE

First_Value SQL Analytic Function in SQL Server

Analytic Function

LAG

Функция SQL LAG() в SQL Server 2012 для вычисления предыдущего значения

Аналитическая функция

The Last_value

The Last_Value SQL Аналитическая функция в SQL Server

Аналитическая функция

Следующее значение

СВОЙСТВЕННАЯ ФУНКЦИЯ В SQL Server 2012.

PERCENT_RANK

Аналитическая функция

PERCENTILE_CONT

Аналитическая функция

PERCENTILE_DISC

Collation Function

COLLATIONPROPERTY

Collation Function

TERTIARY_WEIGHTS

Configuration Function

@@DATEFIRST

Функция конфигурации

@@DBTS

Функция конфигурации

@@ langid

Функция конфигурации

@@ language

Функция конфигурации

@@lockimeaut

@@lockimeout

Функция конфигурации

@@MAX_CONNECTIONS

Функция конфигурации

@@MAX_PRECISION

Configuration Function

@@NESTLEVEL

Configuration Function

@@OPTIONS

Configuration Function

@@ REMSERVER

@@REMSERVER

Функция конфигурации

@@ИМЯ_СЕРВЕРА

Функция конфигурации

@@SERVICENAME

Configuration Function

@@SPID

Configuration Function

@@TEXTSIZE

Configuration Функция

@@VERSION

Функция преобразования

CAST and CONVERT

CAST и CONVERT

1 10004 Conversion Function

PARSE

Conversion Function

TRY_CAST

Conversion Function

TRY_CONVERT

Conversion Function

TRY_PARSE

Криптографическая функция

ASYMKEY_ID

Криптографическая функция

ASYMKEYPROPERTY

Cryptographic function

CERTENCODED

Cryptographic function

CERTPRIVATEKEY

Cryptographic function

DECRYPTBYASYMKEY

Криптографическая функция

DECRYPTBYCERT

Cryptographic function

DECRYPTBYKEY

Cryptographic function

DECRYPTBYKEYAUTOASYMKEY

Cryptographic function

DecryptByKeyAutoCert

Cryptographic function

DECRYPTBYPASSPHRASE

РАСШИФРОВАТЬ БАЙПАРСФРАЗУ

Cryptographic function

ENCRYPTBYASYMKEY

Cryptographic function

ENCRYPTBYCERT

Cryptographic function

ENCRYPTBYKEY

Cryptographic function

ENCRYPTBYPASSPHRASE

Криптографическая функция

HASHBYTES

Cryptographic function

IS_OBJECTSIGNED

Cryptographic function

KEY_GUID

Cryptographic function

KEY_ID

Криптографическая функция

KEY_NAME

Криптографическая функция

SIGNBYASYMKEY

Cryptographic function

SIGNBYCERT

Cryptographic function

SYMKEYPROPERTY

Cryptographic function

VERIFYSIGNEDBYASMKEY

Криптографическая функция

VERIGYSIGNEDBYCERT

Cursor Function

@@CURSOR_ROWS

Cursor Function

@@FETCH_STATUS

Cursor Function

CURSOR_STATUS

Тип данных Функция

DATALENGTH

Тип данных Функция

IDENT_CURRENT

Ident_current

Функция типа данных

IDEA_INCR

IDEAR_INCR

Функция типа данных

IDEAD_SEED

Функция DATA

Function Function

). Функциональная функция

Тип данных Функция

SQL_VARIANT_PROPERTY

Дата и время Функция

@@ DateFirst

Date & Time Function

@@ Язык

@@ Язы и функция времени

DATEADD

функция даты и времени

DATEDIFF

Date & Time Function

DATEDIFF_BIG

Date & Time Function

DATEFROMPARTS

Date & Time Function

DATENAME

Date & Time Функция

DATEPART

Дата и время Функция

DATETIME2FROMPARTS

Date & Time Function

DATETIMEFROMPARTS

Date & Time Function

DATETIMEOFFSETFROMPARTS

Date & Time Function

DAY

Функция даты и времени

КОНМЕСЯЦА

SQL Server EOMonth Функция конца месяца для расчета последнего дня месяца

Date & Time Function

GETDATE

Date & Time Function

GETUTCDATE

Date & Time Function

ISDATE

Date Функция времени и даты

МЕСЯЦ

Функция даты и времени

УСТАНОВКА ДАТЫ ПЕРВОЙ

Date & Time Function

SET DATEFORMAT

Date & Time Function

SET LANGUAGE

Date & Time Function

SMALLDATETIMEFROMPARTS

Дата и время Функция

sp_helplanguage

Date & Time Function

SWITCHOFFSET

Date & Time Function

SYSDATETIME

Date & Time Function

SYSDATETIMEOFFSET

Date & Time Function

SYSUTCDATETIME

Функция даты и времени

TIMEFROMPARTS

Date & Time Function

TODATETIMEOFFSET

Date & Time Function

YEAR

JSON Function

ISJSON

Функция JSON

JSON_MODIFY

Функция JSON

JSON_QUERY

JSON Function

JSON_VALUE

Logical Function

CHOOSE

SQL Choose() Function in SQL Server

Logical Function

IIF

Логическая функция SQL IIF в Transact-SQL с SQL Server

Математическая функция

ABS

Mathematical Function

ACOS

Mathematical Function

ASIN

Mathematical Function

ATAN

Mathematical Function

ATN2

Математическая функция

ПОТОЛОК

Математическая функция

COS

Mathematical Function

COT

Mathematical Function

DEGREES

Mathematical Function

EXP

Математическая функция

ЭТАЖ

Математическая функция

LOG

Mathematical Function

LOG10

Mathematical Function

POWER

Mathematical Function

RADIANS

РАДИАНЫ

Математическая функция

RAND

Математическая функция

ROUND

Mathematical Function

SIGN

Mathematical Function

SIN

Mathematical Function

SQRT

Математическая функция

КВАДРАТ

Математическая функция

TAN

Metadata Function

@@PROCID

Metadata Function

APP_NAME

Metadata Function

APPLOCK_MODE

Metadata Function

APPLOCK_TEST

Функция метаданных

ASSEMBLYPROPERTY

Metadata Function

COL_LENGTH

Metadata Function

COL_NAME

Metadata Function

COLUMNPROPERTY

Metadata Function

DATABASE_PRINCIPAL_ID

Функция метаданных

DATABASEPROPERTYEX

Metadata Function

DB_ID

Metadata Function

DB_NAME

Metadata Function

FILE_ID

Metadata Function

FILE_IDEX

Функция метаданных

FILE_NAME

Metadata Function

FILEGROUP_ID

Metadata Function

FILEGROUP_NAME

Metadata Function

FILEGROUPPROPERTY

Metadata Function

FILEPROPERTY

Функция метаданных

FULLTEXTCATALOGPROPERTY

Metadata Function

FULLTEXTSERVICEPROPERTY

Metadata Function

INDEX_COL

Metadata Function

INDEXKEY_PROPERTY

Metadata Function

ИНДЕКСПРОПЕРТИ

Metadata Function

NEXT VALUE FOR

Metadata Function

OBJECT_DEFINITION

Metadata Function

OBJECT_ID

Metadata Function

OBJECT_NAME

Функция метаданных

OBJECT_SCHEMA_NAME

Metadata Function

OBJECTPROPERTY

Metadata Function

OBJECTPROPERTYEX

Metadata Function

ORIGINAL_DB_NAME

Metadata Function

PARSENAME

Разделить IP-адреса с помощью функции ParseName T-SQL

Metadata Function

SCHEMA_ID

Metadata Function

SCHEMA_NAME

Metadata Function

SCOPE_IDENTITY

Metadata Function

SERVERPROPERTY

Функция метаданных

STATS_DATE

Metadata Function

TYPE_ID

Metadata Function

TYPE_NAME

Metadata Function

TYPEPROPERTY

Metadata Function

VERSION

ВЕРСИЯ

Функция ранжирования

DENSE_RANK

Функция ранжирования

NTILE

Функция ранжирования

Рейтинг

Рейнг

Функция рейтинга

ROW_NUMBE

ROW_NUMBER

SQL PRENTUCTION).

Пейджинг SQL с использованием функции ROW_NUMBER() SQL Server

Функция репликации

PUBLISHINGSERVERNAME

Rowset Function

OPENDATASOURCE

Rowset Function

OPENJSON

Query JSON Data using OpenJSON on SQL Server

Rowset Function

OPENQUERY

Select from Stored Procedure с использованием функции SQL OpenQuery

Функция набора строк

OPENROWSET

Функция Rowset

Openxml

OpenXML

Import XML в SQL Server

Функция безопасности

Certencoded

Security Function

Security Function 9000

CURRENT_USER

Функция безопасности

DATABASE_PRINCIPAL_ID

Security Function

HAS_PERMS_BY_NAME

Security Function

IS_MEMBER

Security Function

IS_ROLEMEMBER

Security Function

IS_SRVROLEMEMBER

Функция безопасности

LOGINPROPERTY

Security Function

ORIGINAL_LOGIN

Security Function

PERMISSIONS

Security Function

PWDCOMPARE

Функция безопасности

PWDENCRYPT

Функция безопасности

SCHEMA_ID

Security Function

SCHEMA_NAME

Security Function

SESSION_USER

Security Function

SUSER_ID

Функция безопасности

SUSER_NAME

Функция безопасности

SUSER_SID

Security Function

SUSER_SNAME

Security Function

sys.

fn_builtin_permissions

sys.fn_builtin_permissions

Security Function

sys.fn_get_audit_file

sys .fn_get_audit_file

Функция безопасности

sys.fn_my_permissions

Security Function

SYSTEM_USER

Security Function

USER_ID

Security Function

USER_NAME

String Function

ASCII

Строковая функция

CHAR

Строковая функция

CHARINDEX

String Function

CONCAT

SQL Concat String Function

String Function

CONCAT_WS

String Function

DIFFERENCE

Строковая функция

ФОРМАТ

Строковая функция

LEFT

String Function

LEN

String Function

LOWER

String Function

LTRIM

Строковая функция

NCHAR

Строковая функция

PATINDEX

String Function

QUOTENAME

String Function

REPLACE

String Function

REPLICATE

SQL Pad Leading Zeros

String Function

REVERSE

ОБРАТНЫЙ

Строковая функция

ПРАВАЯ

Строковая функция

RTRIM

String Function

SOUNDEX

String Function

SPACE

String Function

STR

Строковая функция

STRING_AGG

Строковая функция

STRING_ESCAPE

String Function

STRING_SPLIT

String Function

STUFF

String Function

SUBSTRING

String Function

TRANSLATE

ПЕРЕВОД

Строковая функция

TRIM

Строковая функция

UNICODE

String Function

UPPER

System Function

$PARTITION

System Function

@@ERROR

@@ ОШИБКА

Системная функция

@@IDENTITY

Системная функция

@@PACK_RECEIVED

0004 @@PACK_RECEIVED

9000

System Function

@@ROWCOUNT

System Function

@@TRANCOUNT

System Function

BINARY_CHECKSUM

Системная функция

КОНТРОЛЬНАЯ СУММА

Системная функция

СЖАТИЕ

COMPRESS

System Function

CONNECTIONPROPERTY

System Function

CONTEXT_INFO

System Function

CURRENT_REQUEST_ID

System Function

CURRENT_TRANSACTION_ID

Системная функция

DECOMPRESS

System Function

ERROR_LINE

System Function

ERROR_MESSAGE

System Function

ERROR_NUMBER

Системная функция

ERROR_PROCEDURE

Системная функция

ERROR_SEVERITY

System Function

ERROR_STATE

System Function

FORMATMESSAGE

System Function

GET_FILESTREAM_TRANSACTION_CONTEXT

Системная функция

GETANSINULL

System Function

HOST_ID

System Function

HOST_NAME

System Function

ISNULL

System Function

ISNUMERIC

Системная функция

MIN_ACTIVE_ROWVERSION

System Function

NEWID

Use SQL NEWID in SQL Functions as SQL Random Generator

System Function

NEWSEQUENTIALID

System Function

ROWCOUNT_BIG

Системная функция

SESSION_CONTEXT

Системная функция

session_id

Session_id

Системная функция

XACT_STATE

СТАТИСТИЧЕСКАЯ ФУНКЦИЯ

@@ Connections