SQL로 중복 데이터를 확인 및 삭제하는 법을 알아본다.

사용 데이터

idfirst_namelast_nameemail

출처 : MYSQLTUTORIAL (아래 Reference에 명시)

중복 데이터 확인

공통

GROUP BY와 HAVING을 사용하면 된다. 중복을 확인할 컬럼의 수에 따라 조건을 추가해주면 된다.

SELECT first_name, last_name, email, COUNT(*) as cnt
FROM contacts
GROUP BY email, first_name, last_name
HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 AND COUNT(last_name) > 1;

first_namelast_nameemailcnt

중복 데이터 삭제

중복 데이터를 삭제하기 위해서는 먼저 남길 데이터를 제외한 데이터를 구분할 컬럼(e.g. id)의 값을 구해야 한다.

MySQL

삭제할 데이터 id 확인

SELF JOIN을 통해 중복된 row의 id를 확인하는 방법이지만 결과를 보면 데이터가 오히려 중복되어 더 생기는 현상이 있다.

(삭제 쿼리에 활용할 경우, 문제는 없어보임)

SELECT t1.*
FROM contacts t1 JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

데이터 삭제 쿼리

MySQL에서 지원하는 DELETE JOIN 방식을 활용한다.

contacts(t1) 테이블에서 FROM 절 이후 JOIN된 결과에 해당하는 ROW들을 삭제한다는 의미이다.

DELETE t1 FROM contacts t1 
JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

PostgreSQL & MySQL 8

WINDOW FUNCTION을 지원하는 경우, ROW_NUMBER를 활용하여 처리할 수 있다.

PARTITION BY로 중복된 컬럼을 지정하고, ROW_NUM > 1을 설정하여 삭제할 데이터만 가져올 수 있다.

삭제할 데이터 id 확인

SELECT *
FROM ( SELECT id, first_name, last_name, email,
       ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
       FROM contacts ) a
WHERE row_num > 1
ORDER BY first_name; -- 단순 정렬 용도

idfirst_namelast_nameemailrow_num

간단한 데이터 삭제 쿼리

데이터가 적을 경우, 간단하게 IN 으로 해당 id들을 제거할 수 있다.

DELETE FROM contacts
WHERE id IN (SELECT id
             FROM ( 
                 SELECT id, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
                 FROM contacts 
                 ) tmp
             WHERE row_num > 1);

데이터 삭제 쿼리

PostgreSQL에서는 DELETE + USING 방식을 활용한다. 데이터가 많은 것을 고려할 경우, 이 방법을 먼저 사용해야 할 듯하다.

JOIN 과 ON 대신 USING과 WHERE이 사용된다.

DELETE FROM contacts t1 
USING contacts t2
WHERE t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email AND t1.id > t2.id;

속도가 느릴 경우

데이터가 너무 많을 경우, 일부분씩 나누어서 DELETE를 하는 것을 고려해볼 수 있다. (특히, 실 사용중인 DB인 경우)

위에서 설명한 삭제할 id를 미리 테이블로 만들어 DELETE JOIN에 사용하는 것이다. (JOIN 연산을 단순화한다.)

1. WORKOBJLCK       F4    엔터

2. 오브젝트 :  GKGGINF (TABLE명)

3. 라이브러리 : NDYKLIB

4. 오브젝트 유형 : *FILE            엔트

5. 사용중인 사용자가 나타나는데 옵션에 4(종료)시킨 후 필드 추가한다. 끝

DB2 

아 정말 어려운 Database 어떻게 보면 쉽긴하

나 UI 자체가 없다

1. wrkactjob - 활성화된 작업
2. wrksyssts - 시스템 상태에 대한 작업
               CPU 사용량, 가상 메모리 사용량 등 확인
3. wrkcfgsts + F4 -> 장비유형 : *dev
                     장비확인
   wrkcfgsts > type : *lin -> 링크상태 확인            
4. WRKSPLF
5. strpdm -> 3(멤버에 대한..) -> File : SRCDDS(DB)
                                        SRCCBL(소스) -> 5
                                        SRCPRT(출력프로그램) -> 19
                                        SRCDSPF(화면) ->17
                                        SRCCLP(파라미터사용)
                                        SRCMMU(메뉴)
6. GO DPS000 -> 영업
      DYP000 -> 생관
      DPQ000 -> QC
      DYA000 -> 개발
      DYW000 -> 원가
      DMM000 -> 자재
7. 화면 출력 : Prt Scr -> 2 -> Device명 변경 -> 상태 확인 -> 6(해제)
   PLANCDPF, NAPPLANPF, NAPCONTPF
