Ищем дубликаты записей в базе данных
Недавно мне понадобилось сделать на одном сайте такую систему страниц, чтобы к любой сущности (пост в блоге, страничка автора поста, просто статическая страница) можно было обратиться по её коду, а не по ID. Причём этот код должен идти сразу за адресом самого сайта — без каких бы то ни было папок, подпапок и прочих слэшей в пути. Примерно вот так:
- http://sitename.org/cool_post
- http://sitename.org/even_cooler_post_author
- http://sitename.org/just_a_static_page
Разумеется, чтобы это реализовать, надо убедиться, что коды сущностей уникальны. Я не буду рассказывать, как я добавлял единички к кодам, когда они совпадали, я расскажу о том, как я искал одинаковые коды.
Одна таблица
Начнём с простого — с одной единственной таблицы такого вида:
| id | code | title | text |
|---|---|---|---|
| 1 | code1 | name1 | text1 |
| 2 | code2 | name2 | text2 |
| 3 | code3 | name3 | text3 |
Здесь у нас id — это первичный ключ, он уникален. А поле code — просто некий код, который может повторяться для разных записей. Если быть совсем точным, то я сгенерировал коды просто транслитерировав названия.
Теперь мы хотим найти все повторы. Будем выводить сам повторяющийся код, список Idшек и количество повторов.
MySQL
Для нашего примера получим такой вывод:
| code | ids | cnt |
|---|---|---|
| code2 | 2, 3 | 2 |
PostgreSQL
Особенность PostgreSQL’я в том, что функция конкатенации строк работает только с текстом, поэтому числовые Idшники нужно сначала привести к типу text.
MSSQL
В MSSQL нет агрегирующей функции для конкатенации, поэтому придётся использовать хитрый подзапрос.
Много таблиц
Теперь вернёмся к исходной задаче: нам нужны уникальные коды не в одной таблице, а сразу в нескольких. Возьмём для примера такие:
| id | code | title | text |
|---|---|---|---|
| 1 | code1 | name1 | text1 |
| 2 | code2 | name2 | text2 |
| 3 | code3 | name3 | text3 |
Таблица Article
| id | code | title | text |
|---|---|---|---|
| 1 | code2 | name2 | text2 |
| 2 | code3 | name3 | text3 |
| 3 | code4 | name4 | text4 |
Таблица Author
| id | code | title | text |
|---|---|---|---|
| 1 | code3 | name3 | text3 |
| 2 | code4 | name4 | text4 |
| 3 | code5 | name5 | text5 |
Теперь вместо IDшников я буду выводить названия таблиц, где встречаются одинаковые записи.
В сущности, запрос останется прежним, только теперь выборку будем делать из временной таблицы, в которой объединим все остальные.
MySQL
MSSQL
В MSSQL исходная таблица нам нужна в двух местах — в выборке и в грязном хаке, заменяющем конкатенацию, поэтому будет проще, если мы сделаем для неё синоним.
Заключение
А в конце хочется сказать, что вся эта статья была затеяна только ради конкатенации в MSSQL:)
7 Ways to Find Duplicate Rows in PostgreSQL while Ignoring the Primary Key
Here are seven ways to return duplicate rows in PostgreSQL when those rows have a primary key or other unique identifier column.
This means that the duplicate rows share exactly the same values across all columns with the exception of their primary key/unique ID column.
Sample Data
We’ll use the following data for our examples:
The first two rows are duplicates (except for the DogId column, which is the table’s primary key, and contains a unique value across all rows). The last three rows are also duplicates (except for the DogId column).
The primary key column ensures that there are no duplicate rows, which is good practice in RDBMSs, because primary keys help to enforce data integrity. But because primary keys prevent duplicate rows, they have the potential to interfere with our ability to find duplicates.
In our table above, the primary key column is an incrementing number, and its value carries no meaning and is not significant. We therefore need to ignore that row if we want to find duplicates in the other columns.
Option 1
We can use the SQL GROUP BY clause to group the columns by their significant columns, then use the COUNT() function to return the number of identical rows:
Here we excluded the primary key column by omitting it from our query.
The result tells us that there are three rows containing Wag Johnson and two rows containing Bark Smith. These are duplicates (or triplicates in the case of Wag Johnson). The other two rows do not have any duplicates.
Option 2
We can exclude non-duplicates from the output with the HAVING clause:
Option 3
Here’s an example of checking for duplicates on concatenated columns. In this case we use the CONCAT() function to concatenate our two columns, use the DISTINCT keyword to get distinct values, then use the COUNT() function to return the count:
Option 4
We can alternatively use the ROW_NUMBER() window function:
Using the PARTITION clause results in a new column being added, with a row number that increments each time there’s a duplicate, but resets again when there’s a unique row.
In this case we don’t group the results, which means we can see each duplicate row, including its unique identifier column.
Option 5
We can also use the previous example as a common table expression in a larger query:
This excludes non-duplicates from the output, and it excludes one row of each duplicate from the output. In other words, it only shows the excess rows from the duplicates. These rows are prime candidates for being deleted in a de-duping operation.
Option 6
Here’s a more concise way to get the same output as the previous example:
One difference between this example and the previous one is that this example doesn’t require generating our own separate row number.
Option 7
Here’s yet another option for returning duplicate rows in Postgres: