лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
1 1 1 2 … ... 1 10 2 1 2 2 … ... 2 10 … ... Теперь осталось только вычислить сами значения. Будем считать, что число в первом столбце представляет собой десятки –1, а второй — единицы. Тогда вместо SELECT * FROM… в нашем запросе напишем: 1. SELECT 10 * ( a -1 ) +b 2. FROM … что и даст требуемый результат. А почему бы не взять три таблицы (подзапроса)? Чем больше размер генерируемой последовательности, тем больше таблиц следует взять, чтобы получить более короткий запрос. Аналогично рассуждая и, исходя из того, что 5 * 5 * 5 = 125, получим: 1. SELECT 5 * 5 * ( a -1 ) +5 * ( b -1 ) + c AS num 2. FROM ( SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 3. UNION ALL SELECT 4 UNION ALL SELECT 5 4. ) x CROSS JOIN 5. ( SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 6. UNION ALL SELECT 4 UNION ALL SELECT 5 7. ) y CROSS JOIN 8. ( SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 9. UNION ALL SELECT 4 UNION ALL SELECT 5 10. ) z 11. WHERE 5 * 5 * ( a -1 ) +5 * ( b -1 ) + c <= 100 12. ORDER BY 1 ; Условие 1. WHERE 5 * 5 * ( a -1 ) +5 * ( b -1 ) + c <= 100 использовано для того, чтобы ограничить последовательность значением 100, а не 125. Рассмотрим «практический» пример. Пусть требуется получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. Идея такова: находим максимальный номер модели и далее, используя генерацию последовательности, 100 последующих значений с шагом 1. 1. SELECT ( SELECT MAX ( model ) 2. FROM Product 3. ) + 5 * 5 * ( a -1 ) +5 * ( b -1 ) + c AS num 4. FROM ( SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 5. UNION ALL SELECT 4 UNION ALL SELECT 5 6. ) x CROSS JOIN 7. ( SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 8. UNION ALL SELECT 4 UNION ALL SELECT 5 9. ) y CROSS JOIN 10. ( SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 11. UNION ALL SELECT 4 UNION ALL SELECT 5 12. ) z 13. WHERE 5 * 5 * ( a -1 ) +5 * ( b -1 ) + c <= 100 14. ORDER BY 1 ; Результат выполнения этого запроса мы не будем здесь приводить из экономии места. Проверьте самостоятельно, щелкнув по кнопке "Выполнить". Если ваш сервер поддерживает CTE, то получение числовой последовательности существенно упрощается. Вы можете использовать Консоль учебника, чтобы решить рассмотренную здесь задачу этим способом. За примерами вы можете обратиться к главе, посвященной рекурсивным CTE , и где рассматривается несколько числовых последовательностей Вероятно, ввиду часто возникающей потребности в числовых последовательностях, в PostgreSQL имеется функция, которая возвращает такую последовательность: 1. generate_series ( start, stop [ , step ]) Здесь start - начальное значение последовательности, stop - конечное значение последовательности, step - шаг последовательности (по умолчанию равен 1). Применение данной функции проще показать на примерах. Начнем с задачи, которая рассматривалась на предыдущей странице : Получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. Решение для PostgreSQL можно записать очень лаконично: 1. SELECT CAST ( MAX ( model ) AS INT ) + generate_series ( 1 , 100 ) AS num FROM Product; Преобразование типа здесь необходимо, поскольку номер модели (model) имеет тип VARCHAR. Следующий пример - это вывод алфавита, который мы получали с помощью SQL-рекурсии . Применим тот же алгоритм, а именно, к коду первой буквы будем последовательно добавлять единицы, после чего преобразуем полученные коды к символам: 1. SELECT CHR ( ASCII ( 'A' ) + generate_series ( 0 , 25 )) AS letter ORDER BY 1 ; Наконец, рассмотрим довольно часто возникающую необходимость получения последовательности дат. В связи с этим отметим, что третий параметр (step) может быть не только типа INT, но и типа INTERVAL. Последнее позволит нам непосредственно работать с последовательностями дат, минуя преобразование последовательности чисел к последовательности дат. Итак, Вывести последовательность дат между датами первого и последнего полета пассажира с id_psg=5. 1. SELECT generate_series ( MIN ( date ) , MAX ( date ) , '1 day' ) 2. FROM pass_in_trip WHERE id_psg = 5 ; Поскольку пока на внутренних страницах учебника есть возможность выполнять скрипты только под SQL Server, вы можете для выполнения приведенных здесь запросов воспользоваться консолью , выбрав PostgreSQL в списке поддерживаемых СУБД. Нумерация Обычно необходимость нумерации строк возникает при формировании отчетов. В этом случае нумерацию строк, возвращаемых запросом, обычно реализуют на клиенте. Например, не составляет особого труда перенумеровать строки отчета, подготовленного в MS Access. Однако иногда это необходимо сделать в самом запросе. Этот случай мы сейчас и рассмотрим. Нумерация строк в соответствии с порядком, заданном значениями первичного ключа Естественно, нумероваться строки должны в соответствии с некоторым порядком. Пусть этот порядок задается столбцом первичного ключа, то есть в порядке возрастания (или убывания) значений в этом единственном столбце. Для определенности предположим, что нам нужно перенумеровать модели в таблице Product, где номер модели как раз является первичным ключом. Существенным здесь является то, значения первичного ключа не содержат дубликатов и NULL-значений, в результате чего имеется принципиальная возможность установить однозначное соответствие между номером модели и номером строки в заданном порядке сортировки моделей. Рассмотрим сначала следующий запрос: 1. SELECT P1.model, P2.model 2. FROM Product P1 JOIN 3. Product P2 ON P1.model <= P2.model; Здесь выполняется соединение двух идентичных таблиц по неравенству P1.model <= P2.model, в результате чего каждая модель из второй таблицы (P2.model) будет соединяться только с теми моделями из первой таблицы (P1.model), номера которых меньше или равны номеру этой модели. В результате получим, например, что модель с минимальным номером (1121) будет присутствовать во втором столбце результирующего набора только один раз, так как она меньше или равна только самой себе. На другом конце будет находиться модель с максимальным номером, так как любая модель будет меньше или равна ей. Следовательно, модель с максимальным номером будет сочетаться с каждой моделью, и число таких сочетаний будет равно общему числу моделей в таблице Product. Из сказанного выше ясно, что это количество раз, которое каждая из моделей встречается во втором столбце результирующего набора как раз и будет порядковым номером модели при сортировке моделей по возрастанию. Таким образом, чтобы решить нашу задачу нумерации достаточно пересчитать модели в правом столбце, что нетрудно сделать при помощи группировки и использования агрегатной функции COUNT: Решение 8.2.1 1. SELECT COUNT ( * ) no, P2.model 2. FROM Product P1 JOIN 3. Product P2 ON P1.model <= P2.model 4. GROUP BY P2.model; Не будем экономить место и представим результат выполнения этого запроса: no model 1 1121 2 1232 3 1233 4 1260 5 1276 6 1288 7 1298 8 1321 9 1401 10 1408 11 1433 12 1434 13 1750 14 1752 15 2112 16 2113 Для нумерации в обратном порядке достаточно поменять знак неравенства на противоположный. Если ваша СУБД поддерживает ранжирующие функции , то пронумеровать строки можно совсем просто: 1. SELECT ROW_NUMBER () OVER ( ORDER BY model ) no, model 2. FROM Product; Несколько усложним задачу, и попытаемся пронумеровать модели каждого производителя отдельно. Воспользуемся предыдущим решением, и внесем в него следующие изменения: 1. Добавим в условие соединения равенство производителей, чтобы выделить модели каждого производителя в отдельную группу. 1. SELECT COUNT ( * ) no, P2.model 2. FROM Product P1 JOIN 3. Product P2 ON P1.maker =P2.maker AND P1.model <= P2.model 4. GROUP BY P2.model 5. ORDER BY P2.model; В принципе, это и все. Правда, результат не отличается наглядностью. 2. Добавим в вывод производителя, при этом все равно из какой таблицы мы его возьмем в силу равенства. Однако тогда необходимо добавить производителя в столбцы группировки (MySQL не в счет): 1. SELECT COUNT ( * ) no, P1.maker, P2.model 2. FROM Product P1 JOIN 3. Product P2 ON P1.maker =P2.maker AND P1.model <= P2.model 4. GROUP BY P1.maker, P2.model 5. ORDER BY P2.model; 3. Ну и, наконец, добавим сортировку для наглядности результата. Столбец maker должен быть первым столбцом сортировки, чтобы каждая группа выводилась отдельно. 1. SELECT COUNT ( * ) no, P1.maker, P2.model 2. FROM Product P1 JOIN 3. Product P2 ON P1.maker =P2.maker AND P1.model <= P2.model 4. GROUP BY P1.maker, P2.model 5. ORDER BY P1.maker, P2.model; no maker model 1 A 1232 2 A 1233 3 A 1276 4 A 1298 5 A 1401 6 A 1408 7 A 1752 1 B 1121 2 B 1750 1 C 1321 1 D 1288 2 D 1433 1 E 1260 2 E 1434 3 E 2112 4 E 2113 Я надеюсь, что выполнить раздельную нумерацию моделей по типам продукции вам теперь не составит труда. Сделайте это в качестве самостоятельного задания. Ранжирующие функции , естественно, упрощают запрос: 1. SELECT ROW_NUMBER () OVER ( PARTITION BY maker ORDER BY model ) no, maker, model 2. FROM Product 3. ORDER BY maker, model; Нумерация строк при наличии дубликатов в результирующем столбце Согласно реляционной теории в таблице не может быть одинаковых строк. И хотя реализации допускают построение таблиц, не имеющих первичного ключа, и, как следствие, допускающих наличие одинаковых строк, следует, на наш взгляд, отнести эту ситуацию к ошибкам в проектировании. Кроме того, таблица, не имеющая первичного ключа или уникального индекса, не является обновляемой. Последнее заключение вполне естественно, так как система не имеет информации о том, какой из дубликатов предпочесть. Поэтому, говоря о дубликатах, мы имеем в виду дубликаты в результирующем наборе, появление которых может быть обусловлено тем, что первичный ключ весь или частично (в случае составного ключа) отсутствует в результирующем наборе. Чтобы пояснить сказанное, рассмотрим следующий запрос 1. SELECT id_psg 2. FROM pass_in_trip; который вернет номера пассажиров, совершавших полеты, зафиксированные в базе данных. Поскольку один и тот же пассажир может совершить несколько рейсов, мы получаем здесь дубликаты. Однако ни один пассажир не может в один и тот же день более одного раза полететь одним и тем же рейсом, что регламентируется соответствующим первичным ключом — {trip_no, date, id_psg}. Итак, нам нужно перенумеровать пассажиров, которые могут повторяться. Зададимся для начала порядком, в котором их нужно перенумеровать. Пусть этот порядок соответствует сортировке по трем полям — дате полета, идентификатору пассажира и номеру рейса (по возрастанию). Чтобы свести задачу к ранее рассмотренной (а это возможно, так как три перечисленных поля представляют собой первичный ключ), сконструируем столбец, который объединял бы информацию из перечисленных полей. Поскольку поля имеют разные типы данных, приведем их к единому символьному представлению и выполним конкатенацию. При этом нам нужно определиться с количеством символов. Поскольку в представлении даты вылета отсутствует время, ограничимся 11 символами. Номер рейса везде представлен четырехсимвольным числом. Остается идентификатор пассажира. В соответствии с имеющейся базой данных ограничимся двумя символами, что не принижает общности подхода. Однако для правильности сортировки нужно «односимвольных» пассажиров записывать с лидирующим нулем — 01, 09 и т. д. Иначе пассажир с номером 10 будет предшествовать, скажем, пассажиру с идентификационным номером 2. Выполним соответствующие преобразования: Преобразования 8.2.2 1. Дата: CAST ( date AS CHAR ( 11 )) 2. Номер рейса: CAST ( trip_no AS CHAR ( 4 )) 3. Идентификатор пассажира: RIGHT ( '00' + CAST ( id_psg AS VARCHAR ( 2 )) , 2 ) В последнем преобразовании идентификатора пассажира мы использовали нестандартную функцию RIGHT (SQL Server), которая извлекает из строки указанное количество символов справа. Можно было бы применить функцию SUBSTRING, однако так получается короче и, кроме того, наверняка в других коммерческих продуктах имеются аналогичные «расширения» стандарта. Соединяя эти выражения в указанном порядке, мы получим уникальный столбец, который и будет служить для нумерации пассажиров в соответствии с возрастанием (убыванием) значений в этом столбце. Используя этот столбец, мы можем воспользоваться решением 8.2.1 Окончательно получим: 1. SELECT COUNT ( * ) num, P2.id_psg 2. FROM ( SELECT *, CAST ( date AS CHAR ( 11 )) + 3. RIGHT ( '00' + CAST ( id_psg AS VARCHAR ( 2 )) , 2 ) + 4. CAST ( trip_no AS CHAR ( 4 )) dit 5. FROM Pass_in_trip 6. ) P1 JOIN 7. ( SELECT *, CAST ( date AS CHAR ( 11 )) + 8. RIGHT ( '00' + CAST ( id_psg AS VARCHAR ( 2 )) , 2 ) + 9. CAST ( trip_no AS CHAR ( 4 )) dit 10. FROM pass_in_trip 11. ) P2 ON P1.dit <= P2.dit 12. GROUP BY P2.dit, P2.id_psg 13. ORDER BY 1 ; Для нумерации в другом порядке достаточно конкатенировать преобразования 8.2.2 в другой последовательности. Например, чтобы пронумеровать пассажиров в порядке их идентификационных номеров, первым слагаемым должно быть: 1. RIGHT ( '00' + CAST ( id_psg AS VARCHAR ( 2 )) , 2 ) В этом примере еще более наглядно, чем для решения 8.2.1 , проявляются достоинства ранжирующих функций . Вот как просто и понятно можно переписать решение нашей задачи с их использованием: 1. SELECT ROW_NUMBER () OVER ( ORDER BY date, id_psg, trip_no ) num, id_psg 2. FROM Pass_in_trip 3. ORDER BY num; Об использовани и оператора CASE В главе 5 ( пункт 5.10 ) описан синтаксис этого оператора и приведены примеры его использования. Однако эти примеры демонстрируют применение CASE только в предложении SELECT. Действительно, это место наиболее частого использования оператора, однако не единственное. Мы можем применять CASE практически везде, где ожидается скалярное значение. Здесь мы приведем несколько примеров его применения в других предложениях оператора SELECT. Предложение WHERE Предложение WHERE ограничивает выходной набор теми строками, которые удовлетворяют предикату в этом предложении. Предположим, что у нас имеется следующее соответствие между объемом памяти ПК и типом используемой операционной системы (естественно, условно): RAM < 64 W95 RAM >=64 и < 128 W98 RAM >= 128 W2k Если мы захотим отобрать компьютеры по типу ОС (заметим, что такого поля нет в таблице PC), то можно написать следующий оператор: 1. SELECT * 2. FROM PC 3. WHERE CASE 4. WHEN ram < 64 5. THEN 'W95' 6. WHEN ram < 128 7. THEN 'W98' 8. ELSE 'W2k' 9. END = 'W98' ; Здесь мы выбираем модели, подходящие для операционной системы W98. Ниже приведен результат выполнения этого запроса. code model speed |