[SQL] SQL 기초 - 3

GROUP BY와 함께 쓰이는 소계: ROLLUP, CUBE, GROUPING SETS,

• ROLLUP은 순서탐
• CUBE는 모든 경우의 수
• GROUPING SETS는 원하는 경우만
• GROUPING은 ROLLUP, CUBE, GROUPING SETS와 함께 쓰이며, GROUP BY에서 쓰인 소계 함수결과 CASE에서 빠진 컬럼(NULL이 되는 자리)에 대해 1을 반환한다. 아니면 0을 반환.

ROLLUP
엑셀에 있는 SUM(A1:C5) 과 같은 기능을 한다.
가장 마지막 행에 최종 합을 넣는다.
group by절을 rollup으로 묶는다.

select order_dt, count(*) from starbucks_order group by order_dt order by order_dt;
select order_dt, count(*) from starbucks_order group by rollup(order_dt) order by order_dt;

-- gruop by로 2개 묶기
-- 날짜별 소계와 전체 총합이 나오는 쿼리
select order_dt, order_item , count(*) from starbucks_order group by rollup(order_dt, order_item) order by order_dt;

rollup은 순서를 탄다. 
위의 결과는 `group by order_dt, order_item + group by order_dt + 총합계`

-- group by로 3개를 묶었을 때
select order_dt, order_item, reg_name, count(*) from starbucks_order
    group by rollup(order_dt, order_item, reg_name) order by order_dt;

위의 결과는
group by order_dt, order_item, reg_name 그룹바이 묶고

• group by order_dt, order_item 소계
• group by order_dt 소계
• 총합계
  와 같다.

※ Group By의 가장 끝쪽 컬럼에서 부터 하나씩 - 처리 (하나씩 빠짐)

• group by로는 묶었으나, 소계가 필요하진 않을 때. ( )로 묶어 rollup에서는 제외하기

select order_dt, order_item, reg_name, count(*)
    from starbucks_order
    group by rollup(order_dt, (order_item, reg_name))
    order by order_dt;

서브쿼리

• 쿼리 안의 쿼리 (메인쿼리와 서브쿼리)

1. select절에 사용하는 스칼라 서브쿼리
2. from절에 사용하는 인라인 뷰
3. where절에 사용하는 중첩 서브쿼리

3. where 중첩 서브쿼리

• 결과를 하나만 반환하는 경우를 '단일행 서브쿼리'라 한다.

-- 서브쿼리(인라인뷰)로 구현
select * from hr.employees A 
where A.department_ID in ( select b.department_id
			from hr.departments B
			where B.location_id=1700);

-- inner join으로 구현
select *
	from hr.employees A,
		 hr.departments B
	where A.department_id = B.department_id
	  and B.location_id = 1700;

• 비효율적인 서브쿼리: 동일한 테이블을 몇번이고 access하는 것

2. from절에 사용하는 인라인 뷰

• 쿼리내에 직접 기술 된 뷰

1. select절에 사용하는 스칼라 서브쿼리

• 스칼라 서브쿼리 - select 절 (그 외 컬럼값이 올 수 있는 대부분의 곳)
• 스칼라 서브쿼리는 하나의 값만 출력되는 서브쿼리

select A.first_name, A.last_name, A.salary, A.department_id,
    	(select B.department_name
	from hr.departments B
	where B.department_id = A.department_id
	and rownum = 1 -- 여러개가 나오는 경우를 방지하기 위해서
	) as department_name
    from hr.employees A
    where salary > 5000;

• 스칼라 서브쿼리는 캐싱 기능이 있다.

분석/집계 함수
select 문에서는 집계
select 다음에 over는 전체로우 옆에 집계함수를 보여준다
특정 그룹만 대상으로 하겠다. partition by

https://livesql.oracle.com/apex/livesql/file/tutorial_GNRYA4548AQNXC0S04DXVEV08.html

over() 집계된 결과를 행의 개수만큼 보여준다.

select count(*), min(weight), max(weight), avg(weight) from bricks;

select count(*) over () from bricks; -- bricks의 row수 만큼 보여준다.

select B.*, 
       count(*) over ()
    from bricks B;

over(partition by) 그룹영역을 지정해준다. 파티션을 지정해주는것

select b.*, 
       count(*) over (
         partition by colour -- 컬러별로 카운트를 진행하겠다는 의미
       ) bricks_per_colour, 
       sum ( weight ) over (
         partition by colour -- 컬러별로 무게의 총합을 내겠다는 의미
       ) weight_per_colour
from   bricks b;

over(order by) partition의 개념에서 누적값으로 진행하는것!

• order by 뒤의 컬럼에는 유니크한 값이 들어가는 것이 좋다. (like id)
• windowing clause를 사용한다면 order by로 묶어준다

-- 구분없이 전체 brick들을 누적
select b.*, 
       count(*) over (
         order by brick_id
       ) running_total, 
       sum ( weight ) over (
         order by brick_id
       ) running_weight
from   bricks b;

-- 색깔별 구분(partition by colour)하여 누적
select b.*, 
       count(*) over (
         partition by colour
         order by brick_id
       ) running_total, 
       sum ( weight ) over (
         partition by colour
         order by brick_id
       ) running_weight
from   bricks b;

쿼리 실행순서가 from > where > group by 이기 때문에
from절에서 테이블을 확인하고
where절에서 해당 '테이블에 있는 값'을 끌어다가 쓴다. select 문에 있을 내용을 where절에서 바로 사용 할 수 없다.

-- 아래의 쿼리는 에러를 일으킨다.
select colour
	from bricks
	where  count(*) over ( partition by colour ) >= 2;

--위의 에러 쿼리는 아래와 같이 작성해야한다.
select * from (
    select B.*,
    	count(*) over(partition by colour) as colour_cnt
	from bricks B
)
where colour_cnt >= 2;

rownum, row_number()
rownum()은 단순 넘버링. 때문에 order by를 하게 되면 뒤죽박죽 흐트러지게 된다.
rownum은 반드시 1부터 시작해야 한다.
페이징(?)에 사용된다.

select bricks.*, rownum
	from bricks
	order by weight;

이에 rownum()은 보통 인라인뷰(from절의 서브쿼리)의 바깥쪽으로 들어가는 경우가 많다.
(row_number()는 이를 한번에 처리 가능케 한다.)

select rownum, A.*
    from (select *
            from bricks
            order by weight) A;

row_number()은 정렬된 순서대로 넘버링을 입힌다. order by로 기준 지정

select brick_id,
       weight, 
       row_number() over ( order by weight ) rn
from bricks;

rank(), dense_rank()
rank는 공동1위가 존재할 때 1위 개수 다 지나고 2위를 줌
dense_rank는 공동1위가 존재할때 그 다음에 2위를 줌

select brick_id, weight, 
       row_number() over ( order by weight ) rn, 
       rank() over ( order by weight ) rk, 
       dense_rank() over ( order by weight ) dr
from   bricks;

lag(), lead()
Lag and lead enable you to get values from rows backwards and forwards in your results.

select b.*,
       lag ( shape ) over ( order by brick_id ) prev_shape,
       lead ( shape ) over ( order by brick_id ) next_shape
from   bricks b;

first_value, last_value
min과 max의 개념이 아닌, 첫번째 '행'과 마지막 '행'의 값을 가져온다.

select b.*,
       first_value ( weight ) over ( 
         
       ) first_weight_by_id,
       last_value ( weight ) over ( 
         
       ) last_weight_by_id
from   bricks b;