изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

• Название: Она позволяет решать многошаговые задачи одним выражением
• Дата: 09.02.2018
• Размер: 1.6 Mb.
• Имя файла: изучаем SQL.pdf
• Тип: Документы; #36127
• страница: 24 из 31

Выражения case с перебором вариантов
Приведенное ранее в этой главе выражение case – пример выражения
case с перебором вариантов
(searched case expression), имеющего сле дующий синтаксис:
CASE
WHEN C1 THEN E1
WHEN C2 THEN E2
WHEN CN THEN EN
[ELSE ED]
END
В этом описании символами C1, C2, …, CN обозначены условия, а символа ми E1, E2, …, EN – выражения, которые должны быть возвращены выра жением case. Если условие в блоке when выполняется, выражение case возвращает соответствующее выражение. Кроме того, символ ED пред ставляет применяемое по умолчанию выражение, возвращаемое выра жением case, если не выполнено ни одно из условий C1, C2, …, CN (блок else является необязательным, поэтому он заключен в квадратные скоб ки). Все выражения, возвращаемые различными блоками when, должны обеспечивать результаты одного типа (например, date, number, varchar).
Вот пример выражения case с перебором вариантов:
CASE
WHEN employee.title = 'Head Teller'
THEN 'Head Teller'
WHEN employee.title = 'Teller'

Выражение case
219
AND YEAR(employee.start_date) > 2004
THEN 'Teller Trainee'
WHEN employee.title = 'Teller'
AND YEAR(employee.start_date) < 2003
THEN 'Experienced Teller'
WHEN employee.title = 'Teller'
THEN 'Teller'
ELSE 'Non Teller'
END
Это выражение case возвращает строку, с помощью которой можно оп ределять расценки почасовой оплаты, печатать бейджи с именами и т. д.
При вычислении выражения case блоки when обрабатываются сверху вниз. Как только одно из условий блока when принимает значение true,
возвращается соответствующее выражение, а все остальные блоки when игнорируются. Если ни одно из условий блока when не выполняется,
возвращается выражение блока else.
Хотя предыдущий пример возвращает строковые выражения, помни те, что выражения case могут возвращать выражения любого типа,
включая подзапросы. Вот еще одна версия приведенного ранее в этой главе запроса имени физического лица/названия фирмы, в которой для извлечения данных из таблиц individual и business вместо внеш них соединений используются подзапросы:
mysql> SELECT c.cust_id, c.fed_id,
> CASE
> WHEN c.cust_type_cd = 'I' THEN
> (SELECT CONCAT(i.fname, ' ', i.lname)
> FROM individual i
> WHERE i.cust_id = c.cust_id)
> WHEN c.cust_type_cd = 'B' THEN
> (SELECT b.name
> FROM business b
> WHERE b.cust_id = c.cust_id)
> ELSE 'Unknown'
> END name
> FROM customer c;
+
+
+
+
| cust_id | fed_id | name |
+
+
+
+
| 1 | 111 11 1111 | James Hadley |
| 2 | 222 22 2222 | Susan Tingley |
| 3 | 333 33 3333 | Frank Tucker |
| 4 | 444 44 4444 | John Hayward |
| 5 | 555 55 5555 | Charles Frasier |
| 6 | 666 66 6666 | John Spencer |
| 7 | 777 77 7777 | Margaret Young |
| 8 | 888 88 8888 | Louis Blake |
| 9 | 999 99 9999 | Richard Farley |
| 10 | 04 1111111 | Chilton Engineering |

