스터디노트(SQL 15~16)

zoe·2023년 4월 23일
sql
0

실습환경 만들기

  • AWS RDS(databasse-1) zerobase에 접속
mysql -h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 -u admin -p
use zerobase;
  • police_station 데이터 확인
select * from police_station;
  • crime_status 데이터 확인
select * from crime_status;



Aggregate Functions(집계함수)

  • 여러 컬럼 혹은 테이블 전체 컬럼으로부터 하나의 결과값을 반환하는 함수
  • count : 총 갯수를 계산해주는 함수
  • sum : 합계를 계산해주는 함수
  • avg : 평균을 계산해주는 함수
  • min : 가장 작은 값을 찾아주는 함수
  • max : 가장 큰 값을 찾아주는 함수
  • first : 첫번째 결과값을 리턴하는 함수
  • last : 마지막 결과값을 리턴하는 함수


COUNT

  • count : 총 갯수를 계산해주는 함수
select count(column) 
from tablename 
where condition;

  • 예제1 (count) police_station 테이블에서 데이터는 모두 몇 개
select count(*) from police_station;
  • 예제2 (count) crime_status 테이블에서 경찰서는 총 몇군데
select count(distinct police_station) from crime_station;
select distinct police_station from crime_station;
  • 예제3 (count) crime_type 은 총 몇 가지
select count(distinct crime_type) from crime_status;
select distinct crime_type from crime_status;



SUM

  • sum : 숫자 컬럼의 합계를 계산해주는 함수
select sum(column)
from tablename
where conditon;

  • 예제1 (sum) 범죄 총 발생건수
select sum(case_number) from crime_status where status_type = '발생';
  • 예제2 (sum) 살인의 총 발생건수
select sum(case_number) from crime_status where crime_type like '%살인%' and status_type = '발생';
  • 예제3 (sum) 중부 경찰서에서 검거된 총 범죄 건수
select sum(case_number) from crime_status where police_station = '중부' and status_type = '검거';

문제 1. police_station 에서 경찰서는 총 몇개이고, 각각 경찰서 이름음 무엇인지 확인

select count(distinct name) from police_station;
select distinct name from police_station;

문제 2. crime_status 에서 status_type 은 총 몇개이고, 각각 타입은 무엇인지 확인

select count(distinct status_type) from crime_status;
select distinct status_type from crime_status;

문제 3. 종로경찰서와 남대문경찰서의 강도 발생 건수의 합

 select sum(case_number) from crime_status where police_station in ('종로', '남대문') and crime_type like '%강도%' and status_type = '발생';

문제 4. 폭력 범죄의 검거 건수의 합

select sum(case_number) from crime_status where crime_type like '%폭력%' and status_type = '검거';



AVG

  • avg : 숫자 컬럼의 평균을 계산해주는 함수
select avg(column)
from tablename
where condition;

  • 예제1 (avg) 평균 폭력 검거 건수
select avg(case_number) from crime_status where crime_type like '%폭력%' and status_type = '검거';
 select police_station, crime_type, status_type, case_number from crime_status where crime_type like '%폭력%' and status_type = '검거';
  • 예제2 (avg) 중부경찰서 범죄 평균 발생 건수
select avg(case_number) from crime_status where police_station = '중부' and status_type = '발생';
select police_station,crime_type, status_type, case_number from crime_status where police_station = '중부' and status_type = '발생';



MIN

  • min : 숫자 컬럼 중 가장 작은 값을 찾아주는 함수
select min(column)
from table
where condition;

  • 예제1 (min) 강도 발생 건수가 가장 적은 경우 몇 건
select min(case_number) from crime_status 
where crime_type like '%강도%' and status_type ='발생';
select police_station, crime_type, case_number 
from crime_status 
where crime_type like '%강도%' and status_type ='발생';
  • 예제2 (min) 중부경찰서에서 가장 낮은 검거 건수
select min(case_number) from crime_status where police_station = '중부' and status_type ='검거';
select police_station, crime_type, case_number from crime_status where police_station ='중부' and status_type =' 검거';



MAX

  • max : 숫자 컬럼 중 가장 큰 값을 찾아주는 함수
select max(column)
from tablename
where condition;

  • 예제1 (max) 살인이 가장 많이 검거된 건수
select max(case_number) 
from crime_status 
where crime_type like '%살인%' and status_type ='검거';
select * from crime_status 
where crime_type like '%살인%' and status_type ='검거';
  • 예제2 (max) 강남 경찰서에서 가장 많이 발생한 범죄 건수
