Перейти к содержимому

Как оператор group by обрабатывает поля с null

  • автор:

NULL значения в GROUP BY

В условиях WHERE два NULL значения считаются различными. Но при группировке строк GROUP BY NULL значения считаются идентичными и объединяются в одну группу (как и при исключении повторяющихся строк DISTINCT ).

Подсчитаем количество использований номеров телефонов в магазинах:

SELECT phone, count (*) FROM store_address GROUP BY phone
phone count
NULL 3
7(347)668‒56‒66 1
7(385)777‒77‒07 1
7(495)312‒03‒08 3
7(812)700‒03‒03 1
. .

В таблице store_address для трех адресов магазинов не указан номер телефона. Поэтому в результате мы видим строку

phone count
NULL 3

Если бы NULL значения в GROUP BY считались различными, то мы бы получили вот такой результат:

phone count
NULL 1
NULL 1
NULL 1

Что неудобно для проведения аналитики.

5 заданий по SQL с реальных собеседований

SQL — один из самых востребованных навыков в IT. Разбираем несколько задач с собеседований, разбитых по уровням junior, middle и senior.

SQL — один из самых востребованных навыков в современной IT индустрии (на 3 месте по популярности согласно StackOverflow Developer Survey 2020, даже Python идет на 4 месте).

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

Сегодня Елизавета, аналитик и эксперт IT Resume, делится 5 задачами и вопросами, которые входят в программу подготовки к собеседованию SQL Interview. Это задачи с реальных собеседований в крупные IT-компании, и они разбиты по уровням — Junior, Middle и Senior. Попробуйте и вы свои силы — сможете их решить без подсказки ?

Елизавета Рузова
аналитик и эксперт в IT Resume

Вводные данные

Есть таблица анализов Analysis:

  • an_id — ID анализа;
  • an_name — название анализа;
  • an_cost — себестоимость анализа;
  • an_price — розничная цена анализа;
  • an_group — группа анализов.

Есть таблица групп анализов Groups:

  • gr_id — ID группы;
  • gr_name — название группы;
  • gr_temp — температурный режим хранения.

Есть таблица заказов Orders:

  • ord_id — ID заказа;
  • ord_datetime — дата и время заказа;
  • ord_an — ID анализа.

Далее мы будем работать с этими таблицами.

Задача 1. Уровень: Junior

Формулировка: вывести название и цену для всех анализов, которые продавались 5 февраля 2020 и всю следующую неделю.

Это задача для начинающих специалистов. В ней проверяется базовое знание SELECT-запросов и умение работать с датой-временем.

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

Задача 2. Уровень: Middle

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

Эта задача уже более высокого уровня: ее можно давать как Middle, так и Junior специалистам. Здесь проверяется базовое понимание оконных функций, джоинов и группировок.

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

Задача 3: Уровень Senior

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

Рассмотрим таблицу балансов клиентов:

ClientBalance(client_id, client_name, client_balance_date, client_balance_value)

  • client_id — идентификатор клиента;
  • client_name — ФИО клиента;
  • client_balance_date — дата баланса клиента;
  • client_balance_value — значение баланса клиента.

Формулировка: в данной таблице в какой-то момент времени появились полные дубли. Предложите способ для избавления от них без создания новой таблицы.

Вопрос 1. Уровень: Junior

Есть категория «хитрых» вопросов, которые особенно любят задавать Junior-специалистам. Хотя чего уж там, любым специалистам.

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

Вопрос: Как оператор GROUP BY обрабатывает поля с NULL?

Если Вы не знаете ответ на этот вопрос, то после прочтения ответа обязательно проверьте свои проекты — может у вас где-то закралась ошибка? ?

Учитывая, что NULL в SQL — просто отсутствие значения, то все значения NULL при группировке попадают в одну группу. Например, пусть есть таблица:

name | score ------|------- Vasya | 5 Petya | 10 Petya | 3 Vasya | 4 NULL | 3 NULL | 8

Тогда запрос select sum(score) from table group by name даст:

name | score ------|------- Vasya | 9 Petya | 13 NULL | 11

Вопрос 2. Уровень Middle

Этот вопрос не такой хитрый, как предыдущий, а вполне себе конкретный. Однако, он требует знания оконных функций и их тонкостей, а это исконное Middle требование.

Вопрос: В чем отличие функции RANK() от DENSE_RANK() ?

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

По аналогии с функцией ROW_NUMBER , оконные функции RANK и DENSE_RANK служат для нумерации строк. Однако, делают они это немного иначе: строки с одинаковым значениям получают одинаковый ранг. Для ряда задач это логично: если у двух сотрудников одинаковая зарплата, мы не можем сказать, что кто-то из них первый, а кто-то второй. Они одинаковы. Но при таком подходе возникает проблема: а какой ранг должен получить следующий сотрудник? Например, если первые два были одинаковые и у них ранги 1, то сотрудник со второй зарплатой в компании должен иметь ранг 2 или 3?

Эпилог

Мы разобрали с вами всего 5 возможных заданий, которые судьба и рекрутеры могут подкинуть вам на собеседовании. Однако, вариантов, на самом деле, масса. И чтобы реально подготовиться к собеседованию, нужно нарешать как можно больше задач, понять и прочувствовать теоретические аспекты и буквально научиться «мыслить на SQL». Именно на это и нацелена программа SQL Interview — так что будем рады помочь вам получить работу мечты!

И, помимо этого, не забывайте: SQL — чисто прикладной инструмент. Чтобы его освоить, нужно практиковаться, практиковаться и практиковаться. Выучить его можно буквально за несколько недель интенсивных занятий, а вот освоить на уровне Senior… порой на это уходят годы.

Общий

SQL (structured query language — «язык структурированных запросов») — формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной
реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД). SQL основывается на исчислении кортежей.

Какие есть типы JOIN’ов. Кратко опишите каждый из типов.¶