220
Глава 11. Условная логика
| 11 | 04 2222222 | Northeast Cooling Inc. |
| 12 | 04 3333333 | Superior Auto Body |
| 13 | 04 4444444 | AAA Insurance Inc. |
+
+
+
+
13 rows in set (0.01 sec)
В этом варианте запроса в блок from включена только таблица customer и соответствующее имя для каждого клиента получается с помощью связанного подзапроса. Эта версия мне нравится больше той, где при меняются внешние соединения, поскольку здесь сервер считывает данные таблицы individual и business только в случае необходимости,
а не соединяет все три таблицы.
Простые выражения case
Простое выражение case
(simple case expression) очень похоже на вы ражение case с перебором вариантов, но несколько менее функцио нально. Вот его синтаксис:
CASE V0
WHEN V1 THEN E1
WHEN V2 THEN E2
WHEN VN THEN EN
[ELSE ED]
END
В этом описании V0 представляет значение, а символы V1, V2, …, VN –
значения, сравниваемые с V0. Символы E1, E2, …, EN представляют вы ражения, возвращаемые выражением case, а ED – выражение, которое должно быть возвращено, если ни одно из значений набора V1, V2, …, VN
не соответствует значению V0.
Вот пример простого выражения case:
CASE customer.cust_type_cd
WHEN 'I' THEN
(SELECT CONCAT(i.fname, ' ', i.lname)
FROM individual I
WHERE i.cust_id = customer.cust_id)
WHEN 'B' THEN
(SELECT b.name
FROM business b
WHERE b.cust_id = customer.cust_id)
ELSE 'Unknown Customer Type'
END
Простые выражения case менее функциональны, чем выражения case с перебором вариантов, потому что в них нельзя задать собственные ус ловия; в них просто используются условия равенства. Чтобы понять,
что имеется в виду, рассмотрим выражение case с перебором вариантов,
логика которого аналогична предыдущему простому выражению case.

Примеры выражений case
221
CASE
WHEN customer.cust_type_cd = 'I' THEN
(SELECT CONCAT(i.fname, ' ', i.lname)
FROM individual I
WHERE i.cust_id = customer.cust_id)
WHEN customer.cust_type_cd = 'B' THEN
(SELECT b.name
FROM business b
WHERE b.cust_id = customer.cust_id)
ELSE 'Unknown Customer Type'
END
Выражения case с перебором вариантов позволяют создавать условия вхождения в диапазон, условия неравенства и составные условия, ис пользующие and/or/not, поэтому я бы рекомендовал применять выраже ния case с перебором вариантов во всех случаях, кроме самых простых.
Примеры выражений case
Следующие разделы представляют различные примеры, иллюстри рующие применение условной логики в SQL выражениях.
Трансформации результирующих наборов
Иногда агрегирование проводится по конечному набору значений, на пример по дням недели, но требуется, чтобы результирующий набор включал всего одну строку с количеством столбцов, соответствующим количеству значений, а не по одной строке на каждое значение. В ка честве примера возьмем запрос, возвращающий число счетов, откры тых в каждом году, начиная с 2000 года:
mysql> SELECT YEAR(open_date) year, COUNT(*) how_many
> FROM account
> WHERE open_date > '1999 12 31'
> GROUP BY YEAR(open_date);
+
+
+
| year | how_many |
+
+
+
| 2000 | 3 |
| 2001 | 4 |
| 2002 | 5 |
| 2003 | 3 |
| 2004 | 9 |
+
+
+
5 rows in set (0.00 sec)
Чтобы трансформировать этот результирующий набор в одну строку с шестью столбцами (по одному для каждого года, с 2000 по 2005), по надобится создать шесть столбцов и в каждом столбце просуммировать
только
строки, относящиеся к данному году:

