Рассматриваются приёмы преобразования данных прямо в SQL-запросах: объединение строк, изменение типов с помощью стандартной CAST и оптимизированной для SQL Server CONVERT, арифметические вычисления с форматированием результата, а также работа с датами — форматирование через стили и расчёт разницы между датами. Логика изложения ведёт от простой конкатенации к вычисляемым столбцам и завершается встроенной функцией DATEDIFF, попутно поясняя различия между стандартными и вендор-специфичными инструментами.
Основные мысли
В результате изучения лекции слушатель будет способен:
1. Применять конкатенацию строк для формирования единого текстового поля из нескольких столбцов.
2. Использовать функцию CAST для стандартного преобразования типов данных.
3. Использовать функцию CONVERT для преобразования типов с учётом особенностей SQL Server.
4. Выполнять арифметические вычисления в запросах и приводить результат к нужному числовому типу.
5. Форматировать даты в строковые представления с помощью стилей преобразования.
6. Вычислять разницу между датами, применяя арифметику над строковыми представлениями и функцию DATEDIFF.
7. Выводить текущую системную дату, преобразованную к заданному формату.
Показывать лекцию целиком
Краткое изложение
Конкатенация строк
Переходим к запросам, которые не просто извлекают столбцы, но и преобразуют их. Самый простой пример —
конкатенация (слияние) строк.
Из таблицы
Customer получаем идентификатор клиента и объединяем в одно поле обращение (
Title), имя и фамилию через пробел. Три столбца сливаются в один, который мы называем
CustomerName. Сортировка — по фамилии, затем по имени. Конкатенация выполняется оператором +. Текстовые литералы (пробел) заключаются в одинарные кавычки.
Преобразование типов: CAST и CONVERT
Чтобы включить в объединённую строку числовой идентификатор клиента (
CustomerID), нужно преобразовать его из типа
INTEGER в строковый. Для этого служит стандартная функция
CAST.
Синтаксис:
CAST(столбец AS тип). Пример:
CAST(CustomerID AS NVARCHAR(5)) — преобразуем идентификатор в строку длиной 5 символов. Результат объединяем с обращением, именем и фамилией, получая столбец
CustomerInfo. Дополнительно можно добавить поясняющий текст:
'Customer ID: ' + CAST(CustomerID AS NVARCHAR) + ' ' + Title + ' ' + FirstName + ' ' + LastName.
Аналогично с продуктами: формируем строку вида «Продукт категория:
: », преобразуя числовой ProductCategoryID в NVARCHAR(2), и сортируем по названию продукта.
Функция CAST — часть стандарта SQL и работает одинаково во всех СУБД (Oracle, Microsoft SQL Server, PostgreSQL, MySQL и др.). Однако производители могут создавать собственные оптимизированные функции. В SQL Server такой функцией является CONVERT.
CONVERT преобразует тип, но синтаксис иной: сначала указывается целевой тип, затем через запятую — выражение. Например, CONVERT(DECIMAL(7,2), UnitPrice * (1 - UnitPriceDiscount) * OrderQty). Результат полностью аналогичен CAST, но может выполняться эффективнее за счёт учёта особенностей SQL Server.
Арифметические вычисления
Можно вычислять значения прямо в запросе. Из таблицы OrderDetail берём SalesOrderID, LineTotal (уже готовый итог по строке) и самостоятельно воспроизводим его формулу:
UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
Discount — доля скидки (например, 0.03 для 3%). Выражение 1 - UnitPriceDiscount даёт оплачиваемую долю цены. Результат умножаем на цену за единицу и количество. Приводим итог к типу DECIMAL(7,2) (семь знаков, два после запятой) с помощью CAST. Сортируем по убыванию вычисленного TotalCost. Наш расчёт совпадает с LineTotal, но округляется до двух знаков, тогда как LineTotal хранится с большей точностью.
Другой пример: анализ прибыли. Из таблицы Product выбираем идентификатор, название, цену продажи (ListPrice). Берём себестоимость (StandardCost), увеличиваем на 6% (StandardCost * 1.06) и вычитаем из цены продажи. Результат — будущая прибыль (Profit) при условии сохранения цены продажи и росте закупочной стоимости. Приводим StandardCost * 1.06 к DECIMAL(6,2) и сортируем по убыванию Profit.
Форматирование дат
Даты можно преобразовывать в строку с различными форматами. Из SalesOrderHeader извлекаем PurchaseOrderNumber, SalesOrderID, CustomerID. Поле OrderDate изначально содержит дату и время (время по умолчанию нулевое). Приводим OrderDate, DueDate и ShipDate к NVARCHAR с помощью CONVERT, используя коды стилей.
• Стиль 11 — формат Mon DD YYYY (например, Jun 1 2008).
• Стиль 101 — MM/DD/YYYY с добавлением времени в 12-часовом формате.
• Стиль 112 — YYYYMMDD (дата без разделителей, выглядит как целое число, но остаётся строкой).
Меняя код стиля, получаем разные представления одной и той же даты.
Разность дат и системная дата
Можно вычислить разницу в днях между датами, комбинируя преобразования. Сначала применяем CONVERT со стилем 112, получая строку без разделителей. Затем с помощью CAST преобразуем её в целое число и вычитаем одно из другого. Например, разница между DueDate и ShipDate — 5 дней.
Но в SQL Server есть готовая функция DATEDIFF, которая делает то же самое проще. Указываем единицу измерения (day) и две даты: DATEDIFF(day, OrderDate, ShipDate). Результат аналогичен ручному вычислению.
Также можно вывести текущую системную дату: SELECT CONVERT(NVARCHAR, SYSDATETIME(), 101) AS MyPCD. Функция SYSDATETIME возвращает текущие дату и время, а стиль 101 форматирует их в MM/DD/YYYY.
Далее мы перейдём к соединениям таблиц — INNER JOIN и OUTER JOIN.
Краткие итоги
Владение встроенными средствами преобразования данных в SQL-запросах кардинально меняет подход к получению и подготовке информации. Вместо того чтобы извлекать сырые столбцы и приводить их к нужному виду на стороне приложения, аналитик или разработчик формирует финальное представление непосредственно в базе данных. Это снижает объём передаваемых данных, упрощает код приложения и нередко повышает производительность за счёт оптимизаций движка СУБД.
Ключевая идея — оперировать данными как единым потоком, применяя функции преобразования прямо в SELECT. Конкатенация через оператор + позволяет строить информативные метки и составные ключи, объединяя текстовые и числовые атрибуты после явного приведения типов. Здесь вступает в игру дихотомия «стандарт — реализация»: универсальный CAST обеспечивает переносимость кода между разными СУБД, тогда как вендор-специфичный CONVERT даёт дополнительные возможности (стили форматирования дат) и может работать быстрее за счёт низкоуровневой оптимизации. Понимание этой двойственности позволяет выбирать инструмент исходя из контекста: жертвовать переносимостью ради эффективности или сохранять совместимость.
Вычислительная логика, вынесенная в SQL, особенно ценна при формировании отчётов и витрин данных. Арифметические выражения с управлением точностью через DECIMAL и использованием псевдонимов делают код самодокументированным. Расчет прибыли с моделированием изменения себестоимости — типичный сценарий «что-если», который может быть реализован без написания отдельного скрипта.
Особый пласт — работа с датами. Хранение временных меток в нативном типе и форматирование только на выходе, с помощью стилей CONVERT, избавляет от проблем локализации. Приём двойного преобразования (в строку без разделителей, а затем в число) для вычисления разницы дней наглядно демонстрирует гибкость, но одновременно подводит к пониманию ценности встроенных функций вроде DATEDIFF. Переход от ручного вычитания к специализированной функции — иллюстрация зрелого подхода: не изобретать велосипед, а использовать готовые, протестированные механизмы, одновременно упрощая синтаксис и снижая риск ошибок.
Таким образом, материал охватывает не просто синтаксис, а философию обработки данных: от конструирования осмысленных строк и приведения типов до эффективных вычислений и темпоральной арифметики. Практическая ценность очевидна: слушатель получает набор паттернов для повседневной аналитики, построения отчётов и предобработки данных, а также критерии выбора между стандартными и платформозависимыми инструментами.
Конкатенация строк
Для объединения столбцов в одно поле используется оператор +. Текстовые литералы (например, пробел) заключаются в одинарные кавычки.
Пример: SELECT CustomerID, Title + ' ' + FirstName + ' ' + LastName AS CustomerName FROM Customer ORDER BY LastName, FirstName.
Преобразование типов: CAST и CONVERT
Числовые идентификаторы нельзя напрямую конкатенировать со строками. Требуется приведение к строковому типу.
• Стандартная функция CAST: CAST(столбец AS тип).
SELECT CAST(CustomerID AS NVARCHAR(5)) + ' ' + Title + ' ' + FirstName + ' ' + LastName AS CustomerInfo FROM Customer.
• Можно вставлять поясняющий текст: 'Customer ID: ' + CAST(CustomerID AS NVARCHAR) + ' ' + Title ....
Аналогично строится строка с категорией продукта: 'Продукт категория: ' + CAST(ProductCategoryID AS NVARCHAR(2)) + ': ' + Name.
Функция CAST едина для всех СУБД. SQL Server предлагает собственную оптимизированную функцию CONVERT.
Синтаксис: CONVERT(тип, выражение).
CONVERT(DECIMAL(7,2), UnitPrice * (1 - UnitPriceDiscount) * OrderQty). Результат аналогичен CAST, но может работать быстрее.
Арифметические вычисления
В запросе можно вычислять новые значения.
Из OrderDetail выводим SalesOrderID, LineTotal и вручную воспроизводим формулу LineTotal как TotalCost:
UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
Discount — доля скидки, поэтому (1 - UnitPriceDiscount) даёт оплачиваемую долю. Результат приводим к DECIMAL(7,2) через CAST или CONVERT. Сортируем по убыванию TotalCost.
Расчёт будущей прибыли из Product:
ListPrice - CAST(StandardCost * 1.06 AS DECIMAL(6,2)) AS Profit.
Здесь себестоимость увеличивается на 6% и вычитается из цены продажи.
Форматирование дат
Даты в SQL Server содержат время (по умолчанию полночь). С помощью CONVERT можно задать строковое представление, используя коды стилей.
• Стиль 11 → Mon DD YYYY (напр., Jun 1 2008).
• Стиль 101 → MM/DD/YYYY с 12-часовым временем.
• Стиль 112 → YYYYMMDD (строка без разделителей).
Пример: SELECT OrderDate, CONVERT(NVARCHAR, OrderDate, 11) AS NewDate FROM SalesOrderHeader.
Разница дат и системное время
Разницу в днях можно вычислить через двойное преобразование:
CAST(CONVERT(NVARCHAR, DueDate, 112) AS INT) - CAST(CONVERT(NVARCHAR, ShipDate, 112) AS INT).
Но удобнее встроенная функция DATEDIFF: DATEDIFF(day, OrderDate, ShipDate). Она принимает единицу измерения (day, month и т.д.) и две даты.
Текущие дату и время возвращает SYSDATETIME. Её можно отформатировать:
SELECT CONVERT(NVARCHAR, SYSDATETIME(), 101) AS MyPCD.
Выводы
1. Конкатенация строк в T-SQL реализуется оператором +, позволяя объединять столбцы и литералы в одно поле.
2. Для включения чисел в строку требуется явное преобразование типов, например через CAST или CONVERT.
3. CAST — стандартная функция SQL, гарантирующая переносимость кода между разными СУБД.
4. CONVERT — специфичная для SQL Server функция, обладающая дополнительным параметром стиля для форматирования дат.
5. Арифметические выражения можно вычислять прямо в SELECT, назначая результату псевдоним и управляя точностью через DECIMAL.
6. Скидка в таблице OrderDetail представлена долей, поэтому оплачиваемая часть цены вычисляется как 1 - UnitPriceDiscount.
7. Прибыль после изменения себестоимости моделируется вычитанием скорректированной закупочной цены из текущей цены продажи.
8. Дата и время в SQL Server хранятся вместе; время по умолчанию имеет нулевое значение.
9. Коды стилей (11, 101, 112 и др.) управляют текстовым представлением даты при преобразовании в строку.
10. Разницу в днях можно получить, преобразовав даты в стиль 112, затем в целые числа и вычтя их, но удобнее использовать DATEDIFF.
11. Функция DATEDIFF принимает единицу измерения и две даты, возвращая целое число.
12. Системную дату возвращает функция SYSDATETIME; её форматирование также подчиняется кодам стилей.
Вопросы для самопроверки
1. С помощью какого оператора в T-SQL выполняется конкатенация строк?
2. Какая функция является стандартной для преобразования типов данных, а какая — специфичной для SQL Server?
3. Как преобразовать числовой столбец CustomerID в строку длиной 5 символов, используя стандартный подход?
4. Чем различается синтаксис функций CAST и CONVERT?
5. Как вычислить итоговую стоимость позиции заказа с учётом скидки, если известны цена единицы, доля скидки и количество?
6. Что означает тип данных DECIMAL(7,2)?
7. Каким образом можно смоделировать изменение прибыли при увеличении закупочной стоимости на 6%?
8. Назовите три различных кода стиля для форматирования даты и соответствующие им форматы вывода.
9. Как выглядит дата, преобразованная с кодом стиля 112?
10. В чём преимущество использования DATEDIFF перед ручным вычислением разницы дат через преобразование в целые числа?
11. Как вывести текущую системную дату в формате MM/DD/YYYY?
12. Почему разработчик может предпочесть CONVERT вместо CAST при работе с SQL Server?
