Представления и первые CTE

Курс MS SQL с нуля · Урок 10 из 12 · Часть 4. Запросы как инструмент

Способ сохранить сложный запрос под именем — последний инструмент уровня «читать». Представление живёт в схеме базы постоянно, CTE — в пределах оператора, временная таблица — в пределах сеанса. Урок даёт все три и критерии выбора.

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

Содержание

1. Что вы освоите
- Перейти к разделу
2. Представления: именованный текст
- Перейти к разделу
3. CTE: именованный результат
- Перейти к разделу
4. Временные таблицы
- Перейти к разделу
- Критерии выбора инструмента
5. Задачи и эталонные решения
- Перейти к разделу
6. Типичные ошибки урока
- Перейти к разделу
7. Чек-лист практики и источники
- Перейти к разделу

1. Что вы освоите

К концу урока вы:

создаёте и меняете представления идемпотентной командой CREATE OR ALTER;
объясняете, что VIEW хранит текст, а не данные;
разбиваете сложный запрос цепочкой CTE с читаемыми именами;
пользуетесь временной таблицей в пределах одной консоли;
выбираете инструмент по времени жизни и аудитории результата.

↑ К оглавлению

2. Представления: именованный текст

Представление — сохранённый текст запроса, а не данные: при обращении запрос выполняется заново. Ценности организационные: именованный контракт над сборкой соединений, единая точка исправления логики.

Чистый код

CREATE OR ALTER VIEW dbo.v_CatalogActive
AS
SELECT p.ProductID, p.Name AS product_name, p.Price, p.StockQty,
       cat.Name AS category_name, par.Name AS parent_category_name
FROM dbo.Products   AS p
JOIN dbo.Categories AS cat ON cat.CategoryID = p.CategoryID
LEFT JOIN dbo.Categories AS par ON par.CategoryID = cat.ParentCategoryID
WHERE p.IsActive = 1;
GO

Построчный разбор

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

CREATE OR ALTER VIEW dbo.v_CatalogActive
-- → идемпотентная форма: создаёт или заменяет текст единым оператором
AS
SELECT p.ProductID, p.Name AS product_name, p.Price, p.StockQty,
       cat.Name AS category_name, par.Name AS parent_category_name
-- → все вычисляемые и одноимённые столбцы поименованы
FROM dbo.Products   AS p
JOIN dbo.Categories AS cat ON cat.CategoryID = p.CategoryID
LEFT JOIN dbo.Categories AS par ON par.CategoryID = cat.ParentCategoryID
-- → корневой категории родителя нет: LEFT сохраняет её строку
WHERE p.IsActive = 1;
-- → постоянный фильтр витрины: только продающиеся товары
GO

внутри VIEW нет ORDER BY без TOP: порядок принадлежит боевому запросу;
параметров у представления нет: фильтры подаются снаружи в WHERE;
изменение данных идёт базовыми таблицами, не через VIEW.

«VIEW медленный, материализуйте»: представление ничего не хранит и само по себе скорости не добавляет и не отнимает: план строится по тексту запроса. Это организационный инструмент; материализация — отдельная тема продвинутого курса.

↑ К оглавлению

3. CTE: именованный результат

CTE — именованный результат в пределах одного оператора; не хранится нигде и раскрывается оптимизатором как подзапрос. Ценность — читаемость многошаговой логики сверху вниз:

Чистый код

WITH cat_revenue AS
(
    SELECT cat.CategoryID, cat.Name,
           SUM(oi.Quantity * oi.UnitPrice) AS revenue
    FROM dbo.Categories  AS cat
    JOIN dbo.Products    AS p  ON p.CategoryID = cat.CategoryID
    JOIN dbo.OrderItems  AS oi ON oi.ProductID = p.ProductID
    GROUP BY cat.CategoryID, cat.Name
),
rich AS
(
    SELECT CategoryID, Name, revenue
    FROM cat_revenue
    WHERE revenue BETWEEN 1000000 AND 999999999999
)
SELECT Name, revenue
FROM rich
ORDER BY revenue DESC;

Построчный разбор

WITH cat_revenue AS
-- → первый ступенчатый результат: оборот каждой категории
(
    SELECT cat.CategoryID, cat.Name,
           SUM(oi.Quantity * oi.UnitPrice) AS revenue
    FROM dbo.Categories  AS cat
    JOIN dbo.Products    AS p  ON p.CategoryID = cat.CategoryID
    JOIN dbo.OrderItems  AS oi ON oi.ProductID = p.ProductID
    GROUP BY cat.CategoryID, cat.Name
),
rich AS
-- → вторая ступень читает первую: только оборотнейшие категории
(
    SELECT CategoryID, Name, revenue
    FROM cat_revenue
    WHERE revenue BETWEEN 1000000 AND 999999999999
)
SELECT Name, revenue
FROM rich
ORDER BY revenue DESC;
-- → внешний запрос стал тривиальным: цепочка читается сверху вниз

