만료일을 기준으로 중복된 레코드를 선택적으로 삭제하는 SQL

만료일을 기준으로 중복된 레코드를 선택적으로 삭제하는 SQL

다음과 같은 MariaDB 테이블이 있습니다.

userid	사용자 이름	이메일	만기일
1	잭드	jackd@example.com	2018-10-09
2	질프	jillf@example.com	2022-12-19
3	아론	aaron@someone.com	2022-09-29
4	아론1	aaron@someone.com	2021-12-19
5	jackd2	jackd@example.com	2017-11-03
6	jackd3	jackd@example.com	2019-10-09
7	심드	simd@somewhere.com	2023-03-13
8	심드비	simd@somewhere.com	2024-10-09

사용자는 고유한 사용자 이름을 사용하여 로그인할 수 있습니다.다른 열은 고유하지 않습니다.특히 사용자는 서로 다른 사용자 이름을 사용하여 여러 계정을 가질 수 있지만 각 계정은 동일한 전자 메일 주소를 가질 수 있습니다.대부분의 사용자는 하나의 사용자 이름만 가집니다.

우리는 사용자 이름이 아닌 이메일을 기반으로 로그인 시스템으로 이동하고 있습니다. 즉, 이메일은 이제 고유해야 하고 중복된 이메일이 있기 때문에 테이블을 정리해야 합니다.사용자를 전자 메일과 연결한다는 것은 사용자가 만료되지 않은 일부 계정, 만료된 일부 계정 및 이 두 속성의 조합을 가질 수 있음을 의미합니다.예를 들어, jackd@example.com 이메일을 사용하는 사용자는 3개의 계정이 모두 만료된 반면 aaron@someone.com 계정은 하나의 만료된 계정과 하나의 활성 계정을 가지고 있습니다. simd@somewhere.com 계정은 두 개의 활성 계정을 가지고 있습니다.만료된 것과 활성화된 것의 다른 조합이 가능합니다.

저는 다음을 수행하고 싶습니다.

• 모든 만료 날짜가 미래인 모든 중복 전자 메일 행을 보관합니다(예: simd@somewhere.com 의 경우 두 행 모두 보관).
• 전자 메일이 만료된 행과 활성 행의 혼합과 연결된 경우 만료된 행만 삭제합니다(예: arron@someone.com 의 경우 첫 번째 행을 유지하고 두 번째 행을 삭제).
• 전자 메일이 만료 중인 모든 행과 연결된 경우에는 최신 행( 만료 날짜 기준)을 유지하고 나머지 행을 삭제합니다(예: jackd@example.com 의 경우 마지막 행을 유지하고 처음 두 행을 삭제).
• 중복된 전자 메일 행만 대상으로 지정해야 합니다.

엔드 테이블은 다음과 같습니다.

userid	사용자 이름	이메일	만기일
2	질프	jillf@example.com	2022-12-19
3	아론	aaron@someone.com	2022-09-29
6	jackd3	jackd@example.com	2019-10-09
7	심드	simd@somewhere.com	2023-03-13
8	심드비	simd@somewhere.com	2024-10-09

제가 생각할 수 있는 모든 것을 시도했지만, 저는 계속 실패하고 있습니다.어떤 도움이라도 주시면 대단히 감사하겠습니다.감사합니다!

CTE를 사용하여 각 사용자의 만료 날짜 순서를 결정하고 해당 사용자가 만료되지 않은 계정을 가지고 있는지 확인하여 원하는 결과를 얻을 수 있습니다.MIN논리적 값의 값은 다음과 같습니다.AND그런 다음 해당 CTE에서 계정이 만료되지 않은 행을 선택하거나, 전자 메일에 만료된 계정만 있는 경우 계정의 만료 날짜가 최신입니다.

WITH rns AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY expirydate DESC) AS rn,
         MIN(expirydate < CURDATE()) OVER (PARTITION BY email) AS allexpired
  FROM users
)
SELECT userid, username, email, expirydate
FROM rns
WHERE expirydate >= CURDATE()
   OR allexpired AND rn = 1
ORDER BY userid

출력(표본 데이터의 경우):

userid  username    email               expirydate
2       jillf       jillf@example.com   2022-12-19
3       aaron       aaron@someone.com   2022-09-29
6       jackd3      jackd@example.com   2019-10-09
7       simd        simd@somewhere.com  2023-03-13
8       simdb       simd@somewhere.com  2024-10-09

dbfidle 데모

테이블에서 원하지 않는 레코드를 삭제하려면 다음을 사용합니다.DELETEMariaDB 10.4 이상을 실행하는 경우 CTE를 사용합니다(MariaDB 10.3 이하에서는 작동하지 않는 버그가 있음).

DELETE users FROM users
JOIN (
  WITH rns AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY expirydate DESC) AS rn,
           MIN(expirydate < CURDATE()) OVER (PARTITION BY email) AS allexpired
    FROM users
  )
  SELECT userid
  FROM rns
  WHERE expirydate < CURDATE() AND (NOT allexpired OR rn > 1)
) del
WHERE users.userid = del.userid

MariaDB 10.3 이전 버전의 경우 가장 간단한 해결 방법은 원하는 행이 있는 새 테이블을 생성하고 이전 테이블을 삭제한 다음 새 테이블의 이름을 이전 테이블로 변경하는 것입니다.이는 다음과일 것입니다.CREATE TABLE명령:

CREATE TABLE users2 (
  `userid` INTEGER,
  `username` VARCHAR(6),
  `email` VARCHAR(18),
  `expirydate` DATE
) AS
WITH rns AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY expirydate DESC) AS rn,
         MIN(expirydate < CURDATE()) OVER (PARTITION BY email) AS allexpired
  FROM users
)
SELECT userid, username, email, expirydate
FROM rns
WHERE expirydate >= CURDATE()
   OR allexpired AND rn = 1
ORDER BY userid

dbfidle 데모

언급URL : https://stackoverflow.com/questions/71863965/sql-for-selectively-deleting-duplicate-records-based-on-expiry-dates