Sql убрать дубликаты из запроса. Удаление повторений в T-SQL

Информационная архитектура - сочетание схем организации, предметизации и навигации, реализованных в информационной системе.

Информационная архитектур а дает целостное понимание существующей бизнес-среды и используется для решения текущих актуальных проблем бизнеса, обеспечивая в то же время инфраструктуру для проектирования новых бизнес-ориентированных решений. Она помогает свести к минимуму риск и поддерживает полноценное планирование любых ИТ-проектов.

Информационная архитектура охватывает различные контексты, в которых используется информация, такие как направления бизнеса и отдельные функции (финансы, продажи и маркетинг, бизнес-аналитика) и заключает в себе не только модель данных, но и метаданные, необходимые для поддержки всех оперативных систем и внутренних сервисов, таких как хранилище данных. Информационная архитектура помогает увидеть данные и образ их использования так, чтобы преобразовать их в полезную информацию; ее применение позволяет обеспечить гибкость и расширяемость интегрированного решения, а также его способность адекватно реагировать на изменения потребностей бизнеса.

Информационная архитектура используется для оценки эффективности ИТ-проектов, а также проверки спроектированных решений на предмет полноты удовлетворения как текущих нужд, так и перспективных потребностей. Она применяется для испытания сценариев бизнес-процессов и определения того, позволяют ли они удовлетворить более широкие потребности бизнеса, такие как анализ и отчетность.

Основные составляющие информационной архитектуры следующие:

Бизнес-модель, включающая различные представления организационной структуры, заинтересованных сторон, их интересов и мотивации, а также описание бизнес-процессов и выполняемых функций;

Информационная модель, в которую входят информационные объекты («сущности»), описывающие их метаданные, таксономии классификаций объектов, а также онтологии для соответствующих информационных объектов;

Методология для извлечения, анализа и сохранения характеристик бизнес- и информационных объектов;

Инструменты, такие как репозиторий, в который помещаются все артефакты и знания, создаваемые в ходе построения архитектуры, в том числе описания бизнес-объектов, объектов данных,

Информационная архитектура предоставляет базис для сопоставления потребностей и ожиданий бизнеса, требований к процессам и используемой в них информации таким способом, который позволит объединить разрозненные области бизнес- информации и организационные уровни на предприятии. Информационные концепты, смоделированные без привязки к направлениям бизнеса, приложениям, бизнес-процессам, функциям и департаментам, обеспечивают гибкость связывания организационных данных и метаданных, использования объектов и учета их происхождения. Создание пригодных для многократного использования в различных функциональных областях моделей этих информационных объектов позволяет проектировать и создавать легко взаимодействующие друг с другом приложения обработки данных, органично связанные с лежащими в их основе информационными моделями.

34.Основные характеристики модели «клиент-сервер». Ограничения модели

Клиент-сервер -- это модель взаимодействия процессов в вычислительной системе, при которой один процесс (клиент) делает запрос, другой процесс (сервер) его обрабатывает и возвращает первому ответ или предоставляет определенную услугу в виде вычислений, каких-либо данных и т.п. Это вычислительная или сетевая архитектура, в которой задания или сетевая нагрузка распределены между поставщиками услуг, называемыми серверами, и заказчиками услуг, называемыми клиентами. Физически клиент и сервер - это программное обеспечение. Обычно они взаимодействуют через компьютерную сеть посредством сетевых протоколов и находятся на разных вычислительных машинах, но могут выполняться также и на одной машине. Программы - сервера, ожидают от клиентских программ запросы и предоставляют им свои ресурсы в виде данных (например, загрузка файловпосредством HTTP, FTP, BitTorrent, потоковое мультимедиа или работа с базами данных) или сервисных функций (например, работа с электронной почтой, общение посредством систем мгновенного обмена сообщениями, просмотр web-страниц во всемирной паутине).

Основной принцип технологии "клиент-сервер" заключается в разделении функций приложения на группы. Программы имеют распределенный характер, т.е. одна часть функций прикладной программы реализуется в программе-клиенте, а другая - в программе-сервере, а для их взаимодействия определяется некоторый протокол.

