[코드잇] SQL로 하는 데이터 분석 '강의를 수강하고 정리한 내용입니다.
목차
- 데이터베이스 기본 개념 및 테이블 생성
- 데이터 조회
- 데이터 분석
- 테이블 조인을 통한 깊이있는 데이터 분석
- 서브쿼리와 뷰를 활용한 유연한 데이터 분석
2. 테이블 조회
쿼리 실행 - ctrl + enter(드래그 후 실행시 드래그한 쿼리만 실행)
쿼리 전체 실행 - ctrl +shift +enter
SQL 작성법
- SQL문 끝에는 항상 세미 콜론 써줄것
- SQL 문 안에는 공백이나 개행등을 자유롭게 넣을 수 있음(문장 구분은 세미콜론 기준)
- SQL문의 대소문자 구분 문자 - 대문자 : 예약어, 사용자지정부분 - 소문
- 데이터 베이스 이름과 테이블 이름
- 데이터 베이스이름.테이블이름 - 데이터 베이스 이름 뒤에 점.을 붙이고 테이블 이름을 쓰는것 - 해당 데이터 베이스 안의 테이블을 가리키는 것
- SHEMAS 패널 에서 사용할 데이터 베이스 이름을 선택 - from 다음에 테이블 이름만 적어줘도됨
- SQL문 : USE 사용할데이터베이스명 ; - 맨 앞에 사용할 데이터 베이스를 선언하면 다음 쿼리 부터는 테이블 이름만 써도
SQL의 작성 순서 SF / WGH / OL
1. SELCET - 어떤 컬럼 가져올거야?
2. FROM - 어떤 TABLE에서 가져올거야?
3. WHERE - 어떤 ROW가져올거야? (행의 조건)
4. GROUP BY - 그룹 어떻게 형성할거야?
5. HAVING - 어떤 그룹이야?(그룹의 조건)
6. ORDER BY - 최종적으로 어떻게 정렬?
7. LIMIT - 몇개 제한으로?
SQL의 실행 순서
1. FROM : 어떤 테이블을?
2. WHERE : 어떤 ROW를? // TABLE과 ROW 먼저 지정
3. GROUP BY : 어떤 그룹으로?
4. HAVING : 어떤 조건의 그룹을?
5. SELECT : 어떤 컬럼들을? // SELECT로 다 가져온 후에 , 정렬+제한
6. ORDER BY : 어떻게 정렬을?
7. LIMIT : 최종 출력할 ROW중 어떤 ROW를?
[특정 패턴 조회하기]
문자열 패턴 매칭 (LIKE + 와일드 카드)
- LIKE : 문자열 패턴 매칭 조건을 걸기 위해 사용되는 키워드
- % : 임의의 길의를 가진 문자열(0자도 포함)
- _ : 한 자리의 문자
1. BETWEEN A and B(A부터B까지)
SELECT * FROM [테이블명] WHERE number BETWEEN 1 and 3;
SELECT * FROM [테이블명] WHERE number BETWEEN '2019-01-01' and '2019-08-31';
2. 이 중에 있는~ (IN)
SELECT * FROM [테이블명] WHERE text IN (1, 2, 3);
SELECT * FROM [테이블명] WHERE text IS NULL;
3. 와일드 카드 (임의의 길이의 문자열%, 한 글자를 나타내는 _)
SELECT * FROM [테이블명] WHERE text LIKE '서울%';
SELECT * FROM [테이블명] WHERE text LIKE '%서울%';
SELECT * FROM [테이블명] WHERE date LIKE '____-08%'; // 8월 데이터만 불러오는 쿼
SELECT * FROM [테이블명] WHERE email LIKE 'c___@%'; // 이메일 주소가 c로 시작하고 4글자
4. 같지 않음 (!=, <>)
SELECT * FROM [테이블명] WHERE text != 'male';
SELECT * FROM [테이블명] WHERE text <> 'male';
문자열 패턴 매칭에서 2가지 주의점
1. 이스케이핑(escaping) 문자 → \
역슬래쉬
%
앞에 \
역슬래쉬(백슬래쉬, backslash) 기호 → '문자로서의 %
'로 인식
예. SELECT * FROM test WHERE sentence LIKE ‘%%%’ → 두번째 %는 문자로 인식 , 냠%뇽 같은 걸 조회
(1) ‘
(작은따옴표) 이스케이핑 : LIKE ‘%\’%’ → i’m a teacher
(2) _
(언더바) 이스케이핑 : LIKE ‘%_%’ → Nice to meet you_^^!
(3)”
(큰따옴표) 이스케이핑 : LIKE ‘%%”\”%’ → “ Spyder man”
2. 대소문자 구분 문제
조건식에 소문자를 써도 대문자가 포함된 row도 함께조회
방법1 .MySql 기본설정을 바꿀 수는 있으나 권장되지 않음.
방법 2. BINARY ‘%g%’
→ 소문자 g가 포함된 문자열만 조회됨 예. good afternoon!
[ DATE 데이터 타입 관련 함수 ]
1. 연도, 월, 일 추출
(1)1992년에 태어난 회원 조회
SELECT * FROM [테이블명] WHERE YEAR(date) = '1992';
(2) 여름(6, 7, 8월)에 가입한 회원들만 조회하기
SELECT * FROM [테이블명] WHERE MONTH(sign_up_day) IN(6,7,8);
(3) 각 달의 후반부(15~31일)에 가입했던 회원들만 조회
SELECT * FROM [테이블명] WHERE DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31;
2. 날짜 간의 차이 구하기
DATEDIFF( 날짜 a, 날짜 b)
: 차이일 수를 알려줌
예: 가입일자가 2019년 기준 며칠 이후 인지확인
SELECT email, sign_up_day, DATEDIFF(sign_up_day, '2019-01-01') FROM 테이블명;
예. 오늘 날짜 기준으로 확인하기
SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM 테이블명;
예. 회원들이 몇살일때 가입했는지(가입일 - 생일 / 365)
SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday)/ 365 FROM member;
3. 날짜 더하기 빼기
DATE_ADD()
더하는 함수 DATE_SUB()
빼는 함수
예. 가입일 기준 300일 이후
SELECT email, sign_up_day , DATE_ADD(sign_up_day, INTERVAL 300 DAY) FROM 테이블명;
예. 가입일 기준 250일 이전의 날짜
SELECT email, sign_up_day, DATE_SUB(sign_up_day, INTERVAL 250 DAY) FROM 테이블명;
4. UNIX Timestamp 값
UNIX_TIMESTAMP
DATE 타입의 값을 Unix Timestamp(1970년 1월 1일 기준 얼마나 시간이 지났는지 확인)로 바꿔주는 함수
FROM_UNIXTIME()
Unix timestamp를 읽을 수 있는 datetime날짜로 바꾸는 함수
SELECT email, sign_up_day, UNIX_TIMESTAMP(sign_up_day) FROM member;
SELECT email, sign_up_day, FROM_UNIXTIME(UNIX_TIMESTAMP(sign_up_day)) FROM member;
- 날짜, 시간 관련 데이터 타입 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
- 날짜, 시간 관련 함수 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
[ 여러 개의 조건 걸기 ]
WHERE id = 1 OR id = 2
라고 적었어야할 부분에, WHERE id = 1 OR 2
라고 적어버리는 실수
AND, OR
SELECT * from member
WHERE gender ='m'
AND address LIKE '서울%'
AND age BETWEEN 25 and 29;
SELECT * FROM member
WHERE MONTH(sign_up_day) BETWEEN 3 AND 5
OR MONTH(sign_up_day) BETWEEN 9 AND 11;
AND와 OR간의 우선순위
- OR가 AND 보다 우선순위가 높음
- 연결 시에 로 먼저 실행할 조건에 ( )
SELECT * FROM member
WHERE (gender = 'm' AND height >= 180)
OR (gender ='f' AND height >= 170);
AND 조건이 여러개 → IN 으로 묶기
SELECT * FROM member WHERE age = 20 OR age = 30 OR age = 40;
→ SELECT * FROM member WHERE age IN (20, 30, 40);
[ 테이블 정렬하기 ASC, DESC ]
ORDER BY 컬럼명 DESC(내림차순), ASC(오름차순)
먼저 쓴 컬럼 기준으로 차례대로 정렬, 가입연도 → 이메일 컬럼 기준으로 정렬됨.
SELECT * FROM member
ORDER BY YEAR(sign_up_day) DESC , email ASC;
숫자형(INT 등)인 경우와, 문자열형(TEXT 등)에 따른 정렬
- 문자열형일 경우, 수의 크기가 아니라 한 문자씩 비교 → 120, 19, 230, 27 순으로 정렬
CAST(컬럼명 AS signed/decimal)
데이터 타입을 일시적으로 변환하는 함수
signed : 양수와 음수를 포함한 모든 정수 decimal :소수점이 있는 수 타입
SELECT * FROM member ORDSE BY CAST(data AS signed)
[ 데이터 일부만 추려보기 LIMIT ]
LIMIT n (row의 개수)
LIMIT m, n (row 0을 기준으로 하는 시작 row번호, row의 개수)
FROM - WHERE - ORDER BY - LIMIT 순
앞에서 10개 데이터까지만 보기 → LIMIT 10
SELECT * FROM member
ORDER BY sign_up_day DESC
LIMIT 10;
앞에서 9번째부터 2개의 데이터 보기 → LIMIT 8, 2 (row는 0부터 셈)
SELECT * FROM member
ORDER BY sign_up_day DESC
LIMIT 8,2;
'Data Science > Data Analysis' 카테고리의 다른 글
[A/B test] Ch6. 조직 운영을 위한 지표 | 목표지표, 동인 지표, 가드레일 지표 (0) | 2024.04.01 |
---|---|
[프로그래머스] SQL | Join 2문제 (0) | 2024.01.27 |
SQL로 하는 데이터 분석 | 1.데이터베이스 기본 개념 및 테이블 생성 (1) | 2023.12.21 |