select max(case_number) from crime_status where police_station = '강남' and status_type ='발생';
select * from crime_status where police_station = '강남' and status_type ='발생';

문제 1. 살인의 평균 발생 건수를 검색하고 확인

select avg(case_number) 
from crime_status 
where crime_type like '%살인%' and status_type = '발생';
select * from crime_status 
where crime_type like '%살인%' and status_type = '발생';

문제 2. 서초경찰서의 범죄 별 평균 검거 건수를 검색하고 확인

 select avg(case_number), crime_type 
 from crime_status 
 where police_station ='서초' and status_type = '검거';
select police_station, case_number, crime_type, status_type 
from crime_status 
where police_station ='서초' and status_type = '검거';

문제 3. 구로경찰서와 도봉경찰서의 평균 살인 검거 건수를 검색하고 확인

select avg(case_number) 
from crime_status 
where police_station in ('구로','도봉') 
and crime_type = '살인' and status_type ='검거';
select * from crime_status 
where police_station in ('구로','도봉') 
and crime_type = '살인' and status_type ='검거';

문제 4. 광진경찰서에서 가장 낮은 범죄 검거 건수를 검색하고 확인

select min(case_number) from crime_status 
where police_station = '광진' and status_type ='검거';
 select * from crime_status 
 where police_station = '광진' and status_type ='검거';

문제 5. 성북경찰서에서 가장 낮은 범죄 발생 건수를 검색하고 확인

select min(case_number) 
from crime_status 
where police_station = '성북' and status_type = '발생';
 select * from crime_status 
 where police_station = '성북' and status_type = '발생';

문제 6. 영등포경찰서의 가장 높은 범죄 발생 건수를 검색하고 확인

select max(case_number) 
from crime_status 
where police_station = '영등포' and status_type = '발생';
select * from crime_status 
where police_station = '영등포' and status_type = '발생';

문제 7. 절도 검거가 가장 많은 건수를 검색하고 확인

select max(case_number) from crime_status 
where crime_type like '%절도%' and status_type = '검거';
select * from crime_status 
where crime_type like '%절도%' and status_type = '검거';



GROUP BY

  • group by : 그룹화하여 데이터를 조회
select column1, column2, ...
from table
where condition
group by column1, column2, ...
order by column1, column2, ...

  • 예제1 (group by) crime_status 에서 경찰서별로 그룹화 하여 경찰서 이름을 조회 (경찰서 종류를 검색 - DISTINCT 를 사용하는 경우 (ORDER BY 를 사용할 수 없음)
    )
 select police_station 
 from crime_status 
 group by police_station 
 order by police_station;
  • 💡 예제2 (group by) 경찰서 별로 총 발생 범죄 건수를 검색
select police_station, sum(case_number) as '발생건수' 
from crime_status 
where status_type ='발생' 
group by police_station order by 발생건수 desc;
  • 예제3 (group by) 경찰서 별로 평균 범죄 검거 건수를 검색
select police_station, avg(case_number) as 평균검거건수 
from crime_status 
where status_type='검거' 
group by police_station 
order by 평균검거건수 desc;
  • 예제4 (group by) 경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수를 검색
 select police_station, status_type, avg(case_number) 
 from crime_status 
 group by police_station, status_type ;



HAVING

  • having : 조건에 집계함수가 포함되는 경우 where 대신 having 사용
select column1, column2, ...
from table
where condition
group by column1, column2, ...
having condition (Aggregate Functions)
order by column1, column2, ...

  • 예제1 (having) 경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 보다 큰 경우를 검색
select police_station, sum(case_number) as 'count' 
from crime_status 
where status_type='발생' 
group by police_station 
having count > 4000;
  • 예제2 (having) 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우를 검색
select police_station, avg(case_number) 
from crime_status 
where (crime_type like  '%폭력%' or crime_type like '절도') and status_type ='발생' 
group by police_station 
having avg(case_number) >= 2000;

문제 1. 경찰서 별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색하고 확인

select police_station, avg(case_number) 
from crime_status 
where crime_type like '%절도%' and status_type ='발생' 
group by police_station 
order by avg(case_number) desc limit 10;
select avg(case_number) 
from crime_status 
where police_station like '%송파%' and crime_type like '%절도%' and status_type ='발생';

