| ← Сентябрь 2009 → | ||||||
|
1
|
2
|
3
|
4
|
6
|
||
|---|---|---|---|---|---|---|
|
7
|
8
|
9
|
10
|
11
|
13
|
|
|
14
|
15
|
16
|
17
|
18
|
20
|
|
|
21
|
22
|
23
|
24
|
25
|
27
|
|
|
28
|
29
|
30
|
||||
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 258
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 258 от 05 сентября 2009 г.Новым посетителям сайта
Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения
состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 48-ми задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§
Как заметил Arni, сбилась проверка задачи 57, в результате чего проходили не вполне верные решения. Исправил. § На sql-tutorial.ru появилась консоль, с помощью которой можно адресовать запросы не только к SQL Server, но и к MySQL. На очереди PostgreSQL. § Продолжалась работа над сервисом Моделирование. Спасибо за ваши замечания, которые помогли нам устранить ряд багов. §
Изменения среди лидеров (решенные за неделю задачи третьего этапа): §
Продвинулись в рейтинге: §
Новые лица в ТОР 100 и вернувшиеся туда: §
Продвижение ближайших претендентов на попадание в ТОР 100: §
На этой неделе сертифицированы: § Число подписчиков - 3912 Число участников рейтинга - 16589 Число участников второго этапа - 1934 Число участников третьего этапа - 54 Сертифицировано на сайте - 392 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLОператор PIVOTМоисеенко С.И.Чтобы объяснить, что такое PIVOT, я бы начал с электронных таблиц EXCEL. В версии MS Excel 5.0 появились так называемые сводные таблицы. Сводные таблицы представляют собой двумерную визуализацию многомерных структур данных, применяемых в технологии OLAP для построения хранилищ данных. Правильней даже сказать, что это двумерные сечения трехмерных OLAP-кубов, если иметь в виду наличие на сводной таблице элемента, который называется "страница". Сводные таблицы позволяют выполнять стандартные операции с многомерными структурами, например, упоминавшееся уже сечение куба, свертку и детализацию - операцию обратную свертке. Следует сказать, что сводная таблица не является реляционной, поскольку имеет не только заголовки столбцов, но и заголовки строк, при этом и те и другие формируются из данных, находящихся в столбцах обычных реляционных таблиц. Последнее, кстати, означает, что число строк и столбцов заранее неизвестно, т.к. они формируются динамически при выполнении запроса к реляционным данным. Кроме того, заголовки могут иметь многоуровневые подзаголовки, что и позволяет выполнять операции свертки (переход на более высокий уровень иерархии) и детализации (переход на более низкий уровень иерархии). Подобные свойства сводных таблиц позволяют их использовать, наряду со сводными диаграммами, в качестве клиента для визуального отображения многомерных данных, находящихся в хранилищах, поддерживаемых различными СУБД (например, MS SQL Server Analysis Services). Чтобы пояснить сказанное примером, давайте рассмотрим такой запрос к одной из учебных баз на sql-ex.ru: SELECT maker, type 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
П А
р B
о C
и D
з E
в.
Заголовками строк здесь являются уникальные имена производителей, которые берутся из столбца maker вышеприведенного запроса, а заголовками столбцов - уникальные типы продукции (соответственно, из столбца type). А что должно быть в середине? Ответ очевиден - некоторый агрегат, например, функция count(type), которая подсчитает для каждого производителя отдельно число моделей ПК, ноутбуков и принтеров, которые и заполнят соответствующие ячейки этой таблицы. Это простейший вариант сводной таблицы, который имеет всего два уровня иерархии по столбцам и строкам. Т.е. выполняя свертку по вертикали мы получаем количество каждого вида продукции для всех производителей, а по горизонтали - общее число моделей независимо от типа для каждого производителя. Можно было бы добавить уровни иерархии, например, по цветности принтеров, размеру экранов ноутбуков и т.д. Можно сказать, что pivot-таблица - это одноуровневая сводная таблица. Оператор PIVOT не является стандартным (я не уверен, что он когда-нибудь будет стандартизован ввиду нереляционной природы pivot-таблицы), поэтому я буду использовать в примерах его реализацию в языке T-SQL (SQL Server 2005/2008). Я могу и ошибиться в хронологии, но мне представляется, что успех реализации сводной таблицы в Excel привел к появлению так называемых перекрестных запросов в Access, и, наконец, к оператору PIVOT в T-SQL. Давайте рассмотрим такую задачу. Пример 1. Для каждого производителя из таблицы Product определить число моделей каждого типа продукции.Задачу можно решить стандартными средствами с использованием оператора CASE: SELECT maker, SUM(CASE type WHEN 'pc' THEN 1 ELSE 0 END) PC , SUM(CASE type WHEN 'laptop' THEN 1 ELSE 0 END) Laptop , SUM(CASE type WHEN 'printer' THEN 1 ELSE 0 END) Printer FROM Product GROUP BY maker А вот
SELECT maker, -- столбец (столбцы), значения из которого формируют заголовки строк
[pc], [laptop], [printer] -- значения из столбца, который указан в предложении type,
-- формирующие заголовки столбцов
FROM Product -- здесь может быть подзапрос
PIVOT -- формирование пивот-таблицы
(COUNT(model) -- агрегатная функция, формирующая содержимое сводной таблицы
FOR type -- указывается столбец, уникальные значения в котором будут являться заголовками столбцов
IN([pc], [laptop], [printer]) --указываются конкретные значения в столбце type,
-- которые следует использовать в качестве заголовков,
-- т.к. нам могут потребоваться не все
) pvt -- алиас для сводной таблицы
Надеюсь, что комментарии к коду достаточно понятны для того, чтобы написать оператор PIVOT без шпаргалки. Давайте попробуем. Пример 2. Посчитать среднюю цену на ноутбуки в зависимости от размера экрана. Задача элементарная и решается с помощью группировки: SELECT screen, AVG(price) avg_ FROM Laptop GROUP BY screen screen avg_ 11 700.00 12 960.00 14 1175.00 15 1050.00 А вот как можно повернуть эту таблицу с помощью PIVOT: SELECT [avg_], [11],[12],[14],[15] FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) x PIVOT (AVG(price) FOR screen IN([11],[12],[14],[15]) ) pvt avg_ 11 12 14 15 average price 700.00 960.00 1175.00 1050.00 В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. требуется знать вид данных, а значит и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников). Если рассмотренных примеров покажется недостаточно, чтобы понять и использовать без затруднений этот оператор, я вернусь к нему, когда придумаю нетривиальные примеры, где использование оператора PIVOT позволяет существенно упростить код. Я написал этот опус в помощь тем, кому оператор PIVOT интуитивно непонятен. Могу согласиться в том, что в реляционном языке SQL он выглядит инородным телом. Собственно, иначе и быть не может ввиду того, что поворот (транспонирование) таблицы является не реляционной операцией, а операцией работы с многомерными структурами данных. Полезная информация§
Приглашаем вас посетить Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. §
Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