JOIN — внутреннее соединение. В результирующем наборе присутствуют только записи, значения связанных полей в которых совпадают.
LEFT JOIN — левое внешнее соединение. В результирующем наборе присутствуют все записи из Table1 и соответствующие им записи из Table2. Если соответствия нет, поля из Table2 будут пустыми.
RIGHT JOIN — правое внешнее соединение. В результирующем наборе присутствуют все записи из Table2 и соответствующие им записи из Table1. Если соответствия нет, поля из Table1 будут пустыми.
FULL JOIN — полное внешнее соединение. Комбинация двух предыдущих. В результирующем наборе присутствуют все записи из Table1 и соответствующие им записи из Table2. Если соответствия нет — поля из Table2 будут пустыми. Записи из Table2, которым не нашлось пары в Table1, тоже будут присутствовать в результирующем наборе. В этом случае поля из Table1 будут пустыми.
CROSS JOIN — Cartesian product. Результирующий набор содержит все варианты комбинации строк из Table1 и Table2. Условие соединения при этом не указывается.

Что такое LEFT JOIN, RIGHT JOIN? Чем они отличаются?¶

Проиллюстрируем каждый тип примерами. Модель данных:

SELECT Table1.Field1, Table2.Field2 FROM Table1 LEFT JOIN Table2 ON Table1.Key1 = Table2.Key2
SELECT Table1.Field1, Table2.Field2 FROM Table1 RIGHT JOIN Table2 ON Table1.Key1 = Table2.Key2

LEFT JOIN — левое внешнее соединение. В результирующем наборе присутствуют все записи из Table1 и соответствующие им записи из Table2. Если соответствия нет, поля из Table2 будут пустыми.
RIGHT JOIN — правое внешнее соединение. В результирующем наборе присутствуют все записи из Table2 и соответствующие им записи из Table1.

Если соответствия нет, поля из Table1 будут пустыми.

Для чего используется слово HAVING?¶

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

SELECT DeptNum, MAX(SALARY) FROM Employees GROUP BY DeptNum HAVING MAX(SALARY) > 1000

В приведенном примере в результат попадут только отделы, максимальная зарплата в которых превышает 1000.

Что такое DDL?¶

DDL — Команды определения структуры данных. В состав DDL-группы входят команды, позволяющие определять внутреннюю структуру базы данных. Перед тем, как сохранять данные в БД, необходимо создать в ней таблицы и, возможно, некоторые другие сопутствующие объекты

Пример некоторых DDL-команд:

Что такое DML?¶

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

Что такое TCL?¶

TCL — TCL-команды используются для управления изменениями данных, производимыми DML-командами. С их помощью несколько DML-команд могут быть объединены в единое логическое целое, называемое транзакцией. При этом все команды на изменение данных в рамках одной транзакции либо завершаются успешно, либо все могут быть отменены в случае возникновения каких-либо проблем с выполнением любой из них.

Что такое DCL?¶

DCL — Команды управления доступом. DCL-команды управляют доступом пользователей к БД и отдельным объектам:

Какой общий синтаксис команди SELECT?¶

В общем виде синтаксис команды SELECT выглядит следующим образом:

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

Про NULL в SQL.¶

Необходимо отметить, что язык SQL, в отличие от языков программирования, имеет встроенные средства поддержки факта отсутствия каких-либо данных. Осуществляется это с помощью NULL-концепции. NULL не является каким-то фиксированным значением, хранящимся в поле записи вместо реальных данных. Значение NULL не имеет определенного типа. NULL — это индикатор, говорящий пользователю (и SQL) о том, что данные в поле записи отсутствуют. Поэтому его нельзя использовать в операциях сравнения. Для проверки факта наличия-отсутствия данных в SQL введены специальные выражения.

Если вы собираетесь соединить несколько таблиц в запросе (например, n таблиц), сколько условий соединения вам нужно использовать?¶

Тогда нужно использовать n-1 условий соединения чтоб исключить декартовой соединения, может быть и такое, что потребуется больше чем n-1 условий соединения, и совсем другие условия соединения для дальнейшего сокращения результирующего набора данных.

Какое практическое применение временных таблиц?¶

Временная таблица — это объект базы данных, который хранится и управляется системой базы данных на временной основе. Они могут быть локальными или глобальными. используется для сохранения результатов
вызова хранимой процедуры, уменьшение числа строк при соединениях агрегирование данных из различных источников или замена курсоров и параметризованных представлений.

Как оператор GROUP BY обрабатывает значение NULL? Ли это общем трактовке таких значений?¶

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

В чем разница между COUNT (*) и COUNT (столбец)?¶

форма COUNT (столбец) подсчитывает количество значений в «столбец». При подсчете количества значений столбца эта форма функции COUNT не принимает во внимание значение NULL. функция COUNT (*) подсчитывает количество строк в таблице, не игнорирует значение NULL, поскольку эта функция оперирует строками, а не столбцами.

В чем разница между операторами DISTINCT и GROUP BY?¶

DISTINCT — указывает, что для вычислений используются только уникальные значения столбца. NULL считается как отдельное значение. Если нужно удалить только дубликаты лучше использовать DISTINCT. GROUP BY группирует выбранный набор строк для получения набора сводных строк по значениям одного или нескольких столбцов или выражений. GROUP BY создает отдельную группу для всех возможных значений (включая значение NULL). GROUP BY лучше использовать для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM).

Есть таблица table1 с колонками id и datetime написать запрос который вернет максимальное значение id и значение даты для этого id.¶

Есть множество решений — самое простое — это получать max значение для id и потом вывести значения:

SELECT id,datetime FROM table1 WHERE id = (SELECT max(id) FROM table1); 
SELECT id,datetime FROM table1 WHERE id in (SELECT max(id) FROM table1);

можно и так (mysql)

SELECT id,datetime FROM table1 ORDER BY id DESC LIMIT 1;

Для чего нужны операторы UNION, INTERSECT, EXCEPT?¶

