[Postgre SQL] JOIN | TIMESTAMP | TRUNC | GROUP BY | GROUP BY ROLLUP | GROUP BY CUBE | Aggregate | CASE WHEN

SQL에서 join 문법은 매우 중요하다.

join에는 1:1 / 1:M / M:1 / M:N 등이 있는데, 

M은, 개별 값이 중복 건으로 들어있는 것을 말한다. 예를 들어, '30' 이라는 값이 한 건만 있지 않고 3건 있는 것을 말하는데,  여기서 1이란, Unique 하다는 것이다. 개별 KEY 값이 고유하게 있다는 것을 의미하며 식별자가 되면 PK라고 한다.

 

보통 활용할 때, 

select * 

from table.column a 

join tabla_a. column b on a.customer_id = b.customer_id ;

위와 같은 느낌으로 활용하곤 한다. 

 

위처럼 사용하는 방법은 join 앞에 (inner)가 생략되어 있다.

table1과 table2에서 값이 같은 행만 반환한다. 

 

left join의 경우, join 왼쪽에 있는 table 1의 모든 결과를 가지고 온 후 table2와 매칭하며, 매칭되는 데이터가 없을 경우 NULL 값을 삽입하게 된다.

 

right join의 경우, 잘 활용하지는 않으나, join 오른쪽에 있는 table2의 모든 결과를 가지고 온 후 table1와 매칭하며, 매칭되는 데이터가 없을 경우 NULL 값 삽입. 

 

full (outer) join은, table1과 table2를 매칭시키고 데이터가 없는 경우 NULL 처리 시킨다. 

 

cross join은 table1과 table2의 모든 행을 join 한다. 

결과는 두 테이블의 행의 개수를 곱한 것과 같음. 

 

[ 날짜와 시간 연산 -interval ]

 

> Date 타입에 숫자값을 더하거나 뺴면 숫자값에 해당하는 일자를 더하거나 빼서 날짜 계산

** 곱하기나 나누기는 안된다. 

select to_date('2022-03-01', 'yyyy-mm-dd') + 2 > 2022-03-03 

 

> timestamp 타입에 숫자값을 더하거나 빼면 오류 발생 

 

> timestamp는 interval 타입을 이용하여 연산 수행 

select to_timestamp('2022-01-01 15:33:12', 'yyyy-mm-dd hh24:mi:ss')  + interval '7 hour'  > 2022-01-01 22:33:12

select to_timestamp('2022-05-08 15:33:12', 'yyyy-mm-dd hh24:mi:ss')  + interval '2days'  > 2022-05-10 15:33:12

select to_timestamp('2022-01-01 14:36:52', 'yyyy-mm-dd hh24:mi:ss') + interval '2 days 7 hours 30 minutes' > 

2022-01-03 22:06:52

 

> 날짜 간의 차이 구하기 -> 차이 값은 정수형으로 떨어진다.

> timestamp간의 차이 구하기 -> 차이값은 interval 

pg_typeof() > 데이터 type을 알 수 있게 하는 함수 

 

> now() : 지금 시간을 표시해줌 : current_timestamp, current_date, current_time

> date_trunc는 뒤에 끝자리를 없애준다. 아래 그 예시가 있음.

 

select trunc( 99.9999, 2) > 실수 값에 소수점 이하 2자리 이외엔 버려라 라는 뜻 

보통 group by 할 때 date_trunc 가장 잘 이용함 > 년, 월, 일 단위로 적용할 때 

 

매출 일자로 찍힘 > 월 매출 총 sum -> group by - date_trunc로 절삭해버리면 된다.

trunc를 잘 사용하면 일, 월, 년도를 각 구분해서 자를 수 있다. 

 

시, 분, 초도 절삭 가능하며

date_trunc는 년, 월, 일 단위로 Group by 적용 시 잘 사용된다. 

 

-- 시분초도 절삭 가능.

select date_trunc('hour', now());


--date_trunc는 년, 월, 일 단위로 Group by 적용 시 잘 사용됨.

