#groupy by로 ticker별 평균 종가를 구함
# groupby의 비용이 크기 때문에 where절로 먼저 데이터를 줄이고 having을 이용하는게 좋음
select ticker, avg(end1) as av #5
from price #1
where day1 > "20201231" #2
group by ticker #3
having av > 100000 #4
order by av desc; #6
#equi 조인
select a.ticker, a.open1, a.end1, b.대표자명
from price a , company b
where a.ticker = b.종목코드;
# non equi join >,<,>= 등등 사용
select a.ticker, a.open1, a.end1, b.대표자명
from price a , company b
where a.test between b.test2 and b.test3;
#standard join
#inner join - 조인 조건에 충족하는 것만 출력
select a.ticker, a.open1, a.end1, b.대표자명
from price a
inner join company b on a.ticker = b.종목코드;
#outer join - 조인조건에 충족하지 않아도 왼쪽 ,오른쪽,full 중 선택한 table에 값들은 전부 출력(충족x시 null값으로)
select a.ticker, a.open1, a.end1, b.대표자명
from price a
left outer join company b on a.ticker = b.종목코드; #right, Full
#natural join - 같은 이름을 가진 컬럼들이 모두 동일한 데이터를 가질떄 join됨
#cross join - 두 테이블을 조합하여 만들 수 있는 모든 경우를 출력
select a.ticker, a.open1, a.end1, b.대표자명
from price a cross join company b ;
#위치에 따른 서브쿼리 구분
#스칼라 쿼리 = select 절 및 대부분 위치(select, update, order by등)에서 사용 컬럼대신 사용되므로 반드시 하나의 값만을 반환
#인라인 뷰 = from 절등 테이블명이 올 수 있는 위치에서 사용
#중첩 서브쿼리 = where절, having 절 메인 쿼리와 연관되 있냐마냐에 따라 비연관 서브쿼리와 연관 서브쿼리로 나뉜다
#스칼라 쿼리
select a.ticker, a.open1 , (select b.대표자명 from company b where a.ticker = b.종목코드 ) as 대표
from price a ;
#인라인 뷰
select a.ticker, a.open1, b.대표자명
from price a , (select c.대표자명,c.종목코드 from company c) b
where a.ticker = b.종목코드;
#중첩 서브쿼리
#비연관서브쿼리 - 서브쿼리 내에 메인쿼리의 컬럼이 존재x
select a.ticker, a.open1
from price a
where a.ticker = (select 종목코드 from company c where c.대표자명 = "노성석");
#연관서브쿼리 - 서브쿼리 내에 메인 쿼리의 컬럼 존재
select 종목코드, 대표자명,hightmp
from company c
where hightmp = (select max(high) from price a where c.종목코드 = a.ticker) ;
#중첩서브쿼리는 반환하는 데이터 형태에 따라 단일 행, 다중 행, 다중컬럼 서브쿼리로 나뉜다
#단일행 서브쿼리 - 1건 이하의 데이터를 반환 =,> 같은 비교연산자 사용
#다중행 서브쿼리 - 서브쿼리가 여러 행의 데이터 반환 in, all,any,some,exists 같은 다중행비교연산자와 사용
#다중 컬럼 서브쿼리 - 서브쿼리가 여러 컬럼의 데이터 반환
#뷰 - select문 앞에 이름을 붙여 재사용하다록 저장해놓은 오브젝트, table처럼 사용하는 가상 table 물리적 저장 x
# 보안성, 독립성, 편리성을 가짐
create or replace view price2021 as
select *
from price
where day1 > 20210000 and day1 <20220000;
#집합연산자
#union all - 합집합 중복된 행 모두 그대로 출력
#union - 합집합 중복된 행은 하나로 출력
#intersect - 교집합 중복된 행은 한줄로 출력
#minus/except - 차집합 - 중복된 행은 한줄로 출력
select * from price2021
union all
select * from price2022;
#그룹함수 - 데이터를 groupby해서 나타낼수 있는 데이터를 구하는 함수
#집계함수 - count, sum, avg, max ,min 등
#소계함수 - rollup, cube, grouping sets 등
#소계함수
#rollup - 소그룹 간의 소계 및 총계를 계산하는 함수
#rollup(a) - a로 그룹핑, 총합계
#rollup(a,b) - a,b로 그룹핑, a로 그룹핑, 총합계
#rollup(a,b,c) - a,b,c로 그룹핑, a,b로 그룹핑, a로 그룹핑, 총합계
select day1, count(*)
from price
group by day1 with rollup;
#group by rollup(day1);
#cube - 집계할 수 있는 모든 소그룹에 대한 소계를 집계
select day1, count(*)
from price
group by cube(day1); #mysql에서는 동작 x 어캐 사용하는지 모르겟음
#grouping set - 특정 항목에 대한 소계 계산 인자값으로 rollup이나 cube 사용가능
select day1,ticker, count(*)
from price
group by grouping set (day1,rollup(ticker)); #mysql에서는 동작 x
#grouping - 롤업, 큐브, 그룹핑 셋과 함계 쓰이면 소계를 나타내는 행을 구분할 수 있게 한다
select day1, count(*), if (grouping(day1)=1, "total", grouping(day1)) as groupp
from price
group by day1 with rollup;
#윈도우함수 over과 함께 사용
#순위 함수 - rank(같은 순위시 그만큼 건너뛰고 순서 이어짐 1,2,2,2,5), dense_rank(같은 순위 다음 순서 이어짐 , 1,2,2,2,3 ), row_number(같은 순위라도 다른 순)
#집계 함수
#행 순서 함수 - first_value,last_value,lag,lead
#비율 함수 - cume_dist, percent_rank, ntile,ratio_to_report
#rank -
select * ,rank() over(order by 상장일 desc) as rk
from company;
#partition by - 파티션을 나눠서 사용
select * ,count(지역) over(partition by 지역) as ct
from company;
#first_value - 파티션 별 가장 선두에 위치한 데이터를 구함
select * , first_value(day1) over(order by day1) as tmp
from price;
select * , first_value(day1) over(partition by ticker order by day1) as tmp
from price;
#last_value - 파티션 별 가장 끝에 위치한 데이터를 구함
#lag - 파티션 별로 특정 수만큼 앞에 데이터를 구하는 함수 인자가 1개일 시 default는 1
select * , lag(day1,3) over(partition by ticker order by day1) as tmp
from price;
#lead- 파티션 별로 특정 수만큼 뒤에 데이터를 구하는 함수 인자가 1개일 시 default는 1
#cume_dist, percent_rank, ntile,ratio_to_report
#ratio_to_report - 파티션별 합계에서 차지하는 비율 구하는 함수
#percent_rank - 해당 파티션의 맨위 행을 0 맨 아래 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
#cume_dist - 누적 백분위 구하기
#ntile - 주어진 수만큼 행들을 n등분한 뒤 현재 행에 해당하는 등급을 구한다
select * , ntile(3) over(partition by ticker order by day1) as tmp
from price;
#top-n 쿼리 순서로 추출하는 방법
select rownum, * #mysql 사용 x
from company
where rownum >5 ;# =은 사용 x >나 >=
#mysql에서 사용법
SELECT @ROWNUM:=@ROWNUM+1, A.*
FROM company A, (SELECT @ROWNUM:=0) R;
#윈도우 함수의 순위 함수 row_number
select row_number() over(order by 회사명) as rk ,회사명, 대표자명
from company;
#위에서는 select가 마지막에 실행되기 떄문 rk로 where 절로 사용할 수 없지만
#아래는 from에서 미리 rk를 생성하기 떄문에 where절을 사용할 수 있다
select *
from(select *, row_number() over(order by 회사명) as rk from company) A #여기 반드시 별칭이 들어가야한다 alias
where rk<10;
#rank로도 사용가능
select *
from(select *, rank() over(order by 회사명) as rk from company) A #여기 반드시 별칭이 들어가야한다 alias
where rk<10;
#셀프 조인 - 나자신과의 조인시 from절에 같은 테이블이 두번 이상 등장하기 떄문에 반드시 alias(별칭)을 달아줘야한다.
