В какой последовательности могут идти ключевые слова в запросе

17 правил для составления оптимального ЗАПРОСа к данным базы 1С

Для формирования и выполнения запросов к таблицам базы данных в платформе 1С используется специальный объект языка программирования Запрос. Создается этот объект вызовом конструкции Новый Запрос. Запрос удобно использовать, когда требуется получить сложную выборку данных, сгруппированную и отсортированную необходимым образом. Классический пример применения запроса — получение сводки по состоянию регистра накопления на определенный момент времени. Так же, механизм запросов позволяет легко получать информацию в различных временных разрезах.

Текст запроса – это инструкция, в соответствии с которой должен быть выполнен запрос. В тексте запроса описывается:

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

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

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

Рекомендации по написанию запросов к базе данных на языке запросов платформы 1С:

1) Текст запроса может содержать предопределенные данные конфигурации, такие как:

• значения перечислений;
• предопределенные данные:
• справочников;
• планов видов характеристик;
• планов счетов;
• планов видов расчетов;
• пустые ссылки;
• значения точек маршрута бизнес-процессов.

Также текст запроса может содержать значения системных перечислений, которые могут быть присвоены полям в таблицах базы данных: ВидДвиженияНакопления, ВидСчета и ВидДвиженияБухгалтерии. Обращение в запросах к предопределенным данным конфигурации и значениям системных перечислений осуществляется с помощью литерала функционального типа ЗНАЧЕНИЕ. Данный литерал позволяет повысить удобочитаемость запроса и уменьшить количество параметров запроса.

Пример использования литерала ЗНАЧЕНИЕ:

• ГДЕ Город = ЗНАЧЕНИЕ(Справочник.Города.Москва)
• ГДЕ Город = ЗНАЧЕНИЕ(Справочник.Города.ПустаяСсылка)
• ГДЕ ТипТовара = ЗНАЧЕНИЕ(Перечисление.ВидыТоваров.Услуга)
• ГДЕ ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
• ГДЕ ТочкаМаршрута = ЗНАЧЕНИЕ(БизнесПроцесс.БизнесПроцесс1.ТочкаМаршрута.Действие1

2) Использование инструкции АВТОУПОРЯДОЧИВАНИЕ в запросе может сильно время выполнения запроса, поэтому, если сортировка не требуется, то лучше вообще ее не использовать. Во большинстве случаях лучше всего применять сортировку с помощью инструкции УПОРЯДОЧИТЬ ПО.

Автоупорядочивание работает по следующим принципам:

• Если в запросе было указано предложение УПОРЯДОЧИТЬ ПО, то каждая ссылка на таблицу, находящаяся в этом предложении, будет заменена полями, по которым по умолчанию сортируется таблица (для справочников это код или наименование, для документов – дата документа). Если поле для упорядочивания ссылается на иерархический справочник, то будет применена иерархическая сортировка по этому справочнику.
• Если в запросе отсутствует предложение УПОРЯДОЧИТЬ ПО, но есть предложение ИТОГИ, тогда результат запроса будет упорядочен по полям, присутствующим в предложении ИТОГИ после ключевого слова ПО, в той же последовательности и, в случае если итоги рассчитывались по полям – ссылкам, то по полям сортировки по умолчанию таблиц, на которые были ссылки.
• Если в запросе отсутствуют предложения УПОРЯДОЧИТЬ ПО и ИТОГИ, но есть предложение СГРУППИРОВАТЬ ПО, тогда результат запроса будет упорядочен по полям, присутствующим в предложении, в той же последовательности и, в случае если группировка велась по полям – ссылкам, то по полям сортировки по умолчанию таблиц, на которые были ссылки.
• В случае же, если в запросе отсутствуют предложения и УПОРЯДОЧИТЬ ПО, ИТОГИ и СГРУППИРОВАТЬ ПО, результат будет упорядочен по полям сортировки по умолчанию для таблиц, из которых выбираются данные, в порядке их появления в запросе.
• В случае, если запрос содержит предложение ИТОГИ, каждый уровень итогов упорядочивается отдельно.

3) Что бы избежать повторного запроса к базе данных при выводе результата запроса пользователю (например, построение запроса или отображение результата запроса с помощью табличного документа) полезно использовать инструкцию ПРЕДСТАВЛЕНИЕССЫЛКИ, которая позволяет получать представление ссылочного значения. Пример:

 ВЫБРАТЬ 
ПРЕДСТАВЛЕНИЕССЫЛКИ(РасходнаяНакладнаяСостав.Номенклатура) КАК НоменклатураПредставление

Так же возможно использование инструкции ПРЕДСТАВЛЕНИЕ — предназначена для получения строкового представления значения произвольного типа. Отличие этих инструкций в том, что в первом случае, если инструкции передать ссылку, результатом будет строка, В остальных случаях результатом будет значение переданного параметра. Во втором случае, результатом инструкции всегда будет строка!

4) Если в запросе имеется поле с составным типом, то для таких полей возникает необходимость привести значения поля к какому-либо определенному типу с помощью инструкции ВЫРАЗИТЬ, что позволит убрать лишние таблицы из левого соединения с полем составного типа данных и ускорить выполнение запроса. Пример:

Имеется регистра накопления ОстаткиТоваров, у которого поле Регистратор имеет составной тип. В запросе выбираются Дата и Номер документов ПоступлениеТоваров, при этом при обращении к реквизитам документа через поле Регистратор не происходит множество левых соединений таблицы регистра накопления с таблицами документов-регистраторов.

 ВЫБРАТЬ 
ВЫРАЗИТЬ(ОстаткиТоваров.Регистратор КАК Документ.ПоступлениеТоваров).Номер КАК НомерПоступления, 
ВЫРАЗИТЬ(ОстаткиТоваров.Регистратор КАК Документ.ПоступлениеТоваров).Дата КАК ДатаПоступления 
ИЗ 
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров

Если приведение типа считается не осуществимым, то результатом приведения типа будет значение NULL.

5) Не стоит забывать про инструкцию РАЗРЕШЕННЫЕ, которая означает, что запрос выберет только те записи, на которые у текущего пользователя есть права. Если данное слово не указать, то в случае, когда запрос выберет записи, на которые у пользователя нет прав, запрос отработает с ошибкой.

6) В случае, если в запросе используется объединение, и в некоторых частях объединения присутствуют вложенные таблицы (документ с табличной частью), а в некоторых нет, возникает необходимость дополнения списка выборки полями – пустыми вложенными таблицами. Делается это при помощи ключевого слова ПУСТАЯТАБЛИЦА, после которого в скобках указываются псевдонимы полей, из которых будет состоять вложенная таблица. Пример:

 // Выбрать поля Номер и Состав 