8. 파일이 가득찼을 경우 파일사이즈 늘리기 : I(해당 사이즈 만큼 증가)
                                         9999(파일 사이즈 무제한)
9. 사용자 권한 Allobj 주기 : wrkusrprf -> 2.change -> special authority 찾는다.
                             해당항목 제일 마지막에서 * 를 +로 변경, 엔터
                             allobj 입력, 추가
10. call dps900 => 서열관리(서열 seq 조회, top-seq 조회, 수정 등) - HMC 재고수량이 변경되지 않을 경우
                  seq no 확인한다. 중간에 빠진 번호가 있으면 넘어가지 않음.
11. 웹으로 사용하기 => http://xxx.xxx.xxx.xxx:xxx/HATSLE
12. 급여봉투 출력 (NVH001CLP)
   1) 접속해서 바로 명령창에 call nvh001clp 입력 후 F4 누름
      -> Lib : nvhclib
         Parameter : VB프로그램에서 인자로 넘기는 변수값 붙여넣기(sRtn_data)
      -> 실행 후 splf 에서 확인
   2) strpdm -> 3 -> file : SRCCBL
                     lib : nvhclib
      -> nvh001clp -> 명령창에 call nvh001clp 입력 후 F4 누름
      -> Lib : nvhclib
         Parameter : VB프로그램에서 인자로 넘기는 변수값 붙여넣기(sRtn_data)
      -> 실행 후 splf 에서 확인

13. UPDDTA -> 데이터 수정 명령어

14. NONSYS 백업 
   1) 서브작업 중지 : endsbs -> *all, 1초
   2) savlib + F4
      - LIB : *NONSYS
      - DEV : TAP01
      - End of media option : *UNLOAD
   3) 서브작업 시작 : strsbs QCTL

