어제 문제
-- 1. 아래 테이블 생성 후(seoul_new.txt)
create table title
(
text varchar2(500)
);
select * from title;
-- 1) 게시글번호 추출
select text,
regexp_substr(text,'\d+') as 게시글번호
from title;
-- 2) 작성날짜 추출
select text,
regexp_substr(text,'\d+-\d+-\d+') as 작성날짜
from title;
-- 3) 조회수 추출
select text,
regexp_substr(text,'\d+-\d+-\d+ (\d+)',1,1,null,1) as 조회수
from title;
-- 4) 게시글 제목 추출
select text,
regexp_substr(text,'\d+ (.*) \d+-\d+-\d+',1,1,null,1) as 게시글제목
from title;
-- 서브그룹으로 원하는 결과 출력하기
select text,
regexp_substr(text,'(\d+) (.+) (\d+-\d+-\d+) (\d+)',1,1,null,1) as 게시글번호,
regexp_substr(text,'(\d+) (.+) (\d+-\d+-\d+) (\d+)',1,1,null,3) as 작성날짜,
regexp_substr(text,'(\d+) (.+) (\d+-\d+-\d+) (\d+)',1,1,null,4) as 조회수,
regexp_substr(text,'(\d+) (.+) (\d+-\d+-\d+) (\d+)',1,1,null,2) as 게시글제목
from title;수업 내용
— [정규식 표현(Regular Expression)]
— 1. regexp_substr(대상,패턴[,시작위치,발견횟수(1),옵션,서브그룹번호])
— 2. regexp_replace(대상,패턴[,바꿀문자열,시작위치,발견횟수(0=모든),옵션])
— 3. regexp_instr(대상,패턴[,시작위치,발견횟수(1)])
select id,
regexp_instr(id,'\d'), -- 숫자
regexp_instr(id,'\W'), -- _ 제외한 특수기호 + 공백
regexp_instr(id,'[[:punct:]]') -- 특수기호
from professor;— 4. regexp_count(대상,패턴[,시작위치,옵션])
select id,
regexp_count(id,'\d'), -- 숫자 1개가 몇개인지
regexp_count(id,'\d+'), -- 숫자 덩어리가 몇개인지
regexp_count(id,'\W'), -- _ 제외한 특수기호 + 공백
regexp_count(id,'[[:punct:]]') -- 특수기호
from professor;— 5. regexp_like(대상,패턴[,옵션])
— 리턴은 참, 거짓이고 where절에서만 사용가능
-- 숫자 가진 행 출력
select id
from professor
where regexp_like(id,'\d');
-- 특수기호 2개이상 가진 행 출력
select id
from professor
where regexp_like(id,'[[:punct:]]{2,}');
select count(id)
from professor
where regexp_like(id,'[a-z][0-9]+');— [윈도우 함수]
— 테이블 추가 접근(조인,서브쿼리) 없이 다른행과의 비교, 연산을 가능하게 하는 문법
— select 절에서만 사용가능. where절 사용불가.
— 1. 집계함수 형태
— min,max,sum,count,avg…over(…)
— 1) sum over() : 누적합 연산
sum(대상) over([partition by …]
[order by …]
[range|rows between A and B]);
— 그룹별 연산 수행 시 partition by 사용(group by와 같은 느낌)
— 누적합 연산 시 order by 필수
— 연산 범위 생략 시 default = range between unbounded preceding and current row
— range : 값이 같은 경우 동시 연산
— row : 각 행씩 연산
— A) unbounded preceding : 처음부터 (default)
— n preceding : n 이전부터
— current row : 현재행부터
— B) unbounded following : 끝까지
— n following : n 이후까지
— current row : 현재행까지 (default)
-- 예제) 각 직원의 사번, 이름, 급여, 부서번호와 함께 각 부서의 급여 총합 함께 출력
-- sol1) 스칼라 서브쿼리
select empno, ename, sal, deptno,
(select sum(sal) from emp where deptno=e1.deptno) as 부서별급여합
from emp e1
order by deptno;
-- sol2) 인라인 서브쿼리 (조인)
select e.empno, e.ename, e.sal, e.deptno, i.sum_sal
from emp e,
(select deptno, sum(sal) as sum_sal
from emp
group by deptno) i
where e.deptno = i.deptno
order by deptno;
-- sol3) 윈도우 함수
select empno, ename, sal, deptno,
sum(sal) over(partition by deptno) as 부서별급여합
from emp;-- 예제) 급여 순서대로 급여의 누적합
select empno, ename, sal,
sum(sal) over(order by sal) as 누적합
from emp;
-- 주의 : sal 순서대로 값이 같은 경우 sal 갑이 같은 값을 하나로 묶어서 동시에 연산처리
-- 아래에 출력결과 확인하기
-- 해결법1) 추가 정렬값 전달
select empno, ename, sal,
sum(sal) over(order by sal, empno) as 누적합
from emp;
-- 해결법2) 옵션 수정 : range->rows
select empno, ename, sal,
sum(sal) over(order by sal
rows between unbounded preceding and current row) as 누적합
from emp;
-- 예제) 각 직원의 이름, 부서번호, 급여와 함께 부서별 급여의 누적합(급여작은순)
select ename, deptno, sal,
sum(sal) over(partition by deptno
order by sal
rows between unbounded preceding and current row) as 부서별누적합
from emp;-- [연습문제]
-- 1. emp에서 각 부서별 최대 급여자 찾기(이름, 부서번호, 급여)
-- sol1) 인라인뷰
select e.ename, e.deptno, e.sal
from emp e,
(select deptno, max(sal) as max_sal
from emp
group by deptno) i
where e.deptno = i.deptno
and e.sal = i.max_sal;
-- sol2) 서브쿼리
select ename, deptno, sal
from emp
where (deptno,sal) in (select deptno,max(sal)
from EMP
group by deptno);
-- sol3) 윈도우함수,
select *
from (select ename, deptno, sal,
max(sal) over(partition by deptno) as max_sal
from emp)
where sal=max_sal;-- 2. student에서 각 학년별 평균몸무게보다 낮은 학생 출력(이름, 학년, 체중)
-- sol1) 인라인뷰
select grade, avg(weight)
from student
group by grade;
select s.name, s.grade, s.weight
from student s,
(select grade, avg(weight) as avg_weight
from student
group by grade) i
where s.weight<i.avg_weight
and s.grade=i.grade;
-- sol2) 윈도우함수
select *
from (select name, grade, weight,
avg(weight) over(partition by grade) as avg_weight
from student)
where weight<avg_weight;
-- sol3) 상호연관
select s.name, s.grade, s.weight
from student S
where s.weight<(select avg(weight)
from student
where grade = s.grade);— 2. 순위
— 1) rank
rank over([partition by …]
order by
[range|rows …])
— 2) dense_rank
dense_rank over([partition by …]
order by
[range|rows …])
— 3) row_number
row_number over([partition by …]
order by
[range|rows …])
— rank : 동순위 인정 & 다음 순위 생략 – 가장 베이직
— dense_rank : dense는 ‘밀집’. 동순위 인정 & 다음 순위 생략X
— row_number : 동순위 인정X
-- 예제) 각 직원의 급여 큰 순서대로 순위(전체기준)
select ename, sal, deptno,
rank() over(order by sal desc) as 순위1,
dense_rank() over(order by sal desc) as 순위2,
row_number() over(order by sal desc) as 순위3
from emp;-- [연습문제]
-- 1. student_exam01에서 각 학년별로 성적이 높은 3명 출력
select *
from (select name, grade, total,
row_number() over(partition by grade
order by total desc) as 순위
from student_exam01)
where 순위<=3;-- 2. student_exam01에서 성적이 높은 4~6등 출력 (동순위 인정X)
-- sol1)
select *
from (select name, grade, total,
row_number() over(order by total desc) as 순위
from student_exam01)
where 순위 between 4 and 6;
-- sol2)
select *
from (select rownum as rn, i.*
from (select *
from student_exam01 S
order by total desc, name) i)
where rn between 4 and 6;-- 3. 전체 성적이 높은 순 3명 출력
-- sol1)
select *
from (select s.*,
row_number() over(order by total desc) as 순위
from student_exam01 s)
where 순위 <= 3;
-- sol2) rownum 사용
select rownum,
s.*
from student_exam01 s
where rownum <= 3
order by total desc; -- 이러면 where절부터 수행돼서 틀린 출력 나옴.
select rownum, i.*
from (select *
from student_exam01
order by total desc) i
where rownum<=3; -- 이렇게 빼서 where을 늦게 수행하게 해야됨— 3. 이전/이후 행
— 1) lag() over() : 이전 값 가져오기
lag(대상,
[n], — 가져올 값의 위치(default : 1)
[대체값]) — 가져올 값이 없을 경우 리턴값(default : null)
over([partition by …]
order by …)
-- 예제) 바로 직전에 입사한 직원의 급여보다 낮은 급여를 받는 직원 출력
select *
from (select ename, hiredate, sal,
lag(sal) over(order by hiredate) as 이전입사자급여
from emp) I
where sal<이전입사자급여;— 2) lead() over() : 이후 값 가져오기
lead(대상,
[n], — 가져올 값의 위치(default : 1)
[대체값]) — 가져올 값이 없을 경우 리턴값(default : null)
over([partition by …]
order by …)
-- [연습문제]
-- 1) 일자별 이용비율 총 합 출력 후 테이블 생성(movie_t1)
select * from movie;
create table movie_t1
as
select to_date(년||'/'||월||'/'||일,'YYYY/MM/DD') as 일자,
sum(이용비율) as 이용비율합
from movie
group by to_date(년||'/'||월||'/'||일,'YYYY/MM/DD');
-- 2) 이전일자대비 이용비율 증가율
select * from movie_t1;
select 일자, 이용비율합,
lag(이용비율합) over(order by 일자) as 이전일자이용비율,
(이용비율합-lag(이용비율합) over(order by 일자)) / lag(이용비율합) over(order by 일자) * 100 as 증가율
from movie_t1;— 4. 최대/최소
— 1) first_value
first_value(대상) over([partition by …]
[order by …]
[range|rows …]) : 처음 값
— 2) last_value
last_value(대상) over() : 마지막 값
-- 예제) 시작값과 끝값 출력
select e.*,
first_value(sal) over(order by sal) as "first_value",
last_value(sal) over(order by sal) as "last_value"
-- default가 처음부터 현재행까지라서 출력이 800, 950, 1100, 1300 이 됨.
from emp E
where job = 'CLERK'
order by sal;-- 예제) 최소, 최대 출력
-- first_value() 사용
select e.*,
first_value(sal) over(order by sal) as 최소,
first_value(sal) over(order by sal desc) as 최대
from emp E
where job = 'CLERK'
order by sal;
-- last_value() 사용
select e.*,
last_value(sal) over(order by sal) as 자기자신,
last_value(sal) over(order by sal
range between unbounded preceding and unbounded following) as 최대,
last_value(sal) over(order by sal desc
range between unbounded preceding and unbounded following) as 최소
from emp E
where job = 'CLERK'
order by sal;— [top n query]
— 상위 n개 추출
— 정렬 필수
— 페이징 처리
— 1) fetch
— oracle은 12c부터 제공
— 대부분의 dbms 제공
— 단일 쿼리로도 상위 n개 추출
— 2) rownum
— 출력되는 순서에 맞게 1,2,3의 항상 1씩 증가하는 번호 부여
— 일반적으로 입력순서에 따라 rownum부여, 사용자 정의 순서대로 rownum 부여 시 서브쿼리 사용 필요
-- 예제) emp에서 rownum을 사용하여 급여 순위 4~6등 출력
select rownum, i.*
from (select *
from emp
order by sal desc) i;— 3) rank over / dense_rank over / row_number over
— 4) top(n)
— SQL-Server 문법
— 단일 쿼리로도 상위 n개 추출