Рассмотрим эти функции:

Функции ввода и отображения данных.

Прикладные функции, характерные для данной предметной области (для банковской системы - открытие счета, перевод денег и т.д.).

Функции хранения и управления информационно-вычислительными ресурсами (базами данных, файловыми системами и т.д.).

Служебные функции, осуществляющие связь между функциями первых трех групп.

Удаление повторений

Исходник базы данных

Потребность в устранении дубликатов из данных встречается очень часто, особенно при решении проблем с качеством данных в средах, где дублирование возникло из-за отсутствия ограничений, которые могли бы обеспечить уникальность данных. Для демонстрации подготовим с помощью следующего кода пример данных с дублирующимися заказами в таблице по имени MyOrders:

IF OBJECT_ID("Sales.MyOrders") IS NOT NULL DROP TABLE Sales.MyOrders; GO SELECT * INTO Sales.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;

Представьте, что вам нужно устранить дублирование данных, оставив только по одному экземпляру с уникальным значением orderid. Дублируюшиеся номера отмечаются с помощью функции ROW_NUMBER с секционированием по предположительно уникальному значению (в нашем случае orderid) и с использованием произвольного упорядочения, если вам неважно, какую строку оставить, а какую удалить. Вот код, в котором функция ROW_NUMBER отмечает дубликаты:

SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

Затем нужно рассмотреть разные варианты в зависимости от количества строк, которые нужно удалить, процента размерности таблицы, какое это количество составляет, активности производственной среды и других обстоятельств. При небольшом числе удаляемых строк обычно достаточно использовать операцию удаления с полным протоколированием, в которой удаляются все экземпляры, у которых номер строки больше единицы:

Но если число удаляемых строк большое - особенно когда оно составляет большую долю строк таблицы, удаление с полной записью операции в журнале будет слишком медленным. В этом случае стоит подумать об использовании операции неполного протоколирования, такой как SELECT INTO, для копирования уникальных строк (с номером 1) в другую таблицу. После этого оригинальная таблица удаляется, затем новой таблице присваивается имя удаленной таблицы, воссоздаются ограничения индексы и триггеры. Вот код законченного решения:

WITH C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- воссоздание индексов, ограничений и триггеров

Для простоты я не добавил сюда никакого контроля транзакций, но нужно всегда помнить, что с данными могут одновременно работать несколько пользователей. При реализации этого метода в производственной среде нужно соблюдать следующую последовательность:

Открыть транзакцию.

Получить блокировку таблицы.

Выполнить инструкцию SELECT INTO.

Удалить и переименовать объекты.

Воссоздать индексы, ограничения и триггеры.

Зафиксировать транзакцию.

Есть еще один вариант - отфильтровать только уникальные или только неуникальные строки. Вычисляются обе функции - ROW_NUMBER и RANK - на основе упорядочения по orderid, примерно так:

SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;

Заметьте, что в результатах только в одной строке для каждого уникального значения в orderid совпадают номер и ранг строки. К примеру, если надо удалить небольшую часть данных, можно инкапсулировать предыдущий запрос в определение CTE, а во внешнем запросе выполнить инструкцию удаления строк, у которых разные номер строки и ранг.

Когда возникает задача оптимизации базы данных или меняется ее структура, иногда появляется попутная задача организации уже накопленных данных. Хорошо, если таблица уже при разработке приведена в нормальную форму, и вся система организована так, что она не копит лишней дублирующей информации. Если же это не так, то при доработке такой системы хочется избавиться от всех избыточных данных и сделать все наиболее качественно.

В этой статье рассмотрим задачу удаления дублирующих строк в таблице БД. Сразу же отмечу, что речь идет о необходимости удалить именно повторяющиеся строки. Например, записи в таблице заказов с полями "код заказа", "код товара", "код покупателя", "дата заказа" могут различаться только кодом заказа, так как все же один покупатель в один день может заказать один и тот же товар несколько раз. А главный показатель здесь, что все правильно – наличие ключевого поля.

Если же мы видим таблицу, изобилующую повторяющимися полями, без явной необходимости каждой записи, то это именно то, что должно быть исправлено.

