Порядок выполнения SQL-операций
Мы привыкли, что компьютер выполняет команды программиста последовательно, в том порядке, который указал автор кода. Однако SQL относится к декларативным языкам, то есть SQL-запрос описывает ожидаемый результат, а не способ его получения.
Давайте разберём, в какой последовательности выполняются шесть операций в SQL: SELECT, FROM, WHERE, GROUP BY, HAVING и ORDER BY.
База данных выполняет команды в строгой очерёдности, о которой полезно знать любому разработчику. Залог оптимального запроса тот же, что и залог успеха в приготовлении вкусного блюда: важно знать не только ингредиенты, но и когда каждый из них должен попасть в блюдо. Если база данных отойдет от стандартного сценария выполнения команд, то ее производительность может сильно пострадать.
База данных сотрудников
В этой статье мы поработаем с типичной базой сотрудников, относящихся к разным отделам. По каждому сотруднику известны его ID, имя, фамилия, зарплата и отдел:
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | DEPARTMENT |
|---|---|---|---|---|
| 100 | James | Smith | 78,000 | ACCOUNTING |
| 101 | Mary | Sexton | 82,000 | IT |
| 102 | Chun | Yen | 80,500 | ACCOUNTING |
| 103 | Agnes | Miller | 95,000 | IT |
| 104 | Dmitry | Komer | 120,000 | SALES |
EMPLOYEE
Порядок выполнения операторов sql запроса
Пожалуйста, дайте порядок выполнения операторов sql запроса или дайте ссылку, где прочитать об этом: что за чем выполняется. Например (с потолка):
SELECT t1.id AS id, t1.name AS name, count(*) AS cnt FROM tbl_name1 t1 INNER JOIN tbl_name2 t2 ON t1.id = t2.id WHERE t1.id > 10 GROUP BY t1.name HAVING cnt > 10 ORDER BY t1.id Сначала GROUP BY , потом? Последним ORDER BY . Нигде не могу найти нормальной информации об этом :((
Отслеживать
user177221
задан 13 авг 2011 в 6:19
466 1 1 золотой знак 4 4 серебряных знака 10 10 бронзовых знаков
4 ответа 4
Сортировка: Сброс на вариант по умолчанию
Для SQL Server смотри ссылку.
Логический порядок обработки инструкции SELECT:
- FROM
- ON
- JOIN
- where
- GROUP BY
- WITH CUBE или WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
Оператор SELECT в MySQL 8: полный обзор с примерами кода
Оператор SELECT используется для извлечения данных из одной или многочисленных таблиц:
-
- Оператор WHERE
- Предложение ORDER BY
- Предложение LIMIT
- Операции соединения SQL
- UNION
- Оптимизация запросов SELECT
- Вас заинтересует / Intresting for you:
SELECT поле1, поле2, поле3 from имя_таблицы [WHERE предложение] [GROUP BY ] [HAVING условие_where] [ORDER BY , . ] [LIMIT]Это универсальная синтаксическая конструкция, используемая для получения данных из одной таблицы:
- поля поле1 и поле2 являются именами столбцов таблицы. Для извлечения всех столбцов из таблицы можно использовать выражение * ;
- имя_таблицы задает имя таблицы, из которой необходимо извлечь данные;
- оператор WHERE может использоваться для задания любого условия в одном и нескольких столбцах;
- функция GROUP BY используется с агрегатными функциями для группирования результирующих наборов;
- предложение HAVING необходимо после GROUP BY для фильтрации на основе условий для группы строк или агрегатов. Если мы используем предложение HAVING без GROUP BY , оно будет действовать аналогично оператору WHERE ;
- предложение ORDER BY используется для сортировки результирующих наборов таблицы по возрастанию или убыванию;
- LIMIT используется для ограничения количества строк, возвращаемых оператором SELECT .
Давайте рассмотрим каждый элемент оператора.
Оператор WHERE
Следующий ниже фрагмент кода содержит универсальную синтаксическую конструкцию для запроса SELECT с оператором WHERE :
SELECT поле1, поле2, поле3, . поле_N from имя_таблицы1, имя_таблицы2 [WHERE условие1 [AND [OR]] условие2.Оператор WHERE является необязательной частью запроса SELECT . Для указания условий можно использовать операторы AND или OR . Оператор WHERE также может использоваться с запросом DELETE и UPDATE , который мы обсудим в ближайшее время в последующей статье.
Ниже приведен список операторов, используемых с оператором WHERE для задания условий. Чтобы понять эти операции, давайте рассмотрим пример схемы таблицы. Мы создадим таблицу users с упомянутой здесь схемой, имеющей поля id, first_name, last_name, address, city, state, zip, login_attempts, contact_number, email, username и password:
CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(255), `last_name` varchar(255), `address` varchar(255), `city` varchar(50), `state` varchar(2), `zip` varchar(10), `login_attempts` int(10), `contact_number` varchar(20), `email` varchar(191), `username` varchar(191), `password` varchar(255), PRIMARY KEY (`id`) ) ENGINE=InnoDB PRIMARY KEY (`id`) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Равно и не равно
Оператор равенства ( = ) проверяет, равны ли значения двух полей. Если они совпадают, то условие становится истинным, и оператор извлекает значение для дальнейшей обработки. Если они не совпадают, то условие должно содержать оператор неравенства ( <> ). Оно будет извлекать данные на основе условия, которое не совпадает.
Например, следующий ниже запрос используется для получения всех записей с городом, соответствующим значению New York:
SELECT * FROM 'users' WHERE 'city' = 'New York';Больше и меньше
Оператор больше, чем ( > ) проверяет, больше ли значение левого поля, чем значение правого поля. Если да, то условие становится истинным. Оператор меньше, чем ( < ) проверяет, меньше ли значение левого поля, чем значение правого поля. Мы также можем использовать операторы >/ < и оператор равенства вместе.
Например, следующий ниже запрос используется для получения всех записей с количеством попыток входа в систему больше, чем 2:
SELECT * FROM 'users' WHERE 'login_attempts' > 2;Оператор LIKE предоставляет простой способ поиска записей в столбце с различными шаблонами. В запросе можно использовать подстановочные символы для построения различных шаблонов. В основном используется два вида подстановочных символов. Давайте рассмотрим каждый из них на примере.
-
% (процент): используйте этот подстановочный символ для поиска ноль или более любых символов. Предположим, что мы хотим отыскать пользователей, чье имя начинается с «a». Тогда мы можем применить этот подстановочный символ, как показано в приведенном ниже запросе.
select * from users where username like 'a%';В случае если мы хотим найти пользователей, чье имя начинается с «a» и заканчивается на «s», то запрос с подстановочным символом % будет таким:
select * from user where username like 'a%s';select * from users where username like '_dmin';IN/NOT IN
Оператор IN используется для сравнения нескольких значений в операторе WHERE . Например, следующий ниже запрос используется для поиска всех пользователей, имеющих город new york или chicago:
select * from users where city IN ('new york','chicago')Оператор NOT IN работает наоборот, например чтобы найти всех пользователей, у которых нет ни города new york, ни города chicago, используется:
select * from users where city NOT IN ('new york','chicago');BETWEEN
Оператор BETWEEN может использоваться в том случае, когда мы хотим получить записи, которые входят в определенный диапазон. Этот диапазон может быть любым, таким как текст, даты или цифры. Предположим, мы хотим отыскать пользователей, дата создания записи о которых находится между 1 июля и 16 июля 2017 года. Тогда приведенный ниже запрос с предложением BETWEEN может нам помочь.
select * from users where created_at BETWEEN '2017-07-01 00:00:00' AND '2017-07-16 00:00:00';Подобным образом мы можем также задавать диапазон в числах для поиска пользователей, которые принадлежат к этому конкретному диапазону. Например, если мы хотим получить студентов, чьи оценки находятся в диапазоне от 70 до 80, то оператор BETWEEN может быть применен для определения диапазона.
Предложение ORDER BY
Предложение ORDER BY помогает в получении записей в упорядоченном виде. Оно обеспечивает сортировку данных по определенному столбцу в порядке возрастания или убывания. По умолчанию сортировка выполняется в порядке возрастания, но мы также можем явно указать способ сортировки с помощью ключевых слов ASC и DESC . Если мы зададим ASC , то оно будет сортировать в порядке возрастания, в то время как ключевое слово DESC будет сортировать в порядке убывания. Ниже приведен запрос, который найдет всех пользователей и выведет их в порядке возрастания по столбцу city.
SELECT * FROM users ORDER BY city ASC;Кроме того, с помощью предложения ORDER BY можно сортировать в нескольких столбцах. Как показано в приведенном ниже запросе, где мы упорядочиваем по столбцам city и username, мы можем передать несколько столбцов.
SELECT * FROM users ORDER BY city, username;Предложение LIMIT
Используя предложение LIMIT , мы можем получить только некоторое количество строк из больших блоков данных. Это помогает ограничить количество строк, возвращаемых в результирующем наборе. Предположим, что в таблице существуют тысячи строк, но нам требуется только 10 записей, тогда вместо извлечения тысяч записей это предложение помогает получить лишь 10 записей. Это действительно помогает настраивать производительность при поиске больших наборов данных.
С предложением LIMIT можно передавать один или два аргумента. В случае двух аргументов один из них будет смещением, которое задает сдвиг первой возвращаемой строки от начала. В то время как второй аргумент будет количеством, которое задает максимальное количество строк, которые будут возвращены. Этот аргумент должен быть нулевым или положительным. Взгляните на приведенный ниже запрос, где мы извлекли 10 записей из таблицы пользователей, начиная с 5-й строки.
SELECT * FROM users limit 5, 10;Если с предложением LIMIT указать всего один аргумент, то аргумент будет считаться количеством строк. Например, следующий ниже запрос используется для извлечения 10 строк из таблицы пользователей:
SELECT * FROM users limit 10;На данный момент мы видели получение данных из одной таблицы; если мы хотим получить данные из нескольких таблиц, используются ключевые слова JOIN и UNION .
Операции соединения SQL
Операция соединения используется для извлечения данных из нескольких таблиц. Например, если есть две таблицы order и customer и мы хотим получить данные, то это можно сделать с помощью предложения JOIN .
Различные типы предложения JOIN следующие:
- INNER JOIN : внутреннее соединение возвращает только те записи, значения которых совпадают в обеих таблицах;
- CROSS JOIN : перекрестное соединение возвращает только те записи, которые имеют совпадающие значения в левой или правой таблице;
- LEFT JOIN : левое соединение возвращает все записи из левой таблицы и только совпадающие записи из правой таблицы;
- RIGHT JOIN : правое соединение возвращает все записи из правой таблицы и только совпадающие записи из левой таблицы.
На следующем ниже рисунке показан краткий пример для рассмотрения:
INNER JOIN
Внутреннее соединение возвращает записи, совпадающие в обеих таблицах. Например, ниже приведена таблица заказов order, используемая в приложениях электронной коммерции:
order_id
customer_id
order_amount
order_date
ship_id
Какая конструкция команды select c name from cities c выполняется первой
Выражения значения применяются в самых разных контекстах, например в списке результатов команды SELECT , в значениях столбцов в INSERT или UPDATE или в условиях поиска во многих командах. Результат такого выражения иногда называют скаляром, чтобы отличить его от результата табличного выражения (который представляет собой таблицу). А сами выражения значения часто называют скалярными (или просто выражениями). Синтаксис таких выражений позволяет вычислять значения из примитивных частей, используя арифметические, логические и другие операции.
Выражениями значения являются:
Константа или непосредственное значение
Ссылка на столбец
Ссылка на позиционный параметр в теле определения функции или подготовленного оператора
Выражение с индексом
Выражение выбора поля
Вызов оконной функции
Применение правил сортировки
Конструктор табличной строки
В дополнение к этому списку есть ещё несколько конструкций, которые можно классифицировать как выражения, хотя они не соответствуют общим синтаксическим правилам. Они обычно имеют вид функции или оператора и будут рассмотрены в соответствующем разделе Главы 9. Пример такой конструкции — предложение IS NULL .
Мы уже обсудили константы в Подразделе 4.1.2. В следующих разделах рассматриваются остальные варианты.
4.2.1. Ссылки на столбцы #
Ссылку на столбец можно записать в форме:
отношение.имя_столбцаЗдесь отношение — имя таблицы (возможно, полное, с именем схемы) или её псевдоним, определённый в предложении FROM . Это имя и разделяющую точку можно опустить, если имя столбца уникально среди всех таблиц, задействованных в текущем запросе. (См. также Главу 7.)
4.2.2. Позиционные параметры #
Ссылка на позиционный параметр применяется для обращения к значению, переданному в SQL-оператор извне. Параметры используются в определениях SQL-функций и подготовленных операторов. Некоторые клиентские библиотеки также поддерживают передачу значений данных отдельно от самой SQL-команды, и в этом случае параметры позволяют ссылаться на такие значения. Ссылка на параметр записывается в следующей форме:
$
числоНапример, рассмотрим следующее определение функции dept :
CREATE FUNCTION dept(text) RETURNS dept AS $$ SELECT * FROM dept WHERE name = $1 $$ LANGUAGE SQL;
Здесь $1 всегда будет ссылаться на значение первого аргумента функции.
4.2.3. Индексы элементов #
Если в выражении вы имеете дело с массивом, то можно извлечь определённый его элемент, написав:
выражение[индекс]или несколько соседних элементов ( « срез массива » ):
выражение[нижний_индекс:верхний_индекс](Здесь квадратные скобки [ ] должны присутствовать буквально.) Каждый индекс сам по себе является выражением, результат которого округляется к ближайшему целому.
В общем случае выражение массива должно заключаться в круглые скобки, но их можно опустить, когда выражение с индексом — это просто ссылка на столбец или позиционный параметр. Кроме того, можно соединить несколько индексов, если исходный массив многомерный. Например:
моя_таблица.столбец_массив[4] моя_таблица.столбец_массив_2d[17][34] $1[10:42] (функция_массив(a,b))[42]
В последней строке круглые скобки необходимы. Подробнее массивы рассматриваются в Разделе 8.15.
4.2.4. Выбор поля #
Если результат выражения — значение составного типа (строка таблицы), тогда определённое поле этой строки можно извлечь, написав:
выражение.имя_поляВ общем случае выражение такого типа должно заключаться в круглые скобки, но их можно опустить, когда это ссылка на таблицу или позиционный параметр. Например:
моя_таблица.столбец $1.столбец (функция_кортеж(a,b)).стол3
(Таким образом, полная ссылка на столбец — это просто частный случай выбора поля.) Важный особый случай здесь — извлечение поля из столбца составного типа:
(составной_столбец).поле (моя_таблица.составной_столбец).поле
Здесь скобки нужны, чтобы показать, что составной_столбец — это имя столбца, а не таблицы, и что моя_таблица — имя таблицы, а не схемы.
Вы можете запросить все поля составного значения, написав .* :
(составной_столбец).*
Эта запись действует по-разному в зависимости от контекста; подробнее об этом говорится в Подразделе 8.16.5.
4.2.5. Применение оператора #
Существуют два возможных синтаксиса применения операторов:
выражение оператор выражение (бинарный инфиксный оператор) оператор выражение (унарный префиксный оператор) где оператор соответствует синтаксическим правилам, описанным в Подразделе 4.1.3, либо это одно из ключевых слов AND , OR и NOT , либо полное имя оператора в форме:
OPERATOR(схема.имя_оператора)Существование конкретных операторов и их тип (унарный или бинарный) зависит от того, как и какие операторы определены системой и пользователем. Встроенные операторы описаны в Главе 9.
4.2.6. Вызовы функций #
Вызов функции записывается просто как имя функции (возможно, дополненное именем схемы) и список аргументов в скобках:
имя_функции([выражение[,выражение. ]])Например, так вычисляется квадратный корень из 2:
sqrt(2)
Список встроенных функций приведён в Главе 9. Пользователь также может определить и другие функции.
Выполняя запросы в базе данных, где одни пользователи могут не доверять другим, в записи вызовов функций соблюдайте меры предосторожности, описанные в Разделе 10.3.
Аргументам могут быть присвоены необязательные имена. Подробнее об этом см. Раздел 4.3.
Примечание
Функцию, принимающую один аргумент составного типа, можно также вызывать, используя синтаксис выбора поля, и наоборот, выбор поля можно записать в функциональном стиле. То есть записи col(table) и table.col равносильны и взаимозаменяемы. Это поведение не оговорено стандартом SQL, но реализовано в PostgreSQL , так как это позволяет использовать функции для эмуляции « вычисляемых полей » . Подробнее это описано в Подразделе 8.16.5.
4.2.7. Агрегатные выражения #
Агрегатное выражение представляет собой применение агрегатной функции к строкам, выбранным запросом. Агрегатная функция сводит множество входных значений к одному выходному, как например, сумма или среднее. Агрегатное выражение может записываться следующим образом:
агрегатная_функция(выражение[ , . ] [предложение_order_by] ) [ FILTER ( WHEREусловие_фильтра) ]агрегатная_функция(ALLвыражение[ , . ] [предложение_order_by] ) [ FILTER ( WHEREусловие_фильтра) ]агрегатная_функция(DISTINCTвыражение[ , . ] [предложение_order_by] ) [ FILTER ( WHEREусловие_фильтра) ]агрегатная_функция( * ) [ FILTER ( WHEREусловие_фильтра) ]агрегатная_функция( [выражение[ , . ] ] ) WITHIN GROUP (предложение_order_by) [ FILTER ( WHEREусловие_фильтра) ]Здесь агрегатная_функция — имя ранее определённой агрегатной функции (возможно, дополненное именем схемы), выражение — любое выражение значения, не содержащее в себе агрегатного выражения или вызова оконной функции. Необязательные предложения предложение_order_by и условие_фильтра описываются ниже.
В первой форме агрегатного выражения агрегатная функция вызывается для каждой строки. Вторая форма эквивалентна первой, так как указание ALL подразумевается по умолчанию. В третьей форме агрегатная функция вызывается для всех различных значений выражения (или набора различных значений, для нескольких выражений), выделенных во входных данных. В четвёртой форме агрегатная функция вызывается для каждой строки, так как никакого конкретного значения не указано (обычно это имеет смысл только для функции count(*) ). В последней форме используются сортирующие агрегатные функции, которые будут описаны ниже.
Большинство агрегатных функций игнорируют значения NULL, так что строки, для которых выражения выдают одно или несколько значений NULL, отбрасываются. Это можно считать истинным для всех встроенных операторов, если явно не говорится об обратном.
Например, count(*) подсчитает общее количество строк, а count(f1) только количество строк, в которых f1 не NULL (так как count игнорирует NULL), а count(distinct f1) подсчитает число различных и отличных от NULL значений столбца f1 .
Обычно строки данных передаются агрегатной функции в неопределённом порядке и во многих случаях это не имеет значения, например функция min выдаёт один и тот же результат независимо от порядка поступающих данных. Однако некоторые агрегатные функции (такие как array_agg и string_agg ) выдают результаты, зависящие от порядка данных. Для таких агрегатных функций можно добавить предложение_order_by и задать нужный порядок. Это предложение_order_by имеет тот же синтаксис, что и предложение ORDER BY на уровне запроса, как описано в Разделе 7.5, за исключением того, что его выражения должны быть просто выражениями, а не именами результирующих столбцов или числами. Например:
SELECT array_agg(a ORDER BY b DESC) FROM table;
Заметьте, что при использовании агрегатных функций с несколькими аргументами, предложение ORDER BY идёт после всех аргументов. Например, надо писать так:
SELECT string_agg(a, ',' ORDER BY a) FROM table;
SELECT string_agg(a ORDER BY a, ',') FROM table; -- неправильно
Последний вариант синтаксически допустим, но он представляет собой вызов агрегатной функции одного аргумента с двумя ключами ORDER BY (при этом второй не имеет смысла, так как это константа).
Если предложение_order_by дополнено указанием DISTINCT , тогда все выражения ORDER BY должны соответствовать обычным аргументам агрегатной функции; то есть вы не можете сортировать строки по выражению, не включённому в список DISTINCT .
Примечание
Возможность указывать и DISTINCT , и ORDER BY в агрегатной функции — это расширение PostgreSQL .
При добавлении ORDER BY в обычный список аргументов агрегатной функции, описанном до этого, выполняется сортировка входных строк для универсальных и статистических агрегатных функций, для которых сортировка необязательна. Но есть подмножество агрегатных функций, сортирующие агрегатные функции, для которых предложение_order является обязательным , обычно потому, что вычисление этой функции имеет смысл только при определённой сортировке входных строк. Типичными примерами сортирующих агрегатных функций являются вычисления ранга и процентиля. Для сортирующей агрегатной функции предложение_order_by записывается внутри WITHIN GROUP (. ) , что иллюстрирует последний пример, приведённый выше. Выражения в предложении_order_by вычисляются однократно для каждой входной строки как аргументы обычной агрегатной функции, сортируются в соответствии с требованием предложения_order_by и поступают в агрегатную функции как входящие аргументы. (Если же предложение_order_by находится не в WITHIN GROUP , оно не передаётся как аргумент(ы) агрегатной функции.) Выражения-аргументы, предшествующие WITHIN GROUP , (если они есть), называются непосредственными аргументами, а выражения, указанные в предложении_order_by — агрегируемыми аргументами. В отличие от аргументов обычной агрегатной функции, непосредственные аргументы вычисляются однократно для каждого вызова функции, а не для каждой строки. Это значит, что они могут содержать переменные, только если эти переменные сгруппированы в GROUP BY ; это суть то же ограничение, что действовало бы, будь эти непосредственные аргументы вне агрегатного выражения. Непосредственные аргументы обычно используются, например, для указания значения процентиля, которое имеет смысл, только если это конкретное число для всего расчёта агрегатной функции. Список непосредственных аргументов может быть пуст; в этом случае запишите просто () , но не (*) . (На самом деле PostgreSQL примет обе записи, но только первая соответствует стандарту SQL.)
Пример вызова сортирующей агрегатной функции:
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households; percentile_cont ----------------- 50489
она получает 50-ый процентиль, или медиану, значения столбца income из таблицы households . В данном случае 0.5 — это непосредственный аргумент; если бы дробь процентиля менялась от строки к строке, это не имело бы смысла.
Если добавлено предложение FILTER , агрегатной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Например:
SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i); unfiltered | filtered ------------+---------- 10 | 4 (1 row)
Предопределённые агрегатные функции описаны в Разделе 9.21. Пользователь также может определить другие агрегатные функции.
Агрегатное выражение может фигурировать только в списке результатов или в предложении HAVING команды SELECT . Во всех остальных предложениях, например WHERE , они запрещены, так как эти предложения логически вычисляются до того, как формируются результаты агрегатных функций.
Когда агрегатное выражение используется в подзапросе (см. Подраздел 4.2.11 и Раздел 9.23), оно обычно вычисляется для всех строк подзапроса. Но если в аргументах (или в условии_filter ) агрегатной функции есть только переменные внешнего уровня, агрегатная функция относится к ближайшему внешнему уровню и вычисляется для всех строк соответствующего запроса. Такое агрегатное выражение в целом является внешней ссылкой для своего подзапроса и на каждом вычислении считается константой. При этом допустимое положение агрегатной функции ограничивается списком результатов и предложением HAVING на том уровне запросов, где она находится.
4.2.8. Вызовы оконных функций #
Вызов оконной функции представляет собой применение функции, подобной агрегатной, к некоторому набору строк, выбранному запросом. В отличие от вызовов не оконных агрегатных функций, при этом не происходит группировка выбранных строк в одну — каждая строка остаётся отдельной в результате запроса. Однако оконная функция имеет доступ ко всем строкам, вошедшим в группу текущей строки согласно указанию группировки (списку PARTITION BY ) в вызове оконной функции. Вызов оконной функции может иметь следующие формы:
имя_функции([выражение[,выражение. ]]) [ FILTER ( WHEREпредложение_фильтра) ] OVERимя_окнаимя_функции([выражение[,выражение. ]]) [ FILTER ( WHEREпредложение_фильтра) ] OVER (определение_окна)имя_функции( * ) [ FILTER ( WHEREпредложение_фильтра) ] OVERимя_окнаимя_функции( * ) [ FILTER ( WHEREпредложение_фильтра) ] OVER (определение_окна)Здесь определение_окна записывается в виде
[
имя_существующего_окна] [ PARTITION BYвыражение[, . ] ] [ ORDER BYвыражение[ ASC | DESC | USINGоператор] [ NULLS < FIRST | LAST >] [, . ] ] [определение_рамки]Необязательное определение_рамки может иметь вид:
< RANGE | ROWS | GROUPS >
начало_рамки[исключение_рамки] < RANGE | ROWS | GROUPS >BETWEENначало_рамкиANDконец_рамки[исключение_рамки]Здесь начало_рамки и конец_рамки задаются одним из следующих способов:
UNBOUNDED PRECEDING
смещениеPRECEDING CURRENT ROWсмещениеFOLLOWING UNBOUNDED FOLLOWINGи исключение_рамки может быть следующим:
EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS
Здесь выражение — это любое выражение значения, не содержащее вызовов оконных функций.
имя_окна — ссылка на именованное окно, определённое предложением WINDOW в данном запросе. Также возможно написать в скобках полное определение_окна , используя тот же синтаксис определения именованного окна в предложении WINDOW ; подробнее это описано в справке по SELECT . Стоит отметить, что запись OVER имя_окна не полностью равнозначна OVER (имя_окна . ) ; последний вариант подразумевает копирование и изменение определения окна и не будет допустимым, если определение этого окна включает определение рамки.
Указание PARTITION BY группирует строки запроса в разделы, которые затем обрабатываются оконной функцией независимо друг от друга. PARTITION BY работает подобно предложению GROUP BY на уровне запроса, за исключением того, что его аргументы всегда просто выражения, а не имена выходных столбцов или числа. Без PARTITION BY все строки, выдаваемые запросом, рассматриваются как один раздел. Указание ORDER BY определяет порядок, в котором оконная функция обрабатывает строки раздела. Оно так же подобно предложению ORDER BY на уровне запроса и так же не принимает имена выходных столбцов или числа. Без ORDER BY строки обрабатываются в неопределённом порядке.
определение_рамки задаёт набор строк, образующих рамку окна, которая представляет собой подмножество строк текущего раздела и используется для оконных функций, работающих с рамкой, а не со всем разделом. Подмножество строк в рамке может меняться в зависимости от того, какая строка является текущей. Рамку можно задать в режимах RANGE , ROWS или GROUPS ; в каждом случае она начинается с положения начало_рамки и заканчивается положением конец_рамки . Если конец_рамки не задаётся явно, подразумевается CURRENT ROW (текущая строка).
Если начало_рамки задано как UNBOUNDED PRECEDING , рамка начинается с первой строки раздела, а если конец_рамки определён как UNBOUNDED FOLLOWING , рамка заканчивается последней строкой раздела.
В режиме RANGE или GROUPS начало_рамки , заданное как CURRENT ROW , определяет в качестве начала первую родственную строку (строку, которая при сортировке согласно указанному для окна предложению ORDER BY считается равной текущей), тогда как конец_рамки , заданный как CURRENT ROW , определяет концом рамки последнюю родственную строку. В режиме ROWS вариант CURRENT ROW просто обозначает текущую строку.
В вариантах определения рамки смещение PRECEDING и смещение FOLLOWING в качестве смещения должно задаваться выражение, не содержащее какие-либо переменные и вызовы агрегатных или оконных функций. Что именно будет означать смещение , определяется в зависимости от режима рамки:
В режиме ROWS смещение должно задаваться отличным от NULL неотрицательным целым числом, и это число определяет сдвиг, с которым начало рамки позиционируется перед текущей строкой, а конец — после текущей строки.
В режиме GROUPS смещение также должно задаваться отличным от NULL неотрицательным целым числом, и это число определяет сдвиг (по количеству групп родственных строк), с которым начало рамки позиционируется перед группой строк, родственных текущей, а конец — после этой группы. Группу родственных строк образуют строки, которые считаются равными согласно ORDER BY . (Для использования режима GROUPS определение окна должно содержать предложение ORDER BY .)
В любом случае расстояние до конца рамки ограничивается расстоянием до конца раздела, так что для строк, которые находятся у конца раздела, рамка может содержать меньше строк, чем для других.
Заметьте, что в режимах ROWS и GROUPS указания 0 PRECEDING и 0 FOLLOWING равнозначны указанию CURRENT ROW . Обычно это справедливо и для режима RANGE , в случае подходящего для типа данных определения значения « нуля » .
Дополнение исключение_рамки позволяет исключить из рамки строки, которые окружают текущую строку, даже если они должны быть включены согласно указаниям, определяющим начало и конец рамки. EXCLUDE CURRENT ROW исключает из рамки текущую строку. EXCLUDE GROUP исключает из рамки текущую строку и родственные ей согласно порядку сортировки. EXCLUDE TIES исключает из рамки все родственные строки для текущей, но не собственно текущую строку. EXCLUDE NO OTHERS просто явно выражает поведение по умолчанию — не исключает ни текущую строку, ни родственные ей.
По умолчанию рамка определяется как RANGE UNBOUNDED PRECEDING , что равносильно расширенному определению RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . С указанием ORDER BY это означает, что рамка будет включать все строки от начала раздела до последней строки, родственной текущей (для ORDER BY ). Без ORDER BY это означает, что в рамку включаются все строки раздела, так как все они считаются родственными текущей.
Действуют также следующие ограничения: в качестве начала_рамки нельзя задать UNBOUNDED FOLLOWING , в качестве конца_рамки не допускается UNBOUNDED PRECEDING и конец_рамки не может идти в показанном выше списке указаний начало_рамки AND конец_рамки перед началом_рамки . В частности, синтаксис RANGE BETWEEN CURRENT ROW AND смещение PRECEDING не допускается. Но при этом, например, определение ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING допустимо, хотя оно и не выберет никакие строки.
Если добавлено предложение FILTER , оконной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Предложение FILTER допускается только для агрегирующих оконных функций.
Встроенные оконные функции описаны в Таблице 9.64, но пользователь может расширить этот набор, создавая собственные функции. Кроме того, в качестве оконных функций можно использовать любые встроенные или пользовательские универсальные, а также статистические агрегатные функции. (Сортирующие и гипотезирующие агрегатные функции в настоящее время использовать в качестве оконных нельзя.)
Запись со звёздочкой ( * ) применяется при вызове не имеющих параметров агрегатных функций в качестве оконных, например count(*) OVER (PARTITION BY x ORDER BY y) . Звёздочка ( * ) обычно не применяется для исключительно оконных функций. Такие функции не допускают использования DISTINCT и ORDER BY в списке аргументов функции.
Вызовы оконных функций разрешены в запросах только в списке SELECT и в предложении ORDER BY .
Дополнительно об оконных функциях можно узнать в Разделе 3.5, Разделе 9.22 и Подразделе 7.2.5.
4.2.9. Приведения типов #
Приведение типа определяет преобразование данных из одного типа в другой. PostgreSQL воспринимает две равносильные записи приведения типов:
CAST (
выражениеASтип)выражение::типЗапись с CAST соответствует стандарту SQL, тогда как вариант с :: — историческое наследие PostgreSQL .
Когда приведению подвергается значение выражения известного типа, происходит преобразование типа во время выполнения. Это приведение будет успешным, только если определён подходящий оператор преобразования типов. Обратите внимание на небольшое отличие от приведения констант, описанного в Подразделе 4.1.2.7. Приведение строки в чистом виде представляет собой начальное присваивание строковой константы и оно будет успешным для любого типа (конечно, если строка содержит значение, приемлемое для данного типа данных).
Явное приведение типа можно опустить, если возможно однозначно определить, какой тип должно иметь выражение (например, когда оно присваивается столбцу таблицы); в таких случаях система автоматически преобразует тип. Однако автоматическое преобразование выполняется только для приведений с пометкой « допускается неявное применение » в системных каталогах. Все остальные приведения должны записываться явно. Это ограничение позволяет избежать сюрпризов с неявным преобразованием.
Также можно записать приведение типа как вызов функции:
имя_типа(выражение)Однако это будет работать только для типов, имена которых являются также допустимыми именами функций. Например, double precision так использовать нельзя, а float8 (альтернативное название того же типа) — можно. Кроме того, имена типов interval , time и timestamp из-за синтаксического конфликта можно использовать в такой записи только в кавычках. Таким образом, запись приведения типа в виде вызова функции провоцирует несоответствия и, возможно, лучше будет её не применять.
Примечание
Приведение типа, представленное в виде вызова функции, на самом деле соответствует внутреннему механизму. Даже при использовании двух стандартных типов записи внутри происходит вызов зарегистрированной функции, выполняющей преобразование. По соглашению именем такой функции преобразования является имя выходного типа, и таким образом запись « в виде вызова функции » есть не что иное, как прямой вызов нижележащей функции преобразования. При создании переносимого приложения на это поведение, конечно, не следует рассчитывать. Подробнее это описано в справке CREATE CAST .
4.2.10. Применение правил сортировки #
Предложение COLLATE переопределяет правило сортировки выражения. Оно добавляется после выражения:
выражениеCOLLATEправило_сортировкигде правило_сортировки — идентификатор правила, возможно дополненный именем схемы. Предложение COLLATE связывает выражение сильнее, чем операторы, так что при необходимости следует использовать скобки.
Если правило сортировки не определено явно, система либо выбирает его по столбцам, которые используются в выражении, либо, если таких столбцов нет, переключается на установленное для базы данных правило сортировки по умолчанию.
Предложение COLLATE имеет два распространённых применения: переопределение порядка сортировки в предложении ORDER BY , например:
SELECT a, b, c FROM tbl WHERE . ORDER BY a COLLATE "C";
и переопределение правил сортировки при вызове функций или операторов, возвращающих языкозависимые результаты, например:
SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";
Заметьте, что в последнем случае предложение COLLATE добавлено к аргументу оператора, на действие которого мы хотим повлиять. При этом не имеет значения, к какому именно аргументу оператора или функции добавляется COLLATE , так как правило сортировки, применяемое к оператору или функции, выбирается при рассмотрении всех аргументов, а явное предложение COLLATE переопределяет правила сортировки для всех других аргументов. (Однако добавление разных предложений COLLATE к нескольким аргументам будет ошибкой. Подробнее об этом см. Раздел 24.2.) Таким образом, эта команда выдаст тот же результат:
SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';
Но это будет ошибкой:
SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";
здесь правило сортировки нельзя применить к результату оператора > , который имеет несравниваемый тип данных boolean .
4.2.11. Скалярные подзапросы #
Скалярный подзапрос — это обычный запрос SELECT в скобках, который возвращает ровно одну строку и один столбец. (Написание запросов освещается в Главе 7.) После выполнения запроса SELECT его единственный результат используется в окружающем его выражении. В качестве скалярного подзапроса нельзя использовать запросы, возвращающие более одной строки или столбца. (Но если в результате выполнения подзапрос не вернёт строк, скалярный результат считается равным NULL.) В подзапросе можно ссылаться на переменные из окружающего запроса; в процессе одного вычисления подзапроса они будут считаться константами. Другие выражения с подзапросами описаны в Разделе 9.23.
Например, следующий запрос находит самый населённый город в каждом штате:
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states;
4.2.12. Конструкторы массивов #
Конструктор массива — это выражение, которое создаёт массив, определяя значения его элементов. Конструктор простого массива состоит из ключевого слова ARRAY , открывающей квадратной скобки [ , списка выражений (разделённых запятыми), задающих значения элементов массива, и закрывающей квадратной скобки ] . Например:
SELECT ARRAY[1,2,3+4]; array --------- (1 row)
По умолчанию типом элементов массива считается общий тип для всех выражений, определённый по правилам, действующим и для конструкций UNION и CASE (см. Раздел 10.5). Вы можете переопределить его явно, приведя конструктор массива к требуемому типу, например:
SELECT ARRAY[1,2,22.7]::integer[]; array ---------- (1 row)
Это равносильно тому, что привести к нужному типу каждое выражение по отдельности. Подробнее приведение типов описано в Подразделе 4.2.9.
Многомерные массивы можно образовывать, вкладывая конструкторы массивов. При этом во внутренних конструкторах слово ARRAY можно опускать. Например, результат работы этих конструкторов одинаков:
SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; array --------------- ,> (1 row) SELECT ARRAY[[1,2],[3,4]]; array --------------- ,> (1 row)
Многомерные массивы должны быть прямоугольными, и поэтому внутренние конструкторы одного уровня должны создавать вложенные массивы одинаковой размерности. Любое приведение типа, применённое к внешнему конструктору ARRAY , автоматически распространяется на все внутренние.
Элементы многомерного массива можно создавать не только вложенными конструкторами ARRAY , но и другими способами, позволяющими получить массивы нужного типа. Например:
CREATE TABLE arr(f1 int[], f2 int[]); INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); SELECT ARRAY[f1, f2, ',>'::int[]] FROM arr; array ------------------------------------------------ ,>,,>,,>> (1 row)
Вы можете создать и пустой массив, но так как массив не может быть не типизированным, вы должны явно привести пустой массив к нужному типу. Например:
SELECT ARRAY[]::integer[]; array ------- <> (1 row)
Также возможно создать массив из результатов подзапроса. В этом случае конструктор массива записывается так же с ключевым словом ARRAY , за которым в круглых скобках следует подзапрос. Например:
SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); array ------------------------------------------------------------------ (1 row) SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); array ---------------------------------- ,,,,> (1 row)
Такой подзапрос должен возвращать один столбец. Если этот столбец имеет тип, отличный от массива, результирующий одномерный массив будет включать элементы для каждой строки-результата подзапроса и типом элемента будет тип столбца результата. Если же тип столбца — массив, будет создан массив того же типа, но большей размерности; в любом случае во всех строках подзапроса должны выдаваться массивы одинаковой размерности, чтобы можно было получить прямоугольный результат.
Индексы массива, созданного конструктором ARRAY , всегда начинаются с одного. Подробнее о массивах вы узнаете в Разделе 8.15.
4.2.13. Конструкторы табличных строк #
Конструктор табличной строки — это выражение, создающее строку или кортеж (или составное значение) из значений его аргументов-полей. Конструктор строки состоит из ключевого слова ROW , открывающей круглой скобки, нуля или нескольких выражений (разделённых запятыми), определяющих значения полей, и закрывающей скобки. Например:
SELECT ROW(1,2.5,'this is a test');
Если в списке более одного выражения, ключевое слово ROW можно опустить.
Конструктор строки поддерживает запись составное_значение .* , при этом данное значение будет развёрнуто в список элементов, так же, как в записи .* на верхнем уровне списка SELECT (см. Подраздел 8.16.5). Например, если таблица t содержит столбцы f1 и f2 , эти записи равнозначны:
SELECT ROW(t.*, 42) FROM t; SELECT ROW(t.f1, t.f2, 42) FROM t;
Примечание
До версии PostgreSQL 8.2 запись .* не разворачивалась в конструкторах строк, так что выражение ROW(t.*, 42) создавало составное значение из двух полей, в котором первое поле так же было составным. Новое поведение обычно более полезно. Если вам нужно получить прежнее поведение, чтобы одно значение строки было вложено в другое, напишите внутреннее значение без .* , например: ROW(t, 42) .
По умолчанию значение, созданное выражением ROW , имеет тип анонимной записи. Если необходимо, его можно привести к именованному составному типу — либо к типу строки таблицы, либо составному типу, созданному оператором CREATE TYPE AS . Явное приведение может потребоваться для достижения однозначности. Например:
CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Приведение не требуется, так как существует только одна getf1() SELECT getf1(ROW(1,2.5,'this is a test')); getf1 ------- 1 (1 row) CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Теперь приведение необходимо для однозначного выбора функции: SELECT getf1(ROW(1,2.5,'this is a test')); ОШИБКА: функция getf1(record) не уникальна SELECT getf1(ROW(1,2.5,'this is a test')::mytable); getf1 ------- 1 (1 row) SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); getf1 ------- 11 (1 row)
Используя конструктор строк (кортежей), можно создавать составное значение для сохранения в столбце составного типа или для передачи функции, принимающей составной параметр. Также вы можете сравнить два составных значения или проверить их с помощью IS NULL или IS NOT NULL , например:
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); -- выбрать все строки, содержащие только NULL SELECT ROW(table.*) IS NULL FROM table;
Подробнее см. Раздел 9.24. Конструкторы строк также могут использоваться в сочетании с подзапросами, как описано в Разделе 9.23.
4.2.14. Правила вычисления выражений #
Порядок вычисления подвыражений не определён. В частности, аргументы оператора или функции не обязательно вычисляются слева направо или в любом другом фиксированном порядке.
Более того, если результат выражения можно получить, вычисляя только некоторые его части, тогда другие подвыражения не будут вычисляться вовсе. Например, если написать:
SELECT true OR somefunc();
тогда функция somefunc() не будет вызываться (возможно). То же самое справедливо для записи:
SELECT somefunc() OR true;
Заметьте, что это отличается от « оптимизации » вычисления логических операторов слева направо, реализованной в некоторых языках программирования.
Как следствие, в сложных выражениях не стоит использовать функции с побочными эффектами. Особенно опасно рассчитывать на порядок вычисления или побочные эффекты в предложениях WHERE и HAVING , так как эти предложения тщательно оптимизируются при построении плана выполнения. Логические выражения (сочетания AND / OR / NOT ) в этих предложениях могут быть видоизменены любым способом, допустимым законами Булевой алгебры.
Когда порядок вычисления важен, его можно зафиксировать с помощью конструкции CASE (см. Раздел 9.18). Например, такой способ избежать деления на ноль в предложении WHERE ненадёжен:
SELECT . WHERE x > 0 AND y/x > 1.5;
SELECT . WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Применяемая так конструкция CASE защищает выражение от оптимизации, поэтому использовать её нужно только при необходимости. (В данном случае было бы лучше решить проблему, переписав условие как y > 1.5*x .)
Однако CASE не всегда спасает в подобных случаях. Показанный выше приём плох тем, что не предотвращает раннее вычисление константных подвыражений. Как описано в Разделе 38.7, функции и операторы, помеченные как IMMUTABLE , могут вычисляться при планировании, а не выполнении запроса. Поэтому в примере
SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
, скорее всего, произойдёт деление на ноль из-за того, что планировщик попытается упростить константное подвыражение, даже если во всех строках в таблице x > 0 , а значит во время выполнения ветвь ELSE никогда не будет выполняться.
Хотя этот конкретный пример может показаться надуманным, похожие ситуации, в которых неявно появляются константы, могут возникать и в запросах внутри функций, так как значения аргументов функции и локальных переменных при планировании могут быть заменены константами. Поэтому, например, в функциях PL/pgSQL гораздо безопаснее для защиты от рискованных вычислений использовать конструкцию IF - THEN - ELSE , чем выражение CASE .
Ещё один подобный недостаток этого подхода в том, что CASE не может предотвратить вычисление заключённого в нём агрегатного выражения, так как агрегатные выражения вычисляются перед всеми остальными в списке SELECT или предложении HAVING . Например, в следующем запросе может возникнуть ошибка деления на ноль, несмотря на то, что он вроде бы защищён от неё:
SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments;
Агрегатные функции min() и avg() вычисляются независимо по всем входным строкам, так что если в какой-то строке поле employees окажется равным нулю, деление на ноль произойдёт раньше, чем станет возможным проверить результат функции min() . Поэтому, чтобы проблемные входные строки изначально не попали в агрегатную функцию, следует воспользоваться предложениями WHERE или FILTER .
Пред. Наверх След. 4.1. Лексическая структура Начало 4.3. Вызов функций