alan_beaulieu-learning_sql-ru (865932), страница 39
Текст из файла (страница 39)
Чтобы понять,что имеется в виду, рассмотрим выражение case с перебором вариантов,логика которого аналогична предыдущему простому выражению case.Примеры выражений case221CASEWHEN customer.cust_type_cd = 'I' THEN(SELECT CONCAT(i.fname, ' ', i.lname)FROM individual IWHERE i.cust_id = customer.cust_id)WHEN customer.cust_type_cd = 'B' THEN(SELECT b.nameFROM business bWHERE 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 > '19991231'> 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 > '19991231';+++++++| 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:Примеры выражений case223SELECT CONCAT('ALERT! : Account #', a.account_id,' Has Incorrect Balance!')FROM account aWHERE (a.avail_balance, a.pending_balance) <>(SELECT SUM(<expression to generate available balance>),SUM(<expression to generate pending balance>)FROM transaction tWHERE t.account_id = a.account_id);Для суммирования отдельных транзакций по данному счету этот за прос использует связанный подзапрос к таблице transaction.
При сум мировании транзакций следует учитывать два факта:• Суммы транзакций всегда положительны, поэтому чтобы понять,является ли транзакция дебетовой или кредитовой, необходимо по смотреть на ее тип и изменить знак (умножить на –1) для дебетовыхтранзакций.• Если дата в столбце funds_avail_date больше текущей, транзакциядолжна быть добавлена в суммарный отложенный остаток, а нев суммарный доступный остаток.Из доступного остатка некоторые транзакции должны быть исключе ны, а в отложенный остаток включаются все транзакции, что делаетего более простым для вычисления.
Вот выражение case для вычисле ния отложенного остатка:CASEWHEN transaction.txn_type_cd = 'DBT'THEN transaction.amount * 1ELSE transaction.amountENDТаким образом, все суммы транзакций для дебетовых транзакций ум ножаются на –1, а для кредитовых транзакций остаются неизменны ми. Точно такая же логика применяется и к вычислению доступногоостатка, но здесь должны быть включены только те транзакции, кото рые стали доступными. Поэтому выражение case для вычисления дос тупного остатка включает один дополнительный блок when:CASEWHEN transaction.funds_avail_date > CURRENT_TIMESTAMP()THEN 0WHEN transaction.txn_type_cd = 'DBT'THEN transaction.amount * 1ELSE transaction.amountENDВ первом блоке when недоступные фонды, такие как неоплаченные че ки, будут добавлять к сумме 0 долларов.
Вот окончательный вариантзапроса с двумя выражениями case:SELECT CONCAT('ALERT! : Account #', a.account_id,' Has Incorrect Balance!')224Глава 11. Условная логикаFROM account aWHERE (a.avail_balance, a.pending_balance) <>(SELECTSUM(CASEWHEN t.funds_avail_date > CURRENT_TIMESTAMP()THEN 0WHEN t.txn_type_cd = 'DBT'THEN t.amount * 1ELSE t.amountEND),SUM(CASEWHEN t.txn_type_cd = 'DBT'THEN t.amount * 1ELSE t.amountEND)FROM transaction tWHERE 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 | 111111111 | I| Y| Y||2 | 222222222 | I| Y| Y|Примеры выражений case225|3 | 333333333 | I| Y| N||4 | 444444444 | I| Y| Y||5 | 555555555 | I| Y| N||6 | 666666666 | I| Y| N||7 | 777777777 | I| N| N||8 | 888888888 | I| Y| Y||9 | 999999999 | I| Y| N||10 | 041111111 | B| Y| N||11 | 042222222 | B| N| N||12 | 043333333 | B| Y| N||13 | 044444444 | B| N| N|++++++13 rows in set (0.00 sec)Каждое выражение case включает связанный подзапрос к таблице account: один для поиска текущих счетов, другой – сберегательных сче тов.
Поскольку каждый блок 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 | 111111111 | I| 3+||2 | 222222222 | I| 2||3 | 333333333 | I| 2||4 | 444444444 | I| 3+||5 | 555555555 | I| 1||6 | 666666666 | I| 2||7 | 777777777 | I| 1||8 | 888888888 | I| 2||9 | 999999999 | I| 3+||10 | 041111111 | B| 2||11 | 042222222 | B| 1||12 | 043333333 | B| 1||13 | 044444444 | 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 |Примеры выражений case227|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)Этот запрос вычисляет отношение остатка на счете к общему остаткудля всех счетов одного типа.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
