SQL에서 중복 찾기: 대화형 예제가 포함된 완벽 가이드

SQL에서 중복을 찾는 것은 지루한 작업일 수 있지만, 데이터베이스를 다루는 모든 사람에게 필수적인 기술입니다. 중복은 데이터에 오류, 불일치, 비일관성을 유발하여 잘못된 결과와 성능 저하로 이어질 수 있습니다. 따라서 데이터 무결성과 정확성을 보장하기 위해 테이블에서 중복을 식별하고 제거하는 것이 중요합니다.

SQL은 요구 사항과 테이블 구조에 따라 데이터에서 중복을 찾는 여러 가지 방법을 제공합니다. GROUP BY 및 HAVING 절을 사용하여 특정 열로 레코드를 그룹화하고 카운트나 조건에 따라 중복을 필터링할 수 있습니다. 또는 DISTINCT 키워드를 사용하여 고유한 값만 선택하고 원본 테이블과 비교하여 중복을 식별할 수 있습니다. COUNT(), EXISTS, JOIN과 같은 특수 함수와 연산자도 있어 더 복잡한 시나리오에서 중복을 찾는 데 도움이 됩니다.

이 가이드 전체에서 브라우저에서 직접 중복 찾기를 연습할 수 있는 대화형 SQL 연습문제를 찾을 수 있습니다. 데이터베이스 설정이 필요 없습니다. 쿼리를 작성하고, 즉각적인 피드백을 받고, 결과가 예상 출력과 일치하는지 확인하세요.

이 문서에서는 간단한 쿼리부터 고급 방법까지 SQL에서 중복을 찾는 다양한 기법을 살펴보고, 이 기술을 마스터하는 데 도움이 되는 예제와 모범 사례를 제공합니다. 초보자이든 숙련된 SQL 개발자이든, 이 가이드는 테이블에서 중복을 감지하고 제거하여 데이터 품질과 효율성을 향상시키는 데 도움이 됩니다. 이러한 쿼리를 연습하려면 구문 강조 및 자동 완성 기능을 제공하는 Beekeeper Studio의 SQL 편집기와 같은 최신 SQL 편집기 사용을 고려해 보세요.

GROUP BY 및 HAVING 절을 사용하여 중복 찾기

SQL에서 중복 값을 찾는 한 가지 방법은 GROUP BY 및 HAVING 절을 사용하는 것입니다. 이러한 절을 사용하면 하나 이상의 열에서 동일한 값을 가진 행을 그룹화한 다음 특정 기준에 따라 그룹을 필터링할 수 있습니다. 작동 방식은 다음과 같습니다:

1. SELECT 문을 사용하여 중복을 확인하려는 열을 선택하는 것으로 시작합니다.
2. GROUP BY 절을 사용하여 선택한 열로 행을 그룹화합니다.
3. HAVING 절에서 COUNT 함수를 사용하여 하나 이상의 행이 있는 그룹을 필터링합니다. 이러한 그룹이 중복을 포함하는 그룹입니다.

예를 들어, “name”과 “email” 열이 있는 “customers”라는 테이블이 있다고 가정해 봅시다. 동일한 이메일 주소로 등록한 모든 고객을 찾고 싶습니다. SQL 쿼리는 다음과 같습니다:

SELECT name, email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

이 쿼리는 고객을 이메일 주소별로 그룹화한 다음 각 그룹의 고객 수를 계산합니다. HAVING 절은 고객이 한 명뿐인 그룹을 필터링하여 중복 이메일 주소가 있는 그룹만 남깁니다.

직접 해보세요:

[[ error ]]

[[ testData.solution ]]

GROUP BY 절에는 COUNT와 같은 집계 함수를 사용하는 것을 제외하고 선택하는 모든 열을 포함해야 합니다. 그렇지 않으면 쿼리에서 오류가 반환됩니다.

