День семьсот шестьдесят второй. #ЗаметкиНаПолях #SQL
На третьем году существования канала я понял, что есть один язык, который я незаслуженно обходил вниманием. Восполняю упущение. Начинаю новую серию постов про SQL. Думаю, что основные основы читателям моего канала объяснять не надо (если надо, пишите в комментариях, что интересно), поэтому буду писать о «фишках». Проблема в том, что они различаются как по наличию в разных СУБД, так и по синтаксису. Поэтому буду стараться выбирать более-менее универсальные вещи и описывать концепцию, а не особенности синтаксиса.

Возвращение Данных из DML Запроса
Поначалу это может показаться странным, но возможность возвращать данные из DML - очень полезная функция.
- В запросе INSERT:
Наиболее распространённый вариант использования - получение автоматически сгенерированных значений. Например, ключей для создания записей в дочерних таблицах. Кроме того, возвращённые таким образом данные могут использоваться для аудита или логирования запросов.
- В запросе UPDATE:
Здесь выражение это используется не так часто, но всё же возможно его применение для получения значений по умолчанию, заданных в базе данных, либо для аудита запросов.
- В запросе DELETE:
Совсем редкий случай использования, в основном для целей аудита, либо, возможно, переноса данных в архивную таблицу, хотя так лучше не делать.

СУБД: Oracle, PostgreSQL, MariaDB
Выражение: RETURNING
Синтаксис:

INSERT INTO | UPDATE | DELETE FROM table …
RETURNING expression1 [INTO variable1]
[, expression2 [INTO variable2]] …;

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

my_row mytable%ROWTYPE;
…
RETURNING * INTO my_row;

Пример:

INSERT INTO users (firstname, lastname) 
VALUES ('Joe', 'Cool') RETURNING id;

СУБД: MsSQL
Выражение: OUTPUT
Синтаксис:

INSERT INTO | UPDATE | DELETE FROM table …
OUTPUT INSERTED.expression1 [INTO variable1]
[, DELETED.expression2 [INTO variable2]] …;

В MsSQL также можно обратиться к псевдотаблицам INSERTED и DELETED. В запросе UPDATE – DELETED представляет заменяемое значение, INSERTED представляет новое значение поля. В запросах INSERT и DELETE используется соответствующая псевдотаблица.

Пример:

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;

Источники:
- https://app.pluralsight.com/library/courses/postgresql-data-manipulation-playbook/
- https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

День семьсот семьдесят девятый. #ЗаметкиНаПолях #SQL
Выражение WITH и Сложные Запросы
В разработке ПО обычной практикой является инкапсуляция инструкций в небольшие и легко понятные единицы - функции или методы. В SQL мы оперируем не инструкциями, а запросами. Чтобы сделать запросы многоразовыми, в SQL-92 были введены представления (view). После создания представление получает имя в схеме базы данных, чтобы другие запросы могли использовать его как таблицу. В SQL-99 добавлено предложение WITH для определения «представлений в области действия оператора». Они не хранятся в схеме базы данных, а живут только в рамках текущего запроса. Это позволяет улучшить структуру запроса, не загрязняя глобальное пространство имен. Предложение WITH также известно как общее табличное выражение (CTE).

Синтаксис

WITH query1 (column1, …) AS
  (SELECT … FROM table …)
SELECT * FROM query1;

WITH не является самостоятельной командой, за ним должен следовать SELECT. Запрос SELECT (и содержащиеся в нем подзапросы) могут ссылаться в своём блоке FROM на определённое в предложении WITH имя подзапроса.

Одно предложение WITH может определять несколько имён подзапросов, разделяя их запятыми. Каждый из этих подзапросов может ссылаться на ранее определённые имена подзапросов:

WITH 
  query1 AS (SELECT …), 
  query2 AS (SELECT … FROM query1 …)
SELECT …

ВАЖНО! Имена подзапросов, определённые в предложении WITH скрывают таблицы или представления с тем же именем.

СУБД
Базовая функциональность предложения WITH доступна во всех современных СУБД.

Производительность
- Большинство баз данных обрабатывают WITH-запросы так же, как и представления: они заменяют ссылку на запрос его определением и оптимизируют общий запрос.
*PostgreSQL до версии 12 оптимизировала каждый подзапрос и главный запрос независимо друг от друга.
- Если WITH-запрос упоминается несколько раз, некоторые базы данных кешируют его результат, чтобы предотвратить двойное выполнение.

