Пейджинг данных CTE

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

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

WITH Orders_CTE AS (
SELECT OrderID, CustomerID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
FROM Orders
)
SELECT OrderID, CustomerID, OrderDate
FROM Orders_CTE
WHERE RowNumber BETWEEN {start} AND {end}

- {start} – начальный номер строки
- {end} – конечный номер строки

Задача повышенной сложности

В базе данных есть таблица "orders" с полями "id" (уникальный идентификатор заказа), "customer_id" (уникальный идентификатор клиента, который сделал заказ), "total_price" (общая стоимость заказа) и "created_at" (дата создания заказа). Необходимо найти все заказы, сделанные клиентом, сумма которых превышает среднюю сумму заказов всех клиентов. Результат должен быть отсортирован по дате создания заказа в порядке убывания.

Решение:
SELECT orders.id, orders.total_price, orders.created_at
FROM orders
WHERE orders.customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING AVG(total_price) < (
SELECT AVG(total_price)
FROM orders
)
)
ORDER BY orders.created_at DESC;

Table Hints в T-SQL

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

Некоторые хинты для таблиц:

- INDEX – оптимизатор запросов будет использовать указанный индекс при обработке запроса;
- FORCESEEK – оптимизатор запросов будет использовать только операцию поиска в индексе в качестве пути доступа к данным;
- FORCESCAN – оптимизатор запросов будет использовать только операцию сканирования в индексе в качестве пути доступа к данным;
- ROWLOCK – блокировки строк применяются вместо блокировки страниц или таблиц;
- HOLDLOCK – накладывает дополнительные ограничения на совмещаемую блокировку, удерживая ее до завершения транзакции. Хинт HOLDLOCK равнозначен хинту SERIALIZABLE;
- NOLOCK – разрешает «грязное чтение». Хинт NOLOCK равнозначен хинту READUNCOMMITTED;
- NOEXPAND – указывает, что индексированное представление не расширяется для доступа к базовым таблицам. Такое представление обрабатывается так же, как и таблица с кластеризованным индексом.

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

Почему данный код выведет ошибку?

SELECT id, name, CONCAT ('EU:', city) AS city_m
FROM customers
WHERE city_m = 'EU:Oslo';

Код выдаст ошибку, так как в данном случае alias нельзя использовать в операторе WHERE.

Порядок выполнения упомянутого SELECT запроса следующий:
1. FROM
2. WHERE
3. SELECT

Соответственно при выполнении команды WHERE, alias, задаваемый в SELECT еще неизвестен.

Для PostgreSQL или MySQL данную проблему можно решить, например, следующим образом:

```WITH ct AS (
SELECT id, name, CONCAT ('EU:', city) AS city_m
FROM customers
)
SELECT ct.*
FROM ct
WHERE ct.city_m = 'EU:Oslo';

#вопросы_с_собеседований
Имеет ли значение порядок колонок в составном индексе?

Да, порядок колонок в составном индексе имеет значение.

Рассмотрим пример:

У нас есть таблица orders с колонками order_id, customer_id, order_date, product_id и quantity. Нам необходимо создать составной индекс для ускорения поиска заказов по customer_id и order_date.

Мы можем создать два индекса:

1. Индекс по (customer_id, order_date)
2. Индекс по (order_date, customer_id)

В первом случае, запросы с условием WHERE customer_id = ? AND order_date = ? будут выполняться быстрее, так как эти колонки находятся в порядке, указанном в индексе.

Во втором случае, запросы с условием WHERE order_date = ? AND customer_id = ? будут выполняться медленнее, так как порядок колонок не соответствует порядку в индексе.

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

Пейджинг данных OFFSET…FECTCH

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

Оператор OFFSET…FETCH был добавлен в SQL Server 2012 и позволяет выбирать данные с определенной позиции. Он имеет следующий синтаксис:

SELECT {columns}
FROM {table}
ORDER BY {column}
OFFSET {offset} ROWS
FETCH NEXT {fetch} ROWS ONLY

- {columns} – список столбцов для извлечения данных
- {table} – имя таблицы
- {column} – столбец для сортировки
- {offset} – количество строк для пропуска
- {fetch} – количество строк для выборки

Стоимостной подход в SQL

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

Для выполнения данного запроса в SQL сервере может быть несколько возможных планов, например:

1. Сначала выполнить выборку из таблицы customers, затем из orders. Далее объединить результаты с помощью оператора JOIN по customer_id.
2. Сначала выполнить выборку из таблицы orders, затем из customers. Далее объединить результаты с помощью оператора JOIN по customer_id.
3. Использовать индексы на таблицах для выполнения быстрой выборки.

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

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