또 한 가지 기억해야 할 점은 COUNT 함수는 고유한 것뿐만 아니라 각 그룹의 모든 행을 계산한다는 것입니다. 따라서 동일한 이름과 이메일 주소를 가진 여러 행이 있는 경우 모두 중복으로 계산됩니다.

GROUP BY 및 HAVING 절을 사용하는 것 외에도 ORDER BY, WHERE, JOIN과 같은 다른 SQL 문을 사용하여 중복 검색을 더욱 세분화할 수 있습니다. 또한 고유 제약 조건을 사용하여 처음부터 테이블에 중복 값이 삽입되는 것을 방지할 수 있습니다.

전반적으로 SQL에서 중복을 찾는 것은 처리되지 않은 데이터를 정리하거나 잠재적인 인적 오류 또는 응용 프로그램 버그를 식별하는 데 강력한 도구가 될 수 있습니다. 올바른 검색 기준을 사용하고 결과를 명확하고 간결하게 출력하면 중복된 행이나 값을 빠르게 식별하고 수정 조치를 취할 수 있습니다.

COUNT 함수 사용

SQL에서 중복을 찾는 한 가지 방법은 COUNT 함수를 사용하는 것입니다. COUNT 함수는 특정 조건을 충족하는 테이블의 행 수를 계산하는 집계 함수입니다. COUNT 함수를 사용하면 열의 특정 값의 발생 횟수를 계산하고 중복을 식별할 수 있습니다.

중복을 찾기 위해 COUNT 함수를 사용하려면 중복을 확인하려는 열로 행을 그룹화해야 합니다. GROUP BY 절은 특정 열의 값에 따라 행을 그룹화하는 데 사용됩니다. 예를 들어 “users” 테이블의 “email” 열에서 중복을 찾으려면 “email” 열로 행을 그룹화합니다.

다음은 “users” 테이블의 “email” 열에서 중복을 찾기 위해 COUNT 함수를 사용하는 쿼리 예시입니다:

SELECT email, COUNT(email) as count
FROM users
GROUP BY email
HAVING COUNT(email) > 1;

이 쿼리에서 GROUP BY 절은 “email” 열로 행을 그룹화하고 COUNT 함수는 각 이메일 주소의 발생 횟수를 계산합니다. HAVING 절은 하나 이상의 발생이 있는 이메일 주소만 표시하도록 결과를 필터링합니다.

이 쿼리의 결과는 중복이 있는 이메일 주소와 각 이메일 주소의 발생 횟수를 보여주는 테이블이 됩니다. 이 정보를 사용하여 테이블에서 중복 행을 식별하고 제거할 수 있습니다.

COUNT 함수를 사용하는 것은 SQL에서 중복을 찾는 간단하고 효과적인 방법입니다. 열의 중복 값을 빠르게 식별하고 테이블에서 제거하는 조치를 취할 수 있습니다.

INNER JOIN 절 사용

SQL에서 중복을 찾는 가장 일반적인 방법 중 하나는 INNER JOIN 절을 사용하는 것입니다. 이 절을 사용하면 공통 열을 기반으로 두 개 이상의 테이블을 결합하고 두 테이블 모두에서 일치하는 값이 있는 행만 반환할 수 있습니다.

중복을 찾기 위해 INNER JOIN 절을 사용하려면 중복 데이터가 포함된 열을 식별해야 합니다. 이러한 열을 식별한 후 INNER JOIN 절을 사용하여 이러한 열에서 테이블을 조인할 수 있습니다.

다음은 “orders”라는 테이블에서 중복을 찾기 위해 INNER JOIN 절을 사용하는 방법의 예시입니다:

SELECT o1.order_id, o2.order_id
FROM orders o1
INNER JOIN orders o2
ON o1.customer_id = o2.customer_id
AND o1.order_date = o2.order_date
AND o1.order_id <> o2.order_id;