Оператор UNION — применяется для объединения результатов двух SQLзапросов в единую таблицу, состоящую из похожих срок. Оба запроса Должны возвращать одинаковое число столбцов и совместимые типы данных в соответствующих столбцах.

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

Оператор EXCEPT — используется для нахождения разности двух множеств. Результатом выполнения является множество строк из множества 1, которые отсутствуют в множестве 2.

Приоритет выполнения операторов над множествами:

INTERSECT -> EXCEPT -> UNION

Что лучше использовать соединение или подзапросы?¶

Обычно лучше использовать JOIN, поскольку в большинстве случаев он понятен, и лучше оптимизируется с помощью Database Engine. но не всегда. Соединение имеет преимущество над подзапросов в случае, когда список выбора SELECT в запросе содержит столбцы более чем одной таблицы. Подзапросы лучшие тогда, когда нужно вычислять агрегатные значение и использовать их во внешних запросах для сравнений.

Что делает функция EXISTS?¶

Аргументом функции EXISTS есть внутренний запрос. она возвращает истину, если запрос возвращает один или более строк, и возвращает ложь если запрос вернет ноль строк.

Использование оператора PIVOT.¶

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

Опишите разницу типов данных DATETIME и TIMESTAMP.¶

DATETIME предназначен для хранения целого числа: YYYYMMDDHHMMSS. И это время не зависит от временной зоны настроенной на сервере.Хранит: 8 байт

TIMESTAMP хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича. При получении из базы отображается с учётом часового пояса. Хранит: 4 байта

Для каких числовых типов недопустимо использовать операцию сложения (вычитания), а значит и функцию SUM ?¶

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

Что такое хранимые процедуры?¶

Хранимая процедура — компилируемый набор SQL-инструкций, являющийся частью базы данных и хранимый на сервере. Есть много общего между ХП и обычными процедурами языков программирования: они могут иметь входные параметры и выходной результат, они могут как выполнять различные численные вычисления, так и выполнять стандартные операции с БД. Как и в процедурах других языков программирования, в них могут быть циклы и ветвления.

Функции ранжирования что это и какие существует?¶

Ранжирующие функции возвращают ранжирующее значение для каждой строки в секции. В зависимости от используемой функции значения некоторых строк могут совпадать. Ранжирующие функции являются недетерминированными. Transact-SQL содержит следующие ранжирующие функции:

Может ли значение в столбце(ах), на который наложено ограничение foreign key, равняться null?¶

Может, если на данный столбец не наложено ограничение not null, пример: при построении таблицы дерева файловой системы, где столбец foreign key — ссылка на эту же самую таблицу, на кортеж с информацией о родительской директории, тогда для корневой директории файловой системы в столбце родительской директории будет — null.

Назовите основные свойства транзакции.¶

ACID — atomicity (атомарность), consistency (непротиворечивость), isolation (изолированность), durability (устойчивость).

  • Свойство атомарности гарантирует неделимость набора операторов, которые изменяют данные в базе данных и являются частью транзакции. Это означает, что или выполняются все изменения данных в транзакции, или в случае любой ошибки все уже выполненные изменения отменяются.
  • Согласованность гарантирует, что транзакция не даст возможности базе данных содержать несогласованные данные. Другими словами, трансформация данных в рамках одной транзакции переводит базу данных из одного согласованного состояния в другое согласованное состояние.
  • Свойство изолированности разделяет все одновременно выполняющиеся транзакции. Другими словами, ни одна активная транзакция не может видеть изменения данных, выполненные в параллельной, но не завершенной транзакции. Это означает, что для обеспечения изолированности для некоторых транзакций может быть выполнен откат.
  • Устойчивость — после своего завершения транзакция сохраняется в системе, которую ничто не может вернуть в исходное (до начала транзакции) состояние, т.е. происходит фиксация транзакции, означающая,
    что ее действие постоянно даже при сбое системы.

Как удалить повторяющиеся строки с использованием ключевого слова Distinct?¶

SELECT DISTINCT columnsName FROM tableName;
  • columnsName — одно или несколько реальных имен столбцов,перечисленных через запятую;
  • tableName — имя той таблицы, из которой выбираются эти столбцы.

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

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

Когда полное сканирование таблицы выгоднее доступа по индексу? Опишите вкратце общие принципы, как оптимизатор выбирает производить ли полное сканирование таблицы или доступ по индексу.¶

Полное сканирование производится многоблочным чтением. Сканирование по индексу — одноблочным. Также, при доступе по индексу сначала идет сканирование самого индекса, а затем чтение блоков из таблицы. Число блоков, которые надо при этом прочитать из таблицы зависит от фактора кластеризации. Если суммарная стоимость всех необходимых одноблочных чтений больше стоимости полного сканирования многоблочным чтением, то полное сканирование выгоднее и оно выбирается оптимизатором. Таким образом, полное сканирование выбирается при слабой селективности предикатов зароса и/или слабой кластеризации данных, либо в случае очень маленьких таблиц.

Имеет ли смысл индексировать поля таблицы, имеющих тип boolean или подобные им — с небольшим количеством возможных значений?¶

Индекс по логическим полям имеет смысл, только в случае, если значения ИСТИНА и ЛОЖЬ распределены примерно равномерно по таблице. Предельный случай — две трети и одна треть.

Что такое агрегатная функция? Приведите примеры агрегатных функций в SQL.¶

Агрегатная функция — это функция, которая возвращает одиночное значение на основании множества записей.

Вот список некоторых агрегатных функций SQL:

  • COUNT — Возвращает количество строк источника записей
  • COUNT — Возвращает количество значений в указанном столбце
  • SUM — Возвращает сумму значений в указанном столбце
  • AVG — Возвращает среднее значение в указанном столбце
  • MIN — Возвращает минимальное значение в указанном столбце
  • MAX — Возвращает максимальное значение в указанном столбце

Дайте определение третьей нормальной форме БД.¶