create table sales.employee
as
select a.*, hiredate + current_time
from sales.employee a ;


-- 입사월로 group by

select date_trunc('month', hiredate) as hire_month, count(*)

from sales.employee

group by date_trunc('month', hiredate);

 

 

** JOIN  구조는 어떻게 될까?

 

select * 

from sales.table a 

join orders.table b on a.customer_id  =  b.customer_id 

where a.category in ('KNIT', 'LEATHER')  

and fromdate >= to_date ('20220203', 'yyyymmdd') ;

 

** Group by에 대해

 

select * 
from sales.table
group by sales.table.category
having avg(sal) >= 2000
;

 

>> Aggregation은 NULL 값을 처리하지 않는다. 

select order_id, count(*), as cnt, sum(sales), max(sales), min(sales), avg(sales)

from sales.table a 

join orders.table b on a.customer_id  =  b.customer_id 

where a.category in ('KNIT', 'LEATHER')  ;

 

>> case when 적용

+ floor 함수 : 소수점 미만 버림

ex) 1.1 -> 1 | 0.9 -> 0  :: sal / 1000 일 때 >> 800/ 1000 : 0 

 

select order_id, count(*), as cnt, sum(sales), max(sales), floor(sales/10000) * 10000 as bin_range

from sales.table a 

join orders.table b on a.customer_id  =  b.customer_id 

group by floor(sales/10000) * 10000;

 

case문 

if a = 10 then z = 15 와 같은 꼴인데,

job이 만약 sales면 sal을 표시해라. 그렇지 않으면 0을 해라 등의 문법 

 

select *,
case when category = 'KNIT' then 'KNITBAG'
	else 'OTHERS' end as gubun
from sales.table;

 

열을 행으로 피봇팅 시키고 싶을 때는 아래와 같이 

select 
	sum(case when category = 'KNIT' then sales end) as knit_sale_sum
    , sum(case when category = 'LEATHER' then sales end) as leather_sale_sum
	, sum(case when category = 'SYNTAX' then sales end) as syntax_sale_sum
	, sum(case when category = 'NYLON' then sales end) as nylon_sale_sum
from sales.table;

 

** rollup과 cube는 group by와 함께 사용되어 group by 절에 사용되는 컬럼들에 대해서 추가적인 group by 수행

 

- rollup은 계층적인 방식으로 group by 추가 수행 >> group by 수행 시 rollup을 사용하면 rollup에 적용된 컬럼의 순서대로 계층적인 group by 추가 수행 ( 소계 구할 때 rollup 사용 )

 

brand -> category(하위개념) -> 전체 

group by 절의 나열된 컬럼 수가 n개이면, group by는 N + 1 회 수행 

 

** YEAR + MONTH + DAY -> 일 매출 

** YEAR + MONTH -> 월 매출 

** YEAR -> 년 매출

 

- cube는 group by 절에 기재된 컬럼들의 가능한 combination으로 group by 수행 >> group by 시 cube를 함께 사용하면 cube에 나열된 컬럼들의 가능한 결합으로, group by 수행 

 

** brand + category -> brand -> category -> 전체 ( 가능한 조합 ) 

brand + categroy레벨 외에, brand 내의 전체 category 레벨, 전체 Aggregation을 수행한다. 

 

cube(YEAR, MONTH, DAY) 

>> YEAR + MONTH + DAY

YEAR + MONTH

YEAR +  DAY

YEAR

MONTH + DAY

MONTH

DAY

전체

 

>> Group by 절의 나열된 컬럼 수가 N개이면, Group by는 2의 N승 회를 수행한다.

 

select * c.category_name, b.product_name, sum(a.sales) as sales
from sales.sale a
	join sales.order b on a.product_id = b.product_id
    join sales.category c on c.category_id = b.category_id 
group by rollup (c.cateogory_name, b.product_name)
order by 1, 2;

 

** cube의 경우 많아야 세 개 테이블, 통상적으로 두 개 테이블 정도 사용한다.