이 예에서 INNER JOIN 절은 “customer_id” 및 “order_date” 열을 조인 기준으로 사용하여 “orders” 테이블을 자체적으로 조인하는 데 사용됩니다. “AND” 연산자는 행이 반환되려면 세 열 모두가 일치해야 함을 지정하는 데 사용됩니다. 쿼리의 마지막 줄 “AND o1.order_id <> o2.order_id”는 두 “order_id” 값이 모두 동일한 행을 쿼리가 반환하지 않도록 합니다.

이 쿼리의 결과는 동일한 고객 ID와 주문 날짜를 가지지만 다른 주문 ID를 가진 모든 주문의 목록이 됩니다. 이것이 추가 조사가 필요한 중복 주문입니다.

셀프 조인으로 연습:

[[ error ]]

[[ testData.solution ]]

이런 방식으로 INNER JOIN 절을 사용하는 것은 SQL에서 중복을 찾는 강력한 도구가 될 수 있습니다. 그러나 이 방법을 사용할 때는 주의해야 합니다. 실수로 거짓 양성을 반환하거나 여러 테이블에 분산된 중복을 놓치기 쉽기 때문입니다. 항상 결과를 다시 확인하고 GROUP BY 및 HAVING 절과 같은 다른 방법을 사용하여 발견 사항을 확인하는 것이 좋습니다.

직접 해보세요:

[[ error ]]

[[ testData.solution ]]

셀프 조인을 사용하여 중복 쌍 찾기

중복을 찾는 또 다른 강력한 기법은 셀프 조인을 사용하여 공통 특성을 공유하는 레코드 쌍을 식별하는 것입니다. 이 접근 방식은 단순히 중복이 존재한다는 것을 식별하는 것보다 정확한 중복 쌍을 찾고 싶을 때 특히 유용합니다.

셀프 조인은 다른 별칭을 사용하여 테이블을 자체적으로 조인하는 것을 포함합니다. 중복을 정의하는 열에서 조인하고 레코드가 자체적으로 일치하지 않도록 하면 중복 쌍을 효과적으로 식별할 수 있습니다.

예를 들어, 트랜잭션 테이블이 있고 동일한 고객 ID와 금액을 가지지만 다른 트랜잭션 ID를 가진 트랜잭션 쌍을 찾고 싶다면 다음과 같은 셀프 조인을 사용할 수 있습니다:

SELECT t1.transaction_id as transaction_id1, 
       t2.transaction_id as transaction_id2, 
       t1.customer_id, 
       t1.amount
FROM transactions t1
INNER JOIN transactions t2 
ON t1.customer_id = t2.customer_id 
AND t1.amount = t2.amount 
AND t1.transaction_id < t2.transaction_id;

이 쿼리의 주요 포인트는 다음과 같습니다:

• t1과 t2는 동일한 테이블의 별칭입니다
• 조인 조건은 레코드를 “중복”으로 만드는 것을 지정합니다(customer_id와 amount)
• t1.transaction_id < t2.transaction_id는 각 쌍을 한 번만 가져오고 레코드가 자체적으로 일치하지 않도록 합니다

직접 해보세요:

[[ error ]]

[[ testData.solution ]]

Common Table Expression(CTE)을 사용하여 중복 찾기

SQL에서 중복을 찾는 방법 중 하나는 Common Table Expression(CTE)을 사용하는 것입니다. CTE는 단일 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문의 실행 범위 내에서 정의되는 임시 결과 집합입니다.

CTE를 사용하여 중복을 찾으려면 결과 집합의 파티션 내에서 각 행에 고유한 순차 번호를 할당하는 ROW_NUMBER() 함수를 사용할 수 있습니다. PARTITION BY 절은 파티션을 정의하는 데 사용되는 열을 지정하고, ORDER BY 절은 각 파티션 내의 행 순서를 지정합니다.

다음은 CTE를 사용하여 중복을 찾는 예시입니다:

WITH CTE AS (
   SELECT column1, column2, column3, ROW_NUMBER() OVER(PARTITION BY column1, column2, column3 ORDER BY column1, column2, column3) AS RowNumber
   FROM table_name
)
SELECT *
FROM CTE
WHERE RowNumber > 1

이 예에서 CTE는 중복을 확인할 열로 정의됩니다. ROW_NUMBER() 함수는 결과 집합의 파티션 내 각 행에 대한 시퀀스 번호를 생성하는 데 사용되며, 파티션은 PARTITION BY 절에 지정된 열로 정의됩니다. 그런 다음 결과 집합은 RowNumber가 1보다 큰 행만 표시하도록 필터링되어 중복이 있음을 나타냅니다.

ROW_NUMBER() 함수 내의 ORDER BY 절은 메인 SELECT 문의 ORDER BY 절과 일치해야 합니다. 그렇지 않으면 결과가 정확하지 않을 수 있습니다.

CTE를 사용하여 중복을 찾는 것은 Common Table Expression을 사용한 그래프 처리 지원을 도입한 SQL Server 2017에서 특히 유용할 수 있습니다. 이를 통해 데이터 간의 관계를 포함하는 더 복잡한 쿼리가 가능합니다.

전반적으로 CTE를 사용하여 SQL에서 중복을 찾는 것은 데이터 분석 및 관리를 위한 강력한 도구가 될 수 있습니다.

직접 해보세요:

[[ error ]]

[[ testData.solution ]]

윈도우 함수를 사용하여 중복 찾기

SQL에서 중복을 찾을 때 가장 효율적인 방법 중 하나는 윈도우 함수를 사용하는 것입니다. 윈도우 함수는 현재 행과 관련된 행 집합에 대해 계산을 수행하는 데 사용할 수 있는 강력한 도구입니다.

윈도우 함수를 사용하여 중복을 찾으려면 ROW_NUMBER() 함수를 PARTITION BY 절과 함께 사용할 수 있습니다. ROW_NUMBER() 함수는 파티션 내의 각 행에 고유 번호를 할당하고, PARTITION BY 절은 특정 열 또는 열 집합을 기반으로 행을 파티션으로 그룹화합니다.

예를 들어, user_id, username, email 열을 포함하는 사용자 테이블이 있다고 가정해 봅시다. 중복 이메일을 가진 모든 사용자를 찾으려면 다음 select 문을 사용할 수 있습니다:

SELECT user_id, username, email
FROM (
  SELECT user_id, username, email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS row_num
  FROM users
) AS subquery
WHERE row_num > 1;

이 예에서 PARTITION BY 절은 email 열로 행을 파티션하고, ROW_NUMBER() 함수는 user_id 열을 기준으로 파티션 내의 각 행에 고유 번호를 할당합니다. WHERE 절은 row_num이 1보다 큰 행, 즉 중복 이메일이 있는 행을 제외한 모든 행을 필터링합니다.

ROW_NUMBER()로 연습:

[[ error ]]

[[ testData.solution ]]

윈도우 함수는 CASE 문이나 집계 함수와 같은 다른 SQL 함수와 결합하여 더 복잡한 쿼리를 만들 수 있습니다. 예를 들어, CASE 문을 사용하여 행을 특정 카테고리로 그룹화한 다음 윈도우 함수를 사용하여 해당 카테고리 내의 중복을 찾을 수 있습니다.

결론적으로, 윈도우 함수를 사용하여 SQL에서 중복을 찾는 것은 강력하고 효율적인 방법이 될 수 있습니다. ROW_NUMBER() 함수를 PARTITION BY 절과 함께 사용하면 행을 파티션으로 쉽게 그룹화하고 파티션 내의 각 행에 고유 번호를 할당할 수 있습니다. 이를 통해 결과에서 중복 행을 빠르게 식별하고 제거할 수 있습니다.

직접 해보세요:

[[ error ]]