Определение третьей нормальной форме БД.

  • Любое поле любой записи хранит только одно значение. (1NF) Например, если в поле хранится список идентификаторов, разделённых запятыми, то это нарушение данного определения.
  • Выполняется условие 1NF и любое неключевое поле полностью зависит от ключа. (2NF) Например, у нас есть запись с полями (Идентификатор, Название CD-Диска, Название группы), где ключом является поле «Идентификатор». При этом, очевидно, что поле «Название группы» зависит не только от «Идентификатора» но и от поля «Название CDДиска». Поэтому такая БД не находится во второй нормальной форме.
  • Выполняется условие 2NF и нет неключевых полей зависящих от значения других неключевых полей. Например у нас в записи хранятся код региона и его название. Понятно, что название региона зависит от кода, и наоборот, поэтому такая БД не будет находиться в третьей нормальной форме.

Что такое денормализация БД? Для чего она нужна?¶

Денормализация — это процесс осознанного приведения базы данных к виду, в котором она не будет соответствовать правилам нормализации. Обычно это необходимо для повышения производительности и скорости извлечения данных, за счет увеличения избыточности данных. Если приложению необходимо часто выполнять выборки, которые занимают слишком много времени (например, объединение данных из множества таблиц), то следует рассмотреть возможность проведения денормализации Возможное решение следующее: вынести результаты выборки в отдельную таблицу. Это позволит увеличить скорость выполнения запросов, но также означает появление необходимости в постоянном обслуживании этой новой таблицы. Прежде чем приступать к денормализации, необходимо убедится, что ожидаемые результаты оправдывают издержки, с которыми придется столкнуться.

Что такое триггер?¶

Триггер — это SQL процедура, которая срабатывает при каком-нибудь событии (INSERT, DELETE или UPDATE). Триггеры хранятся и управляются СУБД. Триггеры используются для поддержания ссылочной целостности данных в одинаковый манер реагируя на события изменения этих данных. Триггер не может быть вызван или выполнен вручную, СУБД автоматически вызывает его после модификации данных в соответствующей таблице. В этом и есть его отличие от хранимых процедур, которые нужно выполнять вручную вызовом CALL. Также триггер может вызывать другие процедуры. Триггер также может содержать вызовы INSERT, DELETE и UPDATE внутри себя, таким образом вызывая другой триггер. Такие триггеры называются вложенными (nested).

Что такое курсоры в базах данных?¶

Курсор — это объект базы данных, который позволяет приложениям работать с записями «по-одной», а не сразу с множеством, как это делается в обычных
SQL командах.

Порядок работы с курсором такой:

  • Определить курсор (DECLARE)
  • Открыть курсор (OPEN)
  • Получить запись из курсора (FETCH)
  • Обработать запись
  • Закрыть курсор (CLOSE)

Какие компромиссы предлагает использование индексов?¶

Некоторые из них:

  • Более быстрые выборки, но более медленные изменения. (При изменениях тратиться время на перестройку индекса).
  • Для хранения индексов необходимо дополнительное дисковое пространство.

Что делает SQL операция MERGE?¶

Операция MERGE официально появилась в стандарте ANSI SQL:2008. Она позволяет одновременно вставлять или изменять записи таблицы согласно критерию. При выполнении критерия строки изменяются, иначе — вставляются. Ее можно заменить последовательным вызовом INSERT и UPDATE. В некоторых базах данных похожая операция называется UPSERT.

В чем различие между выражениями HAVING и WHERE?¶

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

Выражения WHERE используются вместе с операциями SELECT, UPDATE, DELETE, в то время как HAVING только с SELECT и предложением GROUP BY. Например, WHERE нельзя использовать таким образом:

SELECT name, SUM(salary) FROM Employees WHERE SUM(salary) > 1000 GROUP BY name;

В данном случае больше подходит HAVING:

SELECT name, SUM(salary) FROM Employees GROUP BY name HAVING (salary) > 1000;

То есть, использовать WHERE в запросах с агрегатными функциями нельзя, для этого и был введен HAVING.

Что такое целостность данных? Объясните, что такое ограничения.¶

Целостность данных — важное свойство SQL. При правильном использовании оно обеспечивает корректность и валидность хранимых данных в любой момент времени. Также, с их помощью можно обнаруживать ошибки в приложениях, которые тяжело найти другими способами. Целостность данных поддерживается с помощью ограничений. В SQL стандарта ANSI есть 4 основных ограничения: PRIMARY KEY, CHECK, UNIQUE и FOREIGN KEY. Они не являются обязательными для таблицы.

  • PRIMARY KEY — набор полей (1 или более), значения которых образуют уникальную комбинацию и используются для однозначной идентификации записи в таблице. Для таблицы может быть создано только одно такое ограничение. Данное ограничение используется для обеспечения целостности сущности, которая описана таблицей.
  • CHECK используется для ограничения множества значений, которые могут быть помещены в данный столбец. Это ограничение используется для обеспечения целостности предметной области, которую описывают таблицы в базе.
  • Ограничение UNIQUE обеспечивает отсутствие дубликатов в столбце или наборе столбцов. Разница между PRIMARY KEY и UNIQUE описана в primary и unique ключи
  • Ограничение FOREIGN KEY защищает от действий, которые могут нарушить связи между таблицами. FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY, которым не отвечают записи PRIMARY KEY. Таким образом, FOREIGN KEY поддерживает ссылочную целостность данных.

В чем отличие между кластерными индексами и некластерными?¶

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

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

Какие отличия между ограничениями primary и unique?¶

Ограничения primary и unique призваны обеспечить уникальность значений столбца, на котором они определены. Но по умолчанию ограничение primary создает кластерный индекс на столбце, а unique — некластерный. Другим отличием является то, что primary не разрешает NULL записей, в то время как unique разрешает только одну NULL запись.

Как оператор group by обрабатывает поля с null

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

Выражениями значения являются:

Константа или непосредственное значение

Ссылка на столбец

Ссылка на позиционный параметр в теле определения функции или подготовленного оператора

