Оператор CASE позволяет создать новое временное поле в базе данных, которое станет меткой для данных на основе заданных пользователем условий. Чтобы лучше понять смысл оператора CASE, рассмотрим следующий сценарий.
Сценарий
Цель отдела продаж компании sTunes — чтобы как можно больше клиентов потратили от $7 до $15 на покупку музыкальной продукции в онлайн-магазине. Для этой цели были созданы следующие категории покупок: Baseline Purchase (базовая покупка), Low Purchase (незначительная покупка), Target Purchase (целевая покупка) и Top Performer (значительная покупка).
Поскольку стоимость песни составляет от $0,99 до $1,99, любой счет из этого диапазона считается Baseline Purchase (базовой покупкой). Сумма счетов от $2,00 до $6,99 относится к Low Purchase (незначительной покупке). Поскольку основная цель продаж составляет от $7 до $15, любые покупки в этой категории — это Target Purchase (целевая покупка), а выше $15— Top Performer (значительная покупка).
Отдел продаж sTunes хочет узнать, можно ли получить из базы данных какую-либо информацию о продажах для всех перечисленных категорий.
Для создания нового поля PurchaseType в таблице invoices мы можем использовать оператор CASE. Поле PurchaseType будет отображаться вместе с другими уже существующими полями в нашем запросе, как если бы это было просто еще одно поле в базе данных.
Использование в запросе оператора CASE
Сначала создадим простой запрос SELECT, как в главе 4.
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
Total
FROM
invoices
ORDER BY
BillingCity
Теперь нам необходимо отсортировать наши результаты в зависимости от названия города, где выставлен счет, чтобы мы могли увидеть целевые покупки в зависимости от региона.
Чтобы добавить оператор CASE к данному запросу, необходимо добавить его в нижнюю часть блока SELECT после всех существующих полей. Начнем с ключевого слова CASE, за которым вставим ключевое слово END. Между этими двумя ключевыми словами нам необходимо определить условия. Каждая проверка начинается с ключевого слова WHEN, за которым следует условие. Наше первое условие — это Baseline Purchase (базовая покупка), то есть любой счет, сумма которого менее $2,00, другими словами "TOTAL<2.00". После логического условия в случае его выполнения необходимо указать ожидаемый результат. Для этой цели используем оператор THEN. Наш ожидаемый результат — покупки менее $2,00 с меткой Baseline Purchase (базовая покупка), что и предусмотрено для нашего сценария.
Эту же последовательность можно повторить для любого количества условий. Поэтому этот метод мы повторим для остальных категорий покупок. Ключевое слово ELSE всегда ставят за последним явным перечисленным условием. Любые записи, которые еще не определены, будут отнесены к категории, указанной в условии ELSE.
Внимание
Ключевое слово ELSE указывать не обязательно, но рекомендуется. В данных могут быть значения, выходящие за рамки требуемых условий. Условие ELSE фиксирует эти значения, и вы можете понять, что с ними делать. Если условие ELSE не добавлено, любые результаты в вашем наборе данных, выходящие за рамки требуемых условий, будут возвращены как значение NULL.
Последнее, что мы делаем, — создаем псевдоним, то есть новое поле в нашей базе данных. Этот псевдоним будет расположен после оператора END. Создадим новое поле с именем PurchaseType.
НАПОМИНАНИЕ
Мы создаем псевдонимы с помощью ключевого слова AS. Таким образом, оператор CASE завершается словом END AS, а затем псевдонимом, который мы выбрали для нового поля.
Запрос будет выглядеть следующим образом (рис. 60):
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
Total,
CASE
WHEN TOTAL < 2.00 THEN 'Baseline Purchase'
WHEN TOTAL BETWEEN 2.00 AND 6.99 THEN 'Low
Purchase'
WHEN TOTAL BETWEEN 7.00 AND 15.00 THEN 'Target
Purchase'
ELSE 'Top Performers'
END AS PurchaseType
FROM
invoices
ORDER BY
BillingCity
Проанализировав полученные результаты, мы увидим, что добавлена новая категория с именем PurchaseType и к данным добавлены все категории покупок.
Из полученных результатов мы видим, что все категории, которые мы проверяли, представлены в соответствии с их соответствующими категориями покупки (PurchaseType).
Рис. 60
Примечание
Используя условие ORDER BY, мы можем упорядочить результаты по полю, где отображается каждая категория покупки в алфавитном порядке, начиная с Baseline Purchase (базовая покупка) и заканчивая Top Performer (значительная покупка). Для ясности мы присвоили каждой категории свои имена, но вы можете присвоить им любое другое имя.
Теперь, когда новые категории созданы, нам необходимо узнать больше о демографических характеристиках наших клиентов на основе наших новых категорий продаж. Существует множество способов структурировать остальную часть оператора SELECT.
Ответим на следующие вопросы.
• В каких городах осуществляются самые эффективные продажи?
• Самые эффективные продажи в основном осуществляются в США или в других странах?
• В каких городах совершается больше всего базовых покупок?
Рассмотрим первый вопрос. Чтобы получить данные только об эффективных продажах и упорядочить их по городам, мы можем изменить наш существующий запрос, используя условие WHERE.
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
Total,
CASE
WHEN TOTAL < 2.00 THEN 'Baseline Purchase'
WHEN TOTAL BETWEEN 2.00 AND 6.99 THEN 'Low
Purchase'
WHEN TOTAL BETWEEN 7.00 AND 15.00 THEN 'Target
Purchase'
ELSE 'Top Performers'
END AS PurchaseType
FROM
invoices
WHERE PurchaseType = 'Top Performers'
ORDER BY
BillingCity
Рис. 61
Проанализировав результат запроса, мы можем определить, что самые эффективные продажи в основном осуществляются в США.
Комбинации полей, по которым проводится поиск, практически безграничны. Например, мы можем получить данные в зависимости от даты выставления счета, чтобы проанализировать сезонные продажи. Использование операторов CASE с условием WHERE и операторами, изученными в этой главе, поможет получить данные, необходимые для нашего отдела продаж.
Примечание
В примерах в этой главе мы использовали оператор CASE в части запроса SELECT после необходимых для отображения полей. Далее вы можете встретить запрос, в котором оператор CASE содержится в условии WHERE (редкий случай). Все, что сейчас важно, — это помнить, что оператор CASE должен быть указан в условии SELECT, но ссылаться на него можно из другого места программного кода.
Контрольные вопросы
1. Создайте запрос для таблицы invoices, включающий оператор CASE, который будет отмечать все продажи из США — страны, откуда выставлен счет — как Domestic Sales (Продажи на внутреннем рынке), а все другие продажи — как Foreign Sales (Продажи за рубежом). После оператора ENDAS создайте новое поле SalesType.
2. Отсортируйте эти данные по новому полю SalesType.
3. Сколько счетов от продаж на внутреннем рынке превышает сумму $15?
Резюме
• Операторы — это специальные ключевые слова SQL, которые используются с условиями SQL для фильтрации данных в зависимости от определенных условий.
• Использование условия WHERE с комбинацией различных операторов позволяет выполнять поиск определенного текста, даты и числа.
• Функция DATE() позволяет исключить время при указании параметров даты.
• Порядок операций при использовании логических операторов (таких как AND/OR) устанавливается с помощью круглых скобок ().
• Оператор CASE позволяет отмечать записи специальным именем поля в зависимости от заданных пользователем логических условий.
*** Существует некая путаница в терминологии. В английском языке есть два разных слова, statement и operator, которые чаще всего переводят одинаково — оператор. Встречается также перевод слова operator (в применении к арифметическим, логическим и сравнения) как «операция», а перевод слова statement как «инструкция». Мы будем использовать термин «оператор» как наиболее привычный. — Примеч. ред.
Глава 6. Работа с несколькими таблицами
Во всех предыдущих запросах мы рассматривали получение данных только из одной таблицы. Хотя мы изучили некоторые мощные запросы, они не используют все возможности реляционной базы данных. База данных sTunes содержит тринадцать таблиц. Каждая таблица содержит некоторую, но не всю, информацию о компании. Чтобы ответить на более сложные вопросы о компании sTunes, понадобится одновременный доступ к данным из нескольких таблиц. В этой главе мы узнаем, как получить данные из двух или более таблиц с помощью одного запроса и инструментов, называемых соединениями.
Что такое соединение
Соединение (join) — это операция, которая объединяет поля двух или более таблиц реляционной базы данных. Рассмотрим очень простой пример использования таблицы invoices в базе данных sTunes. В предыдущих главах мы много работали с таблицей invoices, поэтому она хорошо нам знакома. На вкладке Browse Data (Просмотр данных) браузера SQL (рис. 62) видно, что таблица invoices состоит из девяти полей. Поле InvoiceId содержит идентификационный номер каждого счета. Поле CustomerId — идентификационный номер каждого клиента (которому выставлен счет). Таблица invoices также содержит поля с информацией о дате и сумме счета. Остальные поля в этой таблице предназначены для адреса плательщика.
Рис. 62
Допустим, отделу маркетинга компании sTunes необходимо более детально проанализировать клиентскую базу. В этом случае он запросит полный список имен клиентов (имя и фамилию) со всеми счетами, выставленными каждому клиенту. Как мы напишем такой запрос, ис