лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Функция CONCAT Для конкатенации строк в SQL Server используется оператор "+". Т.е. если операнды являются числовыми, то выполняется операция сложения, а если – строковыми, то конкатенация: 1. SELECT 1+2+3+4 a, '1' + '2' + '3' + '4' b; a b 10 1234 Если же операнды являются значениями разных типов, то SQL Server выполняет неявное преобразование типов. Выполняя следующий запрос 1. SELECT hd + ' Gb' volume FROM PC WHERE model = 1232 ; мы получим сообщение об ошибке: Error converting data type varchar to real.(Ошибка при преобразовании типа данных varchar к real.) Существует приоритет типов при их неявном преобразовании, и в соответствии с этим приоритетом сервер пытается преобразовать строку ' Gb' к типу данных столбца hd (real). Разумеется, явное преобразование типа решает проблему: 1. SELECT CAST ( hd AS VARCHAR ) + ' Gb' volume FROM PC WHERE model = 1232 ; volume 5 Gb 10 Gb 8 Gb 10 Gb В SQL Server 2012 появилась функция CONCAT, которая выполняет конкатенацию, неявно преобразуя типы аргументов к строковому типу данных. С помощью этой функции предыдущий запрос можно переписать так: 1. SELECT CONCAT ( hd, ' Gb' ) volume FROM PC WHERE model= 1232 ; Еще одна полезная особенность функции CONCAT состоит в том, что NULL-значения неявно преобразуются в пустую строку- ''. Обычная же конкатенация с NULL-значением дает NULL. Вот пример, который это демонстрирует. 1. SELECT NULL + 'concatenation with NULL' plus, 2. CONCAT ( NULL , 'concatenation with NULL' ) concat; plus concat NULL concatenation with NULL Следует отметить, что у функции CONCAT может быть произвольное число аргументов, но не менее двух. 1. SELECT 1+2+3+4 a, CONCAT ( 1 , 2 , 3 , 4 ) b; a b 10 1234 MySQL В MySQL также имеется функция CONCAT, вернее, даже две функции. Первая из них – CONCAT – возвращает NULL, если среди аргументов функции встречается NULL, вторая – CONCAT_WS – опускает аргумент, если его значение NULL. Кроме того, эта функция первым аргументом имеет разделитель, используемый при конкатенации. 1. SELECT CONCAT ( NULL , 'concatenation with NULL' ) concat, 2. CONCAT_WS ( '' , NULL , 'concatenation with NULL' ) concat_ws, 3. CONCAT_WS ( ', ' , 1 , 2 , NULL , 4 ) concat_ws_null; concat concat_ws concat_ws_null (NULL) concatenation with NULL 1, 2, 4 Операторы PIVOT и UNPIVOT Чтобы объяснить, что такое PIVOT, я бы начал с электронных таблиц EXCEL. В версии MS Excel 5.0 появились так называемые сводные таблицы. Сводные таблицы представляют собой двумерную визуализацию многомерных структур данных, применяемых в технологии OLAP для построения хранилищ данных. Правильней даже сказать двумерные сечения трехмерных OLAP-кубов, если иметь в виду наличие на сводной таблице элемента, который называется «страница». Сводные таблицы позволяют выполнять стандартные операции с многомерными структурами, например, упоминавшееся уже сечение куба, свертку и детализацию – операцию обратную свертке. Следует сказать, что сводная таблица не является реляционной, поскольку имеет не только заголовки столбцов, но и заголовки строк, при этом и те и другие формируются из данных, находящихся в столбцах обычных реляционных таблиц. Последнее, кстати, означает, что число строк и столбцов заранее неизвестно, т.к. они формируются динамически при выполнении запроса к реляционным данным. Кроме того, заголовки могут иметь многоуровневые подзаголовки, что и позволяет выполнять операции свертки (переход на более высокий уровень иерархии) и детализации (переход на более низкий уровень иерархии). Такие свойства сводных таблиц позволяют их использовать, наряду со сводными диаграммами, в качестве клиента для визуального отображения многомерных данных, находящихся в хранилищах, поддерживаемых различными СУБД (например, MS SQL Server Analysis Services). Чтобы пояснить сказанное примером, давайте рассмотрим такой запрос к одной из учебных баз на sql- ex.ru: 1. SELECT maker, type 2. FROM product; результатом которого является такая таблица: maker type B PC A PC A PC E PC A Printer D Printer A Laptop C Laptop A Printer A Printer D Printer E Printer B Laptop A Laptop E PC E PC Пусть теперь нам требуется получить таблицу со следующими заголовками: Типы продукции П р о и з в о д и т е л и Laptop PC Printer A B C D E Заголовками строк здесь являются уникальные имена производителей, которые берутся из столбца maker вышеприведенного запроса, а заголовками столбцов – уникальные типы продукции (соответственно, из столбца type). А что должно быть в середине? Ответ очевиден – некоторый агрегат, например, функция count(type), которая подсчитает для каждого производителя отдельно число моделей ПК, ноутбуков и принтеров, которые и заполнят соответствующие ячейки этой таблицы. Это простейший вариант сводной таблицы, который имеет всего два уровня иерархии по столбцам и строкам. Т.е. выполняя свертку по вертикали, мы получаем количество моделей каждого вида продукции для всех производителей, а по горизонтали – общее число моделей независимо от типа для каждого производителя. Можно было бы добавить дополнительные уровни иерархии, например, для градации принтеров по цветности, a ноутбуков по размеру экрана и т.д. Можно сказать, что pivot-таблица в SQL – это одноуровневая сводная таблица. Оператор PIVOT не является стандартным (я не уверен, что он когда-нибудь будет стандартизован ввиду нереляционной природы pivot-таблицы), поэтому я буду использовать в примерах его реализацию в языке T-SQL (SQL Server 2005/2008). Я могу и ошибиться в хронологии, но мне представляется, что успех реализации сводной таблицы в Excel привел к появлению так называемых перекрестных запросов в Access, и, наконец, к оператору PIVOT в T-SQL. Особенности: ORACLE В Oracle операции PIVOT и DRILLDOWN (UNPIVOT) были отданы на откуп средствам визуализации OLAP, т.е. MS Excel, BusinessObjects и пр. В Oracle 11g появились pivot/unpivot, но в pivot есть "тонкость" - нужно ЯВНО указывать столбцы, если результат нужен в ТАБЛИЧНОМ виде. Получение данных для столбцов, не указанных явно, также возможно, но только в виде XML. Оператор PIVOT Давайте рассмотрим такую задачу. Пример 1. Для каждого производителя из таблицы Product определить число моделей каждого типа продукции. Задачу можно решить стандартными средствами с использованием оператора CASE: 1. SELECT maker, 2. SUM ( CASE type WHEN 'pc' THEN 1 ELSE 0 END ) PC 3. , SUM ( CASE type WHEN 'laptop' THEN 1 ELSE 0 END ) Laptop 4. , SUM ( CASE type WHEN 'printer' THEN 1 ELSE 0 END ) Printer 5. FROM Product 6. GROUP BY maker; Теперь решение через PIVOT: 1. SELECT maker, -- столбец (столбцы), значения из которого формируют заголовки строк 2. [ pc ] , [ laptop ] , [ printer ] -- значения из столбца, который указан в предложении type, 3. -- формирующие заголовки столбцов 4. FROM Product -- здесь может быть подзапрос 5. PIVOT -- формирование пивот-таблицы 6. ( COUNT ( model ) -- агрегатная функция, формирующая содержимое сводной таблицы 7. FOR type -- указывается столбец, 8. -- уникальные значения в котором будут являться заголовками столбцов 9. IN ([ pc ] , [ laptop ] , [ printer ]) --указываются конкретные значения в столбце type, 10. -- которые следует использовать в качестве заголовков, 11. -- т.к. нам могут потребоваться не все 12. ) pvt ; -- алиас для сводной таблицы Надеюсь, что комментарии к коду достаточно понятны для того, чтобы написать оператор PIVOT без шпаргалки. Давайте попробуем. Пример 2. Посчитать среднюю цену на ноутбуки в зависимости от размера экрана. Задача элементарная и решается с помощью группировки: 1. SELECT screen, AVG ( price ) avg_ 2. FROM Laptop 3. GROUP BY screen; screen avg_ 11 700.00 12 960.00 14 1175.00 15 1050.00 А вот как можно повернуть эту таблицу с помощью PIVOT: 1. SELECT [ avg_ ] , 2. [ 11 ] , [ 12 ] , [ 14 ] , [ 15 ] 3. FROM ( SELECT 'average price' AS 'avg_' , screen, price FROM Laptop ) x 4. PIVOT 5. ( AVG ( price ) 6. FOR screen 7. IN ([ 11 ] , [ 12 ] , [ 14 ] , [ 15 ]) 8. ) pvt; avg_ 11 12 14 15 average price 700.00 960.00 1175.00 1050.00 В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. для этого нужно знать характер данных, а значит и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников). Если рассмотренных примеров покажется недостаточно, чтобы понять и использовать без затруднений этот оператор, я вернусь к нему, когда придумаю нетривиальные примеры, где использование оператора PIVOT позволяет существенно упростить код. Я написал этот опус в помощь тем, кому оператор PIVOT интуитивно непонятен. Могу согласиться с тем, что в реляционном языке SQL он выглядит инородным телом. Собственно, иначе и быть не может ввиду того, что поворот (транспонирование) таблицы является не реляционной операцией, а операцией работы с многомерными структурами данных. Оператор UNPIVOT Как следует из названия оператора, UNPIVOT выполняет обратную по отношению к PIVOT операцию, т.е. представляет данные, записанные в строке таблицы, в одном столбце. В примере, рассмотренном в предыдущем параграфе , мы с помощью оператора PIVOT разворачивали в строку таблицу, полученную с помощью следующего запроса: 1. SELECT screen, AVG ( price ) avg_ 2. FROM Laptop 3. GROUP BY screen; screen avg_ 11 700.00 12 960.00 14 1175.00 15 1050.00 В результате было получено следующее представление: avg_ 11 12 14 15 average price 700.00 960.00 1175.00 1050.00 Исходный результат мы можем получить, если применим к pivot-запросу unpivot-преобразование: 1. SELECT screen -- заголовок столбца, который будет содержать заголовки 2. -- строк исходной таблицы 3. ,avg__ AS avg_ 4. -- заголовок столбца, который будет содержать значения из строки исходной таблицы 5. FROM ( -- pivot-запрос из предыдущего примера 6. SELECT [ avg_ ] , [ 11 ] , [ 12 ] , [ 14 ] , [ 15 ] 7. FROM ( SELECT 'average price' AS 'avg_' , screen, price 8. FROM Laptop ) x 9. PIVOT ( AVG ( price ) FOR screen IN ([ 11 ] , [ 12 ] , [ 14 ] , [ 15 ]) ) 10. pvt 11. -- конец pivot-запроса 12. ) pvt 13. UNPIVOT ( avg__ -- заголовок столбца, который будет содержать значения 14. -- из столбцов исходной таблицы, перечисленных ниже 15. FOR screen IN ([ 11 ] , [ 12 ] , [ 14 ] , [ 15 ]) 16. ) unpvt; Заметим, что имя avg_ нельзя использовать в операторе UNPIVOT, поскольку оно уже использовалось в операторе PIVOT, поэтому я использовал новое имя avg__, которому затем присвоил алиас, чтобы полностью воссоздать результат, полученный с помощью группировки. Рассмотрим теперь более содержательный пример. Пусть требуется информацию о рейсе 1100 представить в следующем виде: trip_no spec info 1100 id_comp 4 1100 plane Boeing 1100 town_from Rostov 1100 town_to Paris 1100 time_out 14:30:00 1100 time_in 17:50:00 Поскольку информация из строки таблицы трансформируется в столбец, то напрашивается использование оператора UNPIVOT. Здесь следует сделать одно замечание. Значения в этом столбце должны быть одного типа. Поскольку в этот столбец в нашем примере собираются значения из разных столбцов исходной таблицы, то нужно преобразовать их к единому типу. Более того, должны совпадать не только типы, но и размер. Общим типом в нашем случае является строковый тип. Поскольку столбцы town_from и town_to уже имеют тип char(25), то приведем все к этому типу: 1. SELECT trip_no, CAST ( id_comp AS CHAR ( 25 )) id_comp, 2. CAST ( plane AS CHAR ( 25 )) plane,town_from,town_to, 3. CONVERT ( CHAR ( 25 ) ,time_out, 108 ) time_out, 4. CONVERT ( CHAR ( 25 ) ,time_in, 108 ) time_in 5. FROM Trip WHERE trip_no = 1100 ; trip_ no id_co mp pla ne town_fr om town _to time_ out time _in 1100 4 Boei ng Rostov Paris 14:30:0 0 17:50: 00 Здесь мы заодно преобразовали время вылета/прилета, убрав из него составляющую даты: 1. CONVERT ( CHAR ( 25 ) ,time_out, 108 ) Остальное, я надеюсь, понятно из кода: 1. SELECT trip_no, spec, info FROM ( 2. SELECT trip_no, CAST ( id_comp AS CHAR ( 25 )) id_comp, 3. CAST ( plane AS CHAR ( 25 )) plane, 4. CAST ( town_from AS CHAR ( 25 )) town_from, 5. CAST ( town_to AS CHAR ( 25 )) town_to, 6. CONVERT ( CHAR ( 25 ) ,time_out, 108 ) time_out, 7. CONVERT ( CHAR ( 25 ) ,time_in, 108 ) time_in 8. FROM Trip 9. WHERE trip_no = 1100 ) x 10. UNPIVOT ( info 11. FOR spec IN ( id_comp, plane, town_from, town_to, time_out, time_in ) 12. ) unpvt; Столбец с именем spec используется для вывода названий параметров, а столбец info содержит сами параметры. Результат выполнения запроса уже был представлен в условии задачи. Пусть нам требуется повернуть строку, содержащую NULL-значение в одном из столбцов. 1. WITH utest AS 2. ( SELECT 1 a, 2 b, NULL c ) 3. SELECT * FROM utest; Т.е. вместо результата a b c 1 2 NULL мы хотим получить a 1 b 2 c NULL Применим оператор UNPIVOT: 1. WITH utest AS 2. ( SELECT 1 a, 2 b, NULL c ) 3. SELECT col, value FROM utest 4. UNPIVOT ( 5. value FOR col IN ( a,b,c ) 6. ) AS unpvt; Первая неожиданность - ошибка компиляции: Тип столбца "c" конфликтует с типами других столбцов, указанных в списке UNPIVOT. Это означает, что сервер неявно не преобразовал тип столбца "с", содержащий NULL, к типу первых двух столбцов, которые могут быть оценены как целочисленные. Давайте сделаем это явно: 1. WITH utest AS 2. ( SELECT 1 a, 2 b, CAST ( NULL AS INT ) c ) 3. SELECT col,value FROM utest 4. UNPIVOT ( 5. value FOR col IN ( a,b,c ) 6. ) AS unpvt; col value a 1 b 2 Вторая неожиданность - оказывается UNPIVOT игнорирует NULL-значения, не выводя их в результирующем наборе. Первое, что приходит в голову всем, это заменить NULL каким-нибудь валидным значением, заведомо отсутствующим в столбце. Если, в соответствии с ограничениями предметной области, значения с не могут быть отрицательными, заменим NULL на -1: 1. WITH utest AS 2. ( SELECT 1 a, 2 b, COALESCE ( CAST ( NULL AS INT ) , -1 ) c ) 3. SELECT col, value FROM utest 4. UNPIVOT ( 5. value FOR col IN ( a, b, c ) 6. ) AS unpvt; |