лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
CTE можно записать в таком виде: 1. WITH cte ( maker, avg_price ) 2. AS ( 3. SELECT maker, AVG ( price ) avg_price 4. FROM Product P JOIN PC ON P.model=PC.model 5. GROUP BY maker 6. ) 7. SELECT * 8. FROM cte 9. WHERE avg_price>= ALL ( SELECT avg_price 10. FROM cte 11. ) ; Замечу, что поддержка общих табличных выражений появилась в SQL Server 2005 и в PostgreSQL 8.4. Произведение значений столбца Почему среди агрегатных функций SQL нет произведения? Такой вопрос часто задают в профессиональных социальных сетях. Речь идёт о произведении значений столбца таблицы при выполнении группировки. Функции типа PRODUCT нет в стандарте языка, и я не знаю СУБД, которая её бы имела. Хорошей же новостью является то, что такую функцию просто выразить через три другие, которые есть в арсенале практически всех серверов БД. Итак. Пусть требуется перемножить значения столбца value следующей таблицы: 1. SELECT value FROM ( 2. VALUES ( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) 3. ) X ( value ) ; value 2 3 4 5 Воспользуемся следующим свойством логарифмов: логарифм произведения равен сумме логарифмов, для нашего примера это означает 1. ln ( 2 * 3 * 4 * 5 ) = ln ( 2 ) + ln ( 3 ) + ln ( 4 ) + ln ( 5 ) Если теперь применить обратную к натуральному логарифму (Ln) функцию экспоненты (exp), то получим 1. exp ( ln ( 2 * 3 * 4 * 5 )) = 2 * 3 * 4 * 5 = exp ( ln ( 2 ) + ln ( 3 ) + ln ( 4 ) + ln ( 5 )) Итак, произведение чисел мы можем заменить выражением, стоящим в равенстве справа. Осталось записать эту формулу на языке SQL, учитывая, что сами числа находятся в столбце value. 1. SELECT exp ( SUM ( log ( value ))) product FROM ( 2. VALUES ( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) 3. ) X ( value ) ; product 120 Правильность результата легко проверить устным счетом, или в Excel. Рассмотренное решение не является универсальным. Поскольку логарифм не определен для чисел <=0, то если в столбце появятся такие значения, например, 1. SELECT exp ( SUM ( log ( value ))) product FROM ( 2. VALUES ( 2 ) , ( -3 ) , ( 4 ) , ( -5 ) 3. ) X ( value ) ; будет получено сообщение об ошибке: An invalid floating point operation occurred. (Попытка выполнить недопустимую операцию с плавающей запятой.) Для учета "недопустимых" значений доработаем наше решение в соответствии со следующим алгоритмом: 1. Если среди значений есть нули, то результатом будет 0. 2. Если число отрицательных значений нечетное, то домножаем произведение абсолютных значений столбца на -1. 3. Если число отрицательных значений четное, то результатом будет произведение абсолютных значений столбца. Вот решение с комментариями, реализующее этот алгоритм: 1. WITH T AS ( SELECT * FROM ( VALUES ( -2 ) , ( -3 ) , ( 4 ) , ( -5 )) X ( value )) , 2. P AS ( 3. SELECT SUM ( CASE WHEN value< 0 THEN 1 ELSE 0 END ) neg, -- число отрицательных значений 4. SUM ( CASE WHEN value> 0 THEN 1 ELSE 0 END ) pos, -- число положительных значений 5. COUNT ( * ) total -- общее число значений 6. FROM T ) 7. SELECT CASE WHEN total <> pos+neg /* есть нули */ THEN 0 ELSE 8. ( CASE WHEN neg% 2 = 1 THEN -1 ELSE +1 END ) *exp ( SUM ( log ( abs ( value )))) 9. END product 10. FROM T,P 11. WHERE value <> 0 12. GROUP BY neg, pos, total; product -120 Обратите внимание на условие value <> 0 в последней строке запроса. Его присутствие связано с тем, что, хотя ветвь оператора CASE с вычислением выражения через логарифм не реализуется при наличии нулей среди значений столбца (возвращается 0), SQL Server всё равно вычисляет это выражение и возвращает ошибку. Сообразительные уже спросили: "А как быть с NULL?" Действительно, наше решение даёт в этом случае 0. Будем следовать общей логике поведения агрегатных функций - не учитывать NULL. Ниже приводится окончательное решение, которое имеет одно отличие по сравнению с предыдущим решением. Кто догадается какое? 1. WITH T AS ( SELECT * FROM ( VALUES ( -2 ) , ( -3 ) , ( 4 ) , ( -5 ) , ( NULL ) ) X ( value )) , 2. P AS ( 3. SELECT SUM ( CASE WHEN value< 0 THEN 1 ELSE 0 END ) neg, -- число отрицательных значений 4. SUM ( CASE WHEN value> 0 THEN 1 ELSE 0 END ) pos, -- число положительных значений 5. COUNT ( value ) total -- общее число значений 6. FROM T ) 7. SELECT CASE WHEN total <> pos+neg /* есть нули */ THEN 0 ELSE 8. ( CASE WHEN neg% 2 = 1 THEN -1 ELSE +1 END ) *exp ( SUM ( log ( abs ( value )))) 9. END 10. product FROM T,P WHERE value <> 0 GROUP BY neg, pos, total; Использование в запросе нескольких источников записей Как видно из приведенной в конце предыдущего раздела синтаксической формы оператора SELECT , в предложении FROM допускается указание нескольких таблиц. Простое перечисление таблиц через запятую практически не используется, поскольку оно соответствует реляционной операции, которая называется декартовым произведением. То есть в результирующем наборе каждая строка из одной таблицы будет сочетаться с каждой строкой из другой. Например, для таблиц: A a b 1 2 2 1 B c d 2 4 3 3 Результат запроса 1. SELECT * 2. FROM A, B; будет выглядеть следующим образом: a b c d 1 2 2 4 1 2 3 3 2 1 2 4 2 1 3 3 Поэтому перечисление таблиц, как правило, используется совместно с условием соединения строк из разных таблиц, указываемым в предложении WHERE. Для приведенных выше таблиц таким условием может быть совпадение значений, скажем, в столбцах a и c: 1. SELECT * 2. FROM A, B 3. WHERE a = c; Теперь результатом выполнения этого запроса будет следующая таблица: a b c d 2 1 2 4 то есть соединяются только те строки таблиц, у которых в указанных столбцах находятся равные значения (эквисоединение). Естественно, могут быть использованы любые условия, хотя эквисоединение используется чаще всего, поскольку эта операция воссоздает некую исходную сущность предметной области, декомпозированную на две других в результате процедуры нормализации в процессе построения логической модели. Если разные таблицы имеют столбцы с одинаковыми именами, то для однозначности требуется использовать точечную нотацию, которая называется уточнением имени столбца: <имя таблицы>.<имя столбца> В тех случаях, когда это не вызывает неоднозначности, использование данной нотации не является обязательным. Пример 5.6.1 Найти номер модели и производителя ПК, имеющих цену менее $600: 1. SELECT DISTINCT PC.model, maker 2. FROM PC, Product 3. WHERE PC.model = Product.model AND 4. price < 600 ; В результате каждая модель одного и того же производителя выводится только один раз: model maker 1232 A 1260 E Иногда в предложении FROM требуется указать одну и ту же таблицу несколько раз. В этом случае обязательным является переименование. Пример 5.6.2 Вывести пары моделей, имеющих одинаковые цены: 1. SELECT DISTINCT A.model AS model_1, B.model AS model_2 2. FROM PC AS A, PC B 3. WHERE A.price = B.price AND 4. A.model < B.model; Здесь условие a.model < b.model используется для того, чтобы не выводились одинаковые пары, отличающиеся только перестановкой, например: {1232, 1233} и {1233, 1232}. DISTINCT применяется для того, чтобы исключить одинаковые строки, поскольку в таблице PC имеются модели с одинаковыми номерами по одной и той же цене. В результате получим следующую таблицу: model_1 model_2 1232 1233 1232 1260 Переименование также является обязательным, если в предложении FROM используется подзапрос, так как, в противном случае, у нас нет возможности уточнения имени столбца из подзапроса. Так, первый пример можно переписать следующим образом: 1. SELECT DISTINCT PC.model, maker 2. FROM PC, ( SELECT maker, model 3. FROM Product 4. ) AS Prod 5. WHERE PC.model = Prod.model AND 6. price < 600 ; Обратите внимание, что в этом случае в других предложениях оператора SELECT уже нельзя использовать квалификатор Product, поскольку таблица Product уже не используется. Вместо него используется псевдоним Prod. Кроме того, ссылаться извне теперь можно только на те столбцы таблицы Product, которые перечислены в подзапросе. За псевдонимом производного табличного выражения может в скобках стоять список имен столбцов, которые будут использоваться вместо имен табличного выражения. Порядок имен должен, естественно, соответствовать списку столбцов табличного выражения (в нашем случае - списку в предложении SELECT). Это способ позволяет избежать неоднозначности имен и, как следствие, необходимости их уточнения. Вот как может выглядеть предыдущий пример: 1. SELECT DISTINCT model, maker 2. FROM PC, ( SELECT maker, model 3. FROM Product 4. ) AS Prod ( maker, model_1 ) 5. WHERE model = model_1 AND 6. price < 600 ; Явные операции соединения В предложении FROM может быть указана явная операция соединения двух и более таблиц. Среди ряда операций соединения, описанных в стандарте языка SQL, многими серверами баз данных поддерживается только операция соединения по предикату. Синтаксис соединения по предикату имеет вид: 1. FROM <таблица 1 > 2. [ INNER ] 3. {{ LEFT | RIGHT | FULL } [ OUTER ]} JOIN <таблица 2 > 4. [ ON <предикат> ] Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом — LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение. Предикат определяет условие соединения строк из разных таблиц. При этом INNER JOIN означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Как правило, предикат определяет эквисоединение по внешнему и первичному ключам соединяемых таблиц, хотя это не обязательно. Пример 5.6.3. Найти производителя, номер модели и цену каждого компьютера, имеющегося в базе данных: 1. SELECT maker, Product.model AS model_1, 2. PC.model AS model_2, price 3. FROM Product INNER JOIN 4. PC ON PC.model = Product.model 5. ORDER BY maker, model_2; В данном примере в результирующем наборе будут соединяться только те строки из таблиц РС и Product, у которых совпадают номера моделей. Для визуального контроля в результирующий набор включен как номер модели из таблицы PC, так и из таблицы Product: Maker_model_1_model_2_price_A_1232_1232_600_A_1232_1232_400__A_1232_1232_350_A'>Maker model_1 model_2 price A 1232 1232 600 A 1232 1232 400 A 1232 1232 350 A 1232 1232 350 A 1233 1233 600 A 1233 1233 950 A 1233 1233 980 A 1233 1233 970 B 1121 1121 850 B 1121 1121 850 B 1121 1121 850 E 1260 1260 350 Внешнее соединение LEFT JOIN означает, что помимо строк, для которых выполняется условие предиката, в результирующий набор попадут все остальные строки из первой таблицы (левой). При этом отсутствующие значения столбцов из правой таблицы будут заменены NULL-значениями. Пример 5.6.4 Привести все модели ПК, их производителей и цену: 1. SELECT maker, Product.model AS model_1, pc.model AS model_2, price 2. FROM Product LEFT JOIN 3. PC ON PC.model = Product.model 4. WHERE type = 'pc' 5. ORDER BY maker, PC.model; Обратите внимание на то, что по сравнению с предыдущим примером пришлось использовать предложение WHERE для отбора только производителей ПК. В противном случае в результирующий набор попали бы также и модели портативных компьютеров, и принтеров. В рассмотренном ранее примере это условие было бы излишним, так как соединялись только те строки, у которых совпадали номера моделей, и одной из таблиц была таблица PC, содержащая только модели ПК. В результате выполнения запроса получим: Maker model_1 model_2 price A 1232 1232 600 A 1232 1232 400 A 1232 1232 350 A 1232 1232 350 A 1233 1233 600 A 1233 1233 950 A 1233 1233 980 B 1121 1121 850 B 1121 1121 850 B 1121 1121 850 E 2111 NULL NULL E 2112 NULL NULL E 1260 1260 350 Поскольку моделей 2111 и 2112 из таблицы Product нет в таблице PС, в столбцах из таблицы PС содержится NULL. Соединение RIGHT JOIN обратно соединению LEFT JOIN, то есть в результирующий набор попадут все строки из второй таблицы, которые будут соединяться только с теми строками из первой таблицы, для которых выполняется условие соединения. В нашем случае левое соединение 1. Product LEFT JOIN PC ON PC.model = Product.model будет эквивалентно правому соединению 1. PC RIGHT JOIN Product ON PC.model = Product.model Запрос же 1. SELECT maker, Product.model AS model_1, PC.model AS model_2, price 2. FROM Product RIGHT JOIN 3. PC ON PC.model = Product.model 4. ORDER BY maker, PC.model; даст те же результаты, что и внутреннее соединение, поскольку в правой таблице (PC) нет таких моделей, которые отсутствовали бы в левой таблице (Product), что вполне естественно для типа связи «один ко многим», которая имеется между таблицами PC и Product. Наконец, при полном соединении (FULL JOIN) в результирующую таблицу попадут не только те строки, которые имеют одинаковые значения в сопоставляемых столбцах, но и все остальные строки исходных таблиц, не имеющие соответствующих значений в другой таблице. В этих строках все столбцы той таблицы, в которой не было найдено соответствия, заполняются NULL-значениями. То есть полное соединение представляет собой комбинацию левого и правого внешних соединений. Так, запрос для таблиц A и B, приведенных в начале главы , 1. SELECT A.*, B.* 2. FROM A FULL JOIN 3. B ON A.a = B.c; даст следующий результат: A b C d 1 2 NULL NULL 2 1 2 4 NULL NULL 3 3 Заметим, что это соединение симметрично, то есть A FULL JOIN B эквивалентно B FULL JOIN A. Обратите также внимание на обозначение A.*, что означает вывести все столбцы таблицы А. UNION JOIN Этот тип соединения был введен в стандарте SQL-92, но в более поздних версиях стандарта отсутствует. В частности, его уже нет в стандарте SQL2003 (ANSI и ISO). Как и многие другие конструкции языка SQL, соединение UNION JOIN является избыточным, поскольку его можно выразить через разность полного и внутреннего соединений. Формально это можно записать следующим образом: 1. A UNION JOIN B := 2. ( A FULL JOIN B ) 3. EXCEPT 4. ( A INNER JOIN B ) Ну, а если полное соединение не поддерживается ( MySQL ), то его можно получить объединением левого и правого внешних соединений. Тогда наша формула примет вид 1. A UNION JOIN B := 2. (( A LEFT JOIN B ) 3. UNION 4. ( A RIGHT JOIN B )) 5. EXCEPT 6. ( A INNER JOIN B ) Давайте в качестве примера, где мог бы пригодиться этот тип соединения, рассмотрим следующую задачу. Найти производителей, которые выпускают принтеры, но не ПК, или выпускают ПК, но не принтеры. Будь у нас возможность использовать UNION JOIN, мы бы решили задачу так: 1. SELECT * FROM 2. ( SELECT DISTINCT maker FROM Product WHERE type= 'pc' ) m_pc 3. UNION JOIN 4. ( SELECT DISTINCT maker FROM Product WHERE type= 'printer' ) m_printer 5. ON m_pc.maker = m_printer.maker; Воспользуемся формулой. Полное соединение производителей ПК и производителей принтеров даст нам как тех, кто производит что-то одно, так и тех, кто производит и то, и другое. 1. SELECT * FROM 2. ( SELECT DISTINCT maker FROM Product WHERE type= 'pc' ) m_pc 3. FULL JOIN 4. ( SELECT DISTINCT maker FROM Product WHERE type= 'printer' ) m_printer 5. ON m_pc.maker = m_printer.maker; Теперь вычтем из результата тех, кто производит и то, и другое (внутренее соединение): 1. SELECT m_pc.maker m1, m_printer.maker m2 FROM 2. ( SELECT maker FROM Product WHERE type= 'pc' ) m_pc 3. FULL JOIN 4. ( SELECT maker FROM Product WHERE type= 'printer' ) m_printer 5. ON m_pc.maker = m_printer.maker 6. EXCEPT 7. SELECT * FROM 8. ( SELECT maker FROM Product WHERE type= 'pc' ) m_pc 9. INNER JOIN 10. ( SELECT maker FROM Product WHERE type= 'printer' ) m_printer 11. ON m_pc.maker = m_printer.maker; Попутно я убрал из этого решения избыточные DISTINCT, поскольку EXCEPT выполнит исключение дубликатов. Это единственный полезный тут урок, т.к. операцию взятия разности (EXCEPT) можно заменить простым предикатом: 1. WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL или даже так 1. m_pc.maker + m_printer.maker IS NULL ввиду того, что конкатенация с NULL-значением дает NULL. 1. SELECT * FROM 2. ( SELECT DISTINCT maker FROM Product WHERE type= 'pc' ) m_pc 3. FULL JOIN 4. ( SELECT DISTINCT maker FROM Product WHERE type= 'printer' ) m_printer 5. ON m_pc.maker = m_printer.maker 6. WHERE m_pc.maker IS NULL OR m_printer.maker IS NULL ; Наконец, чтобы представить результат в один столбец, воспользуемся функцией |