문제 2. 경찰서 별로 가장 많이 검거한 범죄 건수를 가장 적은 건수 순으로 5개 검색

 select police_station, max(case_number) 
 from crime_status 
 where  status_type ='검거' 
 group by police_station 
 order by max(case_number) asc limit 5;
select max(case_number) 
from crime_status 
where status_type ='검거' and police_station like '방배';

문제 3. 경찰서 별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬하여 검색

select police_station, min(case_number) 
from crime_status 
where status_type = '검거' 
group by police_station having min(case_number)
order by min(case_number) desc  ;
select min(case_number) 
from crime_status 
where status_type = '검거' and police_station like '중랑';

문제 4. '대문' 으로 끝나는 이름의 경찰서 별 범죄발생 건수의 평균이 500건 이상인 경우를 검색

select police_station, avg(case_number) 
from crime_status 
where police_station like '%대문' and status_type ='발 생' 
group by police_station 
having avg(case_number) > 500;
select police_station, avg(case_number) 
from crime_status 
where police_station like '서대문' and status_type ='발생';



실습환경 만들기

  • AWS RDS(databasse-1) zerobase에 접속
mysql -h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 -u admin -p
use zerobase;



vscode - sandwich 테이블 생성

import mysql.connector
import pandas as pd
conn = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'zero',
    password = '<password>', 데이터베이스와 동일
    database = 'zerobase'
)
df = pd.read_csv('03. best_sandwiches_list_chicago2.csv', encoding='utf-8')
df['Price'] = df['Price'].str.replace('$','')
df['Price'] = df['Price'].astype('float')
df.info()
sql = 'create table sandwich (ranking int, cafe varchar(32), menu varchar(32), address varchar(32), price float)'
cursor = conn.cursor(buffered=True)
cursor.execute(sql)
sql = 'insert into sandwich values (%s, %s, %s, %s, %s)'

for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()
conn.close()

Scalar Function

  • 입력값을 기준으로 단일 값을 반환하는 함수
  • UCASE : 영문을 대문자로 변환하는 함수
  • LCASE : 영문을 소문자로 변환하는 함수
  • MID : 문자열 부분을 반환하는 함수
  • LENGTH : 문자열 길이를 반환하는 함수
  • ROUND : 지정한 자리에서 숫자를 반올림하는 함수(0이 소수점 첫째자리)
  • NOW : 현재 날짜 및 시간을 반환하는 함수
  • FORMAT : 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수


UCASE

  • UCASE : 영문을 대문자로 반환하는 함수
select ucase(string);

  • 예제1 (ucase) 다음 문장을 모두 대문자로 조회
select  ucase('This Is ucase Test.');
  • 예제2 (ucase) $15 가 넘는 메뉴를 대문자로 조회
select ucase(menu) from sandwich where price > 15;



LCASE

  • LCASE : 영문을 소문자로 반환하는 함수
select lcase(string);

  • 예제1 (lcase) 다음 문장을 모두 대문자로 조회
select  lcase('This Is ucase Test.');
  • 예제2 (lcase) $5 가 안되는 메뉴를 소문자로 조회
select lcase(menu) from sandwich where price < 5;



MID

  • MID : 문자열 부분을 반환하는 함수
    - string : 원본 문자열
    - start : 문자열 반환 시작 위치 (첫글자는 1, 마지막 글자는 -1)
    - length : 반환할 문자열 길이
select mid(string, start_position, length);

  • 예제1 (mid) 다음 문장에서 1번 위치에서 4글자를 조회
select mid('This is mid test', 1, 4);
  • 예제2 (mid) 다음 문장에서 6번 위치에서 5글자를 조회
select mid('This is mid test', 6, 5);
  • 예제3 (mid) 다음 문장에서 -4번 위치 (뒤에서 4번째 위치) 에서 4글자를 조회
select mid('This is mid test', -4, 4);
  • 예제4 (mid) 다음 문장에서 -8번 위치 (뒤에서 8번째 위치) 에서 3글자를 조회
select mid('This is mid test', -8, 3);
  • 💡 예제5 (mid) 11위 카페이름 중 두번째 단어만 조회 - 6번 위치에서 4글자
select mid(cafe, 6, 4) from sandwich where ranking = 11;



LENGTH

  • length : 문자열의 길이를 반환하는 함수, 문자가 없는 경우 길이도 0, 공백의 경우에도 문자이므로 길이가 1
select length(string);

  • 예제1 (length) 다음 문장의 길이를 조회
select length('This is len test');
  • 예제2 (length) 문자가 없는 경우 길이도 0
