WITH Clause

Предложение WITH позволяет указать одно или несколько подзапросов, на которые можно ссылаться по имени в основном запросе. Подзапросы фактически действуют как временные таблицы или представления на протяжении основного запроса. Каждый подзапрос может быть оператором SELECT, TABLE, VALUES, INSERT, UPDATE или DELETE. При написании модифицирующего данные оператора (INSERT, UPDATE или DELETE) в WITH обычно включается предложение RETURNING. Выходные данные RETURNING, а не базовая таблица, которую изменяет оператор, формируют временную таблицу, которую читает основной запрос. Если RETURNING не указано, оператор все равно выполняется, но не производит вывода, поэтому его нельзя использовать как таблицу в основном запросе.

Для каждого запроса WITH необходимо указать имя (без указания схемы). При желании можно указать список имен столбцов; если это не указано, имена столбцов выводятся из подзапроса.

Если указано RECURSIVE, это позволяет подзапросу SELECT ссылаться на себя по имени. Такой подзапрос должен иметь следующую форму.

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

где рекурсивная само-ссылка должна появиться на правой стороне UNION. В одном запросе разрешена только одна рекурсивная само-ссылка. Рекурсивные операторы модификации данных не поддерживаются, но вы можете использовать результаты рекурсивного SELECT запроса в операторе модификации данных. См. Раздел 7.8 для примера.

Другой эффект RECURSIVE заключается в том, что запросы WITH не обязательно должны быть упорядочены: запрос может ссылаться на другой запрос, который находится позже в списке. (Однако циклические ссылки или взаимная рекурсия не реализованы). Без RECURSIVE запросы WITH могут ссылаться только на соседние запросы WITH, которые находятся раньше в списке WITH.

Когда в предложении WITH присутствует несколько запросов, RECURSIVE должно быть написано только один раз, сразу после WITH. Это применяется ко всем запросам в предложении WITH, хотя оно не влияет на запросы, которые не используют рекурсию или прямые ссылки.

Необязательная фраза SEARCH вычисляет столбец последовательности поиска, который может использоваться для упорядочивания результатов рекурсивного запроса в порядке поиска в ширину или в глубину. Предоставленный список имен столбцов указывает ключ строки, который будет использоваться для отслеживания посещенных строк. В результате запроса WITH будет добавлен столбец с именем search_seq_col_name в список столбцов результата. Этот столбец может быть упорядочен во внешнем запросе для достижения соответствующего порядка. См. Раздел 7.8.2.1 для примеров.

Необязательное предложение CYCLE используется для обнаружения циклов в рекурсивных запросах. Предоставленный список имен столбцов указывает ключ строки, который будет использоваться для отслеживания посещенных строк. В результате запроса WITH будет добавлен столбец с именем cycle_mark_col_name. Этот столбец будет установлен в значение cycle_mark_value, когда будет обнаружен цикл, иначе - в значение cycle_mark_default. Кроме того, обработка рекурсивного объединения будет остановлена, когда будет обнаружен цикл. cycle_mark_value и cycle_mark_default должны быть константами, и они должны быть приводимыми к общему типу данных, а тип данных должен иметь оператор неравенства. (Стандарт SQL требует, чтобы они были логическими константами или строками символов, но PostgreSQL не требует этого). По умолчанию используются значения TRUE и FALSE (типа boolean). Кроме того, в результате запроса WITH будет добавлен столбец с именем cycle_path_col_name. Этот столбец используется внутренне для отслеживания посещенных строк. См. примеры в разделе Раздел 7.8.2.2.

Предложения SEARCH и CYCLE допустимы только для рекурсивных запросов WITH. with_query должен быть UNION (или UNION ALL) из двух команд SELECT (или эквивалентных) (без вложенных UNION). Если используются обе Предложения, столбец, добавленный предложением SEARCH, появляется перед столбцами, добавленными предложением CYCLE.

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

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

Однако запрос WITH может быть помечен как NOT MATERIALIZED, чтобы удалить эту гарантию. В этом случае запрос WITH может быть объединен с основным запросом, как если бы он был простым подзапросом SELECT в разделе FROM основного запроса. Это приводит к дублированию вычислений, если основной запрос ссылается на этот запрос WITH более одного раза; но если каждое такое использование требует только нескольких строк из общего вывода запроса WITH, NOT MATERIALIZED может обеспечить сокращение затрат, позволяя оптимизировать запросы совместно. NOT MATERIALIZED игнорируется, если он применяется к рекурсивному запросу WITH или к запросу, содержащему побочные эффекты (т.е. не являющемуся простым SELECT, не содержащим волатильных функций).