Полезные расширения
1. WITH как префикс DML запроса (PostgreSQL, SQL Server, SQLite)
Некоторые СУБД позволяют использовать WITH не только с запросами SELECT, но и с запросами на манипуляцию с данными (INSERT, UPDATE, DELETE).

2. WITH как цель DML (SQL Server)
SQL Server также позволяет использовать WITH-запрос как цель DML запроса, то есть создавать обновляемые представления.

3. Функции в WITH (Oracle)
Oracle, начиная с версии 12cR1 позволяет определять функции и процедуры в предложении WITH.

4. DML в WITH (PostgreSQL)
Начиная с версии 9.1 PostgreSQL поддерживает использование DML запросов внутри предложения WITH. А при использовании выражения RETURNING, предложение WITH возвращает данные в основной запрос. Таким образом, например, можно сделать запрос к только что вставленным в таблицу записям:

WITH added AS (
  INSERT INTO table1 …
  RETURNING *
)
SELECT * FROM added;

Источник: https://modern-sql.com/feature/with

День семьсот девяносто шестой. #ЗаметкиНаПолях #SQL
Ограничение Результатов с Помощью Оконных Функций
Оконные (window) аналитические функции давно присутствуют в SQL, однако многие до сих пор не умеют их применять.

Окно (window) - это набор строк таблицы, которые можно анализировать или применить к ним функцию. Строки должны быть как-то связаны. Иногда они связаны с конкретной строкой, т.е. строки могут быть выше или ниже друг друга или в пределах заданного диапазона. Либо связь может быть основана на отдельных группах данных в наборе.

Все оконные функции следуют определенному синтаксису.

<название функции>(<выражение>) OVER (
  <окно>
  <сортировка>
)

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

В качестве оконных можно использовать простые агрегатные функции, вроде SUM(), COUNT(), AVG() и т.п. Но есть и несколько специальных.

Номера и ранг строк
Простейший вариант применения оконной функции – вывести номера строк результата (см. пример 1 на картинке ниже). Здесь «окном» является весь набор, а функция ROW_NUMBER() выводит номер строки по порядку.
Мы можем разделить набор данных на группы, используя PARTITION BY. В примере 2 на картинке ниже тот же набор разделён по полю name, а внутри каждой такой группы упорядочен по полю course.

Кроме этого две функции RANK() и DENSE_RANK() выводят ранг строки. Они отличаются от ROW_NUMBER() тем, что при равенстве результатов задают строкам одинаковые значения. При этом DENSE_RANK() продолжает нумерацию, например, 1,1,2,2,3. А RANK() использует следующий номер строки по порядку, оставляя разрывы в нумерации: 1,1,3,3,5. В примере 3 на картинке ниже приводится сравнение этих функций на том же наборе данных. Здесь использована сортировка всего набора по полю name, без разбиения на группы.

Заметьте, что выражение ORDER BY внутри оконной функции никак не связано с выражением ORDER BY всего запроса. Оно влияет только на результаты оконной функции. В предыдущем примере мы могли бы добавить ORDER BY ко всему запросу и изменить порядок строк в запросе, но результаты функций ROW_NUMBER(), RANK() и DENSE_RANK() в строках не изменились бы.

Первое и последнее значение
Эти функции позволяют вывести первое или последнее соответственно значение столбца в группе. В отличие от функций, описанных выше, для каждой группы будет выведена только одна строка. Например, если использовать LAST_VALUE(course) на наборе данных из примера 2(последний по алфавиту курс для каждого студента), то будет выведено 3 строки:
Jason Economics
Lucy Health Science
Martha Biology.

Предыдущие и последующие строки
Часто бывает полезно сравнивать строки с предыдущими или последующими, особенно если данные упорядочены (например, хронологически). Для этого используются функции LAG(), которая извлекает значения из предыдущих строк, и LEAD(), которая извлекает значения из последующих строк. В примере 4 на картинке ниже мы получаем значение суммы из предыдущего месяца с помощью функции LAG(sales,1). В функцию, помимо названия столбца, передаётся целое число, означающее количество строк, которые нужно отсчитать от текущей (в нашем случае 1). Для первой строки, очевидно, нет предыдущего значения, поэтому функция возвращает NULL.

Источник: https://app.pluralsight.com/library/courses/combining-filtering-data-postgresql