[[ testData.solution ]]

PARTITION BY 절과 함께 ROW_NUMBER() 함수 사용

SQL 데이터베이스에서 중복을 검색할 때 PARTITION BY 절과 함께 ROW_NUMBER() 함수가 유용한 도구입니다. 이 함수는 지정된 파티션 기준에 따라 결과 집합의 각 행에 고유 번호를 할당합니다.

PARTITION BY 절을 사용하면 사용자가 하나 이상의 열을 기준으로 행을 파티션으로 그룹화할 수 있습니다. 이는 ROW_NUMBER() 함수가 전체 결과 집합이 아닌 각 파티션 내의 각 행에 고유 번호를 할당한다는 것을 의미합니다.

예를 들어, 주문 ID, 고객 ID, 주문 날짜에 대한 열이 있는 고객 주문 테이블을 생각해 봅시다. 각 고객에 대한 중복 주문을 찾으려면 다음 쿼리를 사용할 수 있습니다:

SELECT order_id, customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id, order_date
                          ORDER BY order_id) AS row_num
FROM orders

이 쿼리에서 ROW_NUMBER() 함수는 PARTITION BY 절과 함께 사용되어 고객 및 주문 날짜별로 주문을 그룹화합니다. 그런 다음 함수는 주문 ID를 기준으로 각 파티션 내의 각 행에 고유 번호를 할당합니다.

결과 테이블에는 할당된 행 번호를 포함하는 “row_num”이라는 추가 열이 있습니다. 그런 다음 row_num이 1보다 큰 행을 선택하여 중복을 식별할 수 있습니다.

PARTITION BY 절과 함께 ROW_NUMBER() 함수를 사용하는 것은 SQL 데이터베이스에서 중복을 찾는 강력한 도구가 될 수 있습니다. 특정 기준에 따라 행을 파티션으로 그룹화하면 함수가 각 파티션 내의 각 행에 고유 번호를 할당하여 중복을 더 쉽게 식별할 수 있습니다.

ROW_NUMBER() 함수와 함께 CASE 문 사용

SQL에서 ROW_NUMBER() 함수는 결과 집합의 각 행에 고유한 순차 번호를 할당하는 데 사용됩니다. 이 함수는 CASE 문과 함께 사용하여 테이블의 중복 레코드를 식별할 수 있습니다.

CASE 문을 사용하면 결과 집합의 각 행에 조건부 로직을 적용할 수 있습니다. CASE 문 내에서 ROW_NUMBER() 함수를 활용하면 각 레코드의 발생 횟수를 결정할 수 있습니다.

예를 들어, 다음 SQL 쿼리를 사용하여 “first_name” 및 “last_name” 열을 기준으로 “users” 테이블에서 중복 레코드를 찾을 수 있습니다:

SELECT *,
CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY id) > 1
    THEN '중복'
    ELSE '고유'
END AS duplicate_status
FROM users;

위 쿼리는 “first_name” 및 “last_name” 열로 결과 집합을 파티션하고 “id” 열로 행을 정렬합니다. ROW_NUMBER() 함수는 각 파티션 내의 각 행에 고유한 순차 번호를 할당합니다.

그런 다음 CASE 문은 ROW_NUMBER() 값이 1보다 큰지 확인합니다. 그렇다면 해당 행은 중복으로 식별됩니다. 그렇지 않으면 행은 고유로 식별됩니다.

결과 집합에는 각 행에 “중복” 또는 “고유”를 표시하는 “duplicate_status”라는 추가 열이 포함됩니다.

위 예에서 행 1, 2, 4는 고유로 식별되고, 행 3과 5는 “first_name” 및 “last_name” 열을 기준으로 중복으로 식별됩니다.

CASE 문 내에서 ROW_NUMBER() 함수를 활용하면 SQL 테이블에서 중복 레코드를 쉽게 식별하고 관리할 수 있습니다.