222
Глава 11. Условная логика mysql> SELECT
> SUM(CASE
> WHEN EXTRACT(YEAR FROM open_date) = 2000 THEN 1
> ELSE 0
> END) year_2000,
> SUM(CASE
> WHEN EXTRACT(YEAR FROM open_date) = 2001 THEN 1
> ELSE 0
> END) year_2001,
> SUM(CASE
> WHEN EXTRACT(YEAR FROM open_date) = 2002 THEN 1
> ELSE 0
> END) year_2002,
> SUM(CASE
> WHEN EXTRACT(YEAR FROM open_date) = 2003 THEN 1
> ELSE 0
> END) year_2003,
> SUM(CASE
> WHEN EXTRACT(YEAR FROM open_date) = 2004 THEN 1
> ELSE 0
> END) year_2004,
> SUM(CASE
> WHEN EXTRACT(YEAR FROM open_date) = 2005 THEN 1
> ELSE 0
> END) year_2005
> FROM account
> WHERE open_date > '1999 12 31';
+
+
+
+
+
+
+
| year_2000 | year_2001 | year_2002 | year_2003 | year_2004 | year_2005 |
+
+
+
+
+
+
+
| 3 | 4 | 5 | 3 | 9 | 0 |
+
+
+
+
+
+
+
1 row in set (0.01 sec)
Все шесть выражений для столбцов предыдущего запроса идентичны,
за исключением значения года. Когда функция extract() возвращает нужный год, выражение case возвращает значение 1. В противном слу чае возвращается 0. Суммируя все счета, открытые с 2000 года, каж дый столбец возвращает число счетов, открытых в соответствующий год. Очевидно, что такие трансформации практически применимы только для небольшого числа значений. Решение задачи по формиро ванию столбцов для каждого года, начиная с 1905 го, быстро стало бы слишком громоздким.
Селективная агрегация
Ранее в главе 9 было показано частичное решение задачи поиска сче тов, остаток на которых не соответствует данным таблицы transaction.
Причиной предоставления частичного решения была необходимость применения условной логики, но теперь у нас есть все для того, чтобы закончить работу. Вот на чем я остановился в главе 9:

Примеры выражений case
223
SELECT CONCAT('ALERT! : Account #', a.account_id,
' Has Incorrect Balance!')
FROM account a
WHERE (a.avail_balance, a.pending_balance) <>
(SELECT SUM(),
SUM()
FROM transaction t
WHERE t.account_id = a.account_id);
Для суммирования отдельных транзакций по данному счету этот за прос использует связанный подзапрос к таблице transaction. При сум мировании транзакций следует учитывать два факта:
•
Суммы транзакций всегда положительны, поэтому чтобы понять,
является ли транзакция дебетовой или кредитовой, необходимо по смотреть на ее тип и изменить знак (умножить на –1) для дебетовых транзакций.
•
Если дата в столбце funds_avail_date больше текущей, транзакция должна быть добавлена в суммарный отложенный остаток, а не в суммарный доступный остаток.
Из доступного остатка некоторые транзакции должны быть исключе ны, а в отложенный остаток включаются все транзакции, что делает его более простым для вычисления. Вот выражение case для вычисле ния отложенного остатка:
CASE
WHEN transaction.txn_type_cd = 'DBT'
THEN transaction.amount * 1
ELSE transaction.amount
END
Таким образом, все суммы транзакций для дебетовых транзакций ум ножаются на –1, а для кредитовых транзакций остаются неизменны ми. Точно такая же логика применяется и к вычислению доступного остатка, но здесь должны быть включены только те транзакции, кото рые стали доступными. Поэтому выражение case для вычисления дос тупного остатка включает один дополнительный блок when:
CASE
WHEN transaction.funds_avail_date > CURRENT_TIMESTAMP()
THEN 0
WHEN transaction.txn_type_cd = 'DBT'
THEN transaction.amount * 1
ELSE transaction.amount
END
В первом блоке when недоступные фонды, такие как неоплаченные че ки, будут добавлять к сумме 0 долларов. Вот окончательный вариант запроса с двумя выражениями case:
SELECT CONCAT('ALERT! : Account #', a.account_id,
' Has Incorrect Balance!')

