sql로 데이터 다루기2

1. Join

1.Join : 두개 이상의 테이블을 연결, 결합하여 데이터를 출력하는 것.

  EQUI join 등가 교집합   : 두 테이블 간에 서로 정확하게 일치하는 경우 조인하는 것.

등가 연산자 ' = ' 을 사용.

대부분 기본키 - 외래키 관계를 기반으로 발생.

  Non EQUI join  비등가 교집합 : 두 테이블 간에 서로 정확하게 일치하지 않는 경우를 활용하는 조인.

등가연산자 외에 > >= < <= BETWEEN 등 을 사용.

 

2. From 절 Join형태

1. Inner Join 내부 JOIN

-> Join의 기본값, inner를 생략하고 join 만 사용한다.

JOIN 조건에서 동일한 값이 있는 행만 반환한다.

on 조건절에 사용할 컬럼명은 같지 않아도 된다.

select employee_id, name, position_name 

from EMPLOYEE e 

join POSITION_T t

on e.position_id = t.position_id

 

2. Using(기준 컬럼) -> SQL server 지원 x

같은 이름을 가진 컬럼들 중 원하는 컬럼에 대해서만 선택적 등가조인.

* 별칭 사용 못함.

select REQUEST_ID, STUDENT_NAME, CLASS_NAME 
from CLASS_REQUEST

join STUDENT 
using(STUDENT_ID)

join CLASS 
using(CLASS_ID);

 

3. Natural Join

같은 이름을 가진 모든 컬럼에 대해 등가조인.

* On 조건절, Using 조건절, Where 절에서 Join 조건 정의 못함.

* 실무에서 많이 사용되지는 않는다.

 

4. Cross Join

Join 조건이 없는 경우, 생길 수 있는 모든 데이터의 조합을 조회.

-> Cross 키워드를 생략하고 Join으로만 사용할 수 있다.

inner join과 Cross join을 구분하는 것은 뒤의 on 절이다.   on 을 쓰면 inner join, 안쓰면 cross join이다. 

 

5. Outer Join

빈 곳은 Null 로 채움.

select * from USER, CLASS where USER.class_id += CLASS.class_id;

+가 왼쪽에 있으므로 left ourter join. 왼쪽 테이블의 정보를 모두 가져오고, 오른쪽 테이블에 없는 값은 Null로 둔다.

select * from USER left (outer) join CLASS on USER.class_id = CLASS.class_id;

두 코드는 서로 같은 쿼리.

 

Right outer join :  오른쪽 테이블을 다 가져오고 왼쪽 테이블에 없는 값은 Null로 둔다.

left outer join에서 두 테이블의 위치를 바꿔도 right outer join과 동일한 결과가 나온다.

실무에서는 Left outer join을 많이 사용하고 왼쪽에 기준 테이블을 둔다.

 

Full outer join : 두 테이블의 값을 모두 가져오고 빈 곳에 Null을 준다.

outer 키워드도 생략할 수 있지만 일반적으로 생략하지 않고 사용한다. 

** Maria DB, MySQL 에서는 full outer join을 지원하지 않는다.

-> left outer join과 right outer join을 UNION 으로 연결하여 사용한다.

 

 

3. 셀프 조인

: 같은 테이블끼리 조인하는 것.

컬럼명과 테이블명이 모두 일치하기 때문에 별칭이 필수적이다.

조인할 컬럼을 줄 때 on 이 아니라 where로 줘야 한다.

SELECT a.employee_id, a.manager_id, b.manager_id as manager
FROM EMPLOYEE a, EMPLOYEE b 
WHERE a.manager_id = b.employee_id;

 

---

2. 서브쿼리

1. 동작방식에 따른 서브쿼리 분류

: 서브쿼리에 메인쿼리의 컬럼이 포함되는지에 따라 구분한다.

연관 서브쿼리 : 메인 쿼리의 컬럼이 서브쿼리에 포함되고, 메인쿼리의 컬럼은 서브쿼리에 특정 조건으로 사용되는 경우.

select id, department_id, name, salary 
from Employee a 
where salary > (
select avg(salary) 
from Employee b 
where b.department_id = a.department_id     // 메인쿼리의 컬럼인 a.department_id가 서브쿼리에 있음.
);

비연관 서브쿼리 : 메인 쿼리의 컬럼이 서브쿼리에 포함되지 않는 경우.

select avg(salary)

from Employee