По умолчанию, безопасный для побочных эффектов запрос WITH сворачивается в основной запрос, если он используется ровно один раз в предложении FROM основного запроса. Это позволяет совместную оптимизацию двух уровней запроса в ситуациях, когда это должно быть семантически невидимо. Однако такое сворачивание может быть предотвращено путем пометки запроса WITH как MATERIALIZED. Это может быть полезно, например, если запрос WITH используется в качестве оптимизационного барьера для предотвращения выбора плохого плана планировщиком. В версиях PostgreSQL до v12 такое сворачивание никогда не выполнялось, поэтому запросы, написанные для более старых версий, могут полагаться на WITH как на оптимизационный барьер.

См. Раздел 7.8 для получения дополнительной информации.

Условие FROM

Предложение FROM указывает одну или несколько исходных таблиц для SELECT. Если указано несколько источников, результатом будет декартово произведение (перекрестное соединение) всех источников. Но обычно добавляются условия квалификации (через WHERE), чтобы ограничить возвращаемые строки до небольшого подмножества декартова произведения.

Предложение FROM может содержать следующие элементы:

Предложение WHERE

Опциональная WHERE фраза имеет общую форму

WHERE condition

где условие - это любое выражение, которое вычисляется в результат типа boolean. Любая строка, которая не удовлетворяет этому условию, будет исключена из вывода. Строка удовлетворяет условию, если она возвращает true, когда фактические значения строки подставляются вместо любых ссылок на переменные.

Предложение GROUP BY

Опциональная GROUP BY фраза имеет общую форму

GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]

GROUP BY сгруппирует в одну строку все выбранные строки, которые имеют одинаковые значения для группирующих выражений. Выражение, используемое внутри элемента группировки, может быть именем входного столбца, именем или порядковым номером выходного столбца (SELECT элемент списка) или произвольным выражением, составленным из значений входных столбцов. В случае неоднозначности, имя GROUP BY будет интерпретировано как имя входного столбца, а не имя выходного столбца.

Если в качестве элементов группировки присутствуют GROUPING SETS, ROLLUP или CUBE, то весь GROUP BY определяет некоторое количество независимых grouping sets. В результате этого создается эквивалентное объединение UNION ALL между подзапросами с отдельными наборами группировки в качестве соответствующих предложений GROUP BY. Опциональное предложение DISTINCT удаляет дублирующиеся наборы перед обработкой; оно не преобразует UNION ALL в UNION DISTINCT. Дополнительные сведения о работе с наборами группировки см. в разделе Раздел 7.2.4.

Все агрегатные функции, если они используются, вычисляются для всех строк, составляющих каждую группу, и для каждой группы вычисляется отдельное значение. (Если есть агрегатные функции, но нет предложения GROUP BY, запрос рассматривается как имеющий одну группу, включающую все выбранные строки). Набор строк, передаваемых каждой агрегатной функции, может быть дополнительно отфильтрован с помощью предложения FILTER, присоединенного к вызову агрегатной функции; см. Раздел 4.2.7 для получения дополнительной информации. Когда присутствует предложение FILTER, входные данные для этой агрегатной функции включают только те строки, которые соответствуют этому выражению.

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

Учтите, что все агрегатные функции вычисляются перед вычислением любых скалярных выражений в предложении HAVING или списке SELECT. Это означает, что, например, выражение CASE не может использоваться для пропуска вычисления агрегатной функции; см. Раздел 4.2.14.

В настоящее время, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE не могут быть указаны с GROUP BY.

Предложение HAVING

Опциональная фраза HAVING имеет общую форму

HAVING 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

Опциональная фраза 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 ]

Если указано существующее имя окна existing_window_name, оно должно ссылаться на ранее введенную запись в списке WINDOW; новое окно копирует предложение секционирования из этой записи, а также предложение сортировки, если таковые имеются. В этом случае новое окно не может указывать свою собственную часть PARTITION BY, и оно может указывать ORDER BY только в том случае, если скопированное окно его не имеет. Новое окно всегда использует свою собственную часть рамки; скопированное окно не должно указывать часть рамки.

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

