How to Find Duplicates using a Windows Function?

Problem

You need to find duplicates and then identify the records by their primary keys.

Background

Here’s an example of how to find duplicate records using a window function in SQL:

Suppose we have a table of students that includes columns for student ID, name, and age. We want to find all the students who have the same name and age. We can use a window function to do this in a single SQL query:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

INSERT INTO students (id, name, age)
VALUES
    (1, 'Alice', 20),
    (2, 'Bob', 22),
    (3, 'Charlie', 20),
    (4, 'Alice', 20),
    (5, 'David', 18),
    (6, 'Charlie', 20),
    (7, 'Eve', 19),
    (8, 'Bob', 22),
    (9, 'Frank', 21);

Solution

Now, to find the duplicates, we can use a window function along with the COUNT aggregate function. The window function allows us to partition the data by name and age, so that the “COUNT” function will only count the number of rows with the same name and age:

select id, name, age
from (
   select id, name, age,
      count(*) over (partition by name, age) as cnt
   from students) sub
where cnt > 1;

In this query, the subquery calculates the count of rows with the same name and age using the window function. The outer query then selects only the rows where the count is greater than 1, i.e. the rows with duplicate name and age.

Using a window function in this way is more efficient than other SQL methods for finding duplicates, such as self-joins or subqueries with group-by clauses. With a window function, we can perform the partition and aggregation in a single pass over the data, rather than having to join or group the data multiple times. This can lead to significant performance improvements, especially for large datasets.

Discussion

Overall, window functions are a powerful and efficient tool in SQL for performing complex data analysis tasks, such as finding duplicates or calculating running totals. By using them in our queries, we can write more concise and efficient SQL code that is easier to understand and maintain.

Alternatives to Windows Functions

Without using window functions, one way to find duplicates in SQL is to use a self-join on the table, where we join the table to itself on the columns that we want to check for duplicates (in this case, name and age). The resulting join will include all pairs of rows that have the same name and age, so we can use a “GROUP BY” clause to group them together and count the number of duplicates.

Self Join

Here’s an example of how to find duplicates using a self-join in SQL:

select s1.id, s1.name, s1.age
from students s1
   join students s2 on s1.name = s2.name and s1.age = s2.age and s1.id < s2.id
group by s1.id, s1.name, s1.age
having count(*) > 1;

In this query, we join the “students” table to itself on the “name” and “age” columns, and we add a condition to ensure that we only match each pair of rows once (by comparing the “id” column). We then group the resulting pairs of rows by the “id”, “name”, and “age” columns, and we use the “HAVING” clause to filter out any groups that don’t have at least two rows (i.e. duplicates).

While this approach can work, it can be less efficient and harder to read and maintain than using a window function. The self-join can be computationally expensive, especially for large datasets, and it can become more complex when we want to check for duplicates on multiple columns or apply more complex filtering or aggregation operations. In contrast, the window function approach allows us to perform the partition and aggregation in a single query, making it more efficient and easier to understand.