select length('');
  • 예제3 (length) 공백의 경우에도 문자이므로 길이가 1
select length(' ');
  • 예제4 (length) NULL 의 경우 길이가 없으므로 NULL
select length(null);
  • 예제5 (length) sandwich 테이블에서 Top 3의 주소 길이를 검색
select length(address), address from sandwich where ranking <= 3;



ROUND

  • round : 지정한 자리에서 숫자를 반올림하는 함수
    - number : 반올림할 대상
    - decimals : 반올림할 소수점 위치(option)
select round(number, decimals_place);

  • 예제1 (round) 반올림할 위치를 지정하지 않을 경우, 소수점 자리 (0) 에서 반올림
select round(315.625);
  • 예제2 (round) 소수점 첫번째 위치는 0
select round(315.625,0);
  • 예제3 (round) 두번째 소수점 위치는 1
select round(315.625,1);
  • 예제4 (round) 세번째 소수점 위치는 2
select round(315.625,2);
  • 예제5 (round) 일단위 위치는 -1
select round(315.625,-1);
  • 예제6 (round) 십단위 위치는 -2
select round(315.625,-2);
  • 예제7 (round) sandwich 테이블에서 소수점 자리는 반올림해서 1달러 단위까지만 표시 (최하위 3개만 표시)
select ranking, round(price,0), price 
from sandwich order by ranking desc limit 3;



NOW

  • now : 현재 날짜 및 시간을 반환하는 함수
select now();

  • 예제1 (now)
 select now();



FORMAT

  • format : 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수
    - number : 포맷을 적용할 문자 혹은 숫자
    - decimals : 표시할 소수점 위치

  • 예제1 (format) 소수점을 표시하지 않을 경우 0

 select format(12345.6789, 0);
  • 예제2 (format) 소수점 두자리까지 표시할 경우 2
select format(12345.6789,2);
  • 💡 예제3 (format) oil_price 테이블에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우 천원단위에 콤마를 넣어서 조회

localmysql에 있음

select format(가격,0) from oil_price where round(가격, -3) >=2000

문제 1. sandwich 테이블에서 가계이름은 대문자, 메뉴이름은 소문자로 조회

select ucase(cafe), lcase(menu) from sandwich;

문제 2. sandwich 테이블에서 10위 메뉴의 마지막 단어를 조회

select ranking,cafe, mid(menu, -3, 3) 
from sandwich where ranking = 10;

💡 문제 3. sandwich 테이블에서 메뉴 이름의 평균 길이를 조회

select menu, avg (length(menu)) from sandwich;

문제 4. oil_price 테이블에서 가격을 십원단위에서 반올림해서 조회

localmysql에 있음

 select 가격, round(가격, -2) from oil_price;

문제 5. oil_price 테이블에서 가격이 십원단위에서 반올림 했을 때 2000원 이상인 경우, 천단위에 콤마를 넣어서 조회

localmysql에 있음

select 가격,format(가격, 0) from oil_price where round(가격,-2) >= 2000;



Subquery

  • 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다

  • 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다
    - 서브쿼리는 메인쿼리의 컬럼 사용 가능
    - 메인쿼리는 서브쿼리의 컬럼 사용 불가

  • Subquery는 괄호를 묶어서 사용

  • 단일 행 혹은 복수 행 비교 연산자와 함께 사용 가능

  • subquery에서는 order by를 사용 X

  • 스칼라 서브쿼리(Scalar Subquery) - SELECT절에 사용

  • 인라인 뷰(Inline View) - FROM절에 사용

  • 중첩 서브쿼리(Nested Subquery) - WHERE 절에 사용


💡 스칼라 서브쿼리(Scalar Subquery)

  • SELECT절에서 사용하는 서브쿼리, 결과는 하나의 Column이어야 한다.
select column1, (select column2 from table2 where condition) 
from table1
where condition;

  • 💡 예제1 (scalar subquery) 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회
select 
	case_number, 
    (select avg(case_number) from crime_status where crime_type like '%강도%' and status_type = '검거') as avg 
from crime_status 
where police_station = '은평' and crime_type like '%강도%' and status_type = '검거';



💡 인라인 뷰(Inline View)

  • From절에 사용하는 서브쿼리, 메인쿼리에서는 인라인 뷰에서 조회한 Column만 사용가능
select a.column, b.column
from table1 a, (select column1, column2 from table2) b
where condition;

  • 💡 예제1 (inline view) 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회