Пример явно избыточной таблицы:

Теперь рассмотрим, как можно решить эту проблему. Здесь можно применить несколько методов.

1. Можно написать функцию для сравнения и перебора всех данных. Это долго, да и писать код для одноразового использования не всегда хочется.

2. Другое решение – создать запрос на выборку с группировкой данных, так чтобы получить только уникальные строки:

SELECT country_id, city_name
FROM mytable
GROUP BY country_id, city_name

Получаем следующую выборку:

Затем, полученный набор данных записываем в другую таблицу.

3. В указанных решениях применяется дополнительный программный код или дополнительные таблицы. Однако, было бы удобней сделать все, используя только запросы SQL без дополнительных таблиц. И вот пример такого решения:

DELETE a.* FROM mytable a,
(SELECT

FROM mytable b

) c
WHERE
a.country_id = c.country_id
AND a.city_name = c.city_name
AND a.id > c.mid

После выполнения такого запроса в таблице останутся только уникальные записи:

Теперь разберемся подробнее, как все это работает. При запросе на удаление, необходимо задать условие, которое укажет какие данные нужно удалить, а какие оставить. Нам необходимо удалить все не уникальные записи. Т.е. если существует несколько одинаковых записей (одинаковые они, если у них равны значения country_id и city_name), то нужно взять одну из строк, запомнить ее код и удалить все записи с такими же значениями country_id и city_name, но другим кодом (id).

Строка SQL запроса:

DELETE a.* FROM mytable a,

указывает, что удаление будет производиться из таблицы mytable.

Затем запрос на выборку формирует вспомогательную таблицу, где мы группируем записи так, чтобы все записи были уникальными:

(SELECT
b.country_id, b.city_name, MIN(b.id) mid
FROM mytable b
GROUP BY b.country_id, b.city_name
) c

MIN(b.id) mid – формирует столбец mid (сокращение min id), в который вносятся минимальное значение id, в каждой подгруппе.

В результате получается таблица, содержащая уникальные записи и id первой строки для каждой группы дублирующих записей.

Теперь мы имеем две таблицы. Одну общую, содержащую все записи. Из нее будут удаляться лишние строки. Вторая содержит информацию о строках, которые нужно сохранить.

Остается только сформировать условие, где указывается: удалить нужно все строки, где совпадают поля country_id и city_name, а id совпадать не будет. В данном случае выбирается минимальное значение id, поэтому удаляются все записи, id которых больше чем выбранный во временную таблицу.

Стоит еще отметить, что описанную операцию можно выполнить при наличии в таблице ключевого поля. Если вдруг встретилась таблица без уникального идентификатора, то просто добавляем его:

ALTER TABLE ` mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`)

Выполнив такой запрос, получим дополнительный столбец, заполненный уникальными числовыми значениями для каждой строки таблицы.

Выполняем все необходимые действия. После того, как операция по очистке таблицы от дубликатов записей выполнена, это поле можно так же удалить.

(25-07-2009)

В предыдущей статье мы рассмотрели решение проблемы с дубликатами, вызванной отсутствием первичного ключа. Рассмотрим теперь более тяжелый случай, когда ключ вроде бы есть, но он является синтетическим, что при неправильном проектировании тоже может привести к появлению дубликатов с точки зрения предметной области.

Странное дело, но, рассказывая на лекциях о недостатках синтетических ключей, я, тем не менее, постоянно сталкиваюсь с тем, что студенты в своих первых проектах с базами данных их неизменно используют. Видимо, в человеке заложена генетическая потребность все перенумеровывать, и помочь здесь может только психотерапевт. :-)

Итак, пусть имеется таблица с первичным ключом 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. Следовательно, мы должны при устранении дубликатов учитывать обе таблицы.

Процедуру "очистки" данных можно провести в два этапа:

1. Выполнить обновление таблицы T_details, приписав данные, относящиеся к одному имени, к id с минимальным номером в группе.
2. Удалить дубликаты из таблицы T_pk, оставив только строки с минимальным id в каждой группе с одинаковым значением в столбце name.

Обновление таблицы 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 ;