// из виртуальной таблицы Документ.РасхНакл 
ВЫБРАТЬ Ссылка.Номер, ПУСТАЯТАБЛИЦА.(Ном, Тов, Кол) КАК Состав 
ИЗ Документ.РасхНакл 
ОБЪЕДИНИТЬ ВСЕ 
ВЫБРАТЬ Ссылка.Номер, Состав.(НомерСтроки, Товар, Количество) 
ИЗ Документ.РасхНакл Документ.РасходнаяНакладная.Состав.*

7) Что бы в результат запроса не попали повторяющиеся строки, следует использовать инструкцию РАЗЛИЧНЫЕ, потому что так нагляднее и понятнее, а инструкция СГРУППИРОВАТЬ ПО применяется для группировки с помощью агрегатных функций. Ксати, при использовании агрегатных функций предложение СГРУППИРОВАТЬ ПО может быть и не указано совсем, при этом все результаты запроса будут сгруппированы в одну единственную строку. Пример:

 // Необходимо узнать, каким вообще контрагентам 
// отгружался товар за период. 
Выбрать Различные 
Документ.РасходнаяНакладная.Контрагент

8) Инструкция СГРУППИРОВАТЬ ПО позволяет обращаться к полям верхнего уровня, без группировки результатов по этим полям, если агрегатные функции применены к полям вложенной таблицы. Хотя в справке 1С написано, при группировке результатов запроса в списке полей выборки обязательно должны быть указаны агрегатные функции, а помимо агрегатных функций в списке полей выборки допускается указывать только поля, по которым осуществляется группировка. Пример:

 ВЫБРАТЬ 
ПоступлениеТоваровИУслуг.Товары.(СУММА(Количество),Номенклатура), 
ПоступлениеТоваровИУслуг.Ссылка, 
ПоступлениеТоваровИУслуг.Контрагент 
ИЗ 
Документ.ПоступлениеТоваровИУслуг КАК ПоступлениеТоваровИУслуг 
СГРУППИРОВАТЬ ПО 
ПоступлениеТоваровИУслуг.Товары.(Номенклатура)

9) Инструкция ЕСТЬNULL предназначена для замены значения NULL на другое значение, но не забываем, что второй параметр будет преобразован к типу первого в случае, если тип первого параметра является строкой или числом.

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

Пример (поиск документов, содержащих в табличной части определенный товар):

 ВЫБРАТЬ 
Приходная.Ссылка 
ИЗ 
Документ.Приходная Где Приходная.Товары.Номенклатура =Номенклатура.

Преимущество этого запроса перед запросом к подчиненной таблице Приходная.Товары в том, что если есть дубли в документах, результат запроса вернет только уникальные документы без использования ключевого слова РАЗЛИЧНЫЕ.

11) Интересный вариант оператора В — это проверка вхождения упорядоченного набора в множество таких наборов (Поле1, Поле2, . , ПолеN) В (Поле1, Поле2, . , ПолеN).

 ВЫБРАТЬ 
Контрагенты.Ссылка 
ГДЕ 
(Контрагенты.Ссылка, Товары.Ссылка) В 
(ВЫБРАТЬ Продажи.Покупатель, Продажи.Товар 
ИЗ РегистрНакопления.Продажи КАК Продажи) 
ИЗ 
Справочник.Контрагенты, 
Справочник.Товары

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

Разработчик может самостоятельно получить те же самые данные, которые система предоставляет ему в качестве виртуальных таблиц, однако алгоритм получения этих данных не будет оптимизирован, так как:

Все виртуальные таблицы параметризованы, т. е. разработчику предоставляется возможность задать некоторые параметры, которые система будет использовать при формировании запроса создания виртуальной таблицы. В зависимости от того, какие параметры виртуальной таблицы указаны разработчиком, система может формировать РАЗЛИЧНЫЕ запросы для получения одной и той же виртуальной таблицы, причем они будут оптимизированы с точки зрения переданных параметров.

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

13) В клиент-серверном варианте работы функция ПОДСТРОКА() реализуется при помощи функции SUBSTRING() соответствующего оператора SQL, передаваемого серверу баз данных SQL Server, который вычисляет тип результата функции SUBSTRING() по сложным правилам в зависимости от типа и значений ее параметров, а так же в зависимости от контекста, в котором она используется. В большинстве случаев эти правила не оказывают влияния на выполнение запроса, но бывают случаи, когда для выполнения запроса существенна максимальная длина строки результата, вычисленная SQL Server. Важно иметь в виду, что в некоторых контекстах использования функции ПОДСТРОКА() максимальная длина ее результата может оказаться равной максимальной длине строки ограниченной длины, которая в SQL Server равна 4000 символам. Это может привести к неожиданному аварийному завершению выполнения запроса:

Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.

HRESULT=80040E14, SQLSTATE=42000, native=8618

Чтобы избежать такой ошибки, не рекомендуют использовать функцию ПОДСТРОКА() с целью приведения строк неограниченной длины к строкам ограниченной длины. Вместо нее лучше использовать операцию приведения типа ВЫРАЗИТЬ().

14) С осторожностью используйте ИЛИ в конструкции ГДЕ, так как использование условия с ИЛИ может значительно «утяжелить» запрос. Решить проблему можно конструкцией ОБЪЕДИНИТЬ ВСЕ. Пример:

 ВЫБРАТЬ 
_ДемоКонтрагенты.НаименованиеПолное 
ИЗ 
Справочник._ДемоКонтрагенты КАК _ДемоКонтрагенты 
ГДЕ 
_ДемоКонтрагенты.Ссылка =Ссылка1 
ОБЪЕДИНИТЬ ВСЕ 
ВЫБРАТЬ 
_ДемоКонтрагенты.НаименованиеПолное 
ИЗ 
Справочник._ДемоКонтрагенты КАК _ДемоКонтрагенты 
ГДЕ 
_ДемоКонтрагенты.Ссылка =Ссылка2

15) Условие НЕ В в конструкции ГДЕ увеличивает время исполнения запроса, так как это своего рода НЕ (ИЛИ1 ИЛИ2 . ИЛИn), поэтому для больших таблиц старайтесь использовать ЛЕВОЕ СОЕДИНЕНИЕ с условием ЕСТЬ NULL. Пример:

 ВЫБРАТЬ 
_ДемоКонтрагенты.Ссылка 
ИЗ 
Справочник._ДемоКонтрагенты КАК _ДемоКонтрагенты 
ЛЕВОЕ СОЕДИНЕНИЕ Документ._ДемоЗаказПокупателя КАК _ДемоЗаказПокупателя 
ПО _ДемоКонтрагенты.Ссылка = _ДемоЗаказПокупателя.Контрагент 
ГДЕ 
_ДемоЗаказПокупателя.Контрагент ЕСТЬ NULL

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

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