224
Глава 11. Условная логика
FROM account a
WHERE (a.avail_balance, a.pending_balance) <>
(SELECT
SUM(CASE
WHEN t.funds_avail_date > CURRENT_TIMESTAMP()
THEN 0
WHEN t.txn_type_cd = 'DBT'
THEN t.amount * 1
ELSE t.amount
END),
SUM(CASE
WHEN t.txn_type_cd = 'DBT'
THEN t.amount * 1
ELSE t.amount
END)
FROM transaction t
WHERE t.account_id = a.account_id);
С помощью условной логики исходные данные поставляются в агре гатные функции sum() двумя выражениями case, позволяя суммиро вать соответствующие денежные объемы.
Проверка существования
В некоторых случаях требуется установить существование связи меж ду двумя сущностями, не касаясь количественных показателей. На пример, нужно узнать, есть ли у клиента текущие или сберегательные счета, не интересуясь тем, сколько у него счетов каждого типа. Вот за прос с несколькими выражениями case, формирующими два столбца выходных данных. Первый показывает, есть ли у клиента текущие счета, а второй – есть ли сберегательные счета:
mysql> SELECT c.cust_id, c.fed_id, c.cust_type_cd,
> CASE
> WHEN EXISTS (SELECT 1 FROM account a
> WHERE a.cust_id = c.cust_id
> AND a.product_cd = 'CHK') THEN 'Y'
> ELSE 'N'
> END has_checking,
> CASE
> WHEN EXISTS (SELECT 1 FROM account a
> WHERE a.cust_id = c.cust_id
> AND a.product_cd = 'SAV') THEN 'Y'
> ELSE 'N'
> END has_savings
> FROM customer c;
+
+
+
+
+
+
| cust_id | fed_id | cust_type_cd | has_checking | has_savings |
+
+
+
+
+
+
| 1 | 111 11 1111 | I | Y | Y |
| 2 | 222 22 2222 | I | Y | Y |

Примеры выражений case
225
| 3 | 333 33 3333 | I | Y | N |
| 4 | 444 44 4444 | I | Y | Y |
| 5 | 555 55 5555 | I | Y | N |
| 6 | 666 66 6666 | I | Y | N |
| 7 | 777 77 7777 | I | N | N |
| 8 | 888 88 8888 | I | Y | Y |
| 9 | 999 99 9999 | I | Y | N |
| 10 | 04 1111111 | B | Y | N |
| 11 | 04 2222222 | B | N | N |
| 12 | 04 3333333 | B | Y | N |
| 13 | 04 4444444 | B | N | N |
+
+
+
+
+
+
13 rows in set (0.00 sec)
Каждое выражение case включает связанный подзапрос к таблице ac count
: один для поиска текущих счетов, другой – сберегательных сче тов. Поскольку каждый блок when использует оператор exists, условия выполняются, если у клиента есть, по крайней мере, один счет иско мого типа.
В других случаях нас может интересовать количество встретившихся строк, но лишь постольку поскольку. Например, следующий запрос с помощью простого выражения case подсчитывает количество счетов каждого клиента, а затем возвращает 'None', '1', '2' или '3+':
mysql> SELECT c.cust_id, c.fed_id, c.cust_type_cd,
> CASE (SELECT COUNT(*) FROM account a
> WHERE a.cust_id = c.cust_id)
> WHEN 0 THEN 'None'
> WHEN 1 THEN '1'
> WHEN 2 THEN '2'
> ELSE '3+'
> END num_accounts
> FROM customer c;
+
+
+
+
+
| cust_id | fed_id | cust_type_cd | num_accounts |
+
+
+
+
+
| 1 | 111 11 1111 | I | 3+ |
| 2 | 222 22 2222 | I | 2 |
| 3 | 333 33 3333 | I | 2 |
| 4 | 444 44 4444 | I | 3+ |
| 5 | 555 55 5555 | I | 1 |
| 6 | 666 66 6666 | I | 2 |
| 7 | 777 77 7777 | I | 1 |
| 8 | 888 88 8888 | I | 2 |
| 9 | 999 99 9999 | I | 3+ |
| 10 | 04 1111111 | B | 2 |
| 11 | 04 2222222 | B | 1 |
| 12 | 04 3333333 | B | 1 |
| 13 | 04 4444444 | B | 1 |
+
+
+
+
+
13 rows in set (0.01 sec)