Выражение с индексом

Выражение выбора поля

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

Применение правил сортировки

Конструктор табличной строки

В дополнение к этому списку есть ещё несколько конструкций, которые можно классифицировать как выражения, хотя они не соответствуют общим синтаксическим правилам. Они обычно имеют вид функции или оператора и будут рассмотрены в соответствующем разделе Главы 9. Пример такой конструкции — предложение IS NULL .

Мы уже обсудили константы в Подразделе 4.1.2. В следующих разделах рассматриваются остальные варианты.

4.2.1. Ссылки на столбцы

Ссылку на столбец можно записать в форме:

отношение.имя_столбца

Здесь отношение — имя таблицы (возможно, полное, с именем схемы) или её псевдоним, определённый в предложении FROM . Это имя и разделяющую точку можно опустить, если имя столбца уникально среди всех таблиц, задействованных в текущем запросе. (См. также Главу 7.)

4.2.2. Позиционные параметры

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

$число

Например, рассмотрим следующее определение функции dept :

CREATE FUNCTION dept(text) RETURNS dept AS $$ SELECT * FROM dept WHERE name = $1 $$ LANGUAGE SQL;

Здесь $1 всегда будет ссылаться на значение первого аргумента функции.

4.2.3. Индексы элементов

Если в выражении вы имеете дело с массивом, то можно извлечь определённый его элемент, написав:

выражение[индекс]

или несколько соседних элементов ( « срез массива » ):

выражение[нижний_индекс:верхний_индекс]

(Здесь квадратные скобки [ ] должны присутствовать буквально.) Каждый индекс сам по себе является выражением, результат которого округляется к ближайшему целому.

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

моя_таблица.столбец_массив[4] моя_таблица.столбец_массив_2d[17][34] $1[10:42] (функция_массив(a,b))[42]

В последней строке круглые скобки необходимы. Подробнее массивы рассматриваются в Разделе 8.15.

4.2.4. Выбор поля

Если результат выражения — значение составного типа (строка таблицы), тогда определённое поле этой строки можно извлечь, написав:

выражение.имя_поля

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

моя_таблица.столбец $1.столбец (функция_кортеж(a,b)).стол3

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

(составной_столбец).поле (моя_таблица.составной_столбец).поле

Здесь скобки нужны, чтобы показать, что составной_столбец — это имя столбца, а не таблицы, и что моя_таблица — имя таблицы, а не схемы.

Вы можете запросить все поля составного значения, написав .* :

(составной_столбец).*

Эта запись действует по-разному в зависимости от контекста; подробнее об этом говорится в Подразделе 8.16.5.

4.2.5. Применение оператора

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

выражение оператор выражение (бинарный инфиксный оператор)
оператор выражение (унарный префиксный оператор)
выражение оператор (унарный постфиксный оператор)

где оператор соответствует синтаксическим правилам, описанным в Подразделе 4.1.3, либо это одно из ключевых слов AND , OR и NOT , либо полное имя оператора в форме:

OPERATOR(схема.имя_оператора)

Существование конкретных операторов и их тип (унарный или бинарный) зависит от того, как и какие операторы определены системой и пользователем. Встроенные операторы описаны в Главе 9.

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

Вызов функции записывается просто как имя функции (возможно, дополненное именем схемы) и список аргументов в скобках:

имя_функции ([выражение [, выражение . ]])

Например, так вычисляется квадратный корень из 2:

sqrt(2)

Список встроенных функций приведён в Главе 9. Пользователь также может определить и другие функции.

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

Аргументам могут быть присвоены необязательные имена. Подробнее об этом см. Раздел 4.3.

Примечание

Функцию, принимающую один аргумент составного типа, можно также вызывать, используя синтаксис выбора поля, и наоборот, выбор поля можно записать в функциональном стиле. То есть записи col(table) и table.col равносильны и взаимозаменяемы. Это поведение не оговорено стандартом SQL, но реализовано в Postgres Pro , так как это позволяет использовать функции для эмуляции « вычисляемых полей » . Подробнее это описано в Подразделе 8.16.5.

4.2.7. Агрегатные выражения

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

