25.10.22 10일차 [ SQL 기초 개념 강의_{주요 내장 함수_연산자,문자열length(),concat(),locate(),left()right(),lower(),upper(),replace(),trim(),format(),substring() | 수학 floor(),ceil(),round(),sqrt(),pow(),exp(),log(),PI(),sin(),cos(),tan(),abs(),rand(),std(),variance() | 시간 NOW
필자의 풀이 = 먼저 한국의 면적을 구하는 서브쿼리를 생성한 이후 where 조건구문에 국가면적/한국면적 >1 인 조건을 추가했다.강사님 답 : 같은 풀이이지만 탭이나 별칭 등 조금 더 깔끔해보인다. 디테일도 중요
문자열
데이터가 문자열이면 대상이 됨
length()
문자열의 길이 반환
한굴은 깨지니 주의, 4c를 보면 공백도 입력되는것을 볼 수 있다,1, 도시 이름 2. 도시 이름의 길이[ len(city.`name`) 3. 인구수 4. 인구수 숫자의 길이
문자열뿐 아니라 수치형 데이터의 길이도 구할 수 있다.
concat()
나열한 문자열(수치 포함)을 모두 결합
하나의 문자열로 생성 -> 전처리
단, 하나라도 결측치(NULL)이 있으면 모두 NULL임
1c열에서는 세 문자열을 이어주는것을, 2c열에서는 한개의 값이 NULL이라서 총 결과셋 또한 NULL로 추출된다.
CONCAT( 컬럼, '-' , 컬럼) 으로 이름-인구수로 추출된다. 이때 컬럼명은 SPEC으로 별칭 부여
locate()
문자열 내에서 특정 문자열이 처음 등장하는 위치를 반환함.
시작 위치는 1부터 출발
0은 없다. -> 파이썬 인덱스랑은 다르다
1행은 w가 1번에, 2행은 or이 2번에있고 'world'에 z는 없어서 0으로 출력된다,
1. city테이블에서 se로 시작하는 도시를 찾는 locate()함수를 썼다. 2. 1 <= loc <= 3 위치값을 가진 도시를 추출하는 조건구문 having을 추가했다.3. order by로 정리
주의사항 -> 이때 1 <= loc <= 3은 안된다, sql은 우리가 생각하는대로 처리하지 않기 때문에 between을 사용하거나 1<= loc and loc <= 3 으로 나눠서 입력해야한다.
left() right()
왼쪽 | 오른쪽 기준에서 특정 개수 추출
left = 왼쪽에서 3글자만, right = 오른쪽에서 3글자만 출력된다,
가운데 `name`칸을 보면 어디가 잘리고 추출되었는지 한 눈에 알 수 있다.수치열 데이터 또한 원하는 글자만큼 추출할 수 있다. 이때 추출값은 문자열로 나온다.
lower() upper()
대소문자 변환
맨 왼쪽 `name`행을 기준으로 lower와 upper의 차이를 비교해볼 수 있다. 이때 포인트는 test용으로 추가한 출력물이 컬럼에 추가되어서 추출되는것
추후 다른 컬럼을 생성하는것은 중요한 개념으로 다시 등장한다.
replace()
문자열을 대체
왼쪽 데이터는 인구수, 오른쪽은 인구수 데이터에서 0 을 *로 대치시킨것, 문법을 주의깊게 볼것
trim()
공백 제거
앞, 뒤, 양쪽, 특정문자 제거 등 다양한 기능을 수행할 수 있다, -> 전처리개념
지정자
both : 양쪽에 존재하는 특정문자 제거
leading : 앞쪽에 존재하는 특정문자 제거
trailing : 뒤쪽에 존재하는 특정문자 제거
trim은 양쪽에 있는 모든 공백을 제거, 문자열 사이는 내비둠, leading, trailing, both의 차이를 데이터를 통해 생각!
1. city테이블에 있는 컬럼 중 `name`들의 첫 글자중 S를 TRIM(LEADING)을 이용해서 삭제함 2. 근데 어디서 할거냐? 도시명이 S로 시작하는 데이터들만 골라서할거다 = LIKE 'S%' 처음에 배운 내용
format()
포맷팅
숫자 타입의 형식 변화 시 사용(세 자릿수마다 콤마 삽입)
포맷팅을 사용하면 세 자릿수 마다 콤마가 삽입된다. 이 때 포맷팅 함수의 뒤에 붙는 인자는 소수점 몇자리까지 표현할것이냐는 의미이다,
인구수를 포맷팅하면 콤마가 추가된다, 왼쪽의 인구수 데이터는 사실 콤마만 표기되어있을뿐 수치형인데 이를 문자열로 대체시켜준다.
substring()
문자열 자르기 (특정 위치에서 특정 길이만큼)
공백 추가(버전마다 작동 안 될수도 있음)
2번에서부터 2글자만 자른 데이터 추출
나라 이름에서 앞에서 1번째에서 3글자씩만 잘라서 추출한 데이터이다
수학
floor() ceil() round()
내림, 올림, 반올림
floor = 내림 ceil = 올림 round = 반올림 ceiling = 소수점이 존재하기만 하면 1을 올림한다, ceil 랑 비슷한듯?
sqrt() pow() exp() log()
sqrt() : 양의 제곱근
pow(a,b) : a는 밑수, b는 지수 => a의 b 거듭제곱
exp() < - > 역함수 < - > log()
exp() : e의 거듭제곱
log() : 자연로그값 계산 - 데이터 분포 조정
sqrt = 4의 제곱근 2 , pow = 2의 3제곱 8 , exp와 log는 같이 움직인다고 하는데 문과라서 잘 모르겠다.. exp(3)의 값을 log에 넣으면 3이 나온다고는 한다
pi() sin() cos() tan()
삼각함수 , 파이
GPS 데이터를 기준으로 직선거리 계산 시 활용할 수 있다
기타 데이터 전처리시 사용
PI = 3.14 원주율 , 나머지는 모른다...수학공부 필요함
ABS() RAND() STD() VARIANCE()
ABS() : 절대값
RAND() : 난수 0.0 ,= X <= 1.0
데이터의 퍼짐 설명
STD() : 표준편차
VARIANCE() : 분산
ABS = -1의 절대값 1을 출력 , RAND 0.0 <= X <= 1.0 사이의 무작위 수
난수에 * 10 을 한 후 반올림처리를 통해 정수인 난수를 추출하는 쿼리이다, 밑의 쿼리는 1부터 100까지의 난수를 추출하려고 쿼리를 작성한 것, 0 ~ 99까지의 수에서 + 1 을 통해서 1 ~ 100까지의 난수를 받는다.두번쨰 쿼리 실행값
STD와 VARIANCE 연습 쿼리, 표준편차가 낮을수록 도시간 인구 차이가 작다는 사실만 알았다, 국가 코드, 표준편차, 분산, 각 나라별 도시 수를 출력한 쿼리이다. 그 중 50개 이상 도시를 가진 국가만 추출하였고 오름차순으로 정리하였다.
시간
NOW() CURDATE() CURTIME()
현재 날짜 + 시간
NOW() = CURDATE() + CURTIME()
YYYY-MM-DD HH:MM:SS
CURDATE() , CURTIME, NOW를 출력한 결과셋이다. 둘을 합친게 NOW이다.
년-월 , 시 : 분 만을 추출한 데이터이다, LEFT를 사용해서 일과 초를 날렸다.
DATE() MONTH() DAY() HOUR() MINUTE() SECOND()
현재 월, 일, 시, 분, 초를 출력해준다
MONTHNAME() DAYNAME()
월의 이름과 요일의 이름을 추출한다.
DAYOFWEEK() DAYOFYEAR() DAYOFYEAR()
이번 주에서 며칠이 지났는지, 이번달에서 며칠이 지났는지, 올해 며칠이 지났는지 각각 표기해준다.
기준 시간, 요일, 월별 세부 시간 정보 추출
시간 | 요일 | 계절 | 월 단위별 통계 분석에 용이한 함수
DATE_FORMAT()
전달받은 날짜 정보와 형식에 맞춰서 포멧팅 (혹은 생성)
하나하나 대입해서 해석!
시간 차이 계산
datediff()
회원 가입일로부터 현재까지 경과된 일 수 계산
최초 구매 이후 다음 구매까지 소요될 일 수 계산
datadiff( 현재 , 특정날짜 ) 문법으로 사용하면 된다, 이 때 순서가 바뀌면 음수로 계산되는데 abs를 통해 절대값으로 변경해주면 해결된다.
형변환
타입 변환
문법
cast ( 원본값 as 타입명 | 표현 )
or
date, json, signed, unsigned, char(xx) 가능
1. 문자열 => 수치 2. 수치 => 문자열 3. 수치 => 시간 4. 문자열 => 시간 모두 가능하다!
랭킹
row_number()
정렬된 순서대로 랭킹 부여
동점 x 동점도 정렬된 순서대로 배치
rank
동점 순위 존재
그 개수만큼 다음 순위는 생략됨
ex : 9등, 9등, 11등, 11등, 11등, 14등
dense_rank
동점 순위 존재
그 다음 순위는 순서대로 부여함
ex : 9등, 9등, 10등, 10등, 10등, 11등
랭킹을 위해 다른 데이터를 가져왔다. 10번부터 본다면 어떤 차이가 있는지 명확하게 판단할 수 있다.
partition by 컬럼 컬럼을 대상으로 그룹화하여 각 컬럼의 그룹별 랭킹(개발팀, 디자인팀, 분석팀. 지역)등등의 순위를 매김
지역별로 그룹화하여 부산의 남자가 1등이고 여자는 2등인것을 볼 수 있다. rank와 dense_rank가 결과값이 다르며밑으로 가면 다시 대구부터 순위가 1등으로 나눠져 지역별로 순위가 달라진것을 볼 수 있다.
범위지정
특정 컬럼의 값을 기준(범위기준) 데이터를 나눠서 그룹화 전략
특정 집단을 구분할 수 있는 컬럼을 결과셋에 추가하는 개념( 범주형 값으로 세팅) ex : 1 < x < 2 등등
데이터마트 구성 시 view 구성할때 종종 등장함
요구사항, 요구사항을 보고 어떤 순서로, 어떤 기능을 사용해서 데이터를 추출할지 정해야 한다.이전 between 파트에서 나온 case when ~ then when ~ then else ~ end 문법. 강사님이 주신 컬럼 파트, 동시에 조건문을 입력해서 서브쿼리로도 사용할 수 있어보인다.
우선 홍콩 면적, 한국 면적은 일단 서브쿼리가 아니고 값을 구해서 정수로 입력하기로 했다.
홍콩 면적 = 1075, 한국 면적 = 99434
그 이후는 지금까지 배운 내용을 토대로 간단히 구성할 수 있다.
1. 먼저 결과셋에 국가코드, 이름, 면적, sa_flag ( 이는 case를 이용해서 새롭게 만들어낸 그룹이다) sa_flag는 1) 홍콩보다 작은 2) 홍콩과 한국사이 3) 한국보다 큰 이렇게 세 가지의 그룹을 담고있는 컬럼이다.
2. 네 가지 결과셋을 country 테이블에서 출력하고 면적으로 내림차순으로 정리한다.
이렇게 기존에 없던 컬럼을 범위지정으로 추가로 그룹화하여 또 하나의 집단을 만들었다.
홍콩 < x < 한국인 나라들은 이렇게 출력된다.
실습
이 또한 순서대로 생각하면 된다.
1. 총 국가수, 평균, 최소, 초대 값을 입력하려면 group by를 사용해야 한다. 2. 그 이후 select 결과셋에 각 데이터들을 입력해준다.
select에 입력된 결과셋 데이터
count와 같이 sql이 움직이기 때문에 count와 서브쿼리를 이용해서 총 국가수 조건구문을 만들었다. 나머지는 배운 내용
그 이후 from country와 group by로 그룹으로 묶고 테이블을 지정해주고 마지막엔 면적평균으로 오름차순으로 정렬하였다.
결과셋
하하하
점점 갈수록 복습해야 할 내용이 많아지고 있다.
어제 늦게까지 복습한 보람이 있다.
오늘은 중간중간에 실습개념으로 몇가지 기능을 섞어서 결과값을 도출하라고 하셨는데 모두 다 푼건 아니지만 몇개는 풀었다.
화요일엔 거의 1개~2개 빼고는 다 못풀고 어디서부터 손을 대야할지 감도 못잡는 경우가 대부분이었는데.
나는 쿼리 실행을 눌렀을때 오류가 뜨는게 당연한 사람이었어서 내 쿼리가 작동할때 그 기분은 정말 말로 표현할 수가 없다.
약간의 성취감에 도파민이 터지는 하루였다.
월요일 회식 이후 사람들이 조금 말이랑 인사를 하기 시작했다.
나는 완전 E라서 이런 현상이 너무나 반갑다
강의장 들어갈때 나만 크게 모두한테 인사한다 ㅋㅋ 다른사람들은 안 하는거같은데
그리고 점심시간에도 이제 밥 먹으면서 얘기도 좀 하고 다른 사람은 하루에 한 명씩 다른사람이랑 밥 먹기 챌린지를 시작했다 ㅋㅋ
그렇게 밥 먹고 야외 9층 정원으로 데려오는게 루틴이 될거같다, 나는 도시락을 싸들고 다녀서 혼자 빠르게 먹고 야외 정원에서 맨날 앉아있었는데 그 친구가 오늘 야외정원 소개시켜준다고 데리고 와서 다른 사람이랑 스몰토크를 가졌다
이제 슬슬 말도 트고 하니 물어보거나 이야기 하는데에 어려움이 없어져서 학원에도 조금 더 재미가 붙을 것 같다.
1대1 상담도 진행하였는데 보통은 앞으로 희망하는 분야, 도메인, 방향성 등등을 위한 상담이었지만 나는 희망하는 분야와 취업처, 방향성보단 앞으로 내가 어떤 식으로 나의 삶을 살고싶은지에 대한 명확한 생각이 있어서 그 부분을 말씀드리니 이해해주셨다. 오히려 확실하게 말씀드리니 조금 더 맘에 들어하시는 눈치였다, 그리고 아직 분야같은거 정할 짬도 안된다, SQL 기초에서도 정신나가버리는데