데이터 품질 진단, 데이터 품질이? (#DQ.01)

데이터 품질이란?

요즘과 같이 데이터가 넘쳐나는 세상에서 데이터의 품질은 매우 중요한 부분이다.

그렇다면, 데이터 품질은 어떤 개념을 이야기 하는것 일까?

DB에는 데이터가 있다. 누군가는 그 데이터를 이용해서 서비스를 받거나, 제공하고 있다.

서비스를 제공하는 사람은 데이터를 입력 또는 가공 하는 행위를 하며, 서비스를 받는 사람은 데이터 값을 이용해서 유의미한 정보를 제공 받는 쪽에 있다.

여기에서 그 데이터의 품질, 즉 퀄리티는 당연히 좋아야 한다.

데이터 품질이 곧 서비스의 성패를 가르게 되는것이다.

그럼 데이터 품질에 대해서 간단한 예를 들어보자.

위와 같은 사원목록 테이블이 있다.

회사의 사정을 잘 모르는 이가 봤을때도 문제가 있는 데이터 두개를 발견할수 있다.

박지원, 허생원의 생일 데이터를 확인 해볼까?

일반 상식 적으로 생각 했을때 생일이 1900-01-23, 2025-03-04 일 수 있을까?

지금은 2020년 이다.

지금 나이가 121살인 사람과 3년뒤에나 태어날 사람이 사원목록에 있는건 말이 안된다.

여기서는 예를 사원의 생일로 따졌지만, 어떤 특정 물품의 판매개시일 이거나, 판매일자이거나 데이터가 생성된 날, 혹은 통계적인 데이터에서 저렇게 잘못된 날짜를 쓰는 데이터가 있는것은 큰 문제가 될 가능성이 아주 높다.

저렇게 상식을 벗어난 데이터들이 우리가 이용하는 서비스의 전체적인 데이터 품질을 망치고 있는것 이다.

또 하나의 예를 들어 보겠다.

위와 같이 국가코드테이블이 있고, 거래처목록이라는 테이블이 있다.

국가코드에는 대한민국, 미국, 호주, 필리핀만 등록이 되어 있다. 그리고 거래처목록 테이블을 보면 각 회사별 거래처 국가코드가 입력되어 있다.

여기서 우리가 주목해야될 데이터는 U사의 거래처국가코드값 JPN이다.

이 값은 국가코드테이블에 없는 코드값 이다. 이것 역시 데이터 품질의 오류 이다.

물론 JPN을 국가코드테이블에 등록 하면 되지만, 그건 개선책일 뿐, 그전까진 그냥 오류데이터 이다.

이건 서비스에서 어떤 문제를 만들수 있을까?

거래처를 등록하는 화면에 국가 목록이 리스트박스로 되어 있다고 한다면, JPN을 입력할수 없는 문제가 생길수 있다.

(사실 입력할수 있다고 된다면 그건 더 큰 문제가 있음을 뜻한다. 메타 데이터가 관리 되고 있지 않고, 소스에서 하드코딩 되어 있다는 의미일 수 있기 때문이다.)

그리고 두번째는 거래처 상세를 조회하는 화면에서 해당 거래처 국가명이 표시 되지 않을수 있다.

그 외에도 여러가지 오류 가능성을 가지고 있다. 코드 관리는 어디서나 매우 중요한 일이 아닐 수 없다.

현장에서 보면 이렇게 코드관리가 되지 않고 쓰이는 데이터들이 많이 있다. 이런건 보통 하드코딩으로 때우거나, 다수가 알지 못하는 또 다른 코드목록 테이블이 존재 한다거나..

이러한 데이터들은 DB 공간을 낭비하기도 하며, 조회 성능을 떨어뜨리기도 한다.

자, 그럼 어떻게 하면 이러한 데이터들을 효율적으로 잘 관리 할 수 있을까?

데이터 품질 진단

우리는 위와 같은 현상들, 사실 훨씬 더 많은 케이스가 있으나 간략하게 안내 했을 뿐이다. 이러한 케이스들이 얼마나 있는지를 먼저 알아야 한다.

그걸 알기 위해서 데이터 품질 진단을 시행 하여야 한다. 우리가 사용하는 DB에 어떤 데이터 들이 있고, 이 데이터들이 어떻게 관리되고 있으며, 상태가 어떤지 알아야 한다.

위와 같이 코드와 매핑이 안되는 데이터들, 그리고 날짜의 어떤 범위를 벗어난 데이터들이 어디에 얼마나 있는지 확인 하는 작업을 데이터 품질 진단 이라고 한다.

데이터 품질 진단는 4~5년전부터 공공기관 및 대기업을 시작으로 지금까지 매년 시행이 되어 오고 있다. 그로 인해서 7~80%에 머물던 유효데이터 비율이 95% 이상으로 올라오기도 한 사례들이 있다.

사실 특정 공공기관을 지칭하진 않겠지만 내가 직접 진단했던 기관은 데이터 오류율이 30%넘게 나오는 경우가 많이 있었다. 놀랍지 않은가? 민간 기업은 그보다 조금 좋은 수준이지만 15~20% 가량의 오류율을 보인다.

이렇게 데이터 품질 진단을 통해서 데이터 품질을 알았다고 한다면 이젠 무엇을 해야 하나?

바로 개선이다.

데이터 품질 개선

데이터 품질 개선이란 데이터 품질 진단을 통해 나온 오류들을 개선해 가는것 이다.

위에서 예를 들었듯이 생일 데이터가 오류면 그것을 정상적인 날짜로 확인하여 변경한다고 데이터 품질 개선이 끝나는게 아니다.

왜? 이런 데이터가 들어 왔는지를 확인 해야 한다.

DB에서는 테이블에 여러가지 제약조건을 걸어 데이터 품질을 기초적인 수준에서 관리 할 수 있다. 하지만 그게 만능 열쇠가 되지는 못한다.

앞에서와 같이 생일 데이터를 예를 들어보자, 이 데이터가 어떻게 입력이 되는지 확인을 해야 할 것이다.

누군가가 사원정보를 입력하는 화면에서 타이핑으로 입력을 하는지, 아니면 개개인이 알려준 정보를 누군가가 종이에 적어 직접 DB 클라이언트로 UPDATE SQL을 만들어 작업을 하는건지 확인을 해야한다.

만약 전자라면, 이것의 입력 방법을 리스트 박스로 선택 하여 입력하게끔 한다던지, 아니면 신분증이나 다른 신원조회 DB의 데이터를 API로 받아 넣게 한다던지의 방법으로 입력 오류를 최소한으로 하게끔 바꿔줘야 한다.

후자의 방법이라면 사원 정보의 전산 입력 시스템을 만들어야 할 것이다.

어떤 데이터는 기계의 센싱값이 저장 된다고 해보자. 이것의 오류가 있었다면 그 기계에 문제가 있는것 인지, 아니면 그 데이터를 가지고오는 프로그램의 문제가 있는것 인지를 확인 하고 그 원천을 개선 해야 데이터 품질 개선이 이루어 졌다고 말 할 수 있을것 이다.

요즘, 많은 기관에서 데이터 품질에 대한 관심도가 높아져 간다. 공공기관 뿐 아니라 민간 기업, 그리고 이젠 시작하는 신생 스타트업 기업들에서도 데이터 품질에 대한 중요성을 이해하고, 그것을 지켜가려고 한다.

데이터 품질이라는것은 하루아침에 만들어 지지 않는다. 데이터 품질이 잘 관리 되려면 화면개발, DB 표준화 설계, 메타데이터 관리 등 선행 되어야 할 것이 많이 있다. 

DBA로 일을 하건 개발자로 일을 하건 이런 데이터 품질에 대한 관심을 가지고 SI 업무를 대한다면 조금은 우리가 서비스의 질을 향상 시킬 수 있지 않을까? 이런 고민을 한번쯤 해봐야 한다.

=====================================================================
1. 테이블을 생성하면서 테이블의 데이타 복사
select * into 생성될테이블명 from 원본테이블명
=====================================================================

2. 테이블 구조만 복사하겠다면
select * into 생성될테이블명 from 원본테이블명 where 1=2
=====================================================================

3. 테이블이 이미 생성되어 있는경우 데이타만 복사
insert into 카피될테이블명 select * from 원본테이블명
=====================================================================

4.특정 데이타만 복사 하겠다면
insert into 카피될테이블명 select * from 원본테이블명 where 검색조건
=====================================================================

5.다른 테이블조인해서 UPDATE 하기
UPDATE 엡데이트 테이블 명
SET 업테이트할 컬럼 = 업데이트 가져오는 컬럼
FROM 엡데이트 테이블 명 A, 다른테이블 명 B 
WHERE 조인조건
=====================================================================

6. 프로시저 검색 
SELECT * FROM INFORMATION_SCHEMA.ROUTINES

-- 최대 동시 접속자 수

SELECT * FROM V$Resource_limit;
processes : 프로세스 갯수 (백그라운드 +dedicate process)

session : 동시접속 세션 수

current_utilization : 현재 접속 카운트

max_uitilization : 오라클을 시작한 이애로 최대 접속했을 때 피크 수

initial_allocation : init.ora 파라메터에서 설정한 수치

-- 현재 접속된 세션수와 전용서버 방식으로 연결한 세션수, 백그라운드 세션 수, active 세션 수를 알 수 있는 쿼리

SELECT Count(*) total_cnt, Count( DECODE( server, 'DEDICATED', 1, null)) dedicated_cnt,

Count( Decode (TYPE, 'BACKGROUND', 1, null)) back_cnt,

Count( DECODE( Status, 'ACTIVE', 1, NULL)) active_cnt

FROM V$SESSION;

--1. Buffer Cache Hit Ratio

SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;

--2. Library Cache Hit Ratio

SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;


--3. Data Dictionary Cache Hit Ratio

SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;

 

-- 테이블 스페이스 사용량

SELECT a.tablespace_name,
             a.total "Total(Mb)",
             a.total - b.free "Used(Mb)",
             nvl(b.free,0) "Free(Mb)",
             round((a.total - nvl(b.free,0))*100/total,0)  "Used(%)"
from    (   select     tablespace_name,
                            round((sum(bytes)/1024/1024),0) as total
               from       dba_data_files
               group by tablespace_name) a,
         (     select     tablespace_name,
                             round((sum(bytes)/1024/1024),0) as free
               from        dba_free_space
               group by  tablespace_name) b
where      a.tablespace_name = b.tablespace_name(+)
order by   a.tablespace_name;

 
--오라클서버의 메모리

select * from v$sgastat

select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool



--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
--and a.spid = '675958'
order by c.PIECE

 
 
--cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE



--프로세스 아이디를 이용하여 쿼리문 알아내기

select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE

  

--세션 죽이기(SID,SERAIL#)

--ALTER SYSTEM KILL SESSION '8,4093'

--오라클 세션과 관련된 테이블*/

--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'

 

--현재 커서 수 확인

SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC

SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC

select * from v$session_wait

select sid, serial#, username, taddr, used_ublk, used_urec
 from v$transaction t, v$session s
 where t.addr = s.taddr;

select *  from sys.v_$open_cursor



--V$LOCK 을 사용한 잠금 경합 모니터링

SELECT s.username, s.sid, s.serial#, s.logon_time,
    DECODE(l.type, 'TM', 'TABLE LOCK',
          'TX', 'ROW LOCK',
       NULL) "LOCK LEVEL",
    o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL

 

--락이 걸린 세션 자세히 알아보기

select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'

 

--락이 걸린 세션 간단히 알아보기

select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
       a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
  and b.id1 = c.object_id
  and b.type = 'TM';

select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'

 
--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다
--kill -9 프로세스아이디

select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다

ALTER SYSTEM KILL SESSION '11,39061'

 

 

alter session으로 죽지않는 프로세스 죽이기

1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys

5.ALTER SYSTEM KILL SESSION '137,1723'