Извлекает строки из таблицы или представления.
[ WITH [ RECURSIVE ] <with_query> [, ...] ]
SELECT [ALL | DISTINCT [ON (<expression> [, ...])]]
[* | <expression> [[AS] <output_name>] [, ...]]
[FROM <from_item> [, ...]]
[WHERE <condition>]
[GROUP BY <grouping_element> [, ...]]
[HAVING <condition> [, ...]]
[WINDOW <window_name> AS (<window_definition>) [, ...] ]
[{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT] <select>]
[ORDER BY <expression> [ASC | DESC | USING <operator>] [NULLS {FIRST | LAST}] [, ...]]
[LIMIT {<count> | ALL}]
[OFFSET <start> [ ROW | ROWS ] ]
[FETCH { FIRST | NEXT } [ <count> ] { ROW | ROWS } ONLY]
[FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF <table_name> [, ...]] [NOWAIT | SKIP LOCKED ] [...]]
where <from_item> can be one of:
[ONLY] <table_name> [ * ] [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
[ TABLESAMPLE <sampling_method> ( <argument> [, ...] ) [ REPEATABLE ( <seed> ) ] ]
[LATERAL] ( <select> ) [ AS ] <alias> [( <column_alias> [, ...] ) ]
<with_query_name> [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
[LATERAL] <function_name> ( [ <argument> [, ...] ] )
[ WITH ORDINALITY ] [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
[LATERAL] <function_name> ( [ <argument> [, ...] ] ) [ AS ] <alias> ( <column_definition> [, ...] )
[LATERAL] <function_name> ( [ <argument> [, ...] ] ) AS ( <column_definition> [, ...] )
[LATERAL] ROWS FROM( <function_name> ( [ <argument> [, ...] ] ) [ AS ( <column_definition> [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] <alias> [ ( <column_alias> [, ...] ) ] ]
<from_item> <join_type> <from_item> { ON <join_condition> | USING ( <join_column> [, ...] ) }
<from_item> NATURAL <join_type> <from_item>
<from_item> CROSS JOIN <from_item>
where <grouping_element> can be one of:
()
<expression>
ROLLUP (<expression> [,...])
CUBE (<expression> [,...])
GROUPING SETS (<grouping_element> [, ...])
where <with_query> is:
<with_query_name> [( <column_name> [, ...] )] AS ( [ NOT ] MATERIALIZED ] ( <select> | <values> | <insert> | <update> | delete )
TABLE [ ONLY ] <table_name> [ * ]SELECT извлекает строки из нуля или более таблиц. Общая последовательность обработки SELECT следующая:
Вы должны иметь привилегию SELECT на каждый столбец, используемый в команде SELECT. Использование FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE также требует привилегии UPDATE (хотя бы на один столбец каждой выбранной таблицы).
Предложение WITH позволяет определить один или несколько подзапросов, на которые можно ссылаться по имени в основном запросе. Подзапросы фактически действуют как временные таблицы или представления на время выполнения основного запроса. Каждый подзапрос может быть оператором SELECT, TABLE, VALUES, INSERT, UPDATE или DELETE. При написании оператора модификации данных (INSERT, UPDATE или DELETE) в WITH обычно включают предложение RETURNING. Именно вывод RETURNING, а не базовая таблица, изменяемая оператором, формирует временную таблицу, считываемую основным запросом. Если RETURNING опущено, оператор всё равно выполняется, но не производит никакого вывода и поэтому не может быть использован основным запросом как таблица.
Для команды SELECT, включающей предложение WITH, в этом предложении может быть не более одного оператора, изменяющего данные таблицы (INSERT, UPDATE или DELETE).
Для каждого подзапроса WITH необходимо указать имя (без указания схемы). При необходимости можно указать список имён столбцов; если он опущен, имена выводятся из подзапроса.
Если указано ключевое слово RECURSIVE, подзапрос SELECT может ссылаться на себя по имени. Такой подзапрос должен иметь форму:
<non_recursive_term> UNION [ALL | DISTINCT] <recursive_term>где рекурсивная самоссылка находится в правой части оператора UNION. Разрешена только одна рекурсивная самоссылка на запрос. Рекурсивные операторы изменения данных не поддерживаются, однако результаты рекурсивного запроса SELECT можно использовать в операторах изменения данных.
Если указано ключевое слово RECURSIVE, порядок запросов WITH может быть произвольным: запрос может ссылаться на другой запрос, находящийся дальше в списке. Однако циклические ссылки или взаимная рекурсия не поддерживаются. Без ключевого слова RECURSIVE запросы WITH могут ссылаться только на предшествующие им запросы в списке WITH.
Если в предложении WITH содержится несколько запросов, ключевое слово RECURSIVE должно указываться только один раз сразу после WITH. Оно применяется ко всем запросам в предложении WITH, хотя не оказывает эффекта на те запросы, которые не используют рекурсию или прямые ссылки.
Ограничения рекурсивного WITH. Следующие элементы не поддерживаются:
<recursive_term>:WITH, где <with_query_name> является частью операции множеств.
Ниже приведён пример ограничения на операции множеств. Этот запрос возвращает ошибку, поскольку операция множества UNION содержит ссылку на таблицу foo.WITH RECURSIVE foo(i) AS (
SELECT 1
UNION ALL
SELECT i+1 FROM (SELECT * FROM foo UNION SELECT 0) bar
)
SELECT * FROM foo LIMIT 5;Этот рекурсивный CTE разрешён, потому что операция множества UNION не содержит ссылки на CTE foo.
WITH RECURSIVE foo(i) AS (
SELECT 1
UNION ALL
SELECT i+1 FROM (SELECT * FROM bar UNION SELECT 0) bar, foo
WHERE foo.i = bar.a
)
SELECT * FROM foo LIMIT 5;Основной запрос и запросы WITH выполняются (условно) одновременно. Это означает, что эффекты оператора изменения данных в WITH не видны из других частей запроса, за исключением чтения его выходных данных RETURNING. Если два таких оператора изменения данных пытаются изменить одну и ту же строку, результат не определён.
Важное свойство запросов WITH заключается в том, что они вычисляются только один раз за выполнение основного запроса, даже если на них ссылается основной запрос более одного раза. В частности, гарантируется, что операторы изменения данных выполняются ровно один раз, независимо от того, читает ли основной запрос все или какие-либо их выходные данные.
Однако запрос WITH можно пометить как NOT MATERIALIZED, чтобы отменить эту гарантию. В этом случае запрос WITH может быть интегрирован в основной запрос так, как если бы он был простым подзапросом SELECT в списке FROM основного запроса. Это приводит к повторным вычислениям, если основной запрос ссылается на такой запрос WITH более одного раза; однако если каждое такое использование требует лишь нескольких строк из общего результата запроса WITH, применение NOT MATERIALIZED может обеспечить суммарную экономию за счёт совместной оптимизации запросов. Указание NOT MATERIALIZED игнорируется, если оно применяется к рекурсивному запросу WITH или к запросу, не являющемуся свободным от побочных эффектов (например, не являющемуся обычным SELECT без использования нестабильных функций).
По умолчанию side-effect-free запрос WITH интегрируется в основной запрос, если он используется ровно один раз в списке FROM основного запроса. Это позволяет совместно оптимизировать два уровня запроса в тех случаях, когда это семантически незаметно. Однако такую интеграцию можно предотвратить, пометив запрос WITH как MATERIALIZED. Это может быть полезно, например, если запрос WITH используется как барьер оптимизации, препятствующий выбору плохого плана планировщиком. Базы данных версии до 7 никогда не выполняли такую интеграцию, поэтому запросы, написанные для старых версий, могут полагаться на то, что WITH работает как барьер оптимизации.
Клауза FROM указывает одну или несколько исходных таблиц для оператора SELECT. Если указано несколько источников, результатом будет декартово произведение (перекрёстное соединение) всех источников. Однако обычно добавляются условия отбора (с помощью WHERE), чтобы ограничить возвращаемые строки небольшим подмножеством декартова произведения.
Клауза FROM может содержать следующие элементы:
table_name
alias
TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]
Клауза TABLESAMPLE после имени таблицы означает, что указанный метод sampling_method должен использоваться для извлечения подмножества строк из этой таблицы. Такая выборка выполняется до применения любых других фильтров, таких как предложения WHERE. Стандартная поставка VMware включает два метода выборки: BERNOULLI и SYSTEM. Дополнительные методы выборки SYSTEM_ROWS и SYSTEM_TIME можно установить в базе данных через расширения.
Методы выборки BERNOULLI и SYSTEM принимают один аргумент — долю таблицы, подлежащую выборке, выраженную в процентах от 0 до 100. Этот аргумент может быть любым вещественным выражением. (Другие методы выборки могут принимать большее количество или иные аргументы.) Оба метода возвращают случайно выбранную выборку из таблицы, которая будет содержать примерно указанный процент строк таблицы. Метод BERNOULLI просматривает всю таблицу и независимо выбирает или игнорирует отдельные строки с указанной вероятностью. Метод SYSTEM выполняет выборку на уровне блоков, при этом каждый блок имеет указанную вероятность быть выбранным; все строки в каждом выбранном блоке возвращаются. Метод SYSTEM значительно быстрее метода BERNOULLI при малых значениях процента выборки, но может возвращать менее случайную выборку из-за эффектов кластеризации.
Метод выборки SYSTEM_ROWS принимает один целочисленный аргумент — максимальное количество строк для чтения. Результатом выборки всегда будет точно указанное количество строк, если только в таблице недостаточно строк, в этом случае выбирается вся таблица. Метод выборки SYSTEM_TIME принимает одно вещественное значение — максимальное время в миллисекундах, затрачиваемое на чтение таблицы. Это позволяет контролировать продолжительность выполнения запроса; компромисс заключается в том, что размер выборки становится труднопредсказуемым. Полученная выборка будет содержать столько строк, сколько удалось прочитать за указанное время, если только сначала не была прочитана вся таблица.
Как и встроенного метода SYSTEM, методы SYSTEM_ROWS и SYSTEM_TIME выполняют выборку на уровне блоков, поэтому выборка может быть не полностью случайной и подвержена эффектам кластеризации, особенно при выборе небольшого количества строк.
Производительность выборки на таблицах с оптимизацией под запись улучшится, если таблица имеет блочный каталог (если у таблицы есть индекс или был ранее) и степень улучшения прямо пропорциональна степени сжатия таблицы и обратно пропорциональна размеру запрашиваемой выборки. Чтобы создать блочный каталог для таблицы без индекса, выполните следующую команду:
CREATE INDEX dummy ON tab(i) WHERE false; DROP INDEX dummy;: Опциональная клауза REPEATABLE задаёт число-семя или выражение, используемое для генерации случайных чисел внутри метода выборки. Значение семени может быть любым ненулевым значением с плавающей точкой. Два запроса, указывающие одинаковые значения семени и аргументов, будут выбирать одну и ту же выборку из таблицы, если таблица не была изменена между запросами. Однако разные значения семени обычно дают разные выборки. Если REPEATABLE не указано, Database выбирает новую случайную выборку для каждого запроса на основе системно сгенерированного семени. Обратите внимание, что некоторые дополнительные методы выборки не поддерживают REPEATABLE и всегда создают новые выборки при каждом использовании.
select
with_query_name
Запрос WITH ссылается в клаузе FROM по его имени, как если бы это имя было именем таблицы. Вы можете указать псевдоним таким же образом, как и для таблицы.
Запрос WITH скрывает таблицу с тем же именем в целях основного запроса. При необходимости вы можете сослаться на таблицу с таким же именем, указав её схему.
function_name
<column_name> <data_type> [, ... ] ). Список определений столбцов должен соответствовать реальному количеству и типам столбцов, возвращаемых функцией.ROWS FROM( ... ), если одной из функций требуется список определений столбцов, предпочтительно поместить этот список после вызова функции внутри ROWS FROM( ... ). Список определений столбцов можно поместить после конструкции ROWS FROM( ... ) только в случае единственной функции и отсутствия клаузы WITH ORDINALITY.
Чтобы использовать ORDINALITY вместе со списком определений столбцов, необходимо использовать синтаксис ROWS FROM( ... ) и поместить список определений столбцов внутрь ROWS FROM( ... ).join_type Один из:
INNER и OUTER необходимо указать условие соединения, а именно ровно одно из NATURAL, ON <join_condition> или USING ( <join_column> [, ...]. Продолжайте чтение для понимания смысла.Клауза JOIN объединяет два элемента FROM, которые для удобства называются «таблицами», хотя на самом деле ими могут быть любые типы элементов FROM. При необходимости используйте круглые скобки для определения порядка вложенности. При отсутствии скобок соединения вкладываются слева направо. JOIN связывает сильнее, чем запятые, разделяющие элементы списка FROM. Все варианты JOIN являются синтаксическим удобством; они не делают ничего, чего нельзя достичь с помощью обычных FROM и WHERE.
LEFT OUTER JOIN возвращает все строки в квалифицированном декартовом произведении (то есть все объединённые строки, прошедшие условие соединения), плюс по одной копии каждой строки левой таблицы, для которой не нашлось подходящей строки в правой таблице, удовлетворяющей условию соединения. Эта строка левой таблицы расширяется до полной ширины объединённой таблицы путём вставки значений NULL в столбцы правой таблицы. Обратите внимание, что при определении наличия совпадений учитывается только собственное условие клаузы JOIN. Внешние условия применяются позже.
Наоборот, RIGHT OUTER JOIN возвращает все объединённые строки, плюс по одной строке для каждой неподходящей строки правой таблицы (расширенной значениями NULL слева). Это просто синтаксическое удобство, поскольку такой запрос можно преобразовать в LEFT OUTER JOIN, поменяв местами левую и правую таблицы.
FULL OUTER JOIN возвращает все объединённые строки, плюс по одной строке для каждой неподходящей строки левой таблицы (расширенной значениями NULL справа), плюс по одной строке для каждой неподходящей строки правой таблицы (расширенной значениями NULL слева).
Опциональная клауза WHERE имеет общий вид:
WHERE <condition>где condition — любое выражение, возвращающее значение типа boolean. Любая строка, не удовлетворяющая этому условию, будет исключена из результата. Строка удовлетворяет условию, если при подстановке реальных значений строки вместо переменных выражение возвращает true.
Необязательное предложение GROUP BY имеет следующий общий вид:
GROUP BY <grouping_element> [, ...]где <grouping_element> может быть одним из:
()
<expression>
ROLLUP (<expression> [,...])
CUBE (<expression> [,...])
GROUPING SETS ((<grouping_element> [, ...]))GROUP BY объединяет в одну строку все выбранные строки, имеющие одинаковые значения выражений группировки. Выражение, используемое внутри grouping_element, может представлять собой имя входного столбца, имя или порядковый номер выходного столбца (элемента списка SELECT) либо произвольное выражение, построенное на основе значений входных столбцов. В случае неоднозначности имя в GROUP BY интерпретируется как имя входного столбца, а не выходного.
Если в качестве элементов группировки присутствуют GROUPING SETS, ROLLUP или CUBE, то всё предложение GROUP BY в целом определяет некоторое количество независимых наборов группировок. Эффект от этого эквивалентен построению UNION ALL между подзапросами, каждый из которых содержит в своём предложении GROUP BY один из этих наборов группировок. Дополнительные сведения об обработке наборов группировок см. в разделе GROUPING SETS, CUBE и ROLLUP документации PostgreSQL.
Агрегатные функции, если они используются, вычисляются по всем строкам, образующим каждую группу, и формируют отдельное значение для каждой группы. (Если используются агрегатные функции, но отсутствует предложение GROUP BY, запрос рассматривается как имеющий одну группу, включающую все выбранные строки.) Вы можете дополнительно отфильтровать набор строк, передаваемых в каждую агрегатную функцию, добавив к вызову агрегатной функции предложение FILTER. При наличии предложения FILTER в агрегатную функцию включаются только те строки, которые ему соответствуют. См. раздел «Выражения агрегирования».
Когда указано GROUP BY или используются какие-либо агрегатные функции, недопустимо, чтобы выражения в списке SELECT ссылались на столбцы, не входящие в группировку, за исключением случаев, когда такие столбцы используются внутри агрегатных функций или функционально зависят от сгруппированных столбцов, поскольку в противном случае для столбца вне группировки может существовать более одного возможного значения. Функциональная зависимость существует, если сгруппированные столбцы (или их подмножество) являются первичным ключом таблицы, содержащей столбец вне группировки.
Имейте в виду, что все агрегатные функции вычисляются до оценки любых «скалярных» выражений в предложении HAVING или списке SELECT. Это означает, например, что выражение CASE нельзя использовать для пропуска вычисления агрегатной функции.
В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указывать вместе с GROUP BY.
База данных предоставляет следующие дополнительные OLAP-расширения группировки (часто называемые супергруппами):
ROLLUP
GROUP BY ROLLUP (a,b,c) эквивалентно:
GROUP BY GROUPING SETS( (a,b,c), (a,b), (a), () ) Обратите внимание, что n элементов ROLLUP преобразуются в n+1 набор группировок. Также важно отметить, что порядок, в котором указываются выражения группировки, имеет значение при использовании ROLLUP.
CUBE
GROUP BY CUBE (a,b,c) эквивалентно:
GROUP BY GROUPING SETS( (a,b,c), (a,b), (a,c), (b,c), (a),
(b), (c), () ) Обратите внимание, что n элементов CUBE преобразуются в 2n наборов группировок. Рассмотрите возможность использования CUBE в ситуациях, требующих кросс-табличных отчётов. CUBE обычно наиболее подходит для запросов, использующих столбцы из нескольких измерений, а не столбцы, представляющие различные уровни одного и того же измерения. Например, часто запрашиваемая кросс-таблица может требовать итогов по всем комбинациям месяца, штата и продукта.
GROUPING SETS
Вы можете выборочно указать нужный набор групп с помощью выражения GROUPING SETS внутри предложения GROUP BY. Это позволяет точно задать группировку по нескольким измерениям без вычисления полного ROLLUP или CUBE. Например:
GROUP BY GROUPING SETS( (a,c), (a,b) )При использовании расширенных предложений группировки ROLLUP, CUBE или GROUPING SETS возникают две задачи. Во-первых, как определить, какие строки результата являются итоговыми, и какой именно уровень агрегации соответствует данной итоговой строке. Или как различить строки результата, содержащие хранимые значения NULL, и «NULL», созданные ROLLUP или CUBE. Во-вторых, когда в предложении GROUP BY указаны дублирующиеся наборы группировки, как определить, какие строки результата являются дубликатами? Для решения этих вопросов в список SELECT можно добавить две дополнительные группирующие функции:
grouping(column [, ...]) — функция grouping может применяться к одному или нескольким атрибутам группировки для различения суперагрегированных строк и обычных сгруппированных строк. Это может помочь отличить «NULL», представляющий множество всех значений в суперагрегированной строке, от значения NULL в обычной строке. Каждый аргумент этой функции формирует бит — 1 или 0, где 1 означает, что строка результата является суперагрегированной, а 0 — что строка результата получена обычной группировкой. Функция grouping возвращает целое число, интерпретируя эти биты как двоичное число и преобразуя его в десятичное.
group_id() — для запросов с расширениями группировки, содержащих дублирующиеся наборы группировки, функция group_id используется для идентификации дублирующихся строк в результате. Все уникальные строки вывода наборов группировки будут иметь значение <group_id>, равное 0. Для каждого обнаруженного дублирующегося набора группировки функция group_id присваивает числовое значение <group_id>, большее 0. Все строки вывода в конкретном дублирующемся наборе группировки идентифицируются одинаковым числом <group_id>.
Необязательное предложение HAVING имеет следующий общий вид:
HAVING <condition>где <condition> имеет тот же формат, что и условие в предложении WHERE.
HAVING исключает строки групп, не удовлетворяющих заданному условию. HAVING отличается от WHERE: WHERE фильтрует отдельные строки до применения GROUP BY, тогда как HAVING фильтрует сгруппированные строки, созданные с помощью GROUP BY. Каждый столбец, на который ссылается <condition>, должен однозначно ссылаться на столбец группировки, если только эта ссылка не находится внутри агрегатной функции или столбец вне группировки функционально зависит от столбцов группировки.
Наличие HAVING превращает запрос в сгруппированный, даже если отсутствует GROUP BY. Это то же самое, что происходит, когда запрос содержит агрегатные функции, но не содержит GROUP BY. Все выбранные строки считаются одной группой, и в списке SELECT и предложении HAVING можно ссылаться на столбцы таблиц только внутри агрегатных функций. Такой запрос вернёт одну строку, если условие HAVING истинно, и ноль строк, если оно ложно.
В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указывать вместе с HAVING.
Необязательная клауза WINDOW определяет поведение оконных функций, указанных в списке SELECT или в клаузе ORDER BY запроса. Общий вид клаузы WINDOW следующий:
WINDOW <window_name> AS ( <window_definition> ) [, ...]где <window_name> — это имя, на которое можно ссылаться в клаузах OVER или в последующих определениях окон, а <window_definition> имеет вид:
[<existing_window_name>]
[PARTITION BY <expression> [, ...]]
[ORDER BY <expression> [ASC | DESC | USING <operator>] [NULLS {FIRST | LAST}] [, ...] ]
[<frame_clause>] При этом запись в клаузе WINDOW не обязательно должна использоваться где-либо; если она не применяется в запросе, она просто игнорируется. Можно использовать оконные функции и без клаузы WINDOW вообще, поскольку вызов оконной функции может напрямую задавать определение окна в своей клаузе OVER. Однако клауза WINDOW позволяет сократить объем вводимого текста, когда одно и то же определение окна требуется для нескольких оконных функций.
Например:
SELECT vendor, rank() OVER (mywindow) FROM sale
GROUP BY vendor
WINDOW mywindow AS (ORDER BY sum(prc*qty));existing_window_name
PARTITION BY
ORDER BY
Примечание!
Элементы клаузы ORDER BY определяют порядок сортировки строк в каждой партиции результирующего набора. Если клауза опущена, строки возвращаются в порядке, который является наиболее эффективным, и этот порядок может меняться.
frame_clause
{ RANGE | ROWS | GROUPS } <frame_start> [ <frame_exclusion> ]
{ RANGE | ROWS | GROUPS } BETWEEN <frame_start> AND <frame_end> [ <frame_exclusion> ]где <frame_start> и <frame_end> могут быть одним из:
UNBOUNDED PRECEDING
<offset> PRECEDING
CURRENT ROW
<offset> FOLLOWING
UNBOUNDED FOLLOWINGа <frame_exclusion> может быть одним из:
EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERSЕсли <frame_end> опущено, по умолчанию используется CURRENT ROW. Ограничения: <frame_start> не может быть UNBOUNDED FOLLOWING, <frame_end> не может быть UNBOUNDED PRECEDING, а вариант <frame_end> не может стоять в приведённом выше списке вариантов <frame_start> и <frame_end> раньше, чем вариант <frame_start>; например, конструкция RANGE BETWEEN CURRENT ROW AND <offset> PRECEDING недопустима.
Параметр фрейминга по умолчанию — RANGE UNBOUNDED PRECEDING, что эквивалентно RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; он устанавливает рамку как все строки от начала партиции до последней одноранговой строки текущей строки (одноранговая строка — это строка, которую клауза ORDER BY окна считает эквивалентной текущей строке; все строки считаются одноранговыми, если ORDER BY отсутствует). В общем случае UNBOUNDED PRECEDING означает, что рамка начинается с первой строки партиции, аналогично UNBOUNDED FOLLOWING означает, что рамка заканчивается последней строкой партиции, независимо от режима RANGE, ROWS или GROUPS. В режиме ROWS CURRENT ROW означает, что рамка начинается или заканчивается текущей строкой; но в режимах RANGE или GROUPS это означает, что рамка начинается или заканчивается первой или последней одноранговой строкой текущей строки в порядке ORDER BY. Значения <offset> PRECEDING и <offset> FOLLOWING меняют своё значение в зависимости от режима рамки. В режиме ROWS значение <offset> — целое число, указывающее, на сколько строк до или после текущей строки начинается или заканчивается рамка. В режиме GROUPS значение <offset> — целое число, указывающее, на сколько групп одноранговых строк до или после группы текущей строки начинается или заканчивается рамка, где группа одноранговых строк — это группа строк, эквивалентных согласно клаузе ORDER BY окна. В режиме RANGE использование параметра <offset> требует, чтобы в определении окна был ровно один столбец ORDER BY. Тогда рамка включает те строки, значение столбца сортировки которых не более чем на <offset> меньше (для PRECEDING) или больше (для FOLLOWING) значения столбца сортировки текущей строки. В этих случаях тип данных выражения <offset> зависит от типа данных столбца сортировки. Для числовых столбцов сортировки это обычно тот же тип, что и у столбца сортировки, а для столбцов даты и времени — интервал. Во всех случаях значение <offset> должно быть не NULL и неотрицательным. Кроме того, хотя смещение не обязано быть простой константой, оно не может содержать переменные, агрегатные функции или оконные функции.
Параметр <frame_exclusion> позволяет исключить строки вокруг текущей строки из рамки, даже если они должны были бы быть включены согласно параметрам начала и конца рамки. EXCLUDE CURRENT ROW исключает текущую строку из рамки. EXCLUDE GROUP исключает текущую строку и её одноранговые строки из рамки. EXCLUDE TIES исключает все одноранговые строки текущей строки из рамки, но не саму текущую строку. EXCLUDE NO OTHERS явно указывает поведение по умолчанию — не исключать текущую строку или её одноранговые строки.
Следует помнить, что режим ROWS может давать непредсказуемые результаты, если порядок сортировки в ORDER BY не обеспечивает уникальность строк. Режимы RANGE и GROUPS предназначены для того, чтобы строки, являющиеся одноранговыми в порядке ORDER BY, обрабатывались одинаково: все строки данной одноранговой группы будут либо включены в рамку, либо исключены из неё.
Используйте одну из клауз ROWS, RANGE или GROUPS, чтобы задать границы окна. Границы окна могут включать одну, несколько или все строки партиции. Вы можете задать границы окна либо как диапазон значений данных относительно значения в текущей строке (RANGE), либо как количество строк относительно текущей строки (ROWS), либо как количество групп одноранговых строк (GROUPS). При использовании клауз RANGE или GROUPS необходимо также использовать клаузу ORDER BY, поскольку вычисления, необходимые для формирования окна, требуют упорядоченности значений. Кроме того, клауза ORDER BY не может содержать более одного выражения, и это выражение должно возвращать либо дату, либо числовое значение. При использовании клауз ROWS, RANGE или GROUPS, если указана только начальная строка, текущая строка используется как последняя строка окна.
PRECEDING — Клауза PRECEDING определяет первую строку окна, используя текущую строку как точку отсчёта. Начальная строка выражается количеством строк перед текущей строкой. Например, при фрейминге ROWS значение 5 PRECEDING устанавливает начало окна на пятую строку перед текущей. При фрейминге RANGE это устанавливает начало окна на первую строку, значение столбца сортировки которой предшествует значению текущей строки на 5 в заданном порядке. Если порядок возрастает по дате, это будет первая строка в пределах 5 дней до текущей строки. UNBOUNDED PRECEDING устанавливает первой строкой окна первую строку в партиции.
BETWEEN — Клауза BETWEEN определяет первую и последнюю строки окна, используя текущую строку как точку отсчёта. Первая и последняя строки выражаются количеством строк до и после текущей строки соответственно. Например, BETWEEN 3 PRECEDING AND 5 FOLLOWING устанавливает окно от третьей строки перед текущей до пятой строки после текущей. Используйте BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, чтобы установить первой и последней строками окна первую и последнюю строки партиции соответственно. Это эквивалентно поведению по умолчанию, если клаузы ROWS, RANGE или GROUPS не указаны.
FOLLOWING — Клауза FOLLOWING определяет последнюю строку окна, используя текущую строку как точку отсчёта. Последняя строка выражается количеством строк после текущей строки. Например, при фрейминге ROWS значение 5 FOLLOWING устанавливает конец окна на пятую строку после текущей. При фрейминге RANGE это устанавливает конец окна на последнюю строку, значение столбца сортировки которой следует за значением текущей строки на 5 в заданном порядке. Если порядок возрастает по дате, это будет последняя строка в пределах 5 дней после текущей строки. Используйте UNBOUNDED FOLLOWING, чтобы установить последней строкой окна последнюю строку в партиции.
Если клаузы ROWS, RANGE или GROUPS не указаны, границы окна начинаются с первой строки партиции (UNBOUNDED PRECEDING) и заканчиваются текущей строкой (CURRENT ROW), если используется ORDER BY. Если ORDER BY не указан, окно начинается с первой строки партиции (UNBOUNDED PRECEDING) и заканчивается последней строкой партиции (UNBOUNDED FOLLOWING).
Цель клаузы WINDOW — определить поведение оконных функций, указанных в списке SELECT или в клаузе ORDER BY запроса. Эти функции могут ссылаться на записи клаузы WINDOW по имени в своих клаузах OVER. Однако запись в клаузе WINDOW не обязана использоваться; если она не применяется в запросе, она просто игнорируется. Можно использовать оконные функции и без клаузы WINDOW, поскольку вызов оконной функции может напрямую задавать определение окна в своей клаузе OVER. Тем не менее, клауза WINDOW экономит время печати, когда одно и то же определение окна нужно для нескольких оконных функций.
В настоящее время нельзя указывать FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE вместе с WINDOW.
Дополнительную информацию об оконных функциях см. в разделе «Оконные выражения».
Список SELECT (между ключевыми словами SELECT и FROM) определяет выражения, формирующие выходные строки оператора SELECT. Эти выражения могут (и обычно делают) ссылаться на столбцы, вычисленные в клаузе FROM.
Выражение в списке SELECT может быть константным значением, ссылкой на столбец, вызовом оператора, вызовом функции, агрегатным выражением, оконным выражением, скалярным подзапросом и т.д. Некоторые конструкции можно классифицировать как выражения, хотя они не подчиняются общим правилам синтаксиса. Как правило, они имеют семантику функции или оператора. Информацию о SQL-выражениях значений и вызовах функций см. в руководстве администратора базы данных в разделе «Запрос данных».
Как и в таблице, каждый выходной столбец оператора SELECT имеет имя. В простом SELECT это имя используется только для подписи столбца при выводе, но когда SELECT является подзапросом более крупного запроса, это имя становится именем столбца виртуальной таблицы, создаваемой подзапросом, для внешнего запроса. Чтобы задать имя выходного столбца, напишите AS <output_name> после выражения столбца. (Можно опустить AS, но только если желаемое имя не совпадает ни с одним ключевым словом SQL. Для защиты от возможных будущих добавлений ключевых слов можно всегда либо писать AS, либо заключать имя в двойные кавычки.) Если имя столбца не указано, система автоматически выбирает имя. Если выражение столбца представляет собой простую ссылку на столбец, выбранное имя совпадает с именем этого столбца. В более сложных случаях может использоваться имя функции или типа, либо система вернётся к сгенерированному имени, например ?column? или columnN.
Имя выходного столбца можно использовать для ссылки на его значение в клаузах ORDER BY и GROUP BY, но не в клаузах WHERE или HAVING; там вместо этого необходимо указывать само выражение.
Вместо выражения в списке вывода можно указать , что является сокращением для всех столбцов выбранных строк. Также можно указать `
Согласно стандарту SQL, выражения в списке вывода должны вычисляться до применения DISTINCT, ORDER BY или LIMIT. Это очевидно необходимо при использовании DISTINCT, поскольку иначе непонятно, какие именно значения должны быть уникальными. Однако во многих случаях удобнее, чтобы выражения вывода вычислялись после ORDER BY и LIMIT; особенно если в списке вывода есть какие-либо изменчивые или затратные функции. При таком поведении порядок вычисления функций становится более интуитивным, и не будет вычислений для строк, которые никогда не появятся в результате. Система фактически вычисляет выражения вывода после сортировки и ограничения, если только эти выражения не используются в DISTINCT, ORDER BY или GROUP BY. (В качестве контрпримера, SELECT f(x) FROM tab ORDER BY 1 явно должен вычислить f(x) до сортировки.) Выражения вывода, содержащие функции, возвращающие множества, фактически вычисляются после сортировки и до ограничения, чтобы LIMIT действовал как ограничение вывода функции, возвращающей множество.
Если указано SELECT DISTINCT, все дублирующиеся строки удаляются из результирующего набора (из каждой группы дубликатов сохраняется одна строка). SELECT ALL задает противоположное поведение: все строки сохраняются; это значение по умолчанию.
SELECT DISTINCT ON ( <expression> [, ...] ) оставляет только первую строку из каждого набора строк, в которых указанные выражения дают одинаковые значения. Выражения DISTINCT ON интерпретируются по тем же правилам, что и для ORDER BY (см. выше). Обратите внимание, что «первая строка» каждой группы определяется непредсказуемо, если не используется ORDER BY для гарантии того, что нужная строка окажется первой. Например:
SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;извлекает самый свежий отчет о погоде для каждого местоположения. Однако если бы мы не использовали ORDER BY для принудительной сортировки значений времени по убыванию для каждого местоположения, мы получили бы отчет за произвольное время для каждого местоположения.
Выражения в DISTINCT ON должны соответствовать самым левым выражениям в ORDER BY. Предложение ORDER BY обычно содержит дополнительные выражения, определяющие желаемый порядок строк внутри каждой группы DISTINCT ON.
В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указывать вместе с DISTINCT.
Предложение UNION имеет следующую общую форму:
<select_statement> UNION [ALL | DISTINCT] <select_statement>Здесь <select_statement> — любое SELECT-выражение без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE. (ORDER BY и LIMIT могут быть присоединены к подзапросу, если он заключён в скобки. Без скобок эти предложения будут применяться к результату всего UNION, а не к его правому операнду.)
Оператор UNION вычисляет объединение множеств строк, возвращённых участвующими SELECT-выражениями. Строка входит в объединение двух результирующих наборов, если она присутствует хотя бы в одном из них. Два SELECT-выражения, являющиеся непосредственными операндами UNION, должны возвращать одинаковое количество столбцов, и соответствующие столбцы должны иметь совместимые типы данных.
Результат UNION не содержит дублирующихся строк, если не указан параметр ALL. ALL запрещает удаление дубликатов. (Поэтому UNION ALL обычно работает значительно быстрее, чем UNION; используйте ALL, когда это возможно.) Ключевое слово DISTINCT можно указать явно, чтобы задать поведение по умолчанию — удаление повторяющихся строк.
Несколько операторов UNION в одном SELECT-выражении вычисляются слева направо, если только круглые скобки не задают другой порядок.
В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указывать ни для результата UNION, ни для любого входного подзапроса UNION.
Предложение INTERSECT имеет следующую общую форму:
<select_statement> INTERSECT [ALL | DISTINCT] <select_statement>Здесь <select_statement> — любое SELECT-выражение без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE.
Оператор INTERSECT вычисляет пересечение множеств строк, возвращённых участвующими SELECT-выражениями. Строка входит в пересечение двух результирующих наборов, если она присутствует в обоих наборах.
Результат INTERSECT не содержит дублирующихся строк, если не указан параметр ALL. При использовании ALL строка, имеющая <m> дубликатов в левой таблице и <n> дубликатов в правой таблице, будет присутствовать в результирующем наборе min(<m>, <n>) раз. Ключевое слово DISTINCT можно использовать для явного указания поведения по умолчанию — удаления дублирующихся строк.
Несколько операторов INTERSECT в одном SELECT-выражении вычисляются слева направо, если только скобки не задают иной порядок. INTERSECT имеет более высокий приоритет, чем UNION. То есть A UNION B INTERSECT C интерпретируется как A UNION (B INTERSECT C).
В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указывать ни для результата INTERSECT, ни для любого входного подзапроса INTERSECT.
Предложение EXCEPT имеет следующую общую форму:
<select_statement> EXCEPT [ALL | DISTINCT] <select_statement>Здесь <select_statement> — любое SELECT-выражение без предложений ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE или FOR KEY SHARE.
Оператор EXCEPT вычисляет множество строк, которые присутствуют в результате левого SELECT-выражения, но отсутствуют в результате правого.
Результат EXCEPT не содержит дублирующихся строк, если не указан параметр ALL. При использовании ALL строка, имеющая <m> дубликатов в левой таблице и <n> дубликатов в правой таблице, будет присутствовать в результирующем наборе max(<m>-<n>,0) раз. Ключевое слово DISTINCT можно использовать для явного указания поведения по умолчанию — удаления дублирующихся строк.
Несколько операторов EXCEPT в одном SELECT-выражении вычисляются слева направо, если только скобки не задают иной порядок. EXCEPT имеет тот же уровень приоритета, что и UNION.
В настоящее время FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE нельзя указывать ни для результата EXCEPT, ни для любого входного подзапроса EXCEPT.
Необязательное предложение ORDER BY имеет следующую общую форму:
ORDER BY <expression> [ASC | DESC | USING <operator>] [NULLS {FIRST | LAST}] [,...]Предложение ORDER BY задаёт сортировку результирующих строк по указанным выражениям. Если две строки равны по самому левому выражению, они сравниваются по следующему выражению и так далее. Если строки равны по всем указанным выражениям, они возвращаются в порядке, зависящем от реализации.
Каждое <expression> может быть именем или порядковым номером выходного столбца (элемента списка SELECT) либо произвольным выражением, составленным из значений входных столбцов.
Порядковый номер обозначает позицию выходного столбца в списке слева направо. Эта возможность позволяет задавать порядок сортировки по столбцу, не имеющему уникального имени. Это никогда не является абсолютно необходимым, поскольку всегда можно присвоить имя выходному столбцу с помощью предложения AS.
Также допускается использование произвольных выражений в предложении ORDER BY, включая столбцы, отсутствующие в списке вывода SELECT. Таким образом, следующий запрос является корректным:
SELECT name FROM distributors ORDER BY code;Ограничение этого механизма состоит в том, что предложение ORDER BY, применяющееся к результату UNION, INTERSECT или EXCEPT, может указывать только имя или номер выходного столбца, но не произвольное выражение.
Если выражение в ORDER BY представляет собой простое имя, совпадающее с именем как выходного, так и входного столбца, ORDER BY интерпретирует его как имя выходного столбца. Это противоположно выбору, который делает GROUP BY в аналогичной ситуации. Такая несогласованность сохранена для совместимости со стандартом SQL.
Дополнительно после любого выражения в предложении ORDER BY можно указать ключевые слова ASC (по возрастанию) или DESC (по убыванию). Если не указано, по умолчанию подразумевается ASC. Также можно указать конкретный оператор сортировки в предложении USING. Обычно ASC эквивалентно USING <, а DESC — USING >. (Однако создатель пользовательского типа данных может определить точный порядок сортировки по умолчанию, который может соответствовать операторам с другими именами.)
Если указано NULLS LAST, значения NULL сортируются после всех ненулевых значений; если указано NULLS FIRST, значения NULL сортируются до всех ненулевых значений. Если ни одно из них не указано, поведение по умолчанию — NULLS LAST при указании или подразумевании ASC и NULLS FIRST при указании DESC (таким образом, по умолчанию NULL рассматриваются как большие по сравнению с ненулевыми значениями). Когда используется USING, порядок сортировки NULL зависит от того, является ли оператор оператором «меньше» или «больше».
Обратите внимание, что параметры сортировки применяются только к выражению, за которым они следуют; например, ORDER BY x, y DESC не эквивалентно ORDER BY x DESC, y DESC.
Строки символов сортируются в соответствии с региональными настройками (collation), установленными при создании базы данных. При необходимости это можно переопределить, добавив предложение COLLATE в выражение, например ORDER BY mycolumn COLLATE "en_US".
Строки символов сортируются в соответствии с collation, применяемой к сортируемому столбцу. Это можно переопределить при необходимости, добавив предложение COLLATE в выражение, например ORDER BY mycolumn COLLATE "en_US". За информацией о создании collation обратитесь к разделу CREATE COLLATION.
Предложение LIMIT состоит из двух независимых подпредложений:
LIMIT {<count> | ALL}
OFFSET <start><count> задаёт максимальное количество возвращаемых строк, а <start> — количество строк, которые нужно пропустить перед началом возврата строк. Когда оба параметра указаны, пропускается <start> строк, после чего начинается отсчёт <count> строк для возврата.
Если выражение <count> вычисляется как NULL, оно интерпретируется как LIMIT ALL, то есть ограничение отсутствует. Если значение OFFSET 0.
SQL:2008 ввёл другую синтаксическую конструкцию для достижения того же результата, которая также поддерживается Database. Она выглядит так:
OFFSET <start> [ ROW | ROWS ]
FETCH { FIRST | NEXT } [ <count> ] { ROW | ROWS } ONLYСогласно стандарту, значения <start> или <count> должны быть литеральными константами, параметрами или именами переменных; как расширение Database допускаются и другие выражения, но, как правило, их необходимо заключать в скобки, чтобы избежать неоднозначности. Если <count> опущено в предложении FETCH, оно по умолчанию принимается равным 1. Слова ROW и ROWS, а также FIRST и NEXT являются служебными и не влияют на действие этих предложений. Согласно стандарту, предложение OFFSET должно стоять перед FETCH, если оба присутствуют; однако Database допускает любой порядок.
При использовании LIMIT рекомендуется применять предложение ORDER BY, которое упорядочивает строки результата однозначно. В противном случае вы получите непредсказуемое подмножество строк запроса — вы можете запросить десятую по двадцатую строки, но десятую по двадцатую в каком порядке? Вы не знаете порядок, если не укажете ORDER BY.
Оптимизатор запросов учитывает LIMIT при формировании плана выполнения запроса, поэтому вы, скорее всего, получите разные планы (и, соответственно, разный порядок строк) в зависимости от значений LIMIT и OFFSET. Таким образом, использование разных значений LIMIT/OFFSET для выборки различных подмножеств результата запроса приведёт к несогласованным результатам, если только вы не обеспечите предсказуемый порядок с помощью ORDER BY. Это не ошибка; это естественное следствие того, что SQL не гарантирует порядок возврата строк запроса, если только ORDER BY не используется для его фиксации.
Возможно даже, что многократное выполнение одного и того же запроса с LIMIT будет возвращать разные подмножества строк таблицы, если нет ORDER BY, обеспечивающего выбор детерминированного подмножества. Опять же, это не ошибка; система не гарантирует детерминированность результатов в таких случаях.
FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE — это клаузы блокировки; они влияют на то, каким образом SELECT блокирует строки при их выборке из таблицы. Глобальный детектор взаимоблокировок оказывает влияние на блокировку, используемую запросами SELECT, содержащими клаузу блокировки (FOR <lock_strength>). Глобальный детектор взаимоблокировок включается путём установки конфигурационного параметра gp_enable_global_deadlock_detector в значение on. Подробную информацию о Глобальном детекторе взаимоблокировок см. в Руководстве администратора баз данных.
Клауза блокировки имеет общий вид:
FOR <lock_strength> [OF <table_name> [ , ... ] ] [ NOWAIT | SKIP LOCKED ] <lock_strength> может принимать одно из следующих значений:
UPDATE — блокирует таблицу с эксклюзивной (EXCLUSIVE) блокировкой.NO KEY UPDATE — блокирует таблицу с эксклюзивной (EXCLUSIVE) блокировкой.SHARE — блокирует таблицу с блокировкой уровня строк (ROW SHARE).KEY SHARE — блокирует таблицу с блокировкой уровня строк (ROW SHARE).Когда Глобальный детектор взаимоблокировок отключён (режим по умолчанию), YMatrix использует указанную блокировку.
Когда Глобальный детектор взаимоблокировок включён, для простых запросов SELECT, содержащих клаузу блокировки, используется блокировка ROW SHARE, а планы запросов содержат узел lockrows. Простые запросы SELECT, содержащие клаузу блокировки, должны удовлетворять всем следующим условиям:
SELECT.FROM содержит одну таблицу, которая не является представлением или таблицей с оптимизацией добавления (append optimized table).SELECT не содержит операций множеств, таких как UNION или INTERSECT.SELECT не содержит подзапросов.Во всех остальных случаях блокировка таблиц для запроса SELECT с клаузой блокировки работает так, как если бы Глобальный детектор взаимоблокировок был отключён.
Примечание! Глобальный детектор взаимоблокировок также влияет на блокировку, используемую операциями DELETE и UPDATE. По умолчанию Database устанавливает эксклюзивную (EXCLUSIVE) блокировку на таблицы при выполнении операций DELETE и UPDATE над кучами (heap tables). Когда Глобальный детектор взаимоблокировок включён, режим блокировки для операций DELETE и UPDATE над таблицами-кучами становится ROW EXCLUSIVE.
Дополнительную информацию о каждом режиме блокировки на уровне строк см. в документации PostgreSQL по теме «Явное управление блокировками» (Explicit Locking).
Чтобы предотвратить ожидание завершения других транзакций, используйте либо опцию NOWAIT, либо опцию SKIP LOCKED. При использовании NOWAIT инструкция выдаст ошибку вместо ожидания, если выбранная строка не может быть немедленно заблокирована. При использовании SKIP LOCKED любые выбранные строки, которые нельзя заблокировать сразу, будут пропущены. Пропуск заблокированных строк приводит к несогласованному представлению данных, поэтому этот способ не подходит для общих задач, но может применяться для избежания конкуренции за блокировки при работе нескольких потребителей с таблицей, используемой как очередь. Обратите внимание, что NOWAIT и SKIP LOCKED применяются только к блокировкам на уровне строк — требуемая блокировка таблицы на уровне ROW SHARE всё равно устанавливается обычным способом. Если вам нужно получить блокировку на уровне таблицы без ожидания, можно сначала использовать команду LOCK с опцией NOWAIT.
Если в клаузе блокировки указаны конкретные таблицы, то блокируются только строки из этих таблиц; все остальные таблицы, используемые в SELECT, читаются обычным образом. Клауза блокировки без списка таблиц влияет на все таблицы, задействованные в инструкции. Если клауза блокировки применяется к представлению или подзапросу, она затрагивает все таблицы, используемые в этом представлении или подзапросе. Однако такие клаузы не действуют на запросы WITH, на которые ссылается основной запрос. Если требуется блокировка строк внутри запроса WITH, необходимо указать клаузу блокировки непосредственно внутри этого запроса WITH.
Можно использовать несколько клауз блокировки, если необходимо задать разное поведение блокировки для разных таблиц. Если одна и та же таблица явно или неявно затрагивается более чем одной клаузой блокировки, она обрабатывается так, будто была указана только самой «сильной» из них. Аналогично, таблица будет обработана с опцией NOWAIT, если эта опция указана хотя бы в одной из затрагивающих её клауз. В противном случае она будет обработана с опцией SKIP LOCKED, если таковая указана хотя бы в одной из затрагивающих её клауз.
Клаузы блокировки нельзя использовать в контекстах, где возвращаемые строки невозможно однозначно связать со строками отдельных таблиц; например, их нельзя использовать с агрегацией.
Когда клауза блокировки указана на верхнем уровне запроса SELECT, блокируются именно те строки, которые возвращает запрос; в случае запроса с объединением (JOIN) блокируются строки, участвующие в формировании результирующих строк соединения. Кроме того, блокируются строки, удовлетворяющие условиям запроса на момент создания снимка (snapshot), даже если они не будут возвращены, потому что были изменены после создания снимка и больше не соответствуют условиям запроса. Если используется LIMIT, блокировка прекращается, как только будет возвращено достаточное количество строк для удовлетворения ограничения (однако строки, пропущенные с помощью OFFSET, всё равно будут заблокированы). Аналогично, если клауза блокировки используется в запросе курсора, блокируются только те строки, которые фактически были извлечены или пропущены курсором.
Когда клауза блокировки появляется в подзапросе SELECT, блокируются строки, возвращаемые внешнему запросу данным подзапросом. Это может охватывать меньшее количество строк, чем можно было бы предположить при анализе одного подзапроса, поскольку условия из внешнего запроса могут использоваться для оптимизации выполнения подзапроса. Например:
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;будет блокировать только строки, имеющие col1 = 5, даже если это условие формально не входит в текст подзапроса.
Возможно, что команда SELECT, выполняемая на уровне изоляции транзакций READ COMMITTED и использующая ORDER BY вместе с клаузой блокировки, вернёт строки в неверном порядке. Это происходит потому, что сначала применяется ORDER BY. Команда сортирует результат, но затем может заблокироваться при попытке получения блокировки на одну или несколько строк. Как только SELECT разблокируется, значения столбцов сортировки могли уже измениться, из-за чего строки покажутся расположенными не по порядку (хотя они упорядочены по исходным значениям столбцов). Эту проблему можно обойти, поместив клаузу FOR UPDATE/SHARE внутрь подзапроса, например:
SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;Обратите внимание, что в этом случае будут заблокированы все строки таблицы mytable, тогда как при использовании FOR UPDATE на верхнем уровне блокируются только реально возвращённые строки. Это может привести к существенной разнице в производительности, особенно если ORDER BY комбинируется с LIMIT или другими ограничениями. Поэтому данный метод рекомендуется использовать только в тех случаях, когда ожидаются параллельные изменения значений столбцов сортировки и требуется строго упорядоченный результат.
На уровнях изоляции REPEATABLE READ или SERIALIZABLE это привело бы к сбою сериализации (с кодом ошибки SQLSTATE 40001), поэтому при этих уровнях изоляции невозможно получить строки в неверном порядке.
Команда
TABLE <name>эквивалентна
SELECT * FROM <name>Она может использоваться как команда верхнего уровня или как компактный синтаксис в частях сложных запросов. С командой TABLE можно использовать только следующие клаузы блокировки: WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH и FOR; использовать WHERE или любые формы агрегации нельзя.
Чтобы объединить таблицу films с таблицей distributors:
SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, JOIN films f USING (did);Чтобы просуммировать столбец length всех фильмов и сгруппировать результаты по полю kind:
SELECT kind, sum(length) AS total FROM films GROUP BY kind;Чтобы просуммировать столбец length всех фильмов, сгруппировать результаты по полю kind и показать только те группы, сумма которых меньше 5 часов:
SELECT kind, sum(length) AS total FROM films GROUP BY kind
HAVING sum(length) < interval '5 hours';Вычислить промежуточные и общие итоги всех продаж по видам фильмов и дистрибьюторам:
SELECT kind, distributor, sum(prc*qty) FROM sales
GROUP BY ROLLUP(kind, distributor)
ORDER BY 1,2,3;Вычислить ранг дистрибьюторов фильмов на основе общего объема продаж:
SELECT distributor, sum(prc*qty),
rank() OVER (ORDER BY sum(prc*qty) DESC)
FROM sales
GROUP BY distributor ORDER BY 2 DESC;Следующие два примера демонстрируют одинаковые способы сортировки отдельных результатов по содержимому второго столбца (name):
SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;Следующий пример показывает, как получить объединение таблиц distributors и actors, ограничив результаты строками, начинающимися с буквы W в каждой таблице. Требуются только уникальные строки, поэтому ключевое слово ALL опущено:
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%';Этот пример показывает использование функции в предложении FROM, как с указанием списка определений столбцов, так и без него:
CREATE FUNCTION distributors(int) RETURNS SETOF distributors
AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE
SQL;
SELECT * FROM distributors(111);
CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS
$$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE
SQL;
SELECT * FROM distributors_2(111) AS (dist_id int, dist_name text);Этот пример использует функцию с добавленным порядковым столбцом ordinality:
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
This example uses a simple WITH clause:
WITH test AS (
SELECT random() as x FROM generate_series(1, 3)
)
SELECT * FROM test
UNION ALL
SELECT * FROM test; Этот пример использует предложение WITH для отображения итоговых продаж по продуктам только в регионах с наибольшими продажами:
WITH regional_sales AS
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales) FROM
regional_sales)
)
SELECT region, product, SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;Данный пример можно было бы написать без предложения WITH, однако потребовалось бы использовать два уровня вложенных подзапросов SELECT.
Этот пример использует предложение WITH RECURSIVE для поиска всех подчинённых (прямых и косвенных) сотрудника Мэри и их уровня косвенности из таблицы, содержащей только прямых подчинённых:
WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
SELECT 1, employee_name, manager_name
FROM employee
WHERE manager_name = 'Mary'
UNION ALL
SELECT er.distance + 1, e.employee_name, e.manager_name
FROM employee_recursive er, employee e
WHERE er.employee_name = e.manager_name
)
SELECT distance, employee_name FROM employee_recursive;Типичная форма рекурсивного запроса — начальное условие, за которым следует UNION [ALL], а затем рекурсивная часть запроса. Убедитесь, что рекурсивная часть запроса в конечном итоге вернёт пустой набор строк, иначе запрос будет выполняться бесконечно. Дополнительные примеры см. в разделе «WITH-запросы (общие табличные выражения)» Руководства администратора баз данных.
В этом примере используется LATERAL для применения функции, возвращающей набор значений get_product_names(), к каждой строке таблицы manufacturers:
SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;Производители, у которых в данный момент нет продуктов, не будут отображаться в результате, поскольку используется внутреннее соединение. Если мы хотим включить в результат названия таких производителей, запрос можно переписать следующим образом:
SELECT m.name AS mname, pname
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;Оператор SELECT соответствует стандарту SQL, однако имеются некоторые расширения и отсутствующие возможности.
Отсутствующие предложения FROM
Database позволяет опускать предложение FROM. Это удобно для вычисления результатов простых выражений. Например:
SELECT 2+2;Некоторые другие СУБД SQL не могут этого делать, кроме как путём введения фиктивной таблицы из одной строки, из которой выполняется SELECT.
Обратите внимание, что если предложение FROM не указано, запрос не может ссылаться ни на одну таблицу базы данных. Например, следующий запрос является недопустимым:
SELECT distributors.* WHERE distributors.name = 'Westward';В более ранних версиях установка серверного параметра конфигурации add_missing_from в true позволяла Database автоматически добавлять неявную запись в предложение FROM для каждой таблицы, на которую ссылается запрос. Сейчас это больше не допускается.
Пустые списки SELECT
Список выходных выражений после SELECT может быть пустым, что приводит к созданию результирующей таблицы с нулём столбцов. Согласно стандарту SQL такой синтаксис недопустим. Database разрешает его для обеспечения согласованности с возможностью существования таблиц с нулём столбцов. Однако пустой список не разрешается при использовании DISTINCT.
Пропуск ключевого слова AS
В стандарте SQL необязательное ключевое слово AS можно опускать перед именем выходного столбца, если новое имя столбца является допустимым именем столбца (то есть не совпадает ни с одним зарезервированным ключевым словом). Database несколько строже: AS требуется, если новое имя столбца совпадает с любым ключевым словом, будь то зарезервированное или нет. Рекомендуется использовать AS либо заключать имена выходных столбцов в двойные кавычки, чтобы избежать возможных конфликтов с будущими ключевыми словами.
В элементах FROM как стандарт, так и Database позволяют опускать AS перед псевдонимом, если он является незарезервированным ключевым словом. Однако это неудобно для имён выходных столбцов из-за синтаксических неоднозначностей.
ONLY и наследование
Стандарт SQL требует круглых скобок вокруг имени таблицы при записи ONLY, например:
SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ...Database считает эти скобки необязательными.
Database позволяет явно указывать завершающий * для явного задания поведения без ONLY, включающего дочерние таблицы. Стандарт не разрешает этого.
Примечание: вышеуказанные замечания одинаково применимы ко всем командам SQL, поддерживающим опцию ONLY.
Вызовы функций в FROM
Database позволяет указывать вызов функции непосредственно как элемент списка FROM. В стандарте SQL необходимо обернуть такой вызов функции в подзапрос SELECT; то есть синтаксис FROM func(...) alias приблизительно эквивалентен FROM LATERAL (SELECT func(...)) alias. Обратите внимание, что LATERAL считается неявным; это связано с тем, что стандарт требует семантики LATERAL для элемента UNNEST() в предложении FROM. Database рассматривает UNNEST() так же, как и другие функции, возвращающие набор значений.
Пространство имён, доступное для GROUP BY и ORDER BY
В стандарте SQL-92 в предложении ORDER BY можно использовать только имена выходных столбцов или их номера, тогда как в предложении GROUP BY можно использовать только выражения, основанные на входных именах столбцов. Database расширяет каждое из этих предложений, позволяя также и другой вариант (однако при неоднозначности применяется интерпретация стандарта). Database также позволяет обоим предложениям указывать произвольные выражения. Обратите внимание, что имена, встречающиеся в выражении, всегда интерпретируются как имена входных столбцов, а не выходных.
SQL:1999 и более поздние версии используют немного другое определение, которое не полностью обратно совместимо с SQL-92. Однако в большинстве случаев Database интерпретирует выражения ORDER BY или GROUP BY так же, как SQL:1999.
Функциональные зависимости
Database распознаёт функциональную зависимость (позволяющую исключать столбцы из GROUP BY) только в том случае, когда первичный ключ таблицы включён в список GROUP BY. Стандарт SQL определяет дополнительные условия, которые также должны распознаваться.
LIMIT и OFFSET
Предложения LIMIT и OFFSET являются специфичными для Database, также используются в MySQL. Стандарт SQL:2008 вводит предложения OFFSET .. FETCH {FIRST|NEXT} ... для аналогичной функциональности, как показано выше в разделе "Предложение LIMIT". Этот синтаксис также используется в IBM DB2. (Приложения для Oracle часто используют обходное решение, основанное на автоматически генерируемом столбце rownum, который недоступен в Database, чтобы реализовать эффект этих предложений.)
FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE
Хотя FOR UPDATE присутствует в стандарте SQL, стандарт разрешает его использовать только как опцию DECLARE CURSOR. Database дополнительно разрешает его в любом запросе SELECT, а также во вложенных подзапросах SELECT, но это является расширением. Варианты FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE, а также опции NOWAIT и SKIP LOCKED отсутствуют в стандарте.
Операторы модификации данных в WITH
Database позволяет использовать INSERT, UPDATE и DELETE в качестве WITH-запросов. Такая возможность отсутствует в стандарте SQL.
Нестандартные предложения
Предложение DISTINCT ON не определено в стандарте SQL.
ROWS FROM( ... ) является расширением стандарта SQL.
Опции MATERIALIZED и NOT MATERIALIZED предложения WITH являются расширениями стандарта SQL.
Ограниченное использование STABLE и VOLATILE функций
Чтобы предотвратить рассинхронизацию данных между сегментами в Database, любая функция, классифицированная как STABLE или VOLATILE, не может выполняться на уровне сегментной базы данных, если она содержит SQL или изменяет базу данных любым способом. Дополнительную информацию см. в разделе CREATE FUNCTION.