어제 문제
--1. sales2 테이블 생성 후 sales.csv 파일을 업로드 한 뒤,
--지점별 품목별 판매량 총 합을 표현하는 교차표 출력
create table sales2(
날짜 varchar2(20),
지점 varchar2(20),
품목 varchar2(20),
판매량 number);
select * from sales2;
select 품목, "'c1'", "'c2'"
from (select 지점, 품목, 판매량 from sales2)
pivot(sum(판매량) for 지점 in ('c1','c2'));--2. delivery 테이블을 사용하여
--1) 요일별 업종별 배달건수의 총 합을 표현하는 교차표 출력
select * from delivery;
desc delivery;
-- 일단 일자, 업종으로 묶기
select 일자, 업종, sum(통화건수) as 일자업종별통화건수
from delivery
group by 일자, 업종
order by 1;
-- 그다음 요일, 업종으로 묶기
select TO_CHAR(TO_DATE(일자, 'YYYYMMDD'), 'day') as 요일, 업종, sum(일자업종별통화건수) as 요일업종별통화건수
from (select 일자, 업종, sum(통화건수) as 일자업종별통화건수
from delivery
group by 일자, 업종)
group by TO_CHAR(TO_DATE(일자, 'YYYYMMDD'), 'day'), 업종
order by 1;
-- 아ㅏㅏㅏㅏㅏ group by를 써도 요일별로 묶이지가 않네;; 왜그러지?
-- ChatGPT가 알려준 쿼리고 잘 동작한다... 바로 pivot을 갈겨야 했나...
SELECT *
FROM (SELECT TO_CHAR(TO_DATE(일자, 'YYYYMMDD'), 'day', 'NLS_DATE_LANGUAGE=KOREAN') AS 요일, 업종, 통화건수
FROM delivery)
PIVOT (SUM(통화건수)
FOR 요일 IN ('월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일'))
ORDER BY 업종;
-- 이게 내가 하나하나 친 쿼리
select *
from (select to_char(to_date(일자,'YYYYMMDD'),'day') as 요일,
업종, 통화건수
from delivery)
pivot (sum(통화건수) for 요일 in ('월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일'));
--2) 음식점별로 배달 주문이 가장 많은 시간대 출력
select *
from (select 시간대, 통화건수, 업종 from delivery)
pivot(sum(통화건수) for 업종 in ('음식점-중국음식','음식점-족발/보쌈전문','피자','치킨'))
order by 시간대;
-- 강사님 방법
-- step1) 기초 데이터
select 업종, 시간대, sum(통화건수) as 통화건수
from delivery
group by 업종, 시간대;
-- step2) 업종별 통화건수 최대
select 업종, max(통화건수)
from (select 업종, 시간대, sum(통화건수) as 통화건수
from delivery
group by 업종, 시간대)
group by 업종;
-- 또는 max() over() 사용
select 업종, 시간대, sum(통화건수) as 통화건수,
max(sum(통화건수)) over(partition by 업종) as 업종별최대통화건수
from delivery
group by 업종, 시간대;
-- 업종별 통화건수 최대인 시간대 구하기
select *
from (select 업종, 시간대, sum(통화건수) as 통화건수,
max(sum(통화건수)) over(partition by 업종) as 업종별최대통화건수
from delivery
group by 업종, 시간대)
where 통화건수 = 업종별최대통화건수;수업 내용
—- [pivot/unpivot]
— 1. pivot
— long data->wide data
-- pivot1 테이블을 메뉴별 연도별 판매량 총합 교차표 작성 후 테이블 생성
create table unpivot1
as
select year,
"'latte'" as latte,
"'mocha'" as mocha,
"'americano'" as americano
from (select qty, name, year from pivot1)
pivot(sum(qty) for name in ('latte','mocha','americano'));
select * from unpivot1;— 2. unpivot
— wide data->long data
select
from 테이블명 또는 서브쿼리
unpivot(value컬럼명 for stack컬럼명 in (컬럼1, 컬럼2,…));
-- 예제) unpivot1 테이블을 long data로 변환
-- pivot1 테이블을 메뉴별 연도별 판매량 총합 교차표 작성 후 테이블 생성
create table unpivot1
as
select year,
"'latte'" as latte,
"'mocha'" as mocha,
"'americano'" as americano
from (select qty, name, year from pivot1)
pivot(sum(qty) for name in ('latte','mocha','americano'));
select * from unpivot1;
select *
from unpivot1
unpivot(qty for menu in (LATTE,MOCHA,AMERICANO));-- 예제) unpivot2 테이블(연령별실업율_40-49세.csv) 생성 후 long data로 변환
create table unpivot2(
월 number,
"2014년" number,
"2015년" number,
"2016년" number,
"2017년" number,
"2018년" number);
select * from unpivot2;
select year, 월, unemploy
from unpivot2
unpivot(unemploy for year in ("2014년","2015년","2016년","2017년","2018년"))
order by 1,2;-- [연습문제]
-- 1. cal 테이블 데이터를 사용하여 캘린더 형태로 출력한 뒤 테이블 cal2로 저장
select * from cal;
create table cal2
as
select "'일'" as 일,
"'월'" as 월,
"'화'" as 화,
"'수'" as 수,
"'목'" as 목,
"'금'" as 금,
"'토'" as 토
from cal
pivot(sum(num_day) for day in ('일','월','화','수','목','금','토'))
order by week;
drop table cal2;-- 2. cal2 테이블 데이터를 다시 long data로 변환
select * from cal2;
select *
from cal2
unpivot(날짜 for 요일 in (일,월,화,수,목,금,토));— [top n 쿼리]
— – 상위 n개 행 추출 (페이징 처리)
— 1. rownum
— 출력되는 행의 순서에 따라 임시의 행번호를 부여
— 첫번째 행이 출력이 된 이후에 순서대로 행번호를 부여하므로 1초과의 값으로 행 선택불가
-- 예) rownum 확인
select rownum, rowid, e.*
from emp e
where deptno=20;
-- 예) rownum을 사용한 행의 수 제한
select *
from emp
where rownum<=10;
-- 예) rownum을 사용한 행 선택불가
select *
from emp
where rownum=10; -- 조회 안 됨
select *
from EMP
where rownum>10; -- 조회 안 됨-- 예제) rownum을 사용한 top n 쿼리 작성(급여높은순 3명출력)
select rownum, i.*
from (select e.*
from emp E
order by sal desc) i
where rownum<=3;
-- 예제) rownum을 사용한 top n 쿼리 작성(급여높은순 4~6등 출력)
select *
from (select rownum as rn, i.*
from (select e.*
from emp E
order by sal desc) i)
where rn between 4 and 6;— 2. rank/dense_rank/row_number
— 3. fetch (oracle 12c부터 지원)
select
from
where
group by
having
order by
[offset n {row|rows}]
fetch {first|next} n {row|rows} only;
— first next를 구분하지 않음
— row rows를 구분하지 않음
— offset은 앞의 n개 행을 건너뛰기
-- 예제) emp에서 급여가 높은 순 상위 5명 선택
select empno, ename, sal
from EMP
order by sal desc
fetch first 5 rows only;
-- 예제 emp에서 급여가 높은 순 4~6번째 선택
select empno, ename, SAL
from EMP
order by sal desc
offset 3 rows
fetch next 3 rows only;— 4. top n (sql-server 문법)
select top n [with ties] 컬럼1, 컬럼2, 컬럼3
from
…
order by …
— with ties 사용 시 같은 값인 행 다 출력시켜줘서 n개 이상의 행이 출력될 수 있음
— [Oracle 12c 설치]
— 1. 제품 다운로드
— http://edelivery.oracle.com
— 2. 압축해제 및 설치
— 3. 설치 확인
cmd 에서 sqlplus / as sysdba
select username from dba_users;
alter user hr account unlock identified by oracle;
create user scott identified by oracle quota unlimited on users;
grant create session, dba to scott;— 4. 테이블 생성
--1) dept 테이블 생성
CREATE TABLE SCOTT.DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
ALTER TABLE SCOTT.DEPT
ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);
--2) emp 테이블 생성
CREATE TABLE SCOTT.EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
ALTER TABLE SCOTT.EMP
ADD CONSTRAINT PK_EMP PRIMARY KEY (EMPNO);
ALTER TABLE SCOTT.EMP
ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO);— 5. dept와 emp 테이블 데이터 unload tool 이용해서 dept, emp 값들 insert 하기
— 6. exit 한 후 ‘sqlplus scott/oracle’ 써서 scott으로 접속 후
— C:/users/itwill/test_data.sql 위치하게 만들고
— cmd창에서 ‘@test_data.sql’ 실행