한 아이디 당 여러 계정을 생성할 수 있다.
가장 최근에 접속한 계정만 살리고 나머지는 삭제하려 한다. (단, 한 아이디에 대해 계정 간 최근접속 날짜는 중복될 수 없다)
WITH T AS (
SELECT 'A' AS 아이디, 'a1' AS 계정이름, TO_DATE('2023-01-01','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'A' AS 아이디, 'a2' AS 계정이름, TO_DATE('2023-01-02','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'A' AS 아이디, 'a3' AS 계정이름, TO_DATE('2023-01-03','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'B' AS 아이디, 'b1' AS 계정이름, TO_DATE('2023-01-03','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'B' AS 아이디, 'b2' AS 계정이름, TO_DATE('2023-01-05','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'C' AS 아이디, 'c1' AS 계정이름, TO_DATE('2023-01-02','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'C' AS 아이디, 'c2' AS 계정이름, TO_DATE('2023-01-03','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'C' AS 아이디, 'c3' AS 계정이름, TO_DATE('2023-01-06','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'D' AS 아이디, 'd1' AS 계정이름, TO_DATE('2023-01-01','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'D' AS 아이디, 'd2' AS 계정이름, TO_DATE('2023-01-04','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'E' AS 아이디, 'e1' AS 계정이름, TO_DATE('2023-01-01','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'F' AS 아이디, 'f1' AS 계정이름, TO_DATE('2023-01-01','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'F' AS 아이디, 'f2' AS 계정이름, TO_DATE('2023-01-08','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'F' AS 아이디, 'f3' AS 계정이름, TO_DATE('2023-01-10','YYYY-MM-DD') AS 최근접속 FROM DUAL UNION ALL
SELECT 'F' AS 아이디, 'f4' AS 계정이름, TO_DATE('2023-01-11','YYYY-MM-DD') AS 최근접속 FROM DUAL
)
SELECT * FROM (
SELECT 아이디, 계정이름, 최근접속,
RANK() OVER(PARTITION BY 아이디 ORDER BY 최근접속 DESC) AS RN,
COUNT(아이디) OVER(PARTITION BY 아이디) AS CNT
FROM T
)
WHERE RN=1;
참고