226
Глава 11. Условная логика
В этом запросе я не хотел проводить различия между клиентами, име ющими более двух счетов, поэтому выражение case просто создает ка тегорию '3+'. Подобный запрос может быть полезным при поиске кли ентов, с которыми можно связаться и предложить открыть новый счет в банке.
Ошибки деления на нуль
Проводя вычисления, включающие деление, нужно все время забо титься о том, чтобы знаменатель никогда не был равен нулю. Некото рые серверы БД, такие как Oracle Database, встретив нулевой знамена тель, формируют ошибку, а MySQL просто присваивает результату вы числения значение null, как показывает следующий пример:
mysql> SELECT 100 / 0;
+
+
| 100 / 0 |
+
+
| NULL |
+
+
1 row in set (0.00 sec)
Чтобы защитить вычисления от ошибок или, еще хуже, от загадочного получения null, следует ко всем знаменателям применять условную логику, как показано далее:
mysql> SELECT a.cust_id, a.product_cd, a.avail_balance /
> CASE
> WHEN prod_tots.tot_balance = 0 THEN 1
> ELSE prod_tots.tot_balance
> END percent_of_total
> FROM account a INNER JOIN
> (SELECT a.product_cd, SUM(a.avail_balance) tot_balance
> FROM account a
> GROUP BY a.product_cd) prod_tots
> ON a.product_cd = prod_tots.product_cd;
+
+
+
+
| cust_id | product_cd | percent_of_total |
+
+
+
+
| 10 | BUS | 0.0000 |
| 11 | BUS | 1.0000 |
| 1 | CD | 0.1538 |
| 6 | CD | 0.5128 |
| 7 | CD | 0.2564 |
| 9 | CD | 0.0769 |
| 1 | CHK | 0.0145 |
| 2 | CHK | 0.0309 |
| 3 | CHK | 0.0145 |
| 4 | CHK | 0.0073 |
| 5 | CHK | 0.0307 |
| 6 | CHK | 0.0017 |

Примеры выражений case
227
| 8 | CHK | 0.0478 |
| 9 | CHK | 0.0017 |
| 10 | CHK | 0.3229 |
| 12 | CHK | 0.5281 |
| 3 | MM | 0.1298 |
| 4 | MM | 0.3219 |
| 9 | MM | 0.5483 |
| 1 | SAV | 0.2694 |
| 2 | SAV | 0.1078 |
| 4 | SAV | 0.4137 |
| 8 | SAV | 0.2091 |
| 13 | SBL | 1.0000 |
+
+
+
+
24 rows in set (0.00 sec)
Этот запрос вычисляет отношение остатка на счете к общему остатку для всех счетов одного типа. Поскольку для некоторых типов счетов,
таких как ссуды коммерческим предприятиям, общий остаток может равняться нулю, если на текущий момент все ссуды полностью выпла чены, лучше всего включить выражение case, гарантирующее, что знаменатель никогда не будет равен нулю.
Условные обновления
При обновлении строк таблицы вам иногда придется принимать реше ния относительно того, какие значения должны быть заданы в опреде ленных столбцах. Например, после вставки новой транзакции долж ны измениться столбцы avail_balance, pending_balance и last_activi ty_date таблицы account. С обновлением двух последних столбцов про блем нет, но чтобы правильно изменить столбец avail_balance, надо проверить столбец funds_avail_date таблицы transaction и выяснить,
сразу ли доступны фонды транзакции. Допустим, только что вставле на транзакция с ID 999, тогда изменить три столбца таблицы account можно с помощью следующего выражения update:
1 UPDATE account
2 SET last_activity_date = CURRENT_TIMESTAMP(),
3 pending_balance = pending_balance +
4 (SELECT t.amount *
5 CASE t.txn_type_cd WHEN 'DBT' THEN 1 ELSE 1 END
6 FROM transaction t
7 WHERE t.txn_id = 999),
8 avail_balance = avail_balance +
9 (SELECT
10 CASE
11 WHEN t.funds_avail_date > CURRENT_TIMESTAMP() THEN 0 12 ELSE t.amount *
13 CASE t.txn_type_cd WHEN 'DBT' THEN 1 ELSE 1 END
14 END
15 FROM transaction t
16 WHERE t.txn_id = 999)