select c.police_station, c.crime_type, c.case_number 
from crime_status as c, 
    ( select police_station, max(case_number) as count 
    from crime_status 
    where status_type ='발생' group by police_station) as m
where c.police_station  = m.police_station and c.case_number = m.count;



💡 중첩 서브쿼리(Nested Subquery)

  • WHERE절에서 사용하는 서브쿼리
    - Single Row - 하나의 열을 검색하는 서브쿼리
    - Multiple Row - 하나 이상의 열을 검색하는 서브쿼리
    - Multiple Column - 하나 이상의 행을 검색하는 서브쿼리


  • Single Row Subquery : 서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다(두개 이상인 경우 에러)
select column_names
from table_name
where column_name = (select column_name from table_name where condition)
order by column_name;

  • 예제1 (single row subquery) 괄호없이(에러)
celect name 
from celeb where name = select host from snl_show;
  • 예제2 (single row subquery) 한 개 이상의 결과(에러)
select name 
from celeb 
where name = (select host from snl_show where id=1);



  • Multiple Row - IN : 서브쿼리 결과 중에 포함될 때
select column_names
from table_name
where column_name in (select column_name from table_name where condition)
order by column_names;

  • 💡 예제1 (Multiple Row - in) SNL 에 출연한 영화배우를 조회
select host 
from snl_show 
where host in (select name 
				from celeb 
                where job_title like '%영화배우%');



  • Multiple Row - EXISTS : 서브쿼리 결과에 값이 있으면 반환
select column_names
from table_name
where exists (select column_name from table_name where condition)
order by column_names;

  • 💡 예제1 (Multiple Row - exists) 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회
select name 
from police_station as p
	where exists (select police_station 
    				from crime_status c 
                    where p.name = c.reference and case_number > 2000);



  • Multiple Row - ANY : 서브쿼리 결과 중에 최소한 하나라도 만족하면 (비교연산자 사용)
select column_names
from table_name
where column_name = any (select column_name from table_name where condition)
order by column_names;

  • 예제1 (Multiple Row - any) SNL 에 출연한 적이 있는 연예인 이름 조회
select name 
from celeb 
where name = any(select host from snl_show);



  • Multiple Row - ALL : 서브쿼리 결과 모두 만족하면 (비교연산자 사용)
select column_names
from table_name
where column_name = all (select column_name from table_name where condition)
order by column_names;

  • 💡 예제1 (Multiple Row - all) 서브쿼리 결과를 모두 만족하면 (비교 연산자 사용)
select name 
from celeb 
where name  = all (select host from snl_show where id = 1);



  • Multi Column Subquery : 연관 서브쿼리, 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
select column_names
from tablename a
where (a.column1, a.column2, ...) in (select b.column1, b.column2, ... from tablename b where a.column_name = b.column_name)
order by column_names;

  • 💡 예제1 (Multi Column Subquery) 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회
select name, sex, agency 
from celeb 
where (sex, agency) = (select sex, agency from celeb where name = '강동원');

💡 문제 1. oil_price 테이블에서 셀프주유의 평균가격과 SK에너지의 가장 비싼 가격을 Scalar Subquery 를 사용하여 조회

 select max(가격), (select avg(가격) from oil_price where 셀프 = 'Y') as avg 
 from oil_price 
 where 상표 ='SK에너지';

💡 문제 2. oil_price 테이블에서 상표별로 가장 비싼 가격과 상호를 Inline View 를 사용하여 조회

select o.가격, o.상호, o.상표 
from oil_price as o , (select 상표, max(가격) as max from oil_price group by 상표) as m
where o.상표 = m.상표 and o.가격 = m.max;

💡 문제 3. 평균가격 보다 높은 주유소 상호와 가격을 Nested Subquery 를 사용하여 조회

select 상호, 가격 
from oil_price where 가격 > (select avg(가격) from oil_price);

문제 4. 3번에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일을 Nested Subquery 를 사용하여 조회(refueling 테이블)

문제 5. refueling 테이블과 oil_price 테이블에서 10만원 이상 주유한 연예인 이름, 상호, 상표, 주유 금액, 가격을 Inline View 를 사용하여 조회

💻 출처 : 제로베이스 데이터 취업 스쿨

profile
zoe
#데이터분석 #퍼포먼스마케팅 #데이터 #디지털마케팅
이전 포스트

😢 스터디노트(SQL 14)

다음 포스트

스터디노트(Git 1)

0개의 댓글