агрегатная_функция (выражение [ , . ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция (ALL выражение [ , . ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция (DISTINCT выражение [ , . ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция ( * ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция ( [ выражение [ , . ] ] ) WITHIN GROUP ( предложение_order_by ) [ FILTER ( WHERE условие_фильтра ) ]

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

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

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

Например, count(*) подсчитает общее количество строк, а count(f1) только количество строк, в которых f1 не NULL (так как count игнорирует NULL), а count(distinct f1) подсчитает число различных и отличных от NULL значений столбца f1 .

Обычно строки данных передаются агрегатной функции в неопределённом порядке и во многих случаях это не имеет значения, например функция min выдаёт один и тот же результат независимо от порядка поступающих данных. Однако некоторые агрегатные функции (такие как array_agg и string_agg ) выдают результаты, зависящие от порядка данных. Для таких агрегатных функций можно добавить предложение_order_by и задать нужный порядок. Это предложение_order_by имеет тот же синтаксис, что и предложение ORDER BY на уровне запроса, как описано в Разделе 7.5, за исключением того, что его выражения должны быть просто выражениями, а не именами результирующих столбцов или числами. Например:

SELECT array_agg(a ORDER BY b DESC) FROM table;

Заметьте, что при использовании агрегатных функций с несколькими аргументами, предложение ORDER BY идёт после всех аргументов. Например, надо писать так:

SELECT string_agg(a, ',' ORDER BY a) FROM table;
SELECT string_agg(a ORDER BY a, ',') FROM table; -- неправильно

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

Если предложение_order_by дополнено указанием DISTINCT , тогда все выражения ORDER BY должны соответствовать обычным аргументам агрегатной функции; то есть вы не можете сортировать строки по выражению, не включённому в список DISTINCT .

Примечание

Возможность указывать и DISTINCT , и ORDER BY в агрегатной функции — это расширение Postgres Pro .

При добавлении ORDER BY в обычный список аргументов агрегатной функции, описанном до этого, выполняется сортировка входных строк для универсальных и статистических агрегатных функций, для которых сортировка необязательна. Но есть подмножество агрегатных функций, сортирующие агрегатные функции, для которых предложение_order является обязательным , обычно потому, что вычисление этой функции имеет смысл только при определённой сортировке входных строк. Типичными примерами сортирующих агрегатных функций являются вычисления ранга и процентиля. Для сортирующей агрегатной функции предложение_order_by записывается внутри WITHIN GROUP (. ) , что иллюстрирует последний пример, приведённый выше. Выражения в предложении_order_by вычисляются однократно для каждой входной строки как аргументы обычной агрегатной функции, сортируются в соответствии с требованием предложения_order_by и поступают в агрегатную функции как входящие аргументы. (Если же предложение_order_by находится не в WITHIN GROUP , оно не передаётся как аргумент(ы) агрегатной функции.) Выражения-аргументы, предшествующие WITHIN GROUP , (если они есть), называются непосредственными аргументами, а выражения, указанные в предложении_order_by — агрегируемыми аргументами. В отличие от аргументов обычной агрегатной функции, непосредственные аргументы вычисляются однократно для каждого вызова функции, а не для каждой строки. Это значит, что они могут содержать переменные, только если эти переменные сгруппированы в GROUP BY ; это суть то же ограничение, что действовало бы, будь эти непосредственные аргументы вне агрегатного выражения. Непосредственные аргументы обычно используются, например, для указания значения процентиля, которое имеет смысл, только если это конкретное число для всего расчёта агрегатной функции. Список непосредственных аргументов может быть пуст; в этом случае запишите просто () , но не (*) . (На самом деле Postgres Pro примет обе записи, но только первая соответствует стандарту SQL.)

Пример вызова сортирующей агрегатной функции:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households; percentile_cont ----------------- 50489

она получает 50-ый процентиль, или медиану, значения столбца income из таблицы households . В данном случае 0.5 — это непосредственный аргумент; если бы дробь процентиля менялась от строки к строке, это не имело бы смысла.

Если добавлено предложение FILTER , агрегатной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Например:

SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i); unfiltered | filtered ------------+---------- 10 | 4 (1 row)

Предопределённые агрегатные функции описаны в Разделе 9.20. Пользователь также может определить другие агрегатные функции.

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

Когда агрегатное выражение используется в подзапросе (см. Подраздел 4.2.11 и Раздел 9.22), оно обычно вычисляется для всех строк подзапроса. Но если в аргументах (или в условии_filter ) агрегатной функции есть только переменные внешнего уровня, агрегатная функция относится к ближайшему внешнему уровню и вычисляется для всех строк соответствующего запроса. Такое агрегатное выражение в целом является внешней ссылкой для своего подзапроса и на каждом вычислении считается константой. При этом допустимое положение агрегатной функции ограничивается списком результатов и предложением HAVING на том уровне запросов, где она находится.

4.2.8. Вызовы оконных функций

Вызов оконной функции представляет собой применение функции, подобной агрегатной, к некоторому набору строк, выбранному запросом. В отличие от вызовов не оконных агрегатных функций, они не связаны с группировкой выбранных строк в одну — каждая строка остаётся отдельной в результате запроса. Однако оконная функция имеет доступ ко всем строкам, вошедшим в группу текущей строки согласно указанию группировки (списку PARTITION BY ) в вызове оконной функции. Вызов оконной функции может иметь следующие формы:

имя_функции ([выражение [, выражение . ]]) [ FILTER ( WHERE предложение_фильтра ) ] OVER имя_окна имя_функции ([выражение [, выражение . ]]) [ FILTER ( WHERE предложение_фильтра ) ] OVER ( определение_окна ) имя_функции ( * ) [ FILTER ( WHERE предложение_фильтра ) ] OVER имя_окна имя_функции ( * ) [ FILTER ( WHERE предложение_фильтра ) ] OVER ( определение_окна )

Здесь определение_окна записывается в виде

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

и необязательное определение_рамки может иметь вид:

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

Здесь начало_рамки и конец_рамки задаются одним из следующих способов:

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

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

имя_окна — ссылка на именованное окно, определённое предложением WINDOW в данном запросе. Также возможно написать в скобках полное определение_окна , используя тот же синтаксис определения именованного окна в предложении WINDOW ; подробнее это описано в справке по SELECT . Стоит отметить, что запись OVER имя_окна не полностью равнозначна OVER (имя_окна . ) ; последний вариант подразумевает копирование и изменение определения окна и не будет допустимым, если определение этого окна включает определение рамки.

Указание PARTITION BY группирует строки запроса в разделы, которые затем обрабатываются оконной функцией независимо друг от друга. PARTITION BY работает подобно предложению GROUP BY на уровне запроса, за исключением того, что его аргументы всегда просто выражения, а не имена выходных столбцов или числа. Без PARTITION BY все строки, выдаваемые запросом, рассматриваются как один раздел. Указание ORDER BY определяет порядок, в котором оконная функция обрабатывает строки раздела. Оно так же подобно предложению ORDER BY на уровне запроса и так же не принимает имена выходных столбцов или числа. Без ORDER BY строки обрабатываются в неопределённом порядке.

определение_рамки задаёт набор строк, образующих рамку окна, которая представляет собой подмножество строк текущего раздела и используется для оконных функций, работающих с рамкой, а не со всем разделом. Рамку можно указать в режимах RANGE или ROWS ; в любом случае она начинается с положения начало_рамки и заканчивается положением конец_рамки . Если конец_рамки опущен, подразумевается CURRENT ROW (текущая строка).

Если начало_рамки задано как UNBOUNDED PRECEDING , рамка начинается с первой строки раздела, а если конец_рамки определён как UNBOUNDED FOLLOWING , рамка заканчивается последней строкой раздела.

В режиме RANGE начало_рамки , заданное как CURRENT ROW , определяет в качестве начала первую родственную строку (строку, которую ORDER BY считает равной текущей), тогда как конец_рамки , заданный как CURRENT ROW , определяет концом рамки последнюю родственную (для ORDER BY ) строку. В режиме ROWS вариант CURRENT ROW просто обозначает текущую строку.

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

По умолчанию рамка определяется как RANGE UNBOUNDED PRECEDING , что равносильно расширенному определению RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . С указанием ORDER BY это означает, что рамка будет включать все строки от начала раздела до последней строки, родственной текущей (для ORDER BY ). Без ORDER BY в рамку включаются все строки раздела, так как все они считаются родственными текущей.

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

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

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

Запись со звёздочкой ( * ) применяется при вызове не имеющих параметров агрегатных функций в качестве оконных, например count(*) OVER (PARTITION BY x ORDER BY y) . Звёздочка ( * ) обычно не применяется для исключительно оконных функций. Такие функции не допускают использования DISTINCT и ORDER BY в списке аргументов функции.

Вызовы оконных функций разрешены в запросах только в списке SELECT и в предложении ORDER BY .

Дополнительно об оконных функциях можно узнать в Разделе 3.5, Разделе 9.21 и Подразделе 7.2.5.

4.2.9. Приведения типов

Приведение типа определяет преобразование данных из одного типа в другой. Postgres Pro воспринимает две равносильные записи приведения типов:

CAST ( выражение AS тип ) выражение::тип

Запись с CAST соответствует стандарту SQL, тогда как вариант с :: — историческое наследие Postgres Pro .

Когда приведению подвергается значение выражения известного типа, происходит преобразование типа во время выполнения. Это приведение будет успешным, только если определён подходящий оператор преобразования типов. Обратите внимание на небольшое отличие от приведения констант, описанного в Подразделе 4.1.2.7. Приведение строки в чистом виде представляет собой начальное присваивание строковой константы и оно будет успешным для любого типа (конечно, если строка содержит значение, приемлемое для данного типа данных).

Явное приведение типа можно опустить, если возможно однозначно определить, какой тип должно иметь выражение (например, когда оно присваивается столбцу таблицы); в таких случаях система автоматически преобразует тип. Однако автоматическое преобразование выполняется только для приведений с пометкой « допускается неявное применение » в системных каталогах. Все остальные приведения должны записываться явно. Это ограничение позволяет избежать сюрпризов с неявным преобразованием.

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

имя_типа ( выражение )

Однако это будет работать только для типов, имена которых являются также допустимыми именами функций. Например, double precision так использовать нельзя, а float8 (альтернативное название того же типа) — можно. Кроме того, имена типов interval , time и timestamp из-за синтаксического конфликта можно использовать в такой записи только в кавычках. Таким образом, запись приведения типа в виде вызова функции провоцирует несоответствия и, возможно, лучше будет её не применять.

Примечание

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

4.2.10. Применение правил сортировки

Предложение COLLATE переопределяет правило сортировки выражения. Оно добавляется после выражения:

выражение COLLATE правило_сортировки

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

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

Предложение COLLATE имеет два распространённых применения: переопределение порядка сортировки в предложении ORDER BY , например:

SELECT a, b, c FROM tbl WHERE . ORDER BY a COLLATE "C";

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

SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

Заметьте, что в последнем случае предложение COLLATE добавлено к аргументу оператора, на действие которого мы хотим повлиять. При этом не имеет значения, к какому именно аргументу оператора или функции добавляется COLLATE , так как правило сортировки, применяемое к оператору или функции, выбирается при рассмотрении всех аргументов, а явное предложение COLLATE переопределяет правила сортировки для всех других аргументов. (Однако добавление разных предложений COLLATE к нескольким аргументам будет ошибкой. Подробнее об этом см. Раздел 22.2.) Таким образом, эта команда выдаст тот же результат:

SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

Но это будет ошибкой:

SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

здесь правило сортировки нельзя применить к результату оператора > , который имеет несравниваемый тип данных boolean .

4.2.11. Скалярные подзапросы

Скалярный подзапрос — это обычный запрос SELECT в скобках, который возвращает ровно одну строку и один столбец. (Написание запросов освещается в Главе 7.) После выполнения запроса SELECT его единственный результат используется в окружающем его выражении. В качестве скалярного подзапроса нельзя использовать запросы, возвращающие более одной строки или столбца. (Но если в результате выполнения подзапрос не вернёт строк, скалярный результат считается равным NULL.) В подзапросе можно ссылаться на переменные из окружающего запроса; в процессе одного вычисления подзапроса они будут считаться константами. Другие выражения с подзапросами описаны в Разделе 9.22.

Например, следующий запрос находит самый населённый город в каждом штате:

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states;

4.2.12. Конструкторы массивов

Конструктор массива — это выражение, которое создаёт массив, определяя значения его элементов. Конструктор простого массива состоит из ключевого слова ARRAY , открывающей квадратной скобки [ , списка выражений (разделённых запятыми), задающих значения элементов массива, и закрывающей квадратной скобки ] . Например:

SELECT ARRAY[1,2,3+4]; array --------- (1 row)

По умолчанию типом элементов массива считается общий тип для всех выражений, определённый по правилам, действующим и для конструкций UNION и CASE (см. Раздел 10.5). Вы можете переопределить его явно, приведя конструктор массива к требуемому типу, например:

SELECT ARRAY[1,2,22.7]::integer[]; array ---------- (1 row)

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

Многомерные массивы можно образовывать, вкладывая конструкторы массивов. При этом во внутренних конструкторах слово ARRAY можно опускать. Например, результат работы этих конструкторов одинаков:

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; array --------------- ,> (1 row) SELECT ARRAY[[1,2],[3,4]]; array --------------- ,> (1 row)

Многомерные массивы должны быть прямоугольными, и поэтому внутренние конструкторы одного уровня должны создавать вложенные массивы одинаковой размерности. Любое приведение типа, применённое к внешнему конструктору ARRAY , автоматически распространяется на все внутренние.

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

CREATE TABLE arr(f1 int[], f2 int[]); INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); SELECT ARRAY[f1, f2, ',>'::int[]] FROM arr; array ------------------------------------------------ ,>,,>,,>> (1 row)

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

SELECT ARRAY[]::integer[]; array ------- <> (1 row)

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

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); array ----------------------------------------------------------------------- (1 row) SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); array ---------------------------------- ,,,,> (1 row)

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

Индексы массива, созданного конструктором ARRAY , всегда начинаются с одного. Подробнее о массивах вы узнаете в Разделе 8.15.

4.2.13. Конструкторы табличных строк

Конструктор табличной строки — это выражение, создающее строку или кортеж (или составное значение) из значений его аргументов-полей. Конструктор строки состоит из ключевого слова ROW , открывающей круглой скобки, нуля или нескольких выражений (разделённых запятыми), определяющих значения полей, и закрывающей скобки. Например:

SELECT ROW(1,2.5,'this is a test');

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

Конструктор строки поддерживает запись составное_значение .* , при этом данное значение будет развёрнуто в список элементов, так же, как в записи .* на верхнем уровне списка SELECT (см. Подраздел 8.16.5). Например, если таблица t содержит столбцы f1 и f2 , эти записи равнозначны:

SELECT ROW(t.*, 42) FROM t; SELECT ROW(t.f1, t.f2, 42) FROM t;

Примечание

До версии PostgreSQL 8.2 запись .* не разворачивалась в конструкторах строк, так что выражение ROW(t.*, 42) создавало составное значение из двух полей, в котором первое поле так же было составным. Новое поведение обычно более полезно. Если вам нужно получить прежнее поведение, чтобы одно значение строки было вложено в другое, напишите внутреннее значение без .* , например: ROW(t, 42) .

По умолчанию значение, созданное выражением ROW , имеет тип анонимной записи. Если необходимо, его можно привести к именованному составному типу — либо к типу строки таблицы, либо составному типу, созданному оператором CREATE TYPE AS . Явное приведение может потребоваться для достижения однозначности. Например:

CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Приведение не требуется, так как существует только одна getf1() SELECT getf1(ROW(1,2.5,'this is a test')); getf1 ------- 1 (1 row) CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Теперь приведение необходимо для однозначного выбора функции: SELECT getf1(ROW(1,2.5,'this is a test')); ОШИБКА: функция getf1(record) не уникальна SELECT getf1(ROW(1,2.5,'this is a test')::mytable); getf1 ------- 1 (1 row) SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); getf1 ------- 11 (1 row)

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

SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); -- выбрать все строки, содержащие только NULL SELECT ROW(table.*) IS NULL FROM table;

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

4.2.14. Правила вычисления выражений

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

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

SELECT true OR somefunc();

тогда функция somefunc() не будет вызываться (возможно). То же самое справедливо для записи:

SELECT somefunc() OR true;

Заметьте, что это отличается от « оптимизации » вычисления логических операторов слева направо, реализованной в некоторых языках программирования.

Как следствие, в сложных выражениях не стоит использовать функции с побочными эффектами. Особенно опасно рассчитывать на порядок вычисления или побочные эффекты в предложениях WHERE и HAVING , так как эти предложения тщательно оптимизируются при построении плана выполнения. Логические выражения (сочетания AND / OR / NOT ) в этих предложениях могут быть видоизменены любым способом, допустимым законами Булевой алгебры.

Когда порядок вычисления важен, его можно зафиксировать с помощью конструкции CASE (см. Раздел 9.17). Например, такой способ избежать деления на ноль в предложении WHERE ненадёжен:

SELECT . WHERE x > 0 AND y/x > 1.5;
SELECT . WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Применяемая так конструкция CASE защищает выражение от оптимизации, поэтому использовать её нужно только при необходимости. (В данном случае было бы лучше решить проблему, переписав условие как y > 1.5*x .)

Однако CASE не всегда спасает в подобных случаях. Показанный выше приём плох тем, что не предотвращает раннее вычисление константных подвыражений. Как описано в Разделе 35.6, функции и операторы, помеченные как IMMUTABLE , могут вычисляться при планировании, а не выполнении запроса. Поэтому в примере

SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

, скорее всего, произойдёт деление на ноль из-за того, что планировщик попытается упростить константное подвыражение, даже если во всех строках в таблице x > 0 , а значит во время выполнения ветвь ELSE никогда не будет выполняться.

Хотя этот конкретный пример может показаться надуманным, похожие ситуации, в которых неявно появляются константы, могут возникать и в запросах внутри функций, так как значения аргументов функции и локальных переменных при планировании могут быть заменены константами. Поэтому, например, в функциях PL/pgSQL гораздо безопаснее для защиты от рискованных вычислений использовать конструкцию IF - THEN - ELSE , чем выражение CASE .

Ещё один подобный недостаток этого подхода в том, что CASE не может предотвратить вычисление заключённого в нём агрегатного выражения, так как агрегатные выражения вычисляются перед всеми остальными в списке SELECT или предложении HAVING . Например, в следующем запросе может возникнуть ошибка деления на ноль, несмотря на то, что он вроде бы защищён от неё:

SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments;

Агрегатные функции min() и avg() вычисляются независимо по всем входным строкам, так что если в какой-то строке поле employees окажется равным нулю, деление на ноль произойдёт раньше, чем станет возможным проверить результат функции min() . Поэтому, чтобы проблемные входные строки изначально не попали в агрегатную функцию, следует воспользоваться предложениями WHERE или FILTER .

Пред. Наверх След.
4.1. Лексическая структура Начало 4.3. Вызов функций
Похожие публикации:
  1. Camera extensions proxy что это
  2. Как завершить процесс c
  3. Как сделать матрицу в ворде
  4. Почему не работает построение графиков в калькуляторе

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *