лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
iteration Exact Res1 Res2 1 1.7320508075688772 3.0 3.0 2 1.7320508075688772 2.0 1.999992 3 1.7320508075688772 1.75 1.7499920000160001 4 1.7320508075688772 1.7321428571428572 1.7321358469571777 5 1.7320508075688772 1.7320508100147274 1.7320438814531474 6 1.7320508075688772 1.7320508075688772 1.7320438793794952 7 1.7320508075688772 1.7320508075688772 1.7320438793795034 Видно, что уже на 6-й итерации вычисления в третьем столбце [Res1] привели к совпадению со значением встроенной функции [Exact] в пределах точности FLOAT(53) для квадратного корня из трех. Вычисления в четвертом столбце [Res2] – нет. В чем же причина таких различий? Не сразу очевидно, но причина в том, что выражение (1./6.) вычисляется с большой ошибкой, так как операнды не приведены к 8-байтовому представлению вещественных чисел (двойная точность). Это повлияло на все вычисления, и мы получили только 5-6 правильных значащих цифр в результате, что согласуется с теорией вычислений в вещественной арифметике с одинарной точностью. Функция EOMONTH Как узнать последний день месяца по заданной дате, например, текущего месяца? Текущую дату мы можем узнать, используя встроенную функцию current_timestamp: 1. SELECT current_timestamp; Чтобы узнать последний день предыдущего месяца, мы можем от текущей даты отнять номер текущего дня месяца, т.е. количество дней, прошедших от начала месяца: 1. SELECT dateadd ( dd, -day ( current_timestamp ) , current_timestamp ) ; Тогда для текущего месяца нам потребуется предварительно добавить один месяц к текущей дате: 1. SELECT dateadd ( dd, -day ( dateadd ( mm, 1 , current_timestamp )) , 2. dateadd ( mm, 1 , current_timestamp )) ; Уберем, наконец, из полученного результата компоненту времени: 1. SELECT CAST ( 2. dateadd ( dd, -day ( dateadd ( mm, 1 , current_timestamp )) , dateadd ( mm, 1 , current_timestamp )) 3. AS date ) ; В SQL Server 2012 появилась функция EOMONTH, которая позволяет сделать то же самое без применения "процедурной" логики: 1. SELECT CAST ( 2. dateadd ( dd, -day ( dateadd ( mm, 1 , current_timestamp )) , 3. dateadd ( mm, 1 , current_timestamp )) AS date 4. ) old_way, eomonth ( current_timestamp ) new_way; Если вы получаете ошибку при выполнении последнего запроса, значит учебник еще не переехал на версию SQL Server, поддерживающую EOMONTH. Ну, а для времени, когда я написал этот запрос, результаты, естественно, совпали: old_way new_way 2016-07-31 2016-07-31 Мы уже знаем, что функция EOMONTH имеет аргументом выражение типа даты. Кроме того, функция имеет также второй (необязательный) целочисленный аргумент, представляющий число месяцев, которые, при наличии, будут добавлены к дате, представленной первым аргументом. Например, следующий запрос даст нам последние дни предыдущего, текущего и следующего месяца для даты '2016-01-28': 1. SELECT eomonth ( '2016-01-28' , -1 ) prev_month, 2. eomonth ( '2016-01-28' ) this_month, 3. eomonth ( '2016-01-28' , 1 ) next_month; prev_month this_month next_month 2015-12-31 2016-01-31 2016-02-29 Функция STRING_AGG Агрегация текстовых данных Рассмотрим такую задачу. Перечислить через запятую все корабли из таблицы Ships, которые принадлежат Японии. Получить список кораблей Японии труда не составляет: 1. SELECT name FROM Ships s JOIN Classes c ON s.class=c.class 2. WHERE country= 'Japan' 3. ORDER BY name; В MySQL есть замечательная агрегатная функция GROUP_CONCAT, которая решает поставленную задачу: 1. SELECT GROUP_CONCAT ( name ) ships_list FROM Ships s JOIN Classes c ON s.class=c.class 2. WHERE country= 'Japan' 3. ORDER BY name; ships_list haruna,hiei,kirishima,kon,musashi,yamato По умолчанию в качестве разделителя как раз используется запятая, хотя мы можем выбрать любой символ. Если выполнить группировку, то легко получить список кораблей для каждой страны: 1. SELECT country, GROUP_CONCAT ( name ) ships_list 2. FROM Ships s JOIN Classes c ON s.class=c.class 3. GROUP BY country 4. ORDER BY country, name; country ships_list gt.britain renown,repulse,resolution,ramillies,revenge,royal oak,royal sovereign japan haruna,hiei,kirishima,kongo,musashi,yamato usa iowa,missouri,new jersey,wisconsin,north carolina,south dakota,washington,california,tennessee Для SQL Server решение нашей задачи можно получить менее естественным способом - через представление результата выборки в форме XML: 1. SELECT STUFF ( 2. ( SELECT ',' +name AS 'data()' FROM Ships s JOIN Classes c ON s.class=c.class 3. WHERE country= 'Japan' 4. ORDER BY name FOR XML PATH ( '' ) 5. ) , 1 , 1 , '' ) ; Группировка по стране еще добавит сложности. Но мы не будем этого делать, поскольку в SQL Server, начиная с версии 2017, появилась функция STRING_AGG, позволяющая конкатенировать строки. Эта функция имеет два обязательных аргумента - строковое выражение, которое и будет использоваться для сцепления, и разделитель. 1. SELECT country, STRING_AGG ( name, ',' ) ships_list 2. FROM Ships s JOIN Classes c ON s.class=c.class 3. GROUP BY country 4. ORDER BY country; country ships_list Gt.Britain Renown,Repulse,Resolution,Ramillies,Revenge,Royal Oak,Royal Sovereign Japan Musashi,Yamato,Haruna,Hiei,Kirishima,Kongo USA North Carolina,South Dakota,Washington,Iowa,Missouri,New Jersey,Wisconsin,California,Tennessee Из представленного результата видно, что корабли в списке не отсортированы. Сортировка в стиле GROUP_CONCAT здесь не работает. Чтобы задать порядок сортировки, используется необязательное предложение WITHIN GROUP: 1. SELECT country, STRING_AGG ( name, ',' ) WITHIN GROUP ( ORDER BY name ) ships_list 2. FROM Ships s JOIN Classes c ON s.class=c.class 3. GROUP BY country 4. ORDER BY country; country ships_list Gt.Britain Ramillies,Renown,Repulse,Resolution,Revenge,Royal Oak,Royal Sovereign Japan Haruna,Hiei,Kirishima,Kongo,Musashi,Yamato USA California,Iowa,Missouri,New Jersey,North Carolina,South Dakota,Tennessee,Washington,Wisconsin При использовании функции STRING_AGGсоединяемые значения преобразуются к типу данных VARCHAR (NVARCHAR). Типом результата будет VARCHAR(8000) или NVARCHAR(4000), если среди соединяемых значений не будет значений типа VARCHAR(MAX) или NVARCHAR(MAX). В последнем случае результат будет иметь тип VARCHAR(MAX) (или NVARCHAR(MAX) соответственно). В нижеследующем примере будет получена ошибка, поскольку длина результата - 8011 - превышает значение 8000: 8000 (значение 'a' будет дополнено пробелами, т.к. используется тип постоянной длины CHAR), 1 символ на запятую-разделитель, 10 (длина значения 'bbbbbbbbbb'). 1. declare @a char ( 8000 ) , @b varchar ( 10 ) ; 2. SELECT @a= 'a' ,@b=replicate ( 'b' , 10 ) ; 3. SELECT string_agg ( x, ',' ) res 4. FROM ( VALUES ( @b ) , ( @a )) X ( x ) ; Результат агрегирования STRING_AGG превышает предел в 8000 байтов. Используйте типы LOB, чтобы избежать усечения результатов. Однако если, скажем, для переменной @b мы используем тип VARCHAR(MAX), то код выполнится без ошибки: 1. declare @a char ( 8000 ) , @b varchar ( MAX ) ; 2. SELECT @a= 'a' ,@b=replicate ( 'b' , 10 ) ; 3. SELECT string_agg ( x, ',' ) res 4. FROM ( VALUES ( @b ) , ( @a )) X ( x ) ; res bbbbbbbbbb,a ...(еще 7999 пробелов) Функция STRING_SPLIT Функция STRING_SPLIT выполняет операцию, обратную STRING_AGG . Она принимает на входе символьную строку и разбивает её на подстроки по заданному вторым параметром разделителю. Эти подстроки формируют значения унарной таблицы. К единственному столбцу этой таблицы можно обратиться по имени value. 1. SELECT * FROM STRING_SPLIT ( '0 1 2 3 4 5 6 7 8 9' , ' ' ) ; value 0 1 2 3 4 5 6 7 8 9 Фактически функция STRING_SPLIT играет роль нестандартного конструктора одностолбцовой таблицы. Стандартная альтернатива выглядит так: 1. SELECT * FROM ( VALUES ( 0 ) , ( 1 ) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 ) , ( 6 ) , ( 7 ) , ( 8 ) , ( 9 )) X ( value ) ; Больший интерес представляет случай, когда на вход функции передается значение столбца некоторой таблицы. Пусть, например, нам требуется разбить многословные названия кораблей в таблице Ships на отдельные слова. Тогда можно выполнить следующий запрос: 1. SELECT name, value FROM Ships CROSS APPLY STRING_SPLIT ( name, ' ' ) 2. WHERE name LIKE '% %' ; name value New Jersey New New Jersey Jersey North Carolina North North Carolina Carolina Royal Oak Royal Royal Oak Oak Royal Sovereign Royal Royal Sovereign Sovereign South Dakota South South Dakota Dakota Функция CHOOSE Нестандартная функция CHOOSE появилась в SQL Server версии 2012. Функция CHOOSE используется для выбора одного из нескольких вариантов. Выбор осуществляется на основании индекса (номера варианта), который является первым параметром функции. Остальные параметры представляют собой варианты выбора. Будет выбран тот вариант, номер которого в списке параметров, совпадает с индексом. Например, запрос 1. SELECT CHOOSE ( 2 , 'PC' , 'Laptop' , 'Printer' ) ; вернет Laptop, т.к. это второй элемент списка. Больший интерес представляет случай, когда индексом является выражение, включающее столбцы таблиц. Пусть нам требуется вместо номера пункта (в базе данных " Вторсырье ") выводить его название. Поскольку в базе данных названия пунктов не хранятся, будем формировать их "налету", используя функцию CHOOSE: 1. SELECT DISTINCT point, 2. CHOOSE ( point, 'point A' , 'point B' , 'point C' ) point_name 3. FROM outcome; point point_name 1 point A 2 point B 3 point C Очевидно, что функция CHOOSE является еще одним частным вариантом выражения CASE . Наш пример с использовании функции CASE можно переписать в виде: 1. SELECT DISTINCT point, 2. CASE point 3. WHEN 1 THEN 'point A' 4. WHEN 2 THEN 'point B' 5. WHEN 3 THEN 'point C' 6. END point_name 7. FROM outcome; Если указанному индексу не будет найдено соответствия, то результатом функции CHOOSE будет NULL: 1. SELECT CHOOSE ( 4 , 'PC' , 'Laptop' , 'Printer' ) ; Для обработки этого случая в выражении CASE предусмотрено предложение ELSE . С помощью этого предложения мы можем вместо NULL (по умолчанию) вывести, например, UNKNOWN: 1. SELECT CASE 4 2. WHEN 1 THEN 'PC' 3. WHEN 2 THEN 'Laptop' 4. WHEN 3 THEN 'Printer' 5. ELSE 'UNKNOWN' 6. END ; Мы легко можем преодолеть этот "недостаток" функции CHOOSE, обернув её функцией COALESCE: 1. SELECT COALESCE ( CHOOSE ( 4 , 'PC' , 'Laptop' , 'Printer' ) , 'UNKNOWN' ) ; но это уже как бы "масло масляное" - case от case. Можно сказать, что функция CHOOSE имеет специфическое применение, но в этих ограниченных случаях она позволяет упростить запись. В заключение приведем еще один пример. Для каждой модели указать, является её номер четным (even) или нечетным (odd). 1. SELECT model, CHOOSE ( model% 2+1 , 'EVEN' , 'ODD' ) 2. FROM product 3. WHERE ISNUMERIC ( model ) = 1 ; Предикат в предложении WHERE используется для того, чтобы ограничиться моделями, номера которых представлены числом. Обратите внимание на тип данных столбца model ! Надеюсь, что те, кто решает упражнения на сайте sql-ex.ru уже в курсе. :-) Они также должны быть в курсе, что использование функции ISNUMERIC в данном контексте не является радикальным решением. Заметки о типах данных В этом разделе я предполагаю писать о том, что мне показалось странным или необычным в отношении типов данных и их преобразовании, о том, что противоречит стандарту и называется «особенностями реализации». CHAR и VARCHAR Недавно мне довелось искать ошибку в решении, которое содержало такое преобразование: 1. CAST ( model AS VARCHAR ) Те, кто изучил схему « Компьютеры », подумают о бессмысленности преобразования типа в тот же самый тип (столбец model определен как VARCHAR(50)). Однако именно это преобразование и делало запрос неверным. Дело в том, что, если размер типа при преобразовании не указан, то в SQL Server принимается значение по умолчанию, которое для VARCHAR равно 30. При этом если преобразуемая строка имеет больший размер, то отсекаются все символы кроме первых 30-ти. Разумеется, никакой ошибки при этом не возникает. Как раз на «длинных» номерах моделей предложенное решение и давало неверный результат. Как говорится в таких случаях, читайте документацию. Однако интересно, что по этому поводу говорит Стандарт? Согласно стандарту, если для типов CHAR и VARCHAR размер не указан, то подразумевается CHAR(1) и VARCHAR(1) соответственно. Давайте проверим, как следуют стандарту доступные мне СУБД: SQL Server, MySQL, PostgreSQL. Тут имеется два аспекта: 1. Преобразование типа 2. Использование типов CHAR/VARCHAR при описании схемы (DDL). Начнем с преобразования типа. SQL Server 2008 1. SELECT CAST ( '11111111112222222222333333333344444444445555555555 ' AS CHAR ) chr, 2. CAST ( '11111111112222222222333333333344444444445555555555 ' AS VARCHAR ) vchr; В результате получим chr vchr 11111111112222222222333333 3333 11111111112222222222333333 3333 То есть оба символьных типа усекаются до значения по умолчанию, которое равно 30. Никаких сообщений об ошибках не возникает, что, собственно, соответствует стандарту. PostgreSQL 8.3 1. SELECT CAST ( '11111111112222222222333333333344444444445555555555 ' AS CHAR ) AS chr, 2. CAST ( '11111111112222222222333333333344444444445555555555 ' AS VARCHAR ) AS vchr; chr vchr 1 11111111112222222222333333333344444444445555555555 Налицо половинчатое следование стандарту, т.е. соответствие ему в отношении типа CHAR. Что касается типа VARCHAR, то согласно документации, если длина строки не указана, принимается строка любого размера, т.е. усечения не происходит. (If character varying is used without length specifier, the type accepts strings of any size.) MySQL 5.0 Как говорится, чем дальше, тем «страньше». Оказывается, в MySQL преобразование к типу VARCHAR вообще не поддерживается. Нам остается проверить только преобразование к CHAR: 1. SELECT CAST ( '11111111112222222222333333333344444444445555555555 ' AS CHAR ) chr; chr 11111111112222222222333333333344444444445555555555 Т.е. строка не усекается; при этом в документации читаю: «Если при использовании функций CAST и CONVERT размер не указан, то длина по умолчанию – 30.» (When n is not specified when using the CAST and CONVERT functions, the default length is 30). Посмотрим теперь, как обстоят дела с определением данных. Ниже приведен тестовый скрипт. 1. CREATE TABLE Test_char ( chr CHAR, vchr VARCHAR ) ; 1. DELETE FROM Test_char; 1. INSERT INTO Test_char 2. VALUES ( '1' , '11111111112222222222333333333344444444445555555555 ' ) ; 3. 4. INSERT INTO Test_char 5. VALUES ( '11111111112222222222333333333344444444445555555555' , '1' ) ; 6. 7. INSERT INTO Test_char 8. VALUES ( '2' , CAST ( '111111111122222222223333333333' AS VARCHAR )) ; 9. 10. INSERT INTO Test_char 11. VALUES ( CAST ( '111111111122222222223333333333' AS CHAR ) , '2' ) ; 12. 13. INSERT INTO Test_char 14. VALUES ( '3' , '3' ) ; 1. SELECT * FROM Test_char; |