Точка с запятой перед WITH: WITH допустим лишь в начале оператора; привычка закрывать предыдущий оператор точкой с запятой снимает знаменитую ошибку Msg 102/156 навсегда.

Куда расти: глубокая сила CTE — рекурсия обхода деревьев: это урок 2 продвинутого курса, и категории TechStore уже готовы к нему.

↑ К оглавлению

4. Временные таблицы

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

Чистый код

DECLARE @last datetime2 = (SELECT MAX(o.OrderDate) FROM dbo.Orders AS o);

SELECT o.OrderID, o.OrderDate, o.CustomerID, o.Status, o.TotalAmount
INTO #recent_orders
FROM dbo.Orders AS o
WHERE o.OrderDate BETWEEN DATEADD(day, -19, CAST(CAST(@last AS date) AS datetime2))
                      AND @last;

SELECT Status, COUNT(*) AS orders_cnt
FROM #recent_orders
GROUP BY Status;
GO

Построчный разбор

DECLARE @last datetime2 = (SELECT MAX(o.OrderDate) FROM dbo.Orders AS o);
-- → опорная точка: самый свежий заказ в данных

SELECT o.OrderID, o.OrderDate, o.CustomerID, o.Status, o.TotalAmount
INTO #recent_orders
-- → временная таблица сеанса создаётся на лету из выборки
FROM dbo.Orders AS o
WHERE o.OrderDate BETWEEN DATEADD(day, -19, CAST(CAST(@last AS date) AS datetime2))
                      AND @last;
-- → последние двадцать дней от опорной точки

SELECT Status, COUNT(*) AS orders_cnt
-- → вторая половина работы идёт по маленькой таблице
FROM #recent_orders
GROUP BY Status;
-- → из другой консоли таблица с решёткой невидима: Msg 208 вне сеанса
GO

Другая консоль — другой сеанс: временная таблица недоступна из второй консоли: диагноз Msg 208. Для переноса промежуточных данных между сеансами рождены постоянные служебные таблицы, а не решётка.

Критерии выбора инструмента

Инструмент	Живёт	Когда берётся
VIEW	в схеме базы постоянно	повторное использование многими запросами и людьми
CTE	один оператор	многошаговая логика одной задачи
#таблица	сеанс консоли	осмотр промежуточных шагов при разборе

↑ К оглавлению

5. Задачи и эталонные решения

Шесть задач на контексте TechStore.

представление каталога активных товаров с двумя именами категорий;
представление месячной выручки: год-месяц, число заказов, выручка;
средний чек по месяцам запросом над представлением, без единственного JOIN снаружи;
цепочка из двух CTE: выручка категорий и отбор категорий-миллионников;
временная таблица последних двадцати дней данных и отдельный отчёт по ней в той же консоли;
уборка: представления и таблица с решёткой удалены, sys.views девственен.

Эталон представления-отчёта

задача 10.2

Чистый код

CREATE OR ALTER VIEW dbo.v_MonthlyRevenue
AS
SELECT YEAR(o.OrderDate)  AS order_year,
       MONTH(o.OrderDate) AS order_month,
       COUNT(*)           AS orders_cnt,
       SUM(o.TotalAmount) AS revenue
FROM dbo.Orders AS o
GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate);
GO

SELECT order_year, order_month,
       CAST(revenue * 1.0 / orders_cnt AS DECIMAL(18,2)) AS avg_check
FROM dbo.v_MonthlyRevenue
ORDER BY order_year, order_month;
GO

Построчный разбор

CREATE OR ALTER VIEW dbo.v_MonthlyRevenue
-- → витрина ежемесячной динамики
AS
SELECT YEAR(o.OrderDate)  AS order_year,
       MONTH(o.OrderDate) AS order_month,
       COUNT(*)           AS orders_cnt,
       SUM(o.TotalAmount) AS revenue
-- → четыре поименованных столбца отчёта
FROM dbo.Orders AS o
GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate);
GO

SELECT order_year, order_month,
       CAST(revenue * 1.0 / orders_cnt AS DECIMAL(18,2)) AS avg_check
-- → внешний запрос без соединений: средний чек из готовых величин
FROM dbo.v_MonthlyRevenue
ORDER BY order_year, order_month;
GO

↑ К оглавлению

6. Типичные ошибки урока

CREATE VIEW вместо CREATE OR ALTER — повторный запуск падает «object already exists»; идемпотентная форма у курса единственная.
ORDER BY внутри VIEW — синтаксис проглотит его только с TOP, а порядок всё равно не гарантируется внешним запросам.
CTE после незавершённого оператора — Msg 102; привычка точки с запятой закрывает случай навсегда.
Таблица с решёткой из другой консоли — другой сеанс, Msg 208; ожидаемо и рабоче.
Правка через VIEW — изменения базовых данных делаются таблицами напрямую; представления курса — для чтения.
Веротерпимость имени — столбцы CTE и VIEW поименованы все; вычисляемая колонка без алиаса падает в объявлении.

↑ К оглавлению