лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
NULL-значений (по умолчанию включено) с помощью соответствующей установки параметра ANSI_NULLS: 1. SET ANSI_NULLS OFF|ON Напишите в Management Studio (или в Query Analyzer для SQL Server 2000 и ранее) следующий код, и вы все поймете: 1. SET ANSI_NULLS OFF; 2. SELECT * 3. FROM ( SELECT name, launched, 4. CASE 5. WHEN launched < 1940 6. THEN NULL 7. ELSE launched 8. END year 9. FROM Ships 10. ) x 11. WHERE year = NULL ; Предикат NOT IN Рассмотрим еще один пример, позаимствованный мной у Селко [7] . Идея его состоит в использовании предиката NOT IN (<список значений, включающий NULL>). Опять таки, для того чтобы вы могли проверить справедливость рассуждений на сайте, давайте искусственно добавим NULL-значения в результат запроса: 1. SELECT name, launched, 2. CASE 3. WHEN launched < 1915 4. THEN NULL 5. ELSE launched 6. END year 7. FROM Ships 8. WHERE launched <= 1915 ; Мы специально взяли 1915 год, чтобы результирующий набор был невелик. Вот он: name launched year Hiei 1914 NULL Kirishima 1915 1915 Kongo 1913 NULL А теперь напишем запрос, который должен вернуть все корабли, год спуска на воду не находится в наборе значений столбца year: 1. SELECT * 2. FROM Ships 3. WHERE launched <= 1916 AND 4. launched NOT IN ( SELECT year 5. FROM ( SELECT name, launched, 6. CASE WHEN launched < 1915 7. THEN NULL 8. ELSE launched 9. END year 10. FROM Ships 11. WHERE launched <= 1915 12. ) x 13. ) ; Запрос 1. SELECT * 2. FROM Ships 3. WHERE launched <= 1915 ; дает нам следующий набор кораблей: name class launched Hiei Kongo 1914 Kirishima Kongo 1915 Kongo Kongo 1913 Казалось бы, мы должны получить корабли Hiei и Kongo, так как год их спуска на воду известен и не равен 1915. Но нет, мы опять получаем пустой результирующий набор. Оценим значение предиката для первого из этих кораблей — Hiei (для остальных все будет аналогично). Итак, 1. 1914 NOT IN ( 1915 , NULL ) Еще одно NULL-значение мы опустили для краткости. Последний предикат можно заменить следующим: 1. 1914 <> ALL ( 1915 , NULL ) что эквивалентно 1. 1914 <> 1915 2. AND 3. 1914 <> NULL Последнее выражение всегда равно UNKNOWN, следовательно, предикат можно переписать в виде: 1. 1914 <> 1915 2. AND 3. UNKNOWN Следовательно, и все выражение будет равно UNKNOWN, так как первое сравнение дает TRUE. Если бы первое сравнение было ложным (для 1915 года), то результат всего выражения был бы равен FALSE. Поэтому можно сделать вывод, что при наличии NULL-значения в наборе предикат NOT IN в предложении WHERE всегда будет давать пустой набор записей. В заключение следует сказать, что если вы выполняете горизонтальную фрагментацию некоторой таблицы, используя некоторое пороговое значение столбца, допускающего NULL-значения, то объединение фрагментов типа 1. SELECT * 2. FROM Ships 3. WHERE launched <= 1915 4. UNION 5. SELECT * 6. FROM Ships 7. WHERE launched > 1915 ; не гарантирует восстановления исходной таблицы. Для этого потребуется еще один фрагмент, содержащий в столбце launched NULL-значения: 1. SELECT * 2. FROM Ships 3. WHERE launched IS NULL ; Эти «хитрые» внешние соединения Пусть требуется для каждого класса определить все корабли с известным годом спуска на воду. Когда говорится «для каждого класса», мы уже знаем, что нужно использовать внешнее соединение, например, левое: Решение 8.6.1 1. SELECT Classes.class, name, launched 2. FROM Classes LEFT JOIN 3. Ships ON Classes.class = Ships.class AND 4. launched IS NOT NULL ; Тем самым мы соединяем таблицу Classes с таблицей Ships по столбцу class и отбираем корабли с известным годом спуска на воду. Вот что, помимо прочего, мы имеем в результате: Class Name launched Bismarck NULL NULL Как же так? Мы же указывали в предикате соединения launched IS NOT NULL? В словах «в предикате соединения» как раз и кроется ответ на наш вопрос. Вернемся к определению внешнего левого соединения : Соединяются все строки из левой таблицы с теми строками из правой, для которых значение предиката истинно. Если для какой-либо строки из левой таблицы нет ни одной соответствующей строки из правой таблицы, то значения столбцов правой таблицы получают значение NULL. В таблице Ships нет ни одного корабля класса Bismarck. Потому мы и получили эту строку, так как класс Bismarck есть в таблице Classes. А если бы такой корабль был? Давайте добавим в таблицу Ships два корабля класса Bismarck — один с известным годом спуска на воду, а другой — с неизвестным: 1. SELECT * 2. FROM Ships 3. UNION ALL 4. SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched 5. UNION ALL 6. SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched; Перепишем решение 8.6.1 с учетом этих новых кораблей: Решение 8.6.2 1. SELECT Classes.class, name, launched 2. FROM Classes LEFT JOIN 3. ( SELECT * 4. FROM Ships 5. UNION ALL 6. SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched 7. UNION ALL 8. SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched 9. ) Ships ON Classes.class = Ships.class AND 10. launched IS NOT NULL ; Теперь получаем ожидаемый результат, а именно, в результирующем наборе будет присутствовать только один корабль класса Bismarck: Class Name launched Bismarck B_1 1941 Вывод. Если вам нужно ограничить результирующий набор внешнего соединения, используйте предложение WHERE, которое как раз и служит для этой цели: Решение 8.6.3 1. SELECT Classes.class, name, launched 2. FROM Classes LEFT JOIN 3. Ships ON Classes.class = Ships.class 4. WHERE launched IS NOT NULL ; Предикат же соединения определяет лишь то, какие строки из разных таблиц будут конкатенированы в результирующем наборе. В заключении замечу, что данный пример не является вполне показательным, так как для решения поставленной задачи вполне подошло бы внутреннее соединение (INNER JOIN), несмотря на слова «для каждого класса». Однако гибкость языка SQL позволяет решить задачу разными способами, и использование стереотипов вполне оправдано. Как правило, в приводимых примерах используются эквисоединения, т.е. соединения по равенству значений (=). Это обусловлено тем, что на практике зачастую используется соединение по внешнему ключу. Подобные примеры можно увидеть на предыдущей странице . Однако предикатом соединения может быть любое логическое выражение. Для иллюстрации рассмотрим следующую задачу. Найти такие поступления в таблице Income_o, каждое из которых превосходит любой из расходов в таблице Outcome_o. Решение. 1. SELECT Income_o.* FROM Outcome_o RIGHT JOIN Income_o ON Outcome_o.out >= Income_o.inc 2. WHERE Outcome_o.out IS NULL ; В вышеприведенном решении внешнее соединение выполняется по неравенству Outcome_o.out >= Income_o.inc, которому отвечают строки из таблицы Income_o, для которых приход не превышает расхода для каких-либо строк в таблице Outcome_o. Кроме того, во внешнем соединении (в данном случае в правом) будут присутствовать и строки из таблицы Income_o, для которых не нашлось ни одной строки в таблице Outcome_o, делающей истинным значение предиката. Это и есть строки, являющиеся решением нашей задачи. Чтобы их выбрать, используем тот факт, что отсутствующие значения столбцов из соединяемой таблицы (у нас левой) заполняются NULL-значениями. Соответствующий критерий помещаем в предложение WHERE. Разумеется, эту задачу можно решить и другими способами, например: max + подзапрос 1. SELECT Income_o.* FROM Income_o 2. WHERE Income_o.inc > ( SELECT MAX ( Outcome_o.out ) FROM Outcome_o ) ; all + подзапрос 1. SELECT Income_o.* FROM Income_o 2. WHERE Income_o.inc > ALL ( SELECT Outcome_o.out FROM Outcome_o ) ; О неявном преобразовании типов в SQL Server Помимо типов данных в реляционной теории вводится фундаментальное понятие домена, как множества допустимых значений, которое может иметь атрибут. Можно сказать, что домен представляет собой пару {базовый тип данных, предикат}. При этом значение принадлежит домену только в том случае, если оно имеет соответствующий тип и предикат, вычисленный на этом значении, есть ИСТИНА. Атрибуты (столбцы таблицы) определяются на домене, то есть помимо контроля типов СУБД при каждом изменении данных должна проверять также значение предиката. Изменение будет отклонено, если сохраняемое значение не удовлетворяет предикату домена. Домен играет еще одну важную роль, а именно, сравниваться могут только значения, принадлежащие одному домену. Рассмотрим в качестве примера таблицу PC, а именно, столбцы speed (тактовая частота процессора) и hd (объем жесткого диска). Оба эти столбца имеют тип integer (или smallint). Однако это совершенно разные характеристики. Достаточно сказать, что в предметной области для них используются разные единицы измерения — герцы и байты соответственно. Так вот, если мы определим эти столбцы на разных доменах, то сравнение значения одного столбца со значением другого станет недопустимым. Причем контролироваться это будет СУБД. По аналогии с категорной и ссылочной целостностью такой контроль можно было бы назвать доменной целостностью, если бы этот термин не был занят в SQL Server под проверку ограничения CHECK, наложенного на столбцы таблицы. А так определенная «доменная целостность» никак не ограничивает сравнения. Не лишним будет напомнить о важности поддержания целостности на стороне СУБД. Ограничения целостности, как правило, моделируют ограничения, реально существующие в предметной области. Поскольку эти ограничения не зависят от приложений, естественно их проверять (и писать) в том месте, которое является общим для всех приложений, а именно, на стороне СУБД. Это помимо прочего: избавляет приложения от необходимости встраивать (и дублировать!) в них необходимые проверки; дает более высокий уровень безопасности; ограничения, встроенные в приложения, легко обойти — достаточно обратиться к базе данных, минуя приложение; если ограничения предметной области изменятся, то соответствующие программные изменения нужно будет сделать в одном месте, а не во всех приложениях, работающих с базой данных. Возвращаясь к доменам, уместно заметить, что и стандарт языка SQL-92 не вкладывает в понятие домена смысла «сравнимости». То, что реализовано стандартом, не более чем возможность один раз записать ограничения, а затем неоднократно применять их при определении спецификаций столбцов, то есть дает возможность избежать дублирования кодов. В цепочке «Теория > Стандарт > Реализация» последовательно теряется строгость реляционной теории, в результате чего мы не можем вполне прозрачно взаимодействовать с реляционными СУБД разных производителей. Здесь мы хотим показать небольшой пример того, как следует обращаться с типами в SQL Server. Итак, реально мы имеем то, что сравниваться могут значения одного типа. Для преобразования типов стандарт предлагает функцию CAST. То есть в общем случае мы должны преобразовать сравниваемые значения к одному типу, а затем уже выполнять операцию сравнения (или присвоения). Что же произойдет, если мы переменной (или столбцу) одного типа просто присвоим значение другого типа? Рассмотрим простой пример кода на SQL Server и Sybase ASE.T-SQL (в примерах используется SQL Server 2000): 1. DECLARE @vc VARCHAR ( 10 ) , @mn MONEY, @ft FLOAT; 2. SELECT @vc = '499.99' ; 3. PRINT @vc; 4. SELECT @ft = @vc; 5. PRINT @ft; Здесь мы описывает три переменные соответственно строкового типа (VARCHAR), денежного типа (MONEY) и числа с плавающей точкой ( float ). Далее строковой переменной присваиваем константу соответствующего типа, а затем присваиваем переменной типа FLOAT значение строковой переменной. В результате получаем два одинаковых результата — 499.99 (оператор PRINT осуществляет сообщения вывод на консоль). То, что произошло, называется неявным преобразованием типов, то есть строковое значение — ‘499.99’ было автоматически приведено к типу FLOAT и присвоено переменной @ft. Добавим в конец кода еще пару строк: 1. SELECT @mn = @vc; 2. PRINT @mn; В результате получим два похожих сообщения об ошибке: Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query. и Implicit conversion from data type money to nvarchar is not allowed. Use the CONVERT function to run this query. в одном из которых говорится о том, что неявное преобразование типа VARCHAR к типу MONEY не допускается, а в другом — о недопустимости и обратного преобразования. Как и следовало ожидать, нам предлагается использовать явное преобразование с помощью функции CONVERT. Однако чтобы быть ближе к стандарту, воспользуемся функцией CAST: 1. SELECT @mn = CAST ( @vc AS MONEY ) ; 2. PRINT @mn; От первого сообщения об ошибке мы избавились. Напрашивается вывод о том, что второе сообщение дает оператор PRINT. Попробуем заглянуть в справку. В BOL об операторе PRINT говорится, что переменная, которая может использоваться в этом операторе, должна быть любого допустимого строкового типа (CHAR или VARCHAR) или же должна неявно приводиться к этому типу. Перепишем последнюю строку: 1. PRINT CAST ( @mn AS VARCHAR ) ; Все работает. Главный вывод, который мы отсюда извлекаем, заключается в том, что не все типы (даже если мы не видим особых причин тому) допускают неявное преобразование. В частности, не выполняется неявное приведение типа MONEY к типам CHAR, VARCHAR, NCHAR, NVARCHAR и наоборот. Примечание: Следует отметить, что неявное преобразование к типу MONEY уже поддерживается в SQL Server в версиях 2005 и выше. А теперь о причине, которая заставила нас написать так много слов. Из-за нашей неряшливости оказалось, что в основной и проверочной базе на сайте некоторые эквивалентные столбцы имели разные типы данных. Например, поле price в таблице РС имело тип FLOAT в одной базе и тип MONEY — в другой. В течение очень долгого времени это никак не влияло на работу сайта, но вдруг за последние несколько дней два наших участника решили использовать неявное преобразование типов в своих запросах с уже известным результатом. Мы решили не ограничиваться извинениями, а написать этот опус об особенностях реализации, надеясь, что это принесет больше пользы. Что же касается типов, то замеченное расхождение уже приведено в соответствие. Случайная выборка строк из таблицы в SQL Server В свое время мы использовали случайную выборку записей для формирования списка вопросов теста. Мы делали это на клиенте, используя функции базового языка, генерирующие псевдослучайное число в заданном диапазоне (например, функция RND в Visual Basic). Однако оказалось, что достаточно просто это можно организовать и на сервере. Причем сделать это можно как аналогичными средствами (с помощью функции RAND в T-SQL) так и на основе типа данных uniqueidentifier, который называется глобальным уникальным идентификатором и имеет вид: 1. xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, где каждое x является шестнадцатеричной цифрой в диапазоне 0–9 или a–f. Например, EEA8BF3A-7633-477A-B8C1-8C60DC9AD20C. Для решения нашей задачи важно то, что этот уникальный идентификатор может генерироваться автоматически при использовании функции NEWID. Просто напишите в QA (Query Analyzer) или в MS (Management Studio) 1. SELECT NEWID () ; или на сайте 1. SELECT TOP 1 NEWID () FROM PC; и вы все увидите. Причем, выполняя этот запрос снова и снова, вы будете получать все время разные значения. В этом и заключается уникальность этого идентификатора. Так вот, идея состоит в том, чтобы в запрос, возвращающий нужное число записей, добавить вычисляемый столбец NEWID, по которому должна выполняться сортировка. Поскольку генерируемые значения произвольны, то мы и получим случайную выборку. Итак, пусть нам нужно выбрать две произвольные модели из таблицы Product: Решение 8.8.1 1. SELECT model 2. FROM ( SELECT TOP 2 model, NEWID () [ id ] 3. FROM Product 4. ORDER BY [ id ] 5. ) x; Или более просто Решение 8.8.2 1. SELECT TOP 2 model 2. FROM Product 3. ORDER BY NEWID () ; Выполним этот запрос несколько раз: --1-- 1232 1433 --2-- 1276 1260 --3-- 1750 1298 Если хотите, можете продолжить. Теперь вернемся к «традиционному» способу. Функция RAND генерирует псевдослучайное число с плавающей точкой в диапазоне от 0 до 1. Термин «псевдослучайное» означает, что такое число вычисляется с помощью некоторого арифметического алгоритма. То есть при одинаковых начальных (входных) условиях получаемое число будет одним и тем же. Эти начальные условия могут быть заданы явно с помощью аргумента функции, которым может быть любое число типа tinyint, int или smallint, или неявно. В последнем случае аргумент опускается, в результате чего начальное значение будет выбрано SQL Server. Попробуем выполнить следующий запрос: 1. SELECT TOP 1 RAND () , RAND ( 350 ) 2. FROM Product; Выполним этот запрос в QA. У меня получилось: 0.0485421339242268 и 0.72009490018203537. Можно с уверенностью утверждать, что первое число у вас другое, однако второе может быть тем же самым, так как во втором случае мы задали начальное значение (350). Попробуем теперь применить функцию RAND для нашей задачи, а именно, для выдачи двух случайным образом выбранных моделей: 1. SELECT TOP 2 model 2. FROM Product 3. ORDER BY RAND ( model ) ; Получаем первые две модели в порядке возрастания их номеров. Вот где проявилась псевдослучайность. Посмотрим, что за случайные числа мы имеем: 1. SELECT model, RAND ( model ) rnd 2. FROM Product 3. ORDER BY rnd; Для краткости приведу их не все: Model rnd 1121 0.73446092102210758 1232 0.73652918083176777 1233 0.73654781380302592 1260 0.73705090402699736 1276 0.7373490315671285 1288 0.73757262722222694 1298 0.73775895693480897 … Я не знаю, какой алгоритм используется для вычисления случайного числа, однако могу утверждать, что функция RAND ведет себя монотонно в данном диапазоне номеров моделей. Потому у нас ничего и не получилось. В BOL приводится пример генерации последовательности случайных чисел с использованием системного времени, чтобы динамически менять начальное значение. Вот он: 1. SELECT RAND ( ( DATEPART ( mm, GETDATE ()) * 100000 ) 2. + ( DATEPART ( ss, GETDATE ()) * 1000 ) 3. + DATEPART ( ms, GETDATE ()) 4. ) ; Однако в таком виде запрос может быть задействован только в медленно выполняющихся пакетах, чтобы неоднократное выполнение запроса происходило не чаще, чем минимальная единица времени, применяемая в запросе (миллисекунды). Очевидно, что использование этой функции в выборке модели не будет отвечать этому условию. Однако если умножить аргумент на некоторый уникальный идентификатор, то мы можем добиться успеха (это решение было предложено Гершовичем В.И.): 1. SELECT model, RAND ( model* ( DATEPART ( mm, GETDATE ()) * 100000 ) 2. + ( DATEPART ( ss, GETDATE ()) * 1000 ) 3. + DATEPART ( ms, GETDATE ()) 4. ) 5. FROM Product 6. ORDER BY model; Однако тут есть одна проблема, связанная с тем, что аргументом функции RAND является целое число. Поэтому если мы превысим максимально допустимое значение для целого числа (для SQL Server оно составляет 2^31-1 или 2 147 483 647), то получим следующее сообщение об ошибке: Arithmetic overflow error converting expression to data type int. («Ошибка переполнения при преобразовании выражения к типу данных int».) В этом можно убедиться, выполнив вышеприведенный запрос на сайте. Ошибка возникает где-то на номерах моделей, превышающих 2000. В аналогичных случаях нужен еще нормирующий множитель, например, 1. CASE 2. WHEN model < 2000 3. THEN model 4. ELSE model/ 10 +model % 10 5. END Здесь добавление model % 10 (остаток от деления на 10) делается для того, чтобы не потерять значащие цифры; в противном случае мы можем получить одинаковые значения для моделей, номера которых отличаются на единицы. В окончательном виде решение будет выглядеть так (естественно, сортировку нужно делать по rnd, а не по model, которую мы оставили для наглядности результата). 1. SELECT model, 2. RAND ( CASE 3. WHEN model < 2000 4. THEN model 5. ELSE model/ 10 +model % 10 6. END * 7. ( DATEPART ( mm, GETDATE ()) * 100000 ) 8. + ( DATEPART ( ss, GETDATE ()) * 1000 ) 9. + DATEPART ( ms, GETDATE ()) 10. ) rnd 11. FROM Product 12. ORDER BY model; А теперь сравните результаты: model Rnd 1121 0.40138073102287292 1232 0.48719939613580043 1233 0.98346802618590112 1260 0.38272122312416984 1276 0.3230194099746666 1288 0.27824305011253919 1298 0.24092941689409972 Вывод. Для решения рассматриваемой задачи проще и надежней использовать функцию NEWID(), которая гарантирует уникальность значений. Однако эти значения не являются числовыми. Поэтому там, где нужно получить именно число, следует обратить внимание на функцию RAND(). Коррелирующие подзапросы Коррелирующие подзапросы позволяют иногда очень кратко написать запросы, которые могут выглядеть весьма громоздко при использовании других языковых средств. Напомним, что коррелирующий подзапрос — это подзапрос, который содержит ссылку на столбцы из включающего его запроса (назовем его основным). Таким образом, коррелирующий подзапрос будет выполняться для каждой строки основного запроса, так как значения столбцов основного запроса будут меняться. Пример 8.9.1 Требуется определить дату и рейсы каждого пассажира, совершенные им в свой последний полетный день. Иными словами, нужно определить максимальную дату полета для каждого пассажира и найти все его рейсы за эту дату. С определением максимальной даты нет никаких проблем: 1. SELECT id_psg, MAX ( date ) 2. FROM pass_in_trip 3. GROUP BY id_psg; Однако тут нет рейса. Если мы попытаемся включить рейс в список вывода: 1. SELECT id_psg, trip_no, MAX ( date ) 2. FROM pass_in_trip 3. GROUP BY id_psg; то получим сообщение об ошибке, так как номер рейса не используется в агрегатной функции и не входит список столбцов группировки. Если включить номер рейса в этот список: 1. SELECT id_psg, trip_no, MAX ( date ) 2. FROM pass_in_trip 3. GROUP BY id_psg, trip_no; мы получим последний полет пассажира каждым рейсом, которым он летал. Это совсем не та задача, которую мы пытаемся решить. Применение коррелирующего подзапроса 1. SELECT id_psg, trip_no, [ date ] 2. FROM pass_in_trip pt_1 3. WHERE [ date ] = ( SELECT MAX ([ date ]) 4. FROM pass_in_trip pt_2 5. WHERE pt_1.id_psg = pt_2.id_psg 6. ) ; дает то, что нужно: id_psg trip_no date 10 1187 2003-04-14 00:00:00.000 9 1182 2003-04-13 00:00:00.000 8 1187 2003-04-14 00:00:00.000 6 1123 2003-04-08 00:00:00.000 5 1145 2003-04-25 00:00:00.000 3 1145 2003-04-05 00:00:00.000 3 1123 2003-04-05 00:00:00.000 2 1124 2003-04-02 00:00:00.000 1 1100 2003-04-29 00:00:00.000 Здесь для каждого рейса проверяется, совершен ли он в последний полетный день данного пассажира. При этом если таких рейсов было несколько, мы получим их все. Очевидным недостатком приведенного решения как раз и является то, что подзапрос должен вычисляться для каждой строки основного запроса. Чтобы избежать этого, можно предложить альтернативное решение, использующее соединение таблицы Pass_in_trip с приведенным в самом начале подзапросом, который вычисляет максимальные даты по каждому пассажиру: 1. SELECT pt_1.id_psg, trip_no, [ date ] 2. FROM pass_in_trip pt_1 JOIN 3. ( SELECT id_psg, MAX ([ date ]) md 4. FROM pass_in_trip 5. GROUP BY id_psg 6. ) pt_2 ON pt_1.id_psg = pt_2.id_psg AND 7. [ date ] = md; Внимание: Напомним, что приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок «Без проверки» на странице с упражнениями на SELECT. Накопительные итоги Коррелирующие подзапросы можно использовать для вычисления накопительных итогов - задачи, часто возникающей на практике. В предположении некоторой упорядоченности строк накопительный итог для каждой строки представляет собой сумму значений некоторого числового столбца для этой строки и всех строк, расположенных выше данной. Другими словами, накопительный итог для первой строки в упорядоченном наборе будет равен значению в этой строке. Для любой другой строки накопительный итог будет равен сумме значения в этой строке и накопительного итога в предыдущей строке. Рассмотрим, например, такую задачу. Для пункта 2 по таблице Outcome_o получить на каждый день суммарный расход за этот день и все предыдущие дни. Вот запрос, который выводит информацию о расходах на пункте 2 в порядке возрастания даты 1. SELECT point, date, out 2. FROM Outcome_o o 3. WHERE point = 2 4. ORDER BY date; point date out 2 2001-03-22 00:00:00.000 1440.00 2 2001-03-29 00:00:00.000 7848.00 2 2001-04-02 00:00:00.000 2040.00 Фактически, чтобы решить задачу нам нужно добавить еще один столбец, содержащий накопительный итог (run_tot). В соответствии с темой, этот столбец будет представлять собой коррелирующий подзапрос, в котором для ТОГО ЖЕ пункта, что и у ТЕКУЩЕЙ строки включающего запроса, и для всех дат, меньших либо равных дате ТЕКУЩЕЙ строки включающего запроса, будет подсчитываться сумма значений столбца out: 1. SELECT point, date, out, 2. ( SELECT SUM ( out ) 3. FROM Outcome_o 4. WHERE point = o.point AND date <= o.date ) run_tot 5. FROM Outcome_o o 6. WHERE point = 2 7. ORDER BY point, date; point date out run_tot 2 2001-03-22 00:00:00.000 1440.00 1440.00 2 2001-03-29 00:00:00.000 7848.00 9288.00 2 2001-04-02 00:00:00.000 2040.00 11328.00 Собственно, использование пункта 2 продиктовано желанием уменьшить результирующую выборку. Чтобы получить накопительные итоги для каждого из пунктов, имеющихся в таблице Outcome_o, достаточно закомментировать строку 1. WHERE point = 2 Ну а чтобы получить "сквозной" накопительный итог для всей таблицы нужно, видимо, убрать условие на равенство пунктов: 1. point= o.point Однако при этом мы получим один и тот же накопительный итог для разных пунктов, работавших в один и тот же день. Вот подобный фрагмент из результирующей выборки, point date out run_tot 1 2001-03-29 00:00:00.000 2004.00 33599.00 2 2001-03-29 00:00:00.000 7848.00 33599.00 Но это не проблема, если понять, что же мы хотим в итоге получить. Если нас интересует накопление расхода по дням, то нужно из выборки вообще исключить пункт и суммировать расходы по дням: 1. SELECT date, SUM ( out ) out, 2. ( SELECT SUM ( out ) 3. FROM Outcome_o 4. WHERE date <= o.date ) run_tot 5. FROM Outcome_o o 6. GROUP BY date 7. ORDER BY date; В противном случае, нам нужно указать порядок, в котором к накопительному итогу будут добавляться расходы пунктов в случае, когда у нескольких пунктов совпадает дата. Например, упорядочим их по возрастанию номеров: 1. SELECT point, date, out, 2. ( SELECT SUM ( out ) 3. FROM Outcome_o 4. WHERE date < o.date OR ( date = o.date AND point <= o.point )) run_tot 5. FROM Outcome_o o 6. ORDER BY date, point; Расширение поддержки оконных функций в SQL Server 2012 позволяет решить задачу о накопительных итогах совсем просто. Применительно к нашей задаче речь идет о следующих появившихся возможностях: 1. Использование сортировки в предложении OVER при применении агрегатных функций. 2. Спецификация диапазона, к значениям которого применяется агрегатная функция. При этом диапазон может быть как ограниченным, так и неограниченным, скажем, от текущей строки до конца или начала отсортированного набора. Т.е. мы можем получить накопительный итог, упорядочив данные по дате и подсчитав сумму от текущей строки и (неограниченно) выше, причем сделать это с помощью одной функции! Задачу о накопительных итогах для пункта 2, которая рассматривалась на предыдущей странице , теперь мы можем решить так: 1. SELECT point, date, out, 2. SUM ( out ) OVER ( PARTITION BY point 3. ORDER BY date -- сортировка по дате 4. RANGE -- диапазон 5. UNBOUNDED -- неограниченный 6. PRECEDING -- от текущей строки и выше 7. ) 8. FROM Outcome_o o 9. WHERE point = 2 10. ORDER BY point, date; Для получения накопительных итогов по каждому пункту отдельно уберем из предыдущего запроса условие отбора по пункту: 1. SELECT point, date, out, 2. SUM ( out ) OVER ( PARTITION BY point ORDER BY date RANGE UNBOUNDED PRECEDING ) 3. FROM Outcome_o o 4. ORDER BY point, date; Представленные здесь решения будут работать в PostgreSQL и Oracle. Что касается MySQL, то там поддержка оконных функций реализована в версии 8.0. Если нам потребуется подсчитать накопительный итог с учетом не всех предшествующих строк, а, скажем, двух. В этом случае мы можем использовать следующий синтаксис: 1. SELECT point,date, out, 2. SUM ( out ) OVER ( ORDER BY date,point ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) 3. FROM Outcome_o o 4. ORDER BY date,point; Суммирование происходит в окне, которое задается предложением 1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW Здесь задается диапазон строк (rows) между (between) текущей строкой (current row) и двумя строками выше (2 preceding). В этом примере рассматриваются все строки таблицы, упорядоченные по date, point (сортировка по point устраняет неоднозначность, поскольку несколько пунктов могут иметь отчетность в один и тот же день). Преобразование даты в строку Речь пойдет о форматировании даты. Форматирование даты обычно используется для представления даты/времени в отчетах, подготовленных для печати. Как правило, такое форматирование выполняется средствами разработки отчетов. Однако и на уровне СУБД есть подобные возможности. Мы не будем здесь обсуждать вопрос о том, где лучше выполнять данное форматирование. Отметим лишь, что в ряде задач на сайте sql-ex.ru требуется представить результат выполнения запроса в том или ином конкретном формате. SQL Server Для форматирования даты в SQL Server используется функция CONVERT Будем рассматривать в качестве примера дату, возвращаемую следующим запросом: 1. SELECT b_datetime FROM utb WHERE b_datetime= '2002-06- 01T01:13:39.000' ; b_datetime 2002-06-01 01:13:39.000 Например, чтобы получить из этого представления временной метки только дату в привычном нам формате "dd-mm-yyyy", достаточно написать 1. SELECT CONVERT ( varchar, b_datetime, 105 ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 01-06-2002 Для вывода в формате "mm-dd-yyyy" можно в функции CONVERT поменять параметр стиля на 110: 1. SELECT CONVERT ( varchar,b_datetime, 110 ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 06-01-2002 Для вывода одной лишь компоненты времени без миллисекунд используется стиль 108: 1. SELECT CONVERT ( varchar, b_datetime, 108 ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 01:13:39 MySQL В MySQL для форматирования даты используется функция DATE_FORMAT, в которой вторым параметром является маска, в соответствии с которой форматируется первый параметр типа даты/времени. Рассмотренные выше примеры для MYSQL можно переписать следующим образом: 1. SELECT date_format ( b_datetime, '%d-%m-%Y' ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 01-06-2002 1. SELECT date_format ( b_datetime, '%m-%d-%Y' ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 06-01-2002 1. SELECT date_format ( b_datetime, '%H:%i:%s' ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 01:13:39 Заметим, что "H" используется для представления 24-часового формата времени, а "h" - для 12-часового формата. PostgreSQL & Oracle Эти СУБД используют для форматирования функцию TO_CHAR с интуитивно понятной маской. Наши примеры для PostgreSQL будут выглядеть так: 1. SELECT to_char ( b_datetime, 'dd-mm-yyyy' ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 01-06-2002 1. SELECT to_char ( b_datetime, 'mm-dd-yyyy' ) 2. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 06-01-2002 1. --12-часовой формат 2. SELECT to_char ( b_datetime, 'hh12:mi:ss' ) 3. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 4. --24-часовой формат 5. SELECT to_char ( b_datetime, 'hh24:mi:ss' ) 6. FROM utb WHERE b_datetime= '2002-06-01T01:13:39.000' ; 01:13:39 Для Oracle принципиальных отличий нет. Чтобы примеры, приведенные для PostgreSQL, работали в Oracle, нам нужно знать, что строковое представление даты нужно явно преобразовывать к соответствующему темпоральному типу (при сравнениях с датой). Кроме того, не поддерживается стандартное представление временной метки с разделителем "T" между датой и временем. Например, последний пример в Oracle будет иметь вид: 1. SELECT to_char ( b_datetime, 'hh24:mi:ss' ) 2. FROM utb WHERE b_datetime=timestamp '2002-06-01 01:13:39.000' ; Новое в стандарте и реализациях языка SQL Оператор MERGE Если головной корабль из таблицы Outcomes отсутствует в таблице Ships, добавить его в Ships, приняв имя класса, совпадающим с именем корабля, и год спуска на воду, равным году самого раннего сражения, в котором участвовал корабль. Если же корабль присутствует в Ships, но дата спуска на воду его неизвестна, установить его равным году самого раннего сражения, в котором участвовал корабль. Эта задача подразумевает выполнение двух разных операторов (INSERT и UPDATE) на одной таблице (Ships) в зависимости от наличия/отсутствия связанных записей в другой таблице (Outcomes). Для решения подобных задач стандарт предоставляет оператор MERGE. Рассмотрим его использование на примере решения данной задачи в SQL Server. Для начала напишем запрос, который вернет нам головные корабли из таблицы Outcomes, т.е. корабли, у которых имя класса совпадает с именем корабля: 1. SELECT ship, ship class FROM Outcomes O JOIN Classes C ON C.class=O.ship; ship class Bismarck Bismarck Tennessee Tennessee Теперь добавим соединение с таблицей Battles и выполним группировку, чтобы найти минимальный год сражений каждого такого корабля: 1. SELECT year ( MIN ( date )) min_year, ship, ship class 2. FROM outcomes O JOIN battles B ON O.battle= B.name 3. JOIN Classes C ON C.class=O.ship GROUP BY ship; min_year ship class 1941 Bismarck Bismarck 1944 Tennessee Tennessee Исходные данные готовы. Теперь мы можем перейти к написанию оператора MERGE. 1. MERGE Ships AS target -- таблица, которая будет меняться 2. USING ( SELECT year ( MIN ( date )) , ship, ship 3. FROM outcomes O JOIN battles B ON O.battle= B.name 4. JOIN Classes C ON C.class=O.ship GROUP BY ship 5. ) AS source ( min_year,ship, class ) -- источник данных, который мы рассмотрели выше 6. ON ( target.name = source.ship ) -- условие связи между источником и изменяемой таблицей 7. WHEN MATCHED AND target.launched IS NULL -- если головной корабль есть в таблице Ships 8. -- с неизвестным годом 9. THEN UPDATE SET target.launched = source.min_year -- обновление 10. WHEN NOT MATCHED -- если головного корабля нет в таблице Ships 11. THEN INSERT VALUES ( source.ship, source.class, source.min_year ) -- вставка 12. OUTPUT $action, inserted.*, deleted.*; -- можно вывести измененные строки $action name class launched name class launched INSERT Bismarck Bismarck 1941 NULL NULL NULL Предложение OUTPUT позволяет вывести измененные строки. Автоматически создаваемые рабочие таблицы inserted и deleted имеют тот же смысл, что и при использовании в триггерах, т.е. inserted содержит строки, которые были добавлены в изменяемую таблицу, а deleted - удаленные из нее строки. Поскольку удаления в нашем запросе не было, то соответствующие столбцы имеют значения NULL. Столбец $action содержит название выполненной операции. В нашем случае была выполнена только вставка, поскольку корабль Tennessee содержится в таблице Ships с известным годом спуска на воду: 1. SELECT * FROM Ships WHERE name= 'Tennessee' ; name class launched Tennessee Tennessee 1920 Инструкция MERGE может иметь не больше двух предложений WHEN MATCHED. Если указаны два предложения, то первое предложение должно сопровождаться дополнительным условием (что имеет место в нашем случае - AND target.launched IS NULL). Для любой строки второе предложение WHEN MATCHED применяется только в том случае, если не применяется первое. Если имеются два предложения WHEN MATCHED, одно должно указывать действие UPDATE, а другое — DELETE. Т.е. если мы добавим в оператор предложение 1. WHEN MATCHED THEN DELETE то удалим корабль Tennessee: $acti on name class launch ed name class launc hed INSER T Bismar ck Bismar ck 1941 NULL NULL NULL DELET E NULL NULL NULL Tennes see Tennes see 1920 Инструкцию MERGE нельзя использовать для обновления одной строки более одного раза, а также для обновления и удаления одной и той же строки. Предложение WHEN NOT MATCHED [BY TARGET] THEN INSERT используется для вставки строк из источника, не совпадающих со строками в изменяемой таблице согласно условию связи. В нашем примере такой строкой является строка, относящаяся к кораблю Bismarck. Инструкция MERGE может иметь только одно предложение WHEN NOT MATCHED. Наконец, оператор MERGE может включать предложение WHEN NOT MATCHED BY SOURCE THEN. Оно воздействует на те строки изменяемой таблицы, для которых нет соответствия в таблице-источнике. Например, если бы мы хотели удалить из таблицы Ships головные корабли, не принимавшие участие в сражениях, то добавили бы следующее предложение: 1. WHEN NOT MATCHED BY SOURCE AND target.name=target.class THEN DELETE Результат: $action name class launched name class launched DELETE NULL NULL NULL Iowa Iowa 1943 DELETE NULL NULL NULL Kongo Kongo 1913 DELETE NULL NULL NULL North Carolina North Carolina 1941 DELETE NULL NULL NULL Renown Renown 1916 DELETE NULL NULL NULL Revenge Revenge 1916 DELETE NULL NULL NULL Yamato Yamato 1941 INSERT Bismarck Bismarck 1941 NULL NULL NULL При помощи этого предложения можно удалять или обновлять строки. Инструкция MERGE может иметь не более двух предложений WHEN NOT MATCHED BY SOURCE. Если указаны два предложения, то первое предложение должно иметь дополнительное условие (как в нашем примере). Для любой выбранной строки второе предложение WHEN NOT MATCHED BY SOURCE применяется только в тех случаях, если не применяется первое. Кроме того, если имеется два предложения WHEN NOT MATCHED BY SOURCE, то одно должно выполнять UPDATE, а другое — DELETE. Функции ранжирования Реляционная модель исходит из того факта, что строки в таблице не имеют порядка, являющегося прямым следствием теоретико-множественного подхода. Поэтому наивными выглядят вопросы новичков, спрашивающих: "А как мне получить последнюю добавленную в таблицу строку?" Ответом на вопрос будет "никак", если в таблице не предусмотрен столбец, содержащий дату вставки строки, или не используется последовательная нумерация строк, реализуемая во многих СУБД с помощью столбца с автоинкрементируемым значением. Тогда можно выбрать строку с максимальным значением даты или счетчика. Вопрос о последней строке имеет смысл только в аспекте выдачи результата выполнения запроса, при этом предполагается некоторая сортировка, которая задается с помощью предложения ORDER BY в операторе SELECT. Если никакая сортировка не задана (предложение ORDER BY отсутствует), то полагаться на то, что порядок вывода строк, полученных при выполнении запроса сегодня, останется таким же и завтра, нельзя, т.к. этот порядок зависит от плана, который выбирает оптимизатор запросов для их выполнения. А план может меняться, и зависит это от многих причин, которые мы здесь опустим. Теоретически каждая строка запроса обрабатывается независимо от других строк. Однако на практике часто требуется при обработке строки соотносить ее с предыдущими или последующими строками (например, для получения нарастающих итогов), выделять группы строк, обрабатываемые независимо от других и т.д. В ответ на потребности практики в ряде СУБД в языке SQL появились соответствующие конструкции, в частности, функции ранжирования и оконные (аналитические) функции, которые де-юре были зафиксированы в стандарте SQL:2003. В SQL Server ранжирующие функции появились в версии 2005. Функция ROW_NUMBER Функция ROW_NUMBER, как следует из ее названия, нумерует строки, возвращаемые запросом. С ее помощью можно выполнить более сложное упорядочивание строк в отчете, чем то, которое дает предложение ORDER BY в рамках Стандарта SQL-92. До появления этой функции для нумерации строк, возвращаемых запросом, приходилось использовать довольно сложный интуитивно непонятный алгоритм, изложенный в параграфе Единственным достоинством данного алгоритма является то, что он будет работать практически на всех СУБД, поддерживающих стандарт SQL-92. Примечание: Естественно, можно выполнить нумерацию средствами процедурных языков, используя при этом курсоры и/или временные таблицы. Но мы здесь говорим о "чистом" SQL. Используя функцию ROW_NUMBER можно: задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора; создать "несквозную" нумерацию, т.е. выделить группы из общего множества строк и пронумеровать их отдельно для каждой группы; использовать одновмеренно несколько способов нумерации, поскольку, фактически, нумерация не зависит от сортировки строк запроса. Проще всего возможности функции ROW_NUMBER показать на простых примерах, к чему мы и переходим. Пример 1. Пронумеровать все рейсы из таблицы Trip в порядке возрастания их номеров. Выполнить сортировку по {id_comp, trip_no}. Решение 1. SELECT row_number () over ( ORDER BY trip_no ) num, 2. trip_no, id_comp 3. FROM trip 4. WHERE ID_comp < 3 5. ORDER BY id_comp, trip_no; Предложение OVER, с которым используется функция ROW_NUMBER задает порядок нумерации строк. При этом используется дополнительное предложение ORDER BY, которое не имеет отношения к порядку вывода строк запроса. Если вы посмотрите на результат, то заметите, что порядок строк в результирующем наборе и порядок нумерации не совпадают: num trip_no id_comp 3 1181 1 4 1182 1 5 1187 1 6 1188 1 7 1195 1 8 1196 1 1 1145 2 2 1146 2 Примечание: Условие отбора id_comp<3 использовано лишь с целью уменьшения размера выборки. Конечно, мы можем потребовать выдачу в порядке нумерации, переписав последнюю строку в виде 1. ORDER BY trip_no (или, что то же самое, order by num ). Или, наоборот, пронумеровать строки в порядке заданной сортировки: 1. SELECT row_number () over ( ORDER BY id_comp, trip_no ) num, 2. trip_no, id_comp 3. FROM trip 4. WHERE ID_comp< 3 5. ORDER BY id_comp, trip_no; num trip_no id_comp 1 1181 1 2 1182 1 3 1187 1 4 1188 1 5 1195 1 6 1196 1 7 1145 2 8 1146 2 А если требуется пронумеровать рейсы для каждой компании отдельно? Для этого нам потребуется еще одна конструкция в предложении OVER - PARTITION BY. Конструкция PARTITION BY задает группы строк, для которых выполняется независимая нумерация. Группа определяется равенством значений в списке столбцов, перечисленных в этой конструкции, у строк, составляющих группу. Пример 2. Пронумеровать рейсы каждой компании отдельно в порядке возрастания номеров рейсов. 1. SELECT row_number () over ( partition BY id_comp ORDER BY id_comp,trip_no ) num, 2. trip_no, id_comp 3. FROM trip 4. WHERE ID_comp < 3 5. ORDER BY id_comp, trip_no; PARTITION BY id_comp означает, что рейсы каждой компании образуют группу, для которой и выполняется независимая нумерация. В результате получим: num trip_no id_comp 1 1181 1 2 1182 1 3 1187 1 4 1188 1 5 1195 1 6 1196 1 1 1145 2 2 1146 2 Отсутствие конструкции PARTITION BY, как это было в первом примере, означает, что все строки результирующего набора образуют одну единственную группу. В MySQL ранжирующих/оконных функций не было до версии 8.0, однако была возможность использовать переменные непосредственно в запросе SQL. В частности, с помощью переменных можно решить задачу нумерации строк запроса. Продемонстрируем это на примере, который рассматривался на предыдущей странице 1. SELECT @i:=@i +1 num, 2. trip_no, id_comp 3. FROM Trip, ( SELECT @i:= 0 ) X 4. WHERE ID_comp < 3 5. ORDER BY id_comp, trip_no; num id_comp trip_no 1 1 1181 2 1 1182 3 1 1187 4 1 1188 5 1 1195 6 1 1196 7 2 1145 8 2 1146 В третьей строке запроса выполняется инициализация переменной и присваивается ей начальное значение. В итоге каждая строка таблицы Trip будет соединяться со строкой из одного столбца, содержащего 0 (просто декартово произведение). В первой строке запроса значение переменной инкрементируется на 1, что происходит при вычислении каждой следующей строки в порядке, заданном предложением ORDER BY. В итоге мы получаем нумерацию строк в порядке сортировки. Если вы опустите инициализацию переменной, то можете получить правильный результат. Но это не гарантировано, в чем можно убедиться, повторно выполнив этот же запрос в текущей сессии соединения с базой данных. Вы должны получить продолжение нумерации с максимального значения переменной @i, достигнутого на предыдущем запуске скрипта. Мы также можем перенумеровать строки для каждой компании отдельно, т.е. сымитировав поведение |