Аналогично, элементы списка ORDER BY интерпретируются примерно так же, как элементы предложения ORDER BY на уровне оператора, за исключением того, что выражения всегда рассматриваются как простые выражения и никогда не являются именем или номером выходного столбца.

Необязательное предложение frame_clause определяет рамку окна для оконных функций, которые зависят от рамки (не все функции зависят). рамка окна - это набор связанных строк для каждой строки запроса (называемой текущей строкой). 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. Ограничения заключаются в том, что UNBOUNDED FOLLOWING нельзя указать как frame_start, а UNBOUNDED PRECEDING - в качестве frame_end, и frame_end не может идти раньше, чем frame_start в списке вышеуказанных вариантов frame_start и frame_end - например, RANGE BETWEEN CURRENT ROW AND offset PRECEDING не допускается.

Параметр по умолчанию для ограничения рамки - RANGE UNBOUNDED PRECEDING, что эквивалентно RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; это устанавливает рамку, которая включает все строки от начала секции до последней строки peer (строки, которые в предложении ORDER BY окна считаются эквивалентными текущей строке; все строки являются peer, если нет 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 зависит от типа данных столбца сортировки. Для числовых столбцов сортировки он обычно имеет тот же тип, что и столбец сортировки, но для столбцов сортировки даты и времени это interval. Во всех этих случаях значение offset должно быть не нулевым и неотрицательным. Кроме того, хотя offset не обязательно должен быть простой константой, он не может содержать переменные, агрегатные функции или оконные функции.

Опция frame_exclusion позволяет исключить строки вокруг текущей строки из рамки, даже если они должны быть включены в соответствии с настройками начала и конца рамки. EXCLUDE CURRENT ROW исключает текущую строку из рамки. EXCLUDE GROUP исключает текущую строку и родственные ей строки из рамки. EXCLUDE TIES исключает любые родственные строки текущей строки из рамки, но не саму текущую строку. EXCLUDE NO OTHERS явно указывает на стандартное поведение, когда текущая строка и ее родственные строки не исключаются.

Будьте осторожны, что режим ROWS может привести к непредсказуемым результатам, если порядок ORDER BY не упорядочивает строки уникальным образом. Режимы RANGE и GROUPS разработаны для обеспечения одинаковой обработки строк, которые являются пирами в порядке ORDER BY: все строки данной группы пиров будут включены в рамку или исключены из нее.

Целью предложения WINDOW является указание поведения оконных функций, которые появляются в списке SELECT запроса или в предложении ORDER BY. Эти функции могут ссылаться на записи предложения WINDOW по имени в своих предложениях OVER. Однако запись предложения WINDOW не обязательно должна быть использована где-либо; если она не используется в запросе, она просто игнорируется. Возможно использование оконных функций без предложения WINDOW вообще, так как вызов оконной функции может указывать свое определение окна непосредственно в своем предложении OVER. Однако, предложение WINDOW экономит набор, когда одно и то же определение окна необходимо для нескольких оконных функций.

В настоящее время, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE и FOR KEY SHARE не могут быть указаны с WINDOW.

Все функции окон подробно описаны в Раздел 3.5, Раздел 4.2.8 и Раздел 7.2.5.

SELECT Список

Список SELECT (между ключевыми словами SELECT и FROM) определяет выражения, которые формируют выходные строки оператора SELECT. Выражения могут (и обычно) ссылаться на столбцы, вычисленные в предложении FROM.

Как и в таблице, у каждого выходного столбца SELECT есть имя. В простом SELECT это имя просто используется для маркировки столбца для отображения, но когда SELECT является подзапросом более крупного запроса, имя видно более крупному запросу как имя столбца виртуальной таблицы, созданной подзапросом. Чтобы указать имя для выходного столбца, напишите AS имя_вывода после выражения столбца. (Вы можете опустить AS, но только если желаемое имя вывода не совпадает с любым ключевым словом Tantor BE (см. Предметный указатель C). Для защиты от возможного добавления ключевых слов в будущем рекомендуется всегда либо писать AS, либо заключать имя вывода в двойные кавычки). Если вы не указываете имя столбца, Tantor BE выбирает имя автоматически. Если выражение столбца является простым ссылкой на столбец, то выбранное имя совпадает с именем этого столбца. В более сложных случаях может использоваться имя функции или типа, или система может прибегнуть к генерируемому имени, такому как ?column?.

Выводимое имя столбца можно использовать для ссылки на значение столбца в предложениях ORDER BY и GROUP BY, но не в предложениях WHERE или HAVING; в этих случаях необходимо написать выражение полностью.

Вместо выражения в выходном списке можно написать * в качестве сокращения для всех столбцов выбранных строк. Также можно написать table_name.* в качестве сокращения для столбцов, которые идут только из этой таблицы. В этих случаях нельзя указать новые имена с помощью AS; имена выходных столбцов будут такими же, как имена столбцов таблицы.

Согласно стандарту SQL, выражения в списке вывода должны быть вычислены перед применением DISTINCT, ORDER BY или LIMIT. Это, очевидно, необходимо при использовании DISTINCT, поскольку иначе неясно, какие значения делаются уникальными. Однако во многих случаях удобно, чтобы выражения вывода вычислялись после ORDER BY и LIMIT, особенно если список вывода содержит волатильные или дорогостоящие функции. С таким поведением порядок вычисления функций более интуитивен, и не будет вычислений, соответствующих строкам, которые не появляются в выводе. Tantor BE эффективно вычисляет выражения вывода после сортировки и ограничения, при условии, что эти выражения не используются в DISTINCT, ORDER BY или GROUP BY. (В качестве противопримера, SELECT f(x) FROM tab ORDER BY 1 явно должен вычислить f(x) перед сортировкой). Выражения вывода, содержащие функции, возвращающие наборы, эффективно вычисляются после сортировки и перед ограничением, так что LIMIT будет обрезать вывод от функции, возвращающей набор.

Условие DISTINCT

Если указано SELECT DISTINCT, все повторяющиеся строки удаляются из результирующего набора (остается одна строка из каждой группы дубликатов). SELECT ALL указывает на обратное: все строки сохраняются; это значение по умолчанию.

SELECT DISTINCT ON ( выражение [, ...] ) сохраняет только первую строку из каждого набора строк, где заданные выражения оцениваются как равные. Выражения 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

Предложение 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

Предложение 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

Предложение 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 имеет следующую общую форму:

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.

Опционально можно добавить ключевое слово ASC (возрастающий) или DESC (убывающий) после любого выражения в предложении ORDER BY. Если не указано, по умолчанию предполагается ASC. В качестве альтернативы, в операторе USING можно указать конкретное имя оператора сортировки. Оператор сортировки должен быть оператором "меньше" или "больше" в некоторой семье операторов B-дерева. Обычно 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.

Символьные строки сортируются в соответствии с правилом сортировки, применяемым к сортируемому столбцу. При необходимости это можно изменить, включив в предложение COLLATE вместе с expression, например ORDER BY mycolumn COLLATE "en_US". Дополнительную информацию см. в разделах Раздел 4.2.10 и Раздел 22.2.

Предложение LIMIT

Предложение LIMIT состоит из двух независимых вложенных предложений:

LIMIT { count | ALL }
OFFSET start

Параметр count определяет максимальное количество строк для возврата, в то время как start указывает количество строк, которые нужно пропустить перед началом возврата строк. Когда оба параметра указаны, сначала пропускается start строк перед началом подсчета count строк, которые будут возвращены.

Если выражение count вычисляется как NULL, оно рассматривается как LIMIT ALL, то есть без ограничений. Если start вычисляется как NULL, он рассматривается так же, как OFFSET 0.

SQL:2008 ввел другой синтаксис для достижения того же результата, который также поддерживает Tantor BE. Он выглядит так:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

В этом синтаксисе значение start или count является обязательным в соответствии со стандартом и может быть литеральной константой, параметром или именем переменной; как расширение Tantor BE, допускаются и другие выражения, но обычно они должны быть заключены в скобки, чтобы избежать неоднозначности. Если в предложении FETCH не указано значение count, оно по умолчанию равно 1. Опция WITH TIES используется для возврата всех дополнительных строк, которые занимают последнее место в результате согласно предложению ORDER BY; в этом случае ORDER BY обязателен, а SKIP LOCKED не разрешается. Слова ROW и ROWS, а также FIRST и NEXT являются шумовыми словами, которые не влияют на результат выполнения этих предложений. Согласно стандарту, предложение OFFSET должно предшествовать предложению FETCH, если оба присутствуют; но Tantor BE более гибок и позволяет использовать любой порядок.

При использовании 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 блокирует строки при их получении из таблицы.

Предложение блокировки имеет общую форму

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

где lock_strength можно записать одним из следующих способов

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

Для получения дополнительной информации о каждом режиме блокировки на уровне строк см. Раздел 13.3.2.

Чтобы предотвратить ожидание завершения других транзакций перед выполнением операции, используйте опцию NOWAIT или SKIP LOCKED. С опцией NOWAIT оператор сообщает об ошибке, а не ожидает, если выбранная строка не может быть немедленно заблокирована. С опцией SKIP LOCKED пропускаются все выбранные строки, которые не могут быть немедленно заблокированы. Пропуск заблокированных строк обеспечивает несогласованное представление данных, поэтому это не подходит для общего использования, но может быть использовано для избежания конфликтов блокировки при доступе нескольких потребителей к таблице-очереди. Обратите внимание, что опции NOWAIT и SKIP LOCKED применяются только к блокировкам на уровне строк, а не к требуемой блокировке таблицы на уровне строк ROW SHARE (см. Глава 13). Если вам нужно получить блокировку таблицы без ожидания, вы можете сначала использовать LOCK с опцией NOWAIT.

Если в блокирующем предложении указаны конкретные таблицы, то блокируются только строки, приходящие из этих таблиц; любые другие таблицы, используемые в SELECT, просто читаются как обычно. Блокирующее предложение без списка таблиц влияет на все таблицы, используемые в операторе. Если блокирующее предложение применяется к представлению или подзапросу, она влияет на все таблицы, используемые в представлении или подзапросе. Однако эти предложения применяются к запросам WITH, на которые ссылается основной запрос. Если нужно, чтобы блокировка строк происходила внутри запроса WITH, укажите блокирующее предложение внутри запроса WITH.

Можно написать несколько блокировок, если необходимо указать разное поведение блокировки для разных таблиц. Если одна и та же таблица упоминается (или неявно затрагивается) более чем одним блокировочным оператором, она обрабатывается так, как если бы она была указана только самым сильным. Аналогично, таблица обрабатывается как NOWAIT, если это указано в любом из затрагивающих ее операторов. В противном случае, таблица обрабатывается как SKIP LOCKED, если это указано в любом из затрагивающих ее операторов.

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

Когда предложение блокировки появляется на верхнем уровне запроса SELECT, строки, которые заблокированы, точно те, которые возвращаются запросом; в случае запроса с соединением, заблокированы будут строки, которые вносят вклад в возвращаемые строки соединения. Кроме того, строки, которые удовлетворяли условиям запроса на момент снимка запроса, будут заблокированы, хотя они не будут возвращены, если они были обновлены после снимка и больше не удовлетворяют условиям запроса. Если используется LIMIT, блокировка останавливается, как только будет возвращено достаточное количество строк для удовлетворения лимита (но обратите внимание, что строки, прне указанные с помощью OFFSET, будут заблокированы). Аналогично, если предложение блокировки используется в запросе курсора, будут заблокированы только строки, фактически выбранные или прне указанные курсором.

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

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

будет блокировать только строки, у которых col1 = 5, даже если это условие не находится текстом в подзапросе.

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

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

не смог бы сохранить блокировку FOR UPDATE после ROLLBACK TO. Это было исправлено в версии 9.3.

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;

Команда TABLE

Команда

TABLE name

эквивалентно

SELECT * FROM name

Это может использоваться как команда верхнего уровня или как синтаксический вариант, экономящий пространство, в частях сложных запросов. Только с предложениями блокировки WITH, UNION, INTERSECT, EXCEPT, ORDER BY, LIMIT, OFFSET, FETCH и FOR можно использовать TABLE; предложение WHERE и любая форма агрегации не могут быть использованы.

Прне указаны предложения FROM

Tantor BE позволяет опустить предложение FROM. Это имеет простое применение для вычисления результатов простых выражений:

SELECT 2+2;

 ?column?
----------
        4

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

Пустые списки SELECT

Список выходных выражений после SELECT может быть пустым, что приводит к созданию таблицы результатов с нулевым количеством столбцов. Это не является допустимым синтаксисом согласно стандарту SQL. Tantor BE позволяет это, чтобы быть согласованным с возможностью создания таблиц с нулевым количеством столбцов. Однако пустой список не допускается при использовании DISTINCT.

Пропуская ключевое слово AS

В стандарте SQL, необязательное ключевое слово AS можно опустить перед именем выходной колонки, если новое имя колонки является допустимым именем колонки (то есть не совпадает с зарезервированным ключевым словом). Tantor BE немного более строгий: AS требуется, если новое имя колонки совпадает с любым ключевым словом, зарезервированным или нет. Рекомендуется использовать AS или обрамлять имена выходных колонок в двойные кавычки, чтобы предотвратить возможные конфликты с будущими добавлениями ключевых слов.

В элементах FROM оба стандарт и Tantor BE позволяют опускать AS перед псевдонимом, который является незарезервированным ключевым словом. Но это непрактично для имен выходных столбцов из-за синтаксической неоднозначности.

Пропуск под-SELECT псевдонимов в FROM

Согласно стандарту SQL, под-SELECT в списке FROM должен иметь псевдоним. В Tantor BE этот псевдоним может быть опущен.

ONLY и Наследование

Стандарт SQL требует наличия скобок вокруг имени таблицы при использовании ключевого слова ONLY, например SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... Tantor BE считает эти скобки необязательными.

Tantor BE позволяет добавить в конец *, чтобы явно указать, что включение дочерних таблиц происходит без использования ONLY. Стандарт не разрешает это.

(Эти пункты одинаково применимы ко всем SQL-командам, поддерживающим опцию ONLY).

Ограничения для предложения TABLESAMPLE

Предложение TABLESAMPLE в настоящее время принимается только для обычных таблиц и материализованных представлений. Согласно стандарту SQL, ее можно применять к любому элементу FROM.

Вызовы функций в FROM

Tantor BE позволяет вызывать функцию напрямую в качестве элемента списка FROM. В стандарте SQL для этого необходимо обернуть вызов функции в подзапрос SELECT; то есть синтаксис FROM func(...) alias примерно эквивалентен FROM LATERAL (SELECT func(...)) alias. Обратите внимание, что LATERAL считается неявным; это связано с тем, что стандарт требует использования семантики LATERAL для элемента UNNEST() в FROM. Tantor BE обрабатывает UNNEST() так же, как и другие функции, возвращающие наборы значений.

Пространство имен доступно для GROUP BY и ORDER BY

В стандарте SQL-92, в предложении ORDER BY можно использовать только имена или номера выходных столбцов, в то время как в предложении GROUP BY можно использовать только выражения, основанные на именах входных столбцов. Tantor BE расширяет каждое из этих предложений, чтобы также разрешить другой выбор (но использует интерпретацию стандарта в случае неоднозначности). Tantor BE также позволяет обоим предложениям указывать произвольные выражения. Обратите внимание, что имена, появляющиеся в выражении, всегда будут приниматься как имена входных столбцов, а не имена выходных столбцов.

SQL:1999 и более поздние версии используют немного другое определение, которое не является полностью обратно совместимым с SQL-92. Однако в большинстве случаев Tantor BE будет интерпретировать выражение ORDER BY или GROUP BY так же, как и SQL:1999.

Функциональные зависимости

Tantor BE распознает функциональную зависимость (позволяя опускать столбцы из GROUP BY) только тогда, когда первичный ключ таблицы включен в список GROUP BY. Стандарт SQL указывает дополнительные условия, которые должны быть распознаны.

LIMIT и OFFSET

Предложения LIMIT и OFFSET являются специфичным синтаксисом Tantor BE, также используемым MySQL. Стандарт SQL:2008 ввел предложения OFFSET ... FETCH {FIRST|NEXT} ... для той же функциональности, как показано выше в LIMIT Clause. Этот синтаксис также используется IBM DB2. (Приложения, написанные для Oracle, часто используют обходное решение, включающее автоматически генерируемый столбец rownum, который недоступен в PostgreSQL, для реализации эффектов этих предложений).

FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE

Хотя FOR UPDATE присутствует в стандарте SQL, стандарт позволяет его использовать только как опцию DECLARE CURSOR. Tantor BE позволяет его использовать в любом запросе SELECT, а также в подзапросах, но это расширение. Варианты FOR NO KEY UPDATE, FOR SHARE и FOR KEY SHARE, а также опции NOWAIT и SKIP LOCKED не присутствуют в стандарте.

Изменение данных в WITH

Tantor BE позволяет использовать запросы INSERT, UPDATE и DELETE в качестве запросов WITH. Это не предусмотрено в стандарте SQL.

Нестандартные условия

DISTINCT ON ( ... ) - это расширение стандарта SQL.

ROWS FROM( ... ) - это расширение стандарта SQL.

Варианты MATERIALIZED и NOT MATERIALIZED в WITH являются расширениями стандарта SQL.