미국 농무부 산하기관인 식품안전검사국(FSIS, Food Safety Inspection Service) 의 육류, 가금류 및 계란 생산업체 데이터로 알아보겠습니다.

여기서 최신 FIS 데이터도 다운받을수 있다고 하지만, 실습예제로 진행해보겠습니다.

데이터 가져오기

CREATE TABLE 테이블 생성
-> COPY 데이터 가져오기
-> CREATE INDEX 검색속도를 높이기위한 company열에 인덱스 생성

이제 이건 외웁니다.

CREATE TABLE meat_poultry_egg_establishments (
    establishment_number text CONSTRAINT est_number_key PRIMARY KEY,
    company text,
    street text,
    city text,
    st text,
    zip text,
    phone text,
    grant_date date,
    activities text,
    dbas text
);

COPY meat_poultry_egg_establishments
FROM 'path위치복사붙혀넣기'
WITH (FORMAT CSV, HEADER);

CREATE INDEX company_idx ON meat_poultry_egg_establishments (company);

데이터 확인하기

SELECT 행 갯수 확인
-> SELECT LIMIT 5 로 열 확인

SELECT count(*) FROM meat_poultry_egg_establishments;

SELECT * FROM meat_poultry_egg_establishments LIMIT 5;

결측값 확인하기

WHERE 컬럼명 IS NULL
참고로 WHERE * IS NULL 은 에러납니다.

SELECT *
FROM meat_poultry_egg_establishments 
WHERE city IS NULL
; 

일관성 확인하기

회사명이 동일한데 띄어쓰기나 쉼표 등으로 중복되어 다른명으로 표기된게 있는지 확인해보겠습니다.

SELECT 
	company, 
	count(*) 
FROM meat_poultry_egg_establishments 
GROUP BY company
ORDER BY company 
; 

회사명이 똑같은데 각각 count된 경우도 보입니다.

글자갯수 확인하기

우편번호의 경우 보통 숫자갯수가 정해져있습니다.
미국 우편번호 5개가 아닌 다른 갯수라면 잘못 표기된 경우겠죠.
length()로 글자수를 세줍니다.

우편번호 글자갯수별로 본다면 어떻게 해야할까요?
GROUP BY ~ 별로
length(zip) 우편번호 글자갯수

그렇다면 주별로 우편번호 글자갯수가 5개미만인 잘못표기된 rows갯수를 확인해보겠습니다.

주별로 GROUP BY st
카운트 count(*)
5개미만 WHERE

데이터 수정하기

테이블수정 ALTER TABLE

• 추가 : ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;

• 삭제 : ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

• 변경 : ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 SET DATA 바꿀데이터타입;

• 제약조건추가 : ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 SET NOT NULL; (이 경우 NOT NULL 제약조건 추가)

• 제약조건제거 : ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 DROP NOT NULL; (이 경우 NOT NULL 제약조건 삭제)

값 수정 UPDATE

UPDATE 테이블명 
SET 컬럼명1 = 값1 , 
    컬럼명2 = 값2 ,
    컬럼명3 = 값3 ; 이런식으로 계속 추가할수도 있음. 

한꺼번에 값 수정

띄어쓰기나 쉼표 등으로 회사명이 다르게 구분되는 경우가 있었습니다.
일괄적으로 동일한 값으로 수정해줍니다.

복사컬럼 추가 ALTER TABLE

기존 company 열을 복사한 새 열을 만들어줍니다.

-- 기존테이블에 새 컬럼 추가 
ALTER TABLE meat_poultry_egg_establishments ADD COLUMN company_copy text; 

-- 새 컬럼에 기존컬럼 복사 
UPDATE meat_poultry_egg_establishments
SET company_copy = company; 

키워드 필터링

Armour로 시작하는 값들을 수정하도록 해줍니다.

UPDATE meat_poultry_egg_establishments       -- 해당 테이블의 
SET company_copy = 'Armour-Eckrich Meats'    -- 추가 컬럼의 값을 '변경할 값' 으로 수정한다. 
WHERE company LIKE 'Armour%'                 -- 기존 컬럼의 값중 'Armour'로 시작하는 모든 값에 한하여 
RETURNING company, company_copy 			 -- 수정한 뒤 보여줄 컬럼 지정 
;

백업테이블 CREATE TABLE

• 기존 테이블을 복사하여 새 테이블 만들기

CREATE TABLE 새테이블명 AS
SELECT * FROM 기존테이블명;

• 잘 만들었는지 확인하기

SELECT
(SELECT count() FROM 새테이블명) ,
(SELECT count() FROM 기존테이블명) ;

CREATE TABLE meat_poultry_egg_establishments_2 AS 
SELECT * FROM meat_poultry_egg_establishments ; 

-- lows 갯수 동일 확인
SELECT 
	(SELECT count(*) FROM meat_poultry_egg_establishments_2), 
	(SELECT count(*) FROM meat_poultry_egg_establishments); 
    
SELECT * FROM meat_poultry_egg_establishments_2 LIMIT 5; 

결측값 채워넣기

UPDATE 테이블명
SET 채워넣을컬럼명 = 채워넣을값
WHERE 확인용컬럼명 = 확인용값

SELECT *
FROM meat_poultry_egg_establishments 
WHERE st IS NULL
;

UPDATE meat_poultry_egg_establishments
SET st = 'TEST'
WHERe establishment_number = 'V18677A' 
; 

데이터 삭제하기

행rows

테이블의 모든 행rows 제거

DELETE FROM 테이블명;

삭제할 행 지정

DELETE FROM 테이블명 WHERE 특정컬럼명;

또는
DELETE FROM 테이블명 WHERE 특정컬럼명 IN ('값1', '값2',,,)

열columns

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

테이블

DROP TABLE 테이블명;