17) Если вы не используете Менеджер временных таблиц, то явно удалять временную таблицу не требуется, она будет удалена после завершения выполнения пакетного запроса, иначе следует удалить временную таблицу одним из способов: командой УНИЧТОЖИТЬ в запросе, вызвать метод МенеджерВременныхТаблиц.Закрыть().

И в дополнении видео от Евгения Гилева : Типовые ошибки при написании запросов на 1С:

В какой последовательности могут идти ключевые слова в запросе

Войдите как ученик, чтобы получить доступ к материалам школы

Язык запросов 1С 8.3 для начинающих программистов: упорядочивание

Автор уроков и преподаватель школы: Владимир Милькин

• Упорядочивание в запросах
• Секция УПОРЯДОЧИТЬ ПО
• Возможность автоупорядочивания
  • Случай #1
  • Случай #2
  • Случай #3
  • Случай #4

  Упорядочивание в запросах

  Давайте напишем запрос, который получает из таблицы Справочник.Еда код и название еды:

  ВЫБРАТЬ Код, Наименование ИЗ Справочник.Еда

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

  С большой долей вероятности у вас получится следующий результат:

  

  Возможно вы удивитесь, но при таком написании запроса никто не гарантирует нам именно такого порядка выдачи записей в таблице. В случае использования клиент-серверного режима работы на различных СУБД результат мог бы быть и такой:

  

  

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

  Секция УПОРЯДОЧИТЬ ПО

  Поля по которым требуется упорядочить запрос перечисляются в секции УПОРЯДОЧИТЬ ПО через запятую:

  

  Следом за именем поля упорядочивания может идти одно из двух ключевых слов:

  • ВОЗР— упорядочивание по возрастанию.
  • УБЫВ — упорядочивание по убыванию.

  Если не указывать ни одно из этих слов, считается, что сортировка идёт по возрастанию.

  Вооружившись знаниями, давайте упорядочим результат нашего запроса по убыванию поля Код:

  ВЫБРАТЬ Код, Наименование ИЗ Справочник.Еда УПОРЯДОЧИТЬ ПО Код УБЫВ

  

  По возрастанию поля Наименование (алфавитный порядок):

  ВЫБРАТЬ Код, Наименование ИЗ Справочник.Еда УПОРЯДОЧИТЬ ПО Наименование ВОЗР

  

  А теперь упорядочим следующую таблицу

  так, чтобы сначала шла сортировка по полю Вкус по возрастанию, а затем (среди строчек с одинаковым значением поля Вкус) шла сортировка по полю Цвет по убыванию:

  ВЫБРАТЬ Вкус, Цвет ИЗ Справочник.Еда УПОРЯДОЧИТЬ ПО Вкус.Наименование ВОЗР, Цвет.Наименование УБЫВ

  

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

  Это связано с тем, что сортировка возможна лишь по полям имеющим один из типов: Строка, Число, Дата.

  А поля Вкус и Цвет являются ссылками на элементы справочников Вкус и Цвет, сортировать по которым не имеет смысла (в этом случае сортировка будет вестись по внутреннему идентификатору ссылки). Зато можно сделать сортировку по одному из реквизитов этих элементов. Наиболее подходящим в нашем случае будет строковый реквизит Наименование.

  Возможность автоупорядочивания
  

  Ключевое слово АВТОУПОРЯДОЧИВАНИЕ позволяет включить режим автоматического формирования полей для упорядочивания результатов запроса.

  Мы сейчас познакомимся с этой возможностью подробно, но сразу хочется оговориться, что фирма «1С» в своих методических рекомендациях не советует использовать её без необходимости (о причинах этого мы поговорим ниже).

  Прежде всего, ключевое слово АВТОУПОРЯДОЧИВАНИЕ может быть расположено в запросе сразу за или вместо секции УПОРЯДОЧИТЬ ПО:

  

  Автоупорядочивание работает по следующим принципам:

  Случай #1

  • есть секция УПОРЯДОЧИТЬ ПО

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

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

  

  Для таблиц документов полем сортировки по умолчанию является дата документа.

  ВЫБРАТЬ Наименование ИЗ Справочник.Клиенты УПОРЯДОЧИТЬ ПО ЛюбимыйЦвет АВТОУПОРЯДОЧИВАНИЕ

  Так как поле сортировки ЛюбимыйЦвет имеет тип Справочник.Цвета, в настройках которого основным представлением выбрано поле Наименование, то этот запрос эквивалентен:

  ВЫБРАТЬ Наименование ИЗ Справочник.Клиенты УПОРЯДОЧИТЬ ПО ЛюбимыйЦвет.Наименование

  Случай #2

  • отсутствует секция УПОРЯДОЧИТЬ ПО
  • но есть секция ИТОГИ ПО (её мы будем проходить позже)

  В этом случае результат запроса будет упорядочен по полям итогов (в той же последовательности).

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

  Случай #3

  • отсутствует секция УПОРЯДОЧИТЬ ПО
  • отсутствует секция ИТОГИ ПО
  • но есть секция СГРУППИРОВАТЬ ПО (группировку мы проходили здесь)

  В этом случае результат запроса будет упорядочен по полям группировки (в той же последовательности).

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

  ВЫБРАТЬ Город, СУММА(Количество) ИЗ Документ.ПрибытиеГостей СГРУППИРОВАТЬ ПО Город АВТОУПОРЯДОЧИВАНИЕ

  Так как поле группировки Город имеет тип Справочник.Города, в настройках которого основным представлением выбрано поле Наименование, то этот запрос эквивалентен:

  ВЫБРАТЬ Город, СУММА(Количество) ИЗ Документ.ПрибытиеГостей СГРУППИРОВАТЬ ПО Город УПОРЯДОЧИТЬ ПО Город.Наименование

  Случай #4

  Наконец, если в запросе:

  • отсутствует секция УПОРЯДОЧИТЬ ПО
  • отсутствует секция ИТОГИ ПО
  • отсутствует секция СГРУППИРОВАТЬ ПО

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

  ВЫБРАТЬ Код, Наименование ИЗ Справочник.Еда АВТОУПОРЯДОЧИВАНИЕ

  Так как данные выбираются из справочника Еда, в настройках которого основным представлением выбрано поле Наименование, то этот запрос будет эквивалентен:

  ВЫБРАТЬ Код, Наименование ИЗ Справочник.Еда УПОРЯДОЧИТЬ ПО Наименование

  Почему использование автоупорядочивания нежелательно
  

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

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

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

  А завтра фирма «1С» (или другой разработчик) изменят настройки базы в конфигураторе так, что полем сортировки по умолчанию для справочника Еда станет, к примеру, поле Код. И, если мы использовали автоупорядочивание в запросе, то наш отчет сломается, ведь порядок сортировки уже будет другим. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.

  Поэтому всегда старайтесь указывать конкретные поля и конкретный порядок сортировки по ним в секции УПОРЯДОЧИТЬ ПО, такой запрос уже не сломать просто так:

  ВЫБРАТЬ Код, Наименование ИЗ Справочник.Еда УПОРЯДОЧИТЬ ПО Наименование ВОЗР

  Разбор тестирования 1С:Профессионал и PMP

  Запросы в 1С обеспечивают табличный способ доступа к данным. Язык запросов заточен под удобное чтение и преобразование больших массивов информации; изменять данные с его помощью нельзя. Язык запросов — это отдельный язык, не являющийся частью встроенного языка 1С:Предприятие; он достаточно сходен с SQL.
  Запрос всегда начинается с ключевого слова ВЫБРАТЬ; собственно кроме него в запросе может и не быть других ключевых слов, если секция ВЫБРАТЬ полностью описывает источник и результат запроса, например:

  ВЫБРАТЬ Справочник.Контрагенты.ИНН

  • группировка
  • объединения разных таблиц-источников
  • условия, ограничивающие выборку
  • порядок представления результата
  • расчет итогов по каким-либо полям

  Запросы можно набирать просто вручную, но намного удобнее использовать специальный инструмент — конструктор запросов:

  Рассмотрим далее вопросы экзамена по общим моментам табличной модели и запросов.

  Вопрос 07.04 экзамена 1С:Профессионал по платформе. Для повышения скорости выполнения запроса необходимо:

  1. Задавать параметры большинства реальных таблиц
  2. Задавать параметры большинства виртуальных таблиц
  3. Вместо задания параметров у реальной или виртуальной таблицы использовать отбор, заданный конструкцией языка запросов «ГДЕ»
  4. Верны ответы 1 и 2

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

  Вопрос 07.15 экзамена 1С:Профессионал по платформе. Можно ли, используя конструктор запроса, повлиять на порядок записей в результирующей таблице запроса?

  1. Нельзя
  2. Это можно сделать на закладке «Порядок»
  3. Это можно сделать на закладке «Объединения/Псевдонимы»
  4. Верны ответы 2 и 3

  Правильный ответ четвертый. Закладка Порядок для этого предназначена очевидно; поля тут можно двигать стрелками, либо включить Автоупорядочивание (по основному представлению объекта):

  На закладке Объединения/Псевдонимы также можно регулировать порядок полей:
  Вопрос 07.34 экзамена 1С:Профессионал по платформе. При описании группировки в тексте запроса:

  1. Все поля должны делиться на те, по которым производится группировка, и на агрегатные функции (рассчитываемые поля)
  2. Все поля должны делиться на те, по которым производится группировка, на агрегатные функции (рассчитываемые поля) и на поля вложенных таблиц
  3. Чем выше определяется группировка, тем выше должно определяться поле в запросе
  4. Перечисленные ограничения не действуют

  Правильный ответ второй. Вложенные таблицы не попадают ни в группируемые, ни в агрегируемые поля.

  Вопрос 07.35 экзамена 1С:Профессионал по платформе. Левое внешнее соединение означает, что в результат соединения войдут:

  1. Все данные из таблицы №2 и они будут дополнены данными из таблицы №1, для которых выполняется условие соединения
  2. Данные из обеих таблиц, для которых выполняется условие соединения, но поля, которые берутся из таблицы №1, будут идти первыми (слева)
  3. Все данные из таблицы №1 и они будут дополнены данными из таблицы №2, для которых выполняется условие соединения

  Правильный ответ первый. Из левой таблицы будет выбрано все, из правой — только совпадающее по ключу с левой.

  Вопрос 07.36 экзамена 1С:Профессионал по платформе. С помощью какой агрегатной функции можно узнать количество записей в результате запроса?

  1. КОЛИЧЕСТВО()
  2. КОЛИЧЕСТВО(*)
  3. КОЛИЧЕСТВО(Различные …)
  4. СУММА

  Правильный ответ второй — функция КОЛИЧЕСТВО, и в ней нужно специфицировать, по каким полям считать количество (в примере по всем).

  В какой последовательности могут идти ключевые слова в запросе

  SELECT, TABLE, WITH — получить строки из таблицы или представления

  Синтаксис

  [ WITH [ RECURSIVE ] запрос_WITH [, . ] ] SELECT [ ALL | DISTINCT [ ON ( выражение [, . ] ) ] ] [ * | выражение [ [ AS ] имя_результата ] [, . ] ] [ FROM элемент_FROM [, . ] ] [ WHERE условие ] [ GROUP BY элемент_группирования [, . ] ] [ HAVING условие ] [ WINDOW имя_окна AS ( определение_окна ) [, . ] ] [ < UNION | INTERSECT | EXCEPT >[ ALL | DISTINCT ] выборка ] [ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS < FIRST | LAST >] [, . ] ] [ LIMIT < число | ALL > ] [ OFFSET начало [ ROW | ROWS ] ] [ FETCH < FIRST | NEXT >[ число ] < ROW | ROWS >ONLY ] [ FOR < UPDATE | NO KEY UPDATE | SHARE | KEY SHARE >[ OF имя_таблицы [, . ] ] [ NOWAIT | SKIP LOCKED ] [. ] ] Здесь допускается элемент_FROM: [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним [ ( псевдоним_столбца [, . ] ) ] ] [ TABLESAMPLE метод_выборки ( аргумент [, . ] ) [ REPEATABLE ( затравка ) ] ] [ LATERAL ] ( выборка ) [ AS ] псевдоним [ ( псевдоним_столбца [, . ] ) ] имя_запроса_WITH [ [ AS ] псевдоним [ ( псевдоним_столбца [, . ] ) ] ] [ LATERAL ] имя_функции ( [ аргумент [, . ] ] ) [ WITH ORDINALITY ] [ [ AS ] псевдоним [ ( псевдоним_столбца [, . ] ) ] ] [ LATERAL ] имя_функции ( [ аргумент [, . ] ] ) [ AS ] псевдоним ( определение_столбца [, . ] ) [ LATERAL ] имя_функции ( [ аргумент [, . ] ] ) AS ( определение_столбца [, . ] ) [ LATERAL ] ROWS FROM( имя_функции ( [ аргумент [, . ] ] ) [ AS ( определение_столбца [, . ] ) ] [, . ] ) [ WITH ORDINALITY ] [ [ AS ] псевдоним [ ( псевдоним_столбца [, . ] ) ] ] элемент_FROM [ NATURAL ] тип_соединения элемент_FROM [ ON условие_соединения | USING ( столбец_соединения [, . ] ) ] и элемент_группирования может быть следующим: ( ) выражение ( выражение [, . ] ) ROLLUP ( < выражение | ( выражение [, . ] ) > [, . ] ) CUBE ( < выражение | ( выражение [, . ] ) > [, . ] ) GROUPING SETS ( элемент_группирования [, . ] ) и запрос_WITH: имя_запроса_WITH [ ( имя_столбца [, . ] ) ] AS ( выборка | values | insert | update | delete ) TABLE [ ONLY ] имя_таблицы [ * ]

  Описание

  SELECT получает строки из множества таблиц (возможно, пустого). Общая процедура выполнения SELECT следующая:

  Выполняются все запросы в списке WITH . По сути они формируют временные таблицы, к которым затем можно обращаться в списке FROM . Запрос в WITH выполняется только один раз, даже если он фигурирует в списке FROM неоднократно. (См. Предложение WITH ниже.)

  Вычисляются все элементы в списке FROM . (Каждый элемент в списке FROM представляет собой реальную или виртуальную таблицу.) Если список FROM содержит несколько элементов, они объединяются перекрёстным соединением. (См. Предложение FROM ниже.)

  Если указано предложение WHERE , все строки, не удовлетворяющие условию, исключаются из результата. (См. Предложение WHERE ниже.)

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

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

  SELECT DISTINCT исключает из результата повторяющиеся строки. SELECT DISTINCT ON исключает строки, совпадающие по всем указанным выражениям. SELECT ALL (по умолчанию) возвращает все строки результата, включая дубликаты. (См. Предложение DISTINCT ниже.)

  Операторы UNION , INTERSECT и EXCEPT объединяют вывод нескольких команд SELECT в один результирующий набор. Оператор UNION возвращает все строки, представленные в одном, либо обоих наборах результатов. Оператор INTERSECT возвращает все строки, представленные строго в обоих наборах. Оператор EXCEPT возвращает все строки, представленные в первом наборе, но не во втором. Во всех трёх случаях повторяющиеся строки исключаются из результата, если явно не указано ALL . Чтобы явно обозначить, что выдаваться должны только неповторяющиеся строки, можно добавить избыточное слово DISTINCT . Заметьте, что в данном контексте по умолчанию подразумевается DISTINCT , хотя в самом SELECT по умолчанию подразумевается ALL . (См. Предложение UNION +, Предложение INTERSECT и Предложение EXCEPT ниже.)

  Если присутствует предложение ORDER BY , возвращаемые строки сортируются в указанном порядке. В отсутствие ORDER BY строки возвращаются в том порядке, в каком системе будет проще их выдать. (См. Предложение ORDER BY ниже.)

  Если указано предложение LIMIT (или FETCH FIRST ) либо OFFSET , оператор SELECT возвращает только подмножество строк результата. (См. Предложение LIMIT ниже.)

  Для всех столбцов, задействованных в команде SELECT , необходимо иметь право SELECT . Применение блокировок FOR NO KEY UPDATE , FOR UPDATE , FOR SHARE или FOR KEY SHARE требует также права UPDATE (как минимум для одного столбца в каждой выбранной для блокировки таблице).

  Параметры

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

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

  Имя (без схемы) должно быть указано для каждого запроса WITH . Также можно задать необязательный список с именами столбцов; если он опущен, имена столбцов формируются из результата подзапроса.

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

  нерекурсивная_часть UNION [ ALL | DISTINCT ] рекурсивная_часть 

  , где рекурсивная ссылка на сам запрос может находиться только справа от UNION . Для одного запроса допускается только одна рекурсивная ссылка на него же. Операторы, изменяющие данные, не могут быть рекурсивными, но результат рекурсивного запроса SELECT в таких операторах можно использовать. За примером обратитесь к Разделу 7.8.

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

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

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

  Основной запрос и все запросы WITH , условно говоря, выполняются одновременно. Это значит, что действие оператора, изменяющего данные в WITH , не будут видеть другие части запроса, кроме как прочитав его вывод RETURNING . Если два таких оператора попытаются изменить одну строку, результат будет неопределённым.

  За дополнительными сведениями обратитесь к Разделу 7.8.

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

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

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

  имя_таблицы

  Имя (возможно, дополненное схемой) существующей таблицы или представления. Если перед именем таблицы указано ONLY , считывается только заданная таблица. Без ONLY считывается и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание * , чтобы явно обозначить, что блокировка затрагивает и все дочерние таблицы. псевдоним

  Альтернативное имя для элемента списка FROM . Этот псевдоним используется для краткости или для исключения неоднозначности с замкнутыми соединениями (когда одна таблица читается неоднократно). Когда задаётся псевдоним, он полностью скрывает настоящее имя таблицы или функции; например, при записи FROM foo AS f , в продолжении запроса SELECT к этому элементу FROM нужно обращаться по имени f , а не foo . Если задан псевдоним таблицы, за ним можно также написать список псевдонимов столбцов, который определит альтернативные имена для столбцов таблицы. TABLESAMPLE метод_выборки ( аргумент [, . ] ) [ REPEATABLE ( затравка ) ]

  Предложение TABLESAMPLE , сопровождающее имя_таблицы , показывает, что для получения подмножества строк в этой таблице должен применяться указанный метод_выборки . Эта выборка предшествует применению любых других фильтров, например, в предложении WHERE . В стандартный дистрибутив Postgres Pro включены два метода выборки, BERNOULLI и SYSTEM ; другие методы выборки можно установить в базу данных через расширения.

  Методы выборки BERNOULLI и SYSTEM принимают единственный аргумент , определяющий, какой процент таблицы должен попасть в выборку, от 0 до 100. Этот аргумент может задаваться любым выражением со значением типа real . (Другие методы выборки могут принимать дополнительные или другие параметры.) Оба этих метода возвращают случайную выборку таблицы, содержащую примерно указанный процент строк таблицы. Метод BERNOULLI сканирует всю таблицу и выбирает или игнорирует отдельные строки независимо, с заданной вероятностью. Метод SYSTEM строит выборку на уровне блоков, определяя для каждого блока шанс его задействовать, и возвращает все строки из каждого задействуемого блока. Метод SYSTEM работает значительно быстрее BERNOULLI , когда выбирается небольшой процент строк, но он может выдавать менее случайную выборку таблицы из-за эффектов кучности.

  В необязательном предложении REPEATABLE задаётся затравка — число или выражение, задающее отправное значение для генератора случайных чисел в методе выборки. Значением затравки может быть любое отличное от NULL число с плавающей точкой. Два запроса, в которых указаны одинаковые значения затравки и аргумента , выдадут одну и ту же выборку таблицы при условии неизменности содержимого таблицы. Но с разными значениями затравки выборки обычно получаются разными. В отсутствие предложения REPEATABLE для каждого запроса выдаётся новая случайная выборка, в зависимости от затравки, сгенерированной системой. Заметьте, что некоторые дополнительные методы выборки не принимают предложение REPEATABLE и выдают разные выборки при каждом использовании. выборка

  Предложение FROM может содержать вложенный запрос SELECT . Можно считать, что из его результата создаётся временная таблица на время выполнения основной команды SELECT . Заметьте, что вложенный запрос SELECT должен заключаться в скобки и для него должен задаваться псевдоним. Здесь также можно использовать команду VALUES . имя_запроса_WITH

  На запрос WITH можно ссылаться по имени, как если бы имя запроса представляло имя таблицы. (На самом деле запрос WITH скрывает любую реальную таблицу с тем же именем для основного запроса. Если необходимо обратиться к одноимённой реальной таблице, можно дополнить имя этой таблицы именем схемы.) Для этого имени можно задать псевдоним, так же, как и для имени таблицы. имя_функции

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

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

  Несколько вызовов функций можно объединить в одном элементе предложения FROM , заключив их в конструкцию ROWS FROM( . ) . Выводом такого элемента будет соединение первых строк всех функций, затем вторых строк и т. д. Если одни функции выдают меньше строк, чем другие, недостающие данные заменяются значениями NULL, так что общее число возвращаемых строк всегда будет равняться максимальному числу строк из возвращённых всеми функциями.

  Если функция определена как возвращающая тип данных record , для неё нужно указать псевдоним или ключевое слово AS , за которым должен идти список определений столбцов в форме ( имя_столбца тип_данных [ , . ]) . Список определений столбцов должен соответствовать фактическому количеству и типу столбцов, возвращаемых функцией.

  Если при использовании синтаксиса ROWS FROM( . ) одна из функций требует наличия списка определений столбцов, этот список лучше разместить после вызова функции внутри ROWS FROM( . ) . Список определений столбцов можно поместить после конструкции ROWS FROM( . ) , только если вызывается всего одна функция, а предложение WITH ORDINALITY отсутствует.

  Чтобы использовать ORDINALITY со списком определений столбцов, необходимо применить запись ROWS FROM( . ) и поместить список с определениями столбцов внутрь ROWS FROM( . ) . тип_соединения

  Один из следующих вариантов:

  LEFT [ OUTER ] JOIN

  RIGHT [ OUTER ] JOIN

  FULL [ OUTER ] JOIN

  Для типов соединений INNER и OUTER необходимо указать условие соединения, а именно одно из предложений NATURAL , ON условие_соединения или USING ( столбец_соединения [, . ]) . Эти предложения описываются ниже. Для CROSS JOIN ни одно из этих предложений не допускается.

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

  CROSS JOIN и INNER JOIN формируют простое декартово произведение, то же, что можно получить, указав две таблицы на верхнем уровне FROM , но ограниченное возможным условием соединения. Предложение CROSS JOIN равнозначно INNER JOIN ON (TRUE) , то есть, никакие строки по условию не удаляются. Эти типы соединений введены исключительно для удобства записи, они не дают ничего такого, что нельзя было бы получить, используя просто FROM и WHERE .

  LEFT OUTER JOIN возвращает все строки ограниченного декартова произведения (т. е. все объединённые строки, удовлетворяющие условию соединения) плюс все строки в таблице слева, для которых не находится строк в таблице справа, удовлетворяющих условию. Строка, взятая из таблицы слева, дополняется до полной ширины объединённой таблицы значениями NULL в столбцах таблицы справа. Заметьте, что для определения, какие строки двух таблиц соответствуют друг другу, проверяется только условие самого предложения JOIN . Внешние условия проверяются позже.

  RIGHT OUTER JOIN , напротив, возвращает все соединённые строки плюс одну строку для каждой строки справа, не имеющей соответствия слева (эта строка дополняется значениями NULL влево). Это предложение введено исключительно для удобства записи, так как его можно легко свести к LEFT OUTER JOIN , поменяв левую и правую таблицы местами.

  FULL OUTER JOIN возвращает все соединённые строки плюс все строки слева, не имеющие соответствия справа, (дополненные значениями NULL вправо) плюс все строки справа, не имеющие соответствия слева (дополненные значениями NULL влево). ON условие_соединения

  Задаваемое условие_соединения представляет собой выражение, выдающее значение типа boolean (как в предложении WHERE ), которое определяет, какие строки считаются соответствующими при соединении. USING ( столбец_соединения [, . ] )

  Предложение вида USING ( a, b, . ) представляет собой сокращённую форму записи ON таблица_слева.a = таблица_справа.a AND таблица_слева.b = таблица_справа.b . . Кроме того, USING подразумевает, что в результат соединения будет включён только один из пары равных столбцов, но не оба. NATURAL

  NATURAL представляет собой краткую запись USING со списком, в котором перечисляются все столбцы двух таблиц, имеющие одинаковые имена. Если одинаковых имён нет, указание NATURAL равнозначно ON TRUE . LATERAL

  Ключевое слово LATERAL может предварять вложенный запрос SELECT в списке FROM . Оно позволяет обращаться в этом вложенном SELECT к столбцам элементов FROM , предшествующим ему в списке FROM . (Без LATERAL все вложенные подзапросы SELECT обрабатываются независимо и не могут ссылаться на другие элементы списка FROM .)

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

  Элемент LATERAL может находиться на верхнем уровне списка FROM или в дереве JOIN . В последнем случае он может также ссылаться на любые элементы в левой части JOIN , справа от которого он находится.

  Когда элемент FROM содержит ссылки LATERAL , запрос выполняется следующим образом: сначала для строки элемента FROM с целевыми столбцами, или набора строк из нескольких элементов FROM , содержащих целевые столбцы, вычисляется элемент LATERAL со значениями этих столбцов. Затем результирующие строки обычным образом соединяются со строками, из которых они были вычислены. Эта процедура повторяется для всех строк исходных таблиц.

  Таблица, служащая источником столбцов, должна быть связана с элементом LATERAL соединением INNER или LEFT , в противном случае не образуется однозначно определяемый набор строк, из которого можно будет получать наборы строк для элемента LATERAL . Таким образом, хотя конструкция X RIGHT JOIN LATERAL Y синтаксически правильная, Y в ней не может обращаться к X .

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

  Необязательное предложение WHERE имеет общую форму

  WHERE условие 

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

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

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

  GROUP BY элемент_группирования [, . ]

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

  Если в элементе группирования задаётся GROUPING SETS , ROLLUP или CUBE , предложение GROUP BY в целом определяет некоторое число независимых наборов группирования . Это даёт тот же эффект, что и объединение подзапросов (с UNION ALL ) с отдельными наборами группирования в их предложениях GROUP BY . Подробнее использование наборов группирования описывается в Подразделе 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 условие 

  Здесь условие задаётся так же, как и для предложения WHERE .

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

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

  В настоящее время указания FOR NO KEY UPDATE , FOR UPDATE , FOR SHARE и FOR KEY SHARE нельзя задать вместе с HAVING .

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

  Необязательное предложение WINDOW имеет общую форму

  WINDOW имя_окна AS ( определение_окна ) [, . ]

  Здесь имя_окна — это имя, на которое можно ссылаться из предложений OVER или последующих определений окон, а определение_окна имеет следующий вид:

  [ имя_существующего_окна ] [ PARTITION BY выражение [, . ] ] [ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS < FIRST | LAST >] [, . ] ] [ предложение_рамки ]

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

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

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

  Необязательное предложение_рамки определяет рамку окна для оконных функций, которые зависят от рамки (не все функции таковы). Рамка окна — это набор связанных строк для каждой строки запроса (называемой текущей строкой). В качестве предложения_рамки может задаваться

   < RANGE | ROWS >начало_рамки < RANGE | ROWS >BETWEEN начало_рамки AND конец_рамки 

  Здесь начало_рамки и конец_рамки может задаваться как

  UNBOUNDED PRECEDING значение PRECEDING CURRENT ROW значение FOLLOWING UNBOUNDED FOLLOWING

  Если конец_рамки опущен, по умолчанию подразумевается CURRENT ROW . В качестве начала_рамки нельзя задать UNBOUNDED FOLLOWING , а в качестве конца_рамки — UNBOUNDED PRECEDING , к тому же выбранный вариант конца_рамки не может стоять в приведённом списке выше варианта начала_рамки — например, комбинация RANGE BETWEEN CURRENT ROW AND значение PRECEDING не допускается.

  По умолчанию рамка образуется предложением RANGE UNBOUNDED PRECEDING , что по сути то же, что RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ; оно устанавливает рамку так, что она включает все строки от начала раздела до последней строки, родственной текущей (строки, которую ORDER BY считает равной текущей), либо все строки раздела, если ORDER BY отсутствует. Вообще UNBOUNDED PRECEDING означает, что рамка начинается с первой строки раздела, а UNBOUNDED FOLLOWING означает, что рамка заканчивается на последней строке раздела (вне зависимости от режима RANGE или ROWS ). В режиме ROWS указание CURRENT ROW означает, что рамка начинается или заканчивается текущей строкой; но в режиме RANGE оно означает, что рамка начинается или заканчивается первой или последней строкой, родственной текущей, согласно порядку ORDER BY . Варианты значение PRECEDING и значение FOLLOWING в настоящее время допускаются только в режиме ROWS . Они показывают, что рамка начинается или заканчивается со сдвигом на заданное количество строк назад или вперёд от текущей. Здесь значение должно быть целочисленным выражением, не содержащим никакие переменные, агрегатные или оконные функции. Это значение не может быть отрицательным или равным NULL, но может быть равно 0 (при этом выбирается текущая строка).

  Учтите, что варианты ROWS могут выдавать непредсказуемые результаты, если согласно порядку, заданному в ORDER BY , строки сортируются неоднозначно. Варианты RANGE предусмотрены для того, чтобы строки, являющиеся родственными в порядке 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 можно опустить, но только если желаемое выходное имя не совпадает с каким-либо ключевым словом Postgres Pro (см. Приложение C). Чтобы не зависеть от появления новых ключевых слов в будущем, рекомендуется всегда писать AS , либо заключать имя в двойные кавычки.) Если имя столбца не задать, Postgres Pro выберет его автоматически. Если выражение столбца представляет собой просто ссылку на столбец, то выбранное таким образом имя будет совпадать с именем столбца. В более сложных случаях может использоваться имя функции или типа, либо в отсутствие других вариантов система может сгенерировать имя вроде ?column? .

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

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

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

  Примечание

  В Postgres Pro до версии 9.6 никакой порядок вычисления выходных выражений по отношению к сортировке или ограничениям количества не гарантировался; он зависел от формы выбранного плана запроса.

  Предложение 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 UNION [ ALL | DISTINCT ] оператор_SELECT 

  Здесь оператор_SELECT — это любой подзапрос 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 можно записать явно, чтобы обозначить, что дублирующиеся строки должны удаляться (это поведение по умолчанию).

  При использовании в одном запросе SELECT нескольких операторов UNION они вычисляются слева направо, если иной порядок не определяется скобками.

  В настоящее время указания FOR NO KEY UPDATE , FOR UPDATE , FOR SHARE и FOR KEY SHARE нельзя задать ни для результата UNION , ни для любого из подзапросов UNION .

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

  Предложение INTERSECT имеет следующую общую форму:

  оператор_SELECT INTERSECT [ ALL | DISTINCT ] оператор_SELECT 

  Здесь оператор_SELECT — это любой подзапрос 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 можно записать явно, чтобы обозначить, что дублирующиеся строки должны удаляться (это поведение по умолчанию).

  При использовании в одном запросе SELECT нескольких операторов INTERSECT они вычисляются слева направо, если иной порядок не диктуется скобками. 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 EXCEPT [ ALL | DISTINCT ] оператор_SELECT 

  Здесь оператор_SELECT — это любой подзапрос 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 можно записать явно, чтобы обозначить, что дублирующиеся строки должны удаляться (это поведение по умолчанию).

  При использовании в одном запросе SELECT нескольких операторов EXCEPT они вычисляются слева направо, если иной порядок не диктуется скобками. EXCEPT связывает свои подзапросы так же сильно, как UNION .

  В настоящее время указания FOR NO KEY UPDATE , FOR UPDATE , FOR SHARE и FOR KEY SHARE нельзя задать ни для результата EXCEPT , ни для любого из подзапросов EXCEPT .

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

  Необязательное предложение ORDER BY имеет следующую общую форму:

  ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS < FIRST | LAST >] [, . ]

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

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

  Если указано NULLS LAST , значения NULL при сортировке оказываются после значений не NULL; с указанием NULLS FIRST значения NULL оказываются перед значениями не NULL. Если не указано ни то, ни другое, по умолчанию подразумевается NULLS LAST при явно или неявно выбранном порядке ASC , либо NULLS FIRST при порядке DESC (то есть по умолчанию считается, что значения NULL больше значений не NULL). С предложением USING порядок NULL по умолчанию зависит от того, является ли указанный оператор оператором «меньше» или «больше».

  Заметьте, что параметры сортировки применяются только к тому выражению, за которым они следуют; в частности, ORDER BY x, y DESC означает не то же самое, что ORDER BY x DESC, y DESC .

  Данные символьных строк сортируются согласно правилу сортировки, установленному для сортируемого столбца. При необходимости это правило можно переопределить, добавив предложение COLLATE в выражение , например так: ORDER BY mycolumn COLLATE «en_US» . За дополнительными сведениями обратитесь к Подразделу 4.2.10 и Разделу 22.2.

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

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

  LIMIT < число | ALL > OFFSET начало 

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

  Если результатом выражения число оказывается NULL, предложение воспринимается как LIMIT ALL , т. е. число строк не ограничивается. Если начало принимает значение NULL, предложение воспринимается как OFFSET 0 .

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

  OFFSET начало < ROW | ROWS >FETCH < FIRST | NEXT >[ число ] < ROW | ROWS >ONLY

  В этом синтаксисе значение начало или число в соответствии со стандартом должно быть буквальной константой, параметром или именем переменной; Postgres Pro позволяет использовать и другие выражения, но их обычно нужно заключать в скобки во избежание неоднозначности. Если число опускается в предложении FETCH , оно принимает значение 1. Слова ROW и ROWS , а также FIRST и NEXT являются незначащими и не влияют на поведение этих предложений. Согласно стандарту предложение OFFSET должно идти перед FETCH , если они присутствуют вместе; но Postgres Pro менее строг и допускает любой порядок.

  Применяя 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 вариант_блокировки [ OF имя_таблицы [, . ] ] [ NOWAIT | SKIP LOCKED ]

  Здесь вариант_блокировки может быть следующим:

  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 , работающая на уровне изоляции 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

  TABLE имя

  SELECT * FROM имя

  Её можно применять в качестве команды верхнего уровня или как более краткую запись внутри сложных запросов. С командой 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, films f WHERE f.did = d.did title | did | name | date_prod | kind -------------------+-----+--------------+------------+---------- The Third Man | 101 | British Lion | 1949-12-23 | Drama The African Queen | 101 | British Lion | 1951-08-11 | Romantic .

  Суммирование значений столбца len (продолжительность) для всех фильмов и группирование результатов по столбцу kind (типу фильма):

  SELECT kind, sum(len) AS total FROM films GROUP BY kind; kind | total ----------+------- Action | 07:34 Comedy | 02:58 Drama | 14:28 Musical | 06:42 Romantic | 04:38

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

  SELECT kind, sum(len) AS total FROM films GROUP BY kind HAVING sum(len) < interval '5 hours'; kind | total ----------+------- Comedy | 02:58 Romantic | 04:38

  Следующие два запроса демонстрируют равнозначные способы сортировки результатов по содержимому второго столбца ( name ):

  SELECT * FROM distributors ORDER BY name; SELECT * FROM distributors ORDER BY 2; did | name -----+------------------ 109 | 20th Century Fox 110 | Bavaria Atelier 101 | British Lion 107 | Columbia 102 | Jean Luc Godard 113 | Luso films 104 | Mosfilm 103 | Paramount 106 | Toho 105 | United Artists 111 | Walt Disney 112 | Warner Bros. 108 | Westward

  Следующий пример показывает объединение таблиц distributors и actors , ограниченное именами, начинающимися с буквы W в каждой таблице. Интерес представляют только неповторяющиеся строки, поэтому ключевое слово ALL опущено.

  distributors: actors: did | name id | name -----+-------------- ----+---------------- 108 | Westward 1 | Woody Allen 111 | Walt Disney 2 | Warren Beatty 112 | Warner Bros. 3 | Walter Matthau . . SELECT distributors.name FROM distributors WHERE distributors.name LIKE 'W%' UNION SELECT actors.name FROM actors WHERE actors.name LIKE 'W%'; name ---------------- Walt Disney Walter Matthau Warner Bros. Warren Beatty Westward Woody Allen

  Этот пример показывает, как использовать функцию в предложении FROM , со списком определений столбцов и без него:

  CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors(111); did | name -----+------------- 111 | Walt Disney CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$ SELECT * FROM distributors WHERE did = $1; $$ LANGUAGE SQL; SELECT * FROM distributors_2(111) AS (f1 int, f2 text); f1 | f2 -----+------------- 111 | Walt Disney

  Пример функции с добавленным столбцом нумерации:

  SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY; unnest | ordinality --------+---------- a | 1 b | 2 c | 3 d | 4 e | 5 f | 6 (6 rows)

  Этот пример показывает, как использовать простое предложение WITH :

  WITH t AS ( SELECT random() as x FROM generate_series(1, 3) ) SELECT * FROM t UNION ALL SELECT * FROM t x -------------------- 0.534150459803641 0.520092216785997 0.0735620250925422 0.534150459803641 0.520092216785997 0.0735620250925422

  Заметьте, что запрос WITH выполняется всего один раз, поэтому мы получаем два одинаковых набора по три случайных значения.

  В этом примере 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 , а затем рекурсивная часть запроса. Убедитесь в том, что рекурсивная часть запроса в конце концов перестанет возвращать строки, иначе запрос окажется в бесконечном цикле. (За другими примерами обратитесь к Разделу 7.8.)

  В этом примере используется 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

  Postgres Pro разрешает опустить предложение FROM . Это позволяет очень легко вычислять результаты простых выражений:

  SELECT 2+2; ?column? ---------- 4

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

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

  SELECT distributors.* WHERE distributors.name = 'Westward';

  До версии 8.1 PostgreSQL мог принимать запросы такого вида, неявно добавляя каждую таблицу, задействованную в запросе, в предложение FROM этого запроса. Теперь это не допускается.

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

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

  Необязательное ключевое слово AS

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

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

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

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

  Postgres Pro позволяет добавлять в конце * , чтобы явно обозначить, что дочерние таблицы включаются в рассмотрение, в отличие от поведения с ONLY . Стандарт не позволяет этого.

  (Эти соображения в равной степени касаются всех SQL-команд, поддерживающих параметр ONLY .)

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

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

  Вызовы функций в предложении FROM

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

  Пространства имён в GROUP BY и ORDER BY

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

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

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

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

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

  Стандарт SQL предоставляет дополнительные возможности для указания предложения_рамки окна. Postgres Pro в настоящее время поддерживает только варианты, описанные выше.

  LIMIT и OFFSET

  Предложения LIMIT и OFFSET относятся к специфическим особенностям Postgres Pro и поддерживаются также в MySQL . В стандарте SQL:2008 для той же цели вводятся предложения OFFSET . FETCH . , рассмотренные ранее в Предложение LIMIT . Этот синтаксис также используется в IBM DB2 . (Приложения, написанные для Oracle , часто применяют обходной способ и получают эффект этих предложений, задействуя автоматически генерируемый столбец rownum , который отсутствует в Postgres Pro.)

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

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

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

  Postgres Pro разрешает использовать INSERT , UPDATE и DELETE в качестве запросов WITH . Стандарт SQL этого не предусматривает.

  Нестандартные предложения

  DISTINCT ON ( . ) — расширение стандарта SQL.

  ROWS FROM( . ) — расширение стандарта SQL.

  Пред.	Наверх	След.
  SECURITY LABEL	Начало	SELECT INTO

  Читать:

  Как переслать сообщение из ватсапа на электронную почту себе