where department_id = (

// 서브쿼리에 메인쿼리에서 사용되는 컬럼이 없고, 서브쿼리만으로도 실행된다.

select department_id from Employee where name = 'elice'   


);

 

2. 반환 데이터 형태에 다른 서브쿼리 분류

 

1. 단일행 서브쿼리 : 서브쿼리가 한 개의 행을 반환.

-> 단일행 비교연산자 = < > <= >=와 함께 사용.

 

2. 다중행 서브쿼리 : 서브쿼리가 둘 이상의 행을 반환.

-> 다중행 비교 연산자 in, all , any, exists 등과 같이 사용.

IN : 서브쿼리결과의 값들 중 하나와 일치하면 메인쿼리를 실행.

EXISTS: 서브쿼리의 결과값이 존재하면 메인쿼리를 실행. 

ALL : 서브쿼리의 모든 결과값들에 대해 조건을 만족해야 메인쿼리를 실행.

ANY : 서브쿼리결과의 값들 중 하나 이상 조건을 만족하면 메인쿼리를 실행.

 

3. 다중컬럼 서브쿼리 : 서브쿼리가 여러개의 컬럼을 반환.

// 각 부서에서 급여가 가장 높은 직원의 이름과 급여를 출력
select name, salary

from EMPLOYEE 

where (department_id, salary) in      <= 컬럼 같게 적어야함.

(select department_id, max(salary) 

from employee 

group by department_id)

-> 메인쿼리에 서브쿼리의 결과값과 비교할 컬럼을 같이 적어줘야 한다.

 

3. 스칼라 서브쿼리

: 하나의 속성을(컬럼) 가지면서 하나의 행만을 반환하는 쿼리.

-> select, where, having 절 등에서 사용가능.

 

4. 뷰

: db에 저장되지 않고 논리적으로만 저장되는 

 

create view view_name as           // 뷰 생성

select col1, col2 fro table where condition;    // 뷰를 정의할 쿼리

이미 있는 뷰를 대체하려면 create 대신 replace를 사용.

 

1. 장점

독립성 : 테이블 구조가 변경되어도 뷰를 사용하고 있는 프로그램은 변경하지 않아도 된다.

편리성 : 자주 사용되는 복잡한 쿼리를 뷰로 미리 정의해 두고 사용할 수 있다.

보안성 : 컬럼마다 열람 가능한 사용자의 권한을 지정할 수 있다.

 

2. 특징

뷰로 다른 뷰를 생성할 때 사용할 수 있다.

뷰는 변경할 수 없다.

삭제하면 재생성 해야 한다.

뷰를 이용해 데이터를 갱신할 때 원천 테이블의 기본키가 포함되어 있어야 한다.

뷰의 원천인 테이블이나 뷰가 삭제되면 이를 기반으로 하는 뷰도 삭제된다.

 

---

 

3. 그룹 함수, 윈도우 함수

1. 윈도우 함수  : 행과 행 사이의 관계를 정의하는 함수

-> over 구문이 필수.

1. 쿼리

SELECT WINDOW_FUNCTION(ARGUMENTS)

OVER( [ PARTITION BY 칼럼 ] [ ORDER BY 칼럼 ] [ WINDOWING 절 ] ) FROM 테이블 명;

• ARGUMENTS : 윈도우 함수의 인수
• PARTITION BY : 전체 집합에 대해 소그룹으로 나누는 기준
• ORDER BY : 소그룹에 대한 정렬 기준
• WINDOWING : 행에 대한 범위 기준
  • ROWS : 물리적 단위, 행의 집합
  • UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫번째 행
  • UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 행
  • CURRENT ROW : 윈도우 시작 위치가 현재 행

 

PARTITION BY 칼럼 : GROUP BY처럼 함수를 실행할 기준 범위를 준다.

그 컬럼 중 같은 값들에 대해 함수를 실행한다.

WINDOWING 절에는 행 중에서 함수를 실행할 범위를 준다. 예를들어 CURRENT ROW를 주면 현재 행부터, UNBOUNDED PRECEDING을 주면 첫 행부터(전체) 실행한다.

 

2. 종류

1. 순위 함수

① RANK( ) OVER ( [ PARTITION BY 칼럼 ] [ ORDER BY 절 ] [ WINDOWING 절 ] )

동일한 값에 동일한 순위를 부여

② DENSE_RANK( )

같은 값에 같은 순위 부여, 같은 순위는 한건으로 취급

③ ROW_NUMBER( )

