컴퓨터의 일 처리 순서컴퓨터는 우리가 코드를 적은 순서대로 일하지 않고, 철저한 내부 규칙에 따라 움직여. 이 순서대로 기억하면 헷갈릴 일이 절대 없어!
|
sub qurey
/*
sub qurey: query 안의 qruery
ex) select 안에 select
종류
select 한개 row + 한개 column : 한 개의 데이터와 + 한 개의 컬럼만을 사용한다 즉, '데이터가 반드시 하나 '
from 다중 row + 다중 column : 다중 데이터와 =+ 다중 컬럼사용한다
where 한개/다중 row + 한개/다중 coulm 을 사용한다
*/
SELECT 절 안에 또 다른 SELECT : 스칼라 서브쿼리(Scalar Subquery)'
| 모든 사원의 사원 번호를 출력하면서, 그 옆에 'Steven'이라는 이름을 복사·붙여넣기 해서 똑같이 채워라 |
--select
select employee_id,
(select first_name from employees where employee_id = 100)
from employees;
| 괄호 안의 코드는 어떤 사원의 줄을 검사하든 상관없이 무조건 'Steven'이라는 글자 한 개(단일 값)만 뱉어내는 녀석이야. 그래서 마치 SELECT employee_id, 'Steven' FROM employees; 라고 글자를 박아놓고 쓴 것과 완전히 똑같이 작동해! |
FROM employees;
컴퓨터가 사원 테이블로 가서 데이터를 한 줄씩(Row) 읽기 시작해. 사원이 총 107명이라면, 이 짓을 107번 반복할 거야.
SELECT employee_id, ...
첫 번째 칸에는 현재 줄에 서 있는 사원의 진짜 아이디(100, 101, 102...)를 정직하게 출력해.
(SELECT first_name FROM employees WHERE employee_id = 100)
컴퓨터는 사원 한 명 한 명을 출력할 때마다 이 괄호 안으로 들어가서 "야, 100번 사원 이름이 뭐냐?" 하고 물어봐.
100번 사원의 이름인 'Steven'이 나온다
컴퓨터는 이 작업을 107번 반복하면서 모든 사원의 옆 칸에 똑같이 'Steven'을 배달해 버려.
| ⚠️ 스칼라 서브쿼리를 쓸 때 절대 잊지 말아야 할 규칙! SELECT 절에 쓰는 서브쿼리는 무조건 딱 1개의 행(Row)과 1개의 열(Column)만 뱉어야 해. 만약 괄호 안의 조건을 WHERE employee_id >= 100 처럼 여러 명이 나오게 바꾸면, 컴퓨터는 "야! 나는 지금 한 칸에 글자 딱 하나만 넣어야 하는데 왜 이름을 무더기로 가져와?!" 하면서 에러(Subquery returns more than 1 row)를 뿜뿜 뱉어내니까 조심해야 해! |
--다중 컬럼은 사용불가 //에러에러
select employee_id,
(select first_name, salary from employees where employee_id = 100)
from employees;
--다중 row 는 사용 불가 //에러에러
select employee_id,
(select first_name, salary from employees where department_id = 20)
from employees;
--그룹함수랑 함께 사용하기
--ex)월급의 평균도 함께 보고 싶을 때
🎯 한 줄 요약!
|
//에러에러
SELECT employee_id, first_name,salary, avg(sarlry)
from employees;그냥 avg(salary)를 썼을 때 컴퓨터 입장에서는 "야! 나보고 107줄을 따로따로 펼쳐서 보여달라는 거야, 아니면 다 뭉쳐서 딱 1줄로 보여달라는 거야? 눈높이를 하나만 해!" 하면서 에러를 뿜어
//정상작동 확인
SELECT employee_id, first_name,salary,
(SELECT avg(salary) from employees) as 평균,
(select count(*) from employees) as 총수
from employees;그룹함수와 같이 출력하고 싶을 경우 -> 서브 쿼리 사용
전체를 뭉쳐서 평균 구하는 일을 비밀의 방(괄호)에서 미리 끝내고 숫자 딱 하나만 들고 오기 때문에, 개별 사원 정보와 나란히 출력할 수 있다!
--ex)100번 부서의 각 사원들과 100번 부서의 평균 급여가 같이 출력되도록 작성하라
SELECT department_id,employee_id,first_name,salary,
(SELECT round(avg(salary))
from employees
group by department_id
HAVING department_id = 100) as "평균 급여 "
from employees --1번
where department_id =100; -- 2번
from
| 50번 부서의 사원중에 급여가 5000이 넘는 사원을 출력하라 |
SELECT dept, 이름, salary
FROM (SELECT department_id as dept, first_name as 이름, salary
FROM employees
WHERE department_id = 50)
WHERE salary > 5000;
목적: "내가 원하는 데이터만 먼저 싹 도려내서 새로운 임시 테이블을 만들고 싶을 때"
왜 써? employees 테이블은 너무 크고 복잡해서 괄호 () 안에다가 50번 부서 애들만 모아놓은 테이블 하나 만들고
먼저 실행 시킨 것
어떻게 작동해?
1. from절 안에 서브 쿼리 where 절 먼저 실행 employees 테이블에서 가져온 부서 아이디가 = 50일때 , 출력해 칼럼명을 dept 로 바꾼 부서 아이디들을 , 칼럼명을 이름으로 바꾼 첫번째 이름들을, 월급을
2. 그리고 그 중에서 월급이 5000보다 많은 사람들을 뽑은다음
3.그 사람들의 dept 로 바꾼 부서 아이디와 이름으로 바꾼 첫번째 이름들 , 월급을 출력해
| 서브 쿼리의 필요성 |
서브 쿼리 없이도 아래처럼 작성하면 출력이 가능한데 왜 굳이굳이 서브쿼리를 사용할까?
SELECT employee_id, first_name, salary
from employees
where department_id = 50
and salary > 5000;
| 1. 🏷️ "바뀐 명찰(별칭, Alias)"을 바로 다음 조건에 써먹고 싶을 때 |
-- ❌ 에러 발생!
SELECT employee_id, salary * 12 as 연봉
FROM employees
WHERE 연봉 > 50000; -- 👈 컴퓨터: "야, WHERE 절 할 때는 SELECT에 적힌 '연봉'이 뭔지 아직 몰라!"그럴 때 서브쿼리로 감싸면 연봉이 뭔지 컴퓨터가 이해해서 연봉이 50000보다 많은 사람을 출력할 수 있게 된다
| 2. 🧩 "다른 테이블과 조인(JOIN)하기 전에 데이터를 미리 다이어트 시킬 때" (실무 핵심!) |
ex) 우리 회사에 사원이 100만 명이 있고, 부서는 딱 3개(50번, 60번, 90번)가 있는데, 50번 부서 사원들의 이름과 부서명"을 합쳐서 출력하고 싶을 때
> 데이터를 다 모은 다음 필터링하고 출력한다 효율성 제로
SELECT e.first_name, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 50; -- 👈 뒤늦은 필터링
1.from절을 통해서 e 테이블과 d 테이블의 열쇠 키인 department_id 로 조인해서 데이터들을 다 가져온다
2.가져온 데이터들을 where 절에서 department_id 가 50인 사람들만 뽑아낸다
3.그렇게 뽑아낸 데이터들을 SELECT 절에서 e.first_name, d.department_name 칼럼별로 출력한다
> 효율성 좋은 방법
SELECT e.first_name, d.department_name
FROM (SELECT first_name, department_id
FROM employees
WHERE department_id = 50) e -- 👈 [치트키] 괄호 안에서 50번 부서원만 먼저 쏙!
JOIN departments d
ON e.department_id = d.department_id;1. employees 테이블에서 자료들을 가져오는데 department_id 가 50 인 사람들을 가져와라 그 다음 first_name, department_id 를 출력해라
2. 1에서 출력한 데이터를 departments d 테이블과 열쇠 키 e.department_id = d.department_id 를 사용해서 조인해라
3. 조인한 데이터에서 e.first_name, d.department_name 를 출력하라
| department_id = 50 인 사람들의 부서 번호와 이름, 월급 출력하기 |
select department_id, first_name, salary
from employees
where department_id = 50;
서브 쿼리 사용해서 작성
select department_id, first_name,salary
from (select department_id, first_name, salary
from employees
where department_id = 50)
where salary>5000;
이름표(Alias)를 바꾸면 생기는 변화 (에러 원인)
-- 에러에러
select department_id, first_name,salary
from (select department_id as dept , first_name as 이름 , salary
from employees
where department_id = 50)
where salary>5000;1. employees 테이블에서 데이터를 가지고 와
2.가지고 온 department_id 를 이제부터 dept 으로 , first_name은 이름으로 , salary는 그대로 출력해
3. 출력한 데이터에서 salary가 5000 이상인 것들만 따로 뽑아내
4. 뽑아낸 데이터에서 department_id 와 first_name , salary를 출력해
5. 에러에러! 뽑아낸 데이터의 칼럼명은 dept , 이름, salary밖에 없는데 department_id 와 first_name을 어떻게 출력해??
--정상작동하게 만드는 법
--// 안에서 dept, 이름 이라고 정의했다면 밖에서도 dept,이름 으로 사용하기
select dept, 이름,salary
from (select department_id as dept , first_name as 이름 , salary
from employees
where department_id = 50)
where salary>5000;| 업무별로 급여의 합계 인원수 사원명 월급 이 출력이 되도록 구현하라 |
--에러에러
SELECT 인원수, 급여합계, last_name, salary
from (select count(job_id) as 인원수, sum(salary) as 급여합계, last_name, salary
from employees)
group by job_id;1. employees 테이블에서 job_id 가 몇개인지 세서 인원수라고 정의한 칼럼명에 데이터 쫙 넣어서 가져와
2. employees 테이블에서 salary 합계 더해서 급여합계라고 정의한 칼럼명에 데이터 쫙 넣어서 가져와
3. last name 이랑 salary는 그대로 가져와
4. 가져온 데이터들을 job_id 가 같은 것끼리 묶어(그룹핑해)
5. 그룹핑한 데이터들에서 인원수랑 사원명 월급을 출력해
6. 에러에러 job_id 가 어디있어??
--정상작동
SELECT e.job_id, j.salsum, j.cnt, e.first_name, e.salary
from employees e , (select job_id, sum(salary) as salsum,
count(*) as cnt
from employees
group by job_id)j
where e.job_id = j.job_id;
1.. employees에서 job_id 가 같은 것들만 그룹핑해서 사용할게.
2.. 그룹핑 된 데이터에서 , job_id 가 같은 salary들의 합계를 정리해 놓은 칼럼을 salsum이라고 불러서 사용할 거야
3.. 그룹핑된 데이터에서 job_id 칼럼을 사용할 거야
4. 그룹핑 된 데이터에서 job_id 를 전부 세어서 정리한 칼럼을 cnt 라고 불러서 사용할 거야
5. 2,3,4 각각의 데이터들은 통합해서J라고 부르겠어
6. 테이블 e와 J 를 열쇠 키 job_id 로 조인할게
7. 조인한 테이터에서 e.job_id, j.salsum, j.cnt, e.first_name, e.salary 를 출력해
//정상작동 이유 -> j 안에 ' 3.그룹핑된 데이터에서 job_id 칼럼을 사용할 거야 '라고 했음
그래서 6번에서 조인이 가능했던 것
where
| 평균 급여보다 많이 받는 사원 |
--에러에러!
select employee_id,first_name,salary
from employees
where salary > avg (salary);1. employees 테이블에서 데이터들을 가져와
2. salary의 평균이 salary보다 적을 때
3. employee_id,first_name,salary를 가져와
에러이유 : where 는 조건만 검사하는 애라서 평균을 계산 할 수 없다
그래서 쿼리문 사용해서 from으로 데이터 불러올 때부터 평균을 구해서 가져오기
--평균급여 구하는 구문 추가해보기
select avg(salary)from employees;
--정상작동
select employee_id,first_name,salary
from employees
where salary > (select avg(salary)from employees);
// where 는 조건만 검사하는 애라서 평균을 계산 할 수 없다고 했는데
//왜 ? 평균 급여 구하는 구문을 where 절에서 사용할까?
| 💡 핵심은: 괄호 ( )가 붙는 순간 '순서가 리셋'된다! 컴퓨터가 한 줄씩 검사하는 WHERE 절 단계에서는 혼자서 평균을 계산할 능력이 없다고 했잖아? 그 규칙은 변하지 않아. 하지만 WHERE 절을 실행하다가 괄호 (SELECT ...)를 마주치는 순간, 컴퓨터는 하던 일을 일시 정지하고 괄호 안쪽 세상의 FROM과 WHERE를 새로 1등으로 실행해! |
//근데 아예 from 절에서 가져와서 데이터를 불러올 수 있게 만들어 두고 where 절에서는 비교만 해서 사용하면 안 되나?? 아래 방식으로??
//에러에러
select e.employee_id,e.first_name, e.salary
from employees e,(select avg(salary)from employees) as b
where e.salary > b;
// 평균과 월급을 구하는 거니까 b 안에 들어가있는 평균을 꺼내서 where 절에서 비교를 해줬어야 함! 아쉽
--정상작동 ( '카테시안 곱(Cross Join)을 이용한 서브쿼리' 방식)
select e.employee_id,e.first_name, e.salary
from employees e,(select avg(salary)from employees) as b
where e.salary > b.avg;
| 부서가 90인 사원의 업무 |
select first_name, job_id,department_id
from employees
where department_id = 90;
| where 절로 표현하기 |
select first_name, job_id,department_id
from employees
where job_id in(SELECT job_id
from employees
where department_id = 90);1. employees 테이블에서 자료를 가져와
2. 부서 아이디가 90인 데이터들만 통과
3. 통과 된 데이터들에서 job_id 만 출력해
//여기서부터는 ( ) 밖
4. employees 테이블에서 job_id 가져오고 job_id 안에 () 안에서 출력된 결과 job_id가 포함되어 있을 때
5. first_name, job_id,department_id 출력해
| 부서별로 가장 급여를 적게 받는 사원의 급여와 같은 급여를 받는 사원 |
select department_id, min(salary)
from employees
group by department_id;-> 부서별로 최저시급 받는 애는 구할 수 있는데 최저시급 받는 애들이랑 같은 급여 받고 있는 애들은 못 구한다
서브 쿼리 이용
--sub 쿼리 이용
select department_id,FIRST_name ,salary
from employees
where salary in (select min(salary)
from employees
group by department_id);1. 직원 테이블에서 데이터 가져와
2. 가져온 데이터 같은 부서 아이디 사용하는 애들로 묶어서 데이터 뽑아
3. 뽑은 데이터들 안에서 제일 적게 월급 받는 애들만 골라서 출력해
// 서브 쿼리문 끝
1. 직원 테이블에서 데이터 가져와
2. 데이터 중 월급 데이터 있지? 그 안에 서브 쿼리문에서 출력한 가장 적게 월급 받는 애들 데이터 포함 되어 있는 애들만 통과 시켜
3. 통과 시킨 애들 중 부서 번호, 이름, 월급 출력해
| 부서별로 가장 급여를 많이 받는 사원 |
SELECT department_id, first_name, salary
from employees
where (department_id, salary ) in (select department_id, max(salary)
from employees
group by department_id);1. 직원 테이블에서 데이터 가져와
2. 가져온 데이터에서 부서 번호랑, 가장 높은 급여 가져와
// 서브 쿼리문 끝
1. 직원 테이블에서 데이터 가져와
2. 가져온 데이터에서 부서 아이디랑 월급에 서브 쿼리문에서 출력한 부서 아이디랑 제일 많은 월급이 포함 되어있을 때
3. 부서 아이디랑 이름 월급 출력해
특수 쿼리 substr
select employee_id, first_name, phone_number,
case substr(phone_number,1,3)
when '515' then '뉴욕'
when '590' then '워싱톤'
when '65' then '보스톤'
else '기타지역'
end
from employees;자바의 스위치 case 문과 비슷하다고 생각하면 편하다
1단계: 직원 테이블 열기 (FROM employees)
1. 직원 테이블에서 데이터 가져와
2단계: 전화번호 앞자리 자르기 (substr)
substr(phone_number, 1, 3) -> 전 직원의 전화번호 칼럼을 보면서 "첫 번째(1) 글자부터 시작해서 딱 3글자만 가위로 싹둑 잘라라!" 라는 뜻
3단계: 스위치문 돌리기 (CASE ~ WHEN ~ THEN ~ END)
잘라온 글자가 '515'네? ➡️ 그럼 화면에는 '뉴욕'
잘라온 글자가 '590'이네? ➡️ 그럼 화면에는 '워싱톤'!
잘라온 글자가 65 네? ➡️ 그럼 화면에는 '보스톤'!
잘라온 글자가 515도, 590도, 65도 아니네? (ELSE) ➡️ 귀찮으니까 그냥 '기타지역'
END: "자, 말 바꾸기 게임 끝!"
over 함수
group by 를 보강하기 위해서 나온 함수
select 절에서만 사용이 가능하다
OVER 함수의 정확한 정체: "복사·붙여넣기 셔틀(배달원)"
원하는 값을 똑같이 필요한 만큼 복붙함으로써 비어있던 나머지 칸들을 채워주는 함수다
| ex) 사원번호와 이름을 출력하고 전체 인원을 세어라 |
--에러에러
select employee_id, first_name,count (*)
from employees;에러이유 : count 결과 값이 107이라고 했을 때 107은 표 한 칸에만 들어가면 되는데 사원번호와 이름은 107개의 칸(줄) 이 필요하므로 서로 다른 수의 줄이 필요해서 에러 발생
1. 직원테이블에서 데이터 가져와
2. 가져온 데이터에서 사원번호, 이름 출력하는데 뭐 가리지 말고 전체 인원수를 다 세워줘
=> 결과 : 엥?? employee_id, first_name 데이터는 107개인데 님이 원한 count 는 값이 하나밖에 없잖아 !! 나 일 안해 !!!!
이럴 때 사용하는 것이 over 함수
서로 출력을 위해 필요한 행의 갯수가 다를 때 서브 쿼리 혹은 over 을 사용해서 문제를 해결 할 수 있다.
1. 서브 쿼리 이용하기
SELECT employee_id, first_name,
(SELECT COUNT(*) FROM employees) AS 전체인원수
FROM employees;1. 직원테이블에서 정보 가져와 , 그리고 가져온 정보 모두 세서 출력해
// 서브 쿼리문 탈출
2. 직원테이블에서 정보 가져와
3. 직원아이디랑, 이름이랑, 아까 1에서 출력한 결과 칼럼명 전체 인원수로 지어주고 출력해
2. over 함수 사용하기
select employee_id, first_name,count (*) over(),
avg (salary) over()
from employees;
1. 직원테이블에서 정보 가져와
2. 직원 아이디, 이름, (직원아이디랑 이름 수 만큼 센 결과 하나 나오지? 그거 빈칸에 전부 빈칸 만들어서 복붙해 ), 급여 평균 계산하고 그거 전부 빈칸만들어서 복붙해
partittion by
원하는 것만 그룹을 짜서 계산을 하게 해 준다
왜 쓸까?
1. group by 사용시 department_id, AVG(salary) 만 출력이 가능하다 사원 개개인의 이름은 출력 불가하다
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;>총 12개 행의 데이터 나옴
2. 만약 사원 개개인의 이름들도 출력하고 싶어서 그룹 바이에 사원 이름도 넣어준다면?
SELECT department_id, first_name, AVG(salary)
FROM employees
GROUP BY department_id, first_name; -- 👈 부서와 이름을 동시에 묶음!> 총 101 개의 데이터가 나옴 (프로듀스 101 도 아니고....)
왜 이럴까??
group by 는 중복되는 것끼리 서로 묶어주기 떄문이다. 근데 2번 출력을 했을 떄 같은 이름을 가진 사원들이 없기 때문에 1번의 결과와는 전혀 다른 결과가 나오게 된다 즉, 이름이 다 다르기 때문에 부서끼리 제대로 뭉치지 못하고, 그냥 각각의 이름대로 (압축 안 된 채) 전체가 다 출력되어 버린다
여기서 평균 값이 사원 개개인의 평균 값으로 계산되어진다 즉, 부서의 평균을 구하고 싶었던 원래의 목적과 달라짐
| 결론 => GROUP BY에 이름까지 같이 묶어버리면 에러는 안 나지만, 그룹이 사원 개개인으로 너무 잘게 쪼개져서 부서의 평균을 구하려던 원래 목적과 완전히 달라진다. 따라서 사원 개인의 정보(이름, 월급)를 그대로 유지하면서, 옆 칸에 **'진짜 부서 평균'**을 나란히 복붙하고 싶을 때는 **PARTITION BY**를 쓰는 것이 정답이다! |
| 파티션 바이 사용 문법 공식 |
함수명() OVER (PARTITION BY 그룹묶을칼럼 ORDER BY 정렬할칼럼)
- PARTITION BY: 어떤 카테고리(편)별로 쪼개서 계산할 것인가?
- ORDER BY: 그 쪼개진 편 안에서 어떤 순서로 줄을 세울 것인가? (필요 없으면 생략 가능)
| 예제 1: 부서별 평균 급여 구하기 (AVG) |
SELECT department_id, first_name, salary,
AVG(salary) OVER (PARTITION BY department_id) AS 부서평균급여
FROM employees;1. 직원테이블에서 정보 가져와
2 이제 같은 부서 아이디를 공유하는 애들끼리 월급 평균을 내, 평균이 나오면 평균쪽 빈칸에 전부 복붙해 ,항목 명은 부서 평균 급여라고 해
3. 부서아이디, 이름 , 월급, 월급 평균 출력해
출력전에 컴퓨터는 OVER()나 PARTITION BY가 붙은 계산방(윈도우)을 제일 먼저 찾아가서 계산을 싹 다 끝내고 출력한다
| over 와 PARTITION by |
select department_id, first_name,
count(*)over(PARTITION by department_id),
avg(salary) over(PARTITION by department_id),
sum(salary) over(PARTITION by department_id)
from employees;1. 직원테이블에서 정보 가져와
2. count( 부서아이디 별로 중복되는 것 끼리 묶어, 묶은 그룹이 부서별로 부서아이디 몇개인지 세고 빈칸에 센 갯수 채워넣어)
3.avg( 부서아이디별로 중복되는 것 끼리 묶어, 묶은 그룹들의 부서별월급 평균 구하고 빈칸에 구한 평균 채워넣어)
4. sum( 부서아이디별로 묶어, 묶은 그룹들의 부서별 월급 총합 구하고 빈칸에 월급 총합 채워넣어)
5. 부서 아이디, 이름, 부서별 부서 아이디를 쓰는 사람 갯수, 부서별 월급의 평균 , 부서별 월급 총합 출력해
if 전체 부서의 평균을 구하려면 파티션 바이를 뺀 오버 함수만 사용하면 된다
SELECT department_id, first_name,
COUNT(*) OVER(), -- 👈 괄호 안을 비우면 회사 전체 인원수!
AVG(salary) OVER(), -- 👈 회사 전체 월급 평균!
SUM(salary) OVER() -- 👈 회사 전체 월급 총합!
FROM employees;
if 그룹별로 구한 평균의 평균을 구하려면??
SELECT AVG(부서평균) AS 부서평균들의_진짜평균
FROM (
-- 👈 1단계에서 만든 부서별 평균 표를 여기에 쏙 집어넣음!
SELECT department_id, AVG(salary) AS 부서평균
FROM employees
GROUP BY department_id
) AS 부서별표;이렇게 하는 이유?
-> 오버나 파티션 바이를 사용해서 생성해낸 칼럼들은 그대로 유지하는 성질을 가지고 있어서 평균의 평균을 구하기가 어렵다. 그래서 계산시 2단계로 나눠주어야 한다
<1단계 GROUP BY로 부서별 평균 표를 먼저 만들기>
1. 직원 테이블에서 정보 가져와
2. 그룹 바이로 부서 아이디 같은 애들끼리 묶어서 데이터 정리해
3. 정리된 데이터를 가지고 부서 아이디, 부서 아이디 같은 애들의 월급 평균을 계산하고 칼럼명은 부서평균이라고 해서 출력해
//서브 쿼리 종료
<2단계: 그 표를 통째로 감싸서 진짜 전체 평균 구하기 (최종)>
1. 서브 쿼리에서 출력한 정보들을 부서별표라는 가상테이블 (임시상자)에 집어넣어
이유: 칼럼은 항목 하나를 뜻하는 건데 1단계에서 만든 건 항목 하나가 아니라 아니라 [부서아이디, 부서평균]이 들어있는 '하나의 완성된 표들 ' 즉, 데이터가 되었기 떄문
2. 부서별표에 정보들 다 모았어? 그럼 부서별표 테이블 안에 들어있는 부서 평균 꺼내, 부서별 평균들의 찐찐찐 전체 종합 평균 구하고 칼럼명은 부서평균들의 진짜 평균이라고 하자
순위 함수
종류
| 함수 이름 | 재미나가 적은 예시 | 컴퓨터의 행동 원리 (특징) |
| RANK() | 1, 2, 3, 3, 5, 6 | "공동 순위 인정, 대신 다음 등수 건너뛰기!" 공동 3등이 2명이니까, 다음 사람은 4등을 건너뛰고 5등이 돼. (올림픽 스타일 🥇🥈🥉🥉) |
| DENSE_RANK() (오타 교정: rand ❌ rank ⭕) |
1, 2, 3, 3, 4, 5 | "공동 순위 인정, 틈새 없이 빽빽하게 꽉 채우기!" Dense(빽빽한)라는 이름답게, 공동 3등이 나와도 다음 사람한테 빈틈없이 바로 4등을 줘. |
| ROW_NUMBER() | 1, 2, 3, 4, 5, 6 | "공동 순위? 내 사전엔 없다! 무조건 1줄로 서!" 값이 똑같든 말든 상관없이, 위에서부터 순서대로 1, 2, 3, 4, 5, 6 고유한 줄 번호를 붙여버려. |
| ROWNUM | 1, 2, 3, 4, 5, 6 | "정렬하기 전, 태어난 순서대로 매기는 번호표!" 위의 3개 함수(윈도우 함수)랑 다르게, 오라클(Oracle) 데이터베이스에서 테이블에서 행을 꺼내오는 순서대로 붙여주는 임시 번호표야. |
목적 -> 순위를 매기기 위함
| 급여를 많이 받는 사원을 1위 부터 10 위 까지 출력해라 |
--에러에러
select first_name,salary
from employees
order by salary desc;> 이걸 실행하면 단순히 줄만 세워줄 뿐 옆에 1등, 2등 등수는 세워주지 않는다
--순위 함수 적용한 정상작동 코드
SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS 월급순위
FROM employees;
1.직원 테이블에서 정보를 가져온다
2.. 월급을 내림차순으로 정리하고 순위를 매긴다 이 정리한 걸 칸들에 복붙한다
4. 이름, 월급, 월급순위르 출력한다 이때 월급 순위는 내림차순으로 정리되어 있다
1단계: (ORDER BY salary DESC) ➡️ 정렬 규칙 확인
컴퓨터가 괄호 안을 가장 먼저 봅니다. "아하, 전 직원의 월급을 높은 순서대로 머릿속으로 줄을 세우라는 뜻이구나!" 하고 기준을 잡아요.
2단계: RANK() OVER (...) ➡️ 등수 계산 완료
그 정렬 규칙을 바탕으로 컴퓨터가 머릿속에서 전 직원의 등수(1등, 2등, 2등, 4등...)를 진짜 숫자로 계산해서 손에 꽉 쥡니다.
3단계: AS 월급순위 ➡️ 최종 명찰 붙이기 (가장 마지막!)
이제 등수 숫자들을 다 구했으니, 화면에 이쁘게 뿌려주기 위해 맨 마지막에 월급순위라는 명찰(칼럼명)을 탕! 붙여서 표의 세로 칸을 완성하는 거야.
헷갈린 부분 !
| OVER(ORDER BY salary DESC) 이 작업에서 over 로 복붙해준 게 맞는데 왜 바로 복붙을 하지 않고 rank까지 한 결과를 over 로 복붙하는 거지?? 재미나가 헷갈렸던 건 "글자 순서상 RANK()가 앞에 있고 OVER()가 뒤에 있는데, 왜 OVER가 먼저 일하지?"라는 부분이었잖아? 컴퓨터가 일하는 진짜 메커니즘을 '복붙'이라는 개념에 찰떡처럼 결합해서 딱 정리해 줄게! 🔄 컴퓨터가 일하는 진짜 순서: "무대 세팅 ➡️ 계산 ➡️ 복붙"컴퓨터는 RANK() OVER (ORDER BY salary DESC)를 보면 다음과 같이 3단계로 나누어서 쾅쾅쾅 처리해.
✍️ 재미나를 위한 명쾌한 결론즉, 재미나 생각대로 "결국 옆 칸에 데이터를 안 합치고 그대로 복붙해 준 일등 공신"은 OVER가 맞다는 거지! RANK()는 그냥 그 안에 들어갈 '등수 숫자'만 계산해 준 기술자일 뿐이야. 😉 |
over ( 순위 결정 코드 ) -> order by
select employee_id, first_name,salary,
rank()over(order by salary desc) as "rank" ,
dense_rank() over(order by salary desc) as "dense_rank",
row_number () over (order by salary desc) as "row_number"
from employees;1. 직원 테이블에서 정보 가져와
2. rank -> 순위 매길건데 월급을 내림차순으로 정리해서 순위 매기고 rank 라는 칼럼 만들어서 거기에 복붙해 : 1등부터 출력
3. "dense_rank" -> 역으로 순위 매길거야 월급도 많은 것부터 역으로 정리해서 순위 매기는데 공동순위 생기면 다 공동순위 인정해주고 그 다음 순위에 4 등줘 "dense_rank" 라는 칼럼 만들어서 복붙해
4.row_number -> 공동 순위 없이 순위 매길거야 그냥 위에서부터 순서대로 고유한 줄 번호 매겨 안에 내림차순으로 정리된 내용으로 줄에 대한 번호 매길거야
| --사원 월급의 순위 1위 부터 10위까지 산출한다 |
--에러에러
select row_number()over(order by salary desc) as rnum,first_name, salary
from employees
where rnum >= 1 and rnum <= 10
order by salary desc;1. 직원테이블에서 정보 가져와
2. 그 정보들에서 rnum 이 1 and rnum 보다 작고 10보다 작거나 같을 때 조건문 통과 시켜
3. 통과 된 정보들의 월급을 내림차순으로 정리해
4. 월급 내림차순으로 정리해서 공동순위 없이 1줄로 세운 데이터를 runm에 복붙해. 이름. 월급 출력해
에러이유 : 아니 근데 출력은 나중인데 내가 rnum 이 뭔지 어떻게 알아?? 이게 변수야?? 자바에서도 변수 선언 먼저하고 사용해
--정상작동 하려면??
--옳은 순서대로 다시 쓰기
--1. 번호 할당 -> 서브 쿼리로 만들어주기
--2. 범위 설정
| 정밀하게 등수를 제어하고 싶을 때 |
select rnum, first_name,salary
from (SELECT ROW_number() over(order by salary desc) as rnum,
first_name, salary
from employees) --> 번호 할당
where rnum between 11 and 20; -->범위 설정| 예제 종류 | 🛠️ 핵심 도구 | 🏁 최종 목표 |
| 1번 예제 | ROW_NUMBER() | "데이터에 번호표(rnum)를 직접 예쁘게 인쇄해서 정밀하게 통제하겠다!" |
ROW_NUMBER() + 서브쿼리 사용
1. 직원테이블에서 정보 가져올게
2. 가져온 정보에서 월급을 내림차순으로 정리하고 공동순서 없이 위에서부터 내림차순 순서대로 1부터 번호 쭉 매겨 그리고 이걸 rnum 이라는 칼럼 안에 복붙해
//서브 쿼리 탈출
1. rnum 으로 가져온 정보들 불러봐
2. 여기서 11등부터 20등 사이에 있는 애들만 조건문 탈출이다
3. 조건문까지 탈출한 정보들로 rnum, 이름, 급여 출력해
출력문에 rnum 적어도 rnum안에 있는 데이터가 다 출력되지 않는 건 이미 where 문에서 11등 20등 사이에 있는 조건들만 가져갈 수 있다고 했기 때문
| 인터넷 게시판처럼 빠르게 페이지를 넘기고 싶을 때 |
select first_name, salary
from employees
order by salary desc
limit 10 -- 원하는 row 의 갯수
offset 0; -- 시작위치. 처음이 1이 아닌 0| 예제 종류 | 🛠️ 핵심 도구 | 🏁 최종 목표 |
| 2번 예제 | LIMIT / OFFSET | "번호표 인쇄고 뭐고 귀찮고, 웹 사이트 게시판처럼 그냥 원하는 페이지만 초고속으로 싹둑 잘라오겠다!" |
LIMIT + OFFSET 사용
| LIMIT : 가져올 데이터의 개수 (자르는 길이 OFFSET : 건너뛸 데이터의 개수 (시작점, 0부터 시작!) |
1. 직원테이블에서 정보 가져와
2. 가져온 정보에서 월급 정보들 꺼내고 내림차순으로 정리해
3. 내림차순으로 정리된 내용에서 건너뛸 데이터는 0개고 내림차순으로 정리된 내용에서 총 10개 가져와
4. 이제 이름이랑 가져 급여 출력해봐
강사님 work
| -- 문제1) EMPLOYEES 테이블에서 Kochhar(last_name)의 급여보다 많은 사원의 사원번호, 이름, 담당업무, 급여를 출력하라. -- 문제2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하여라. -- 문제3) EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라 -- 문제4) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라. -- 단 업무별로 정렬하여라. -- 문제5) EMPLOYEES 테이블에서 업무(JOB_ID)가 SA_MAN 사원들의 이름, 업무, 부서명, 근무지(city)를 출력하라. -- 문제6) EMPLOYEES 테이블에서 가장 많은 사원을 갖는 MANAGER의 사원번호를 출력하라. -- 문제7) EMPLOYEES 테이블에서 가장 많은 사원이 속해 있는 부서 번호와 사원수를 출력하라. -- 문제8) EMPLOYEES 테이블에서 사원번호가 123인 사원의 직업과 같고 사원번호가 192인 사원의 급여(SAL)보다 많은 사원의 사원번호,이름,직업,급여를 출력하라. -- 문제9)직업(JOB)별로 최소급여를 받는 사원의 정보를 사원번호,이름,업무,부서명을 출력하라. < 'PostgresSQL > PostgresSQL 수강일지' 카테고리의 다른 글
|