Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 252
Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"Выпуск 252 от 25 июля 2009 г.Новым посетителям сайта Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только)
СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.
Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 48-ми задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов, которые пригодятся на третьем рейтинговом этапе, который начинается после 138 задачи.Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий. Новости сайта§ Под номером 35 на обучающий этап выставлена новая задача. § Изменения среди лидеров (решенные за неделю задачи третьего этапа): § К 3-му этапу подошел: § Одна задача до третьего этапа осталась: § Продвинулись в рейтинге: § Новые лица в ТОР 100 и вернувшиеся туда: § Продвижение ближайших претендентов на попадание в ТОР 100: § На этой неделе сертифицированы: § Число подписчиков - 3913 Число участников рейтинга - 22305 Число участников второго этапа - 1879 Число участников третьего этапа - 52 Сертифицировано на сайте - 383 Лучшие результаты (ТОР 20)
Лучшие результаты за неделю
Изучаем SQLКак удалить дубликаты строк при наличии первичного ключа?Моисеенко С.И.В предыдущей статье мы рассмотрели решение проблемы с дубликатами, вызванной отсутствием первичного ключа. Рассмотрим теперь более тяжелый случай, когда ключ вроде бы есть, но он является синтетическим, что при неправильном проектировании тоже может привести к появлению дубликатов с точки зрения предметной области. Странное дело, но, рассказывая на лекциях о недостатках синтетических ключей, я, тем не менее, постоянно сталкиваюсь с тем, что студенты в своих первых проектах с базами данных их неизменно используют. Видимо, в человеке заложена генетическая потребность все перенумеровывать, и помочь здесь может только психотерапевт. :-) Я не хочу обсуждать здесь избитую проблему синтетических ключей. Скажу лишь, что если вы решили использовать их в качестве первичного ключа, то следует также создавать естественный уникальный ключ, чтобы избежать описанной ниже ситуации. Итак, пусть имеется таблица с первичным ключом id и столбцом name, который в соответствии с ограничениями предметной области должен содержать уникальные значения. Однако если определить структуру таблицы следующим образом CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY,
name VARCHAR(50))
то появлению дубликатов ничто не препятствует. Следовало бы использовать следующую структуру таблицы: CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY,
name VARCHAR(50) UNIQUE)
Все знают, как нужно правильно поступить, однако зачастую приходится иметь с "унаследованной" структурой и данными, которые нарушают ограничения предметной области. Вот пример: id name 1 John 2 Smith 3 John 4 Smith 5 Smith 6 Tom Вы можете спросить: "А чем эта проблема отличается от предыдущей? Ведь здесь есть даже более простое решение - просто удалить все строки из каждой группы с одинаковыми значениями в столбце name, оставив лишь строку с минимальным/максимальным значением id. Например, так:" DELETE FROM T_pk WHERE id > (SELECT MIN(id) FROM T_pk X WHERE X.name = T_pk.name) Правильно, но я вам еще не все рассказал. :-) Представьте, что у нас имеется дочерняя таблица T_details, связанная с таблицей T_pk по внешнему ключу: CREATE TABLE T_details (id_pk INT FOREIGN KEY REFERENCES T_pk ON DELETE CASCADE, color VARCHAR(10), PRIMARY KEY (id_pk, color) Эта таблица может содержать такие данные: id_pk color 1 blue 1 red 2 green 2 red 3 red 4 blue 6 red Для большей наглядности воспользуемся запросом SELECT id, name, color FROM T_pk JOIN T_details ON id= id_pk чтобы увидеть имена: id name color 1 John blue 1 John red 2 Smith green 2 Smith red 3 John red 4 Smith blue 6 Tom red Таким образом, оказывается, что данные, фактически относящиеся к одному лицу, ошибочно оказались разнесенными по разным родительским записям. Кроме того, дубликаты оказались и в этой таблице: 1 John red 3 John red Очевидно, что подобные данные приведут к ошибочному анализу и отчетам. Более того, каскадное удаление приведет к потере данных. Например, если мы оставим только строки с минимальным идентификатором в каждой группе в таблице T_pk, то потеряем строку 4 Smith blue в таблице T_details. Следовательно, мы должны при устранении дубликатов учитывать обе таблицы. Процедуру "очистки" данных можно провести в два этапа:
Обновление таблицы T_detailsЗапрос SELECT id_pk, name, color
, RANK() OVER(PARTITION BY name, color ORDER BY name, color, id_pk) dup
,(SELECT MIN(id) FROM T_pk WHERE T_pk.name = X.name) min_id
FROM T_pk X JOIN T_details ON id=id_pk
определяет наличие дубликатов (значение dup > 1) и минимальное значение id в группе одинаковых имен (min_id). Вот результат выполнения этого запроса: id_pk name color dup min_id 1 John blue 1 1 1 John red 1 1 3 John red 2 1 4 Smith blue 1 2 2 Smith green 1 2 2 Smith red 1 2 6 Tom red 1 6 Теперь нам нужно заменить значение id_pk значением min_pk для всех строк, кроме третьей, т.к. эта строка есть дубликат второй строки, о чем говорит значение dup=2. Запрос на обновление можно написать так: UPDATE T_details
SET id_pk=min_id
FROM T_details T_d JOIN (
SELECT id_pk, name, color
, RANK() OVER(PARTITION BY name, color ORDER BY name, color, id_pk) dup
,(SELECT MIN(id) FROM T_pk WHERE T_pk.name = X.name) min_id
FROM T_pk X JOIN T_details ON id=id_pk
) Y ON Y.id_pk=T_d.id_pk
WHERE dup =1
После обновления таблица T_details примет следующий вид: id_pk color 1 blue 1 red 2 blue 2 green 2 red 3 red 6 red Как видно, осталась одна лишняя дубликатная строка: 3 red Но о ней можно не беспокоиться, так она будет удалена автоматически при каскадном удалении дубликатов из таблицы T_pk: DELETE FROM T_pk WHERE id > (SELECT MIN(id) FROM T_pk X WHERE X.name = T_pk.name) Последний запрос и является вторым этапом процедуры, в результате выполнения которого мы получим: Таблица T_pk id name 1 John 2 Smith 6 Tom Таблица T_details id_pk color 1 blue 1 red 2 blue 2 green 2 red 6 red Осталось только наложить ограничение, чтобы избежать появления дубликатов в дальнейшем: ALTER TABLE T_pk ADD CONSTRAINT unique_name UNIQUE(name) Полезная информация§ Приглашаем вас посетить Интерактивный учебник по SQL. § Онлайновый выпуск рассылки можно почитать на сайте. § Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL. § Хотите поддержать проект? Вот инструкция по применению. КонтактыПо всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте. |
| В избранное | ||