동일한 값에도 고유한 순위 부여

SELECT MEMBER_ID, SQUAT, BENCH_PRESS, DEADLIFT, (SQUAT + BENCH_PRESS + DEADLIFT) AS WEIGHT_SUM,

RANK() OVER (ORDER BY WEIGHT_SUM DESC) AS RANK

FROM GYM_MEMBER;

 

2. 일반 집계 함수 : 윈도우 함수에 포함된다.

-> AVG(), SUM(), MAX(), MIN()

OVER를 사용하여 PARTITION BY나 ORDER BY, WINDOWING절을 줄 수 있다.

SELECT SELL_ID, SELLER_NAME, PRODUCT_NAME, QUANTITY, 

// SELLER_NAME, PRODUCT_ID 별로 PRICE * QUANTITY의 합계를 SELL_PRICE 컬럼으로 출력

SUM(PRICE * QUANTITY) OVER (PARTITION BY SELLER_NAME, SELL.PRODUCT_ID)

as SELL_PRICE

FROM SELL INNER JOIN PRODUCT

ON SELL.product_id = PRODUCT.product_id

ORDER BY SELL_ID;

 

3. 그룹 내 행 순서 함수

① FIRST_VALUE() : 가장 먼저 나온 값

② LAST_VALUE() : 가장 나중에 나온 값

③ LAG( 컬럼명, X번째 ) : 어떤 컬럼의 이전 X번째 행

④ LEAD( 컬럼명, X번째 ) : 어떤 컬럼의 이후 X번째 행

 

4. 그룹 내 비율 함수

① RATIO_TO_REPORT( 컬럼 ) : 컬럼의 전체 SUM에 대한 각 행의 비율

-> MARIA DB에서 제공 X.  =>  SALARY / SUM(SALARY) 로 구현

② PERCENT_RANK( ) : 순위를 백분율로 구한다. (0부터 1까지 )

③ CUME_DIST( ) : 현재 행보다 작거나 같은 건들의 수를 누적 백분율로 구함 ( 0 초과 1까지 ) 

-> PERCENT_RANK, CUME_DIST 모두 인자에 컬럼은 넣지 않고, OVER구문에 ORDER BY로 기준 컬럼을 준다.

④ NTILE( N ) : 행들을 N등분 한 결과

ORDER BY에 기준 컬럼을 준다.

-> DATA의 수가 N과 맞아떨어지지 않으면 앞에 있는 그룹부터 1개씩 추가.

EX) DATA 수는 7개인데 NTILE(3)이 주어지면 3, 2, 2개로 그룹이 나눠진다.

 

 

2. 그룹함수

1. GROUP BY 컬럼 

 

2. ROLL UP( 컬럼 ) : 그룹화하는 컬럼에 대해 부분적인 통계를 제공

* ORACLE :

GROUP BY ROLL UP( DEPARTMENT_NAME, JOB_NAME ) => 부서이름 기준으로, 전체 행에 대해 소계 행이 생긴다.

* MARIA DB :

GROUP BY DEPARTMENT_NAME, JOB_NAME WITH ROLLUP;

** JOB_NAME 부터 roll up 되서 JOB_NAME 전체 소계를 DEPARTMENT_NAME 에서 내고,

DEPARTMENT_NAME 전체 소계를 전체 데이터에 대한 낸다.

 

3. CUBE( ) : ROLLUP 함수에서 제공하는 결과를 포함해, GROUP BY 하는 컬럼에 결합 가능한 모든 경우의 수에 다차원 집계를 생성.

ROLLUP 에 인자로 준 컬럼들 모두에 대해서, 가능한 소계를 다 나타낸다.

EX) 위 쿼리에서 DEPARTMENT_NAME 과 JOB_NAME 순서로 ROLLUP에 줬을 때의 결과와

JOB_NAME, DEPARTMENT_NAME 순서로 줬을 때 결과가 같이 나온다.

* MARIA DB :

CUBE() 제공 안함 -> UNION으로 ROLLUP 함수들을 모든 경우에 대해 만들고 합쳐야함.

 

4. GROUPING SETS( 컬럼 )

: 주어진 모든 컬럼에 대해 개별 통계를 생성한다.

-> 각 컬럼을 GROUP BY 한 통계를 모두 UNION ALL한 것.

* MARIA DB

GROUPING SETS 지원 X. -> UNION ALL로 GROUP BY를 합쳐줘야 한다.