[PostgreSQL] 여러 개의 값에 대한 조작 - 1

1. 문자열 연결하기

 

select user_id, concat(pref_name, city_name) as name 
from mst_user_location;

 

• concat 함수 사용
• post, redshift는 || 연산자도 사용 가능
  
  

---

2. column 비교하기

 

select year, q1,q2, case 
	when q1 < q2 then '+'
	when q1 = q2 then ''
	when q1 > q2 then '-'
end as judge_q1_q2,
	q2-q1 as diff_q2_q1,
	sign(q2-q1) as sign_q2_q1
from quarterly_sales;

 

• case when, sign 함수 사용
• sign 함수 : 매개변수의 값이 양수라면 1, 0이라면 0, 음수라면 -1을 return
  
  

---

3. 연간 최대/최소 매출을 찾는 쿼리

 

select year, greatest(q1,q2,q3,q4), least(q1,q2,q3,q4)
from quarterly_sales;

 

• greatest 함수 : column에서 최댓값 return
• least 함수 : column에서 최솟값 return
  
  

---

4. 평균 매출을 구하는 쿼리

 

-- 단순 계산식
select year, (q1+q2+q3+q4) / 4 as mean
from quarterly_sales
order by year;

-- coalesce 함수 활용
select year, 
	(coalesce(q1,0)+coalesce(q2,0)+coalesce(q3,0)+coalesce(q4,0))/4 as mean
from quarterly_sales;

-- null이 아닌 column만을 사용
select year,
	(coalesce(q1,0)+coalesce(q2,0)+coalesce(q3,0)+coalesce(q4,0))/
	(sign(coalesce(q1,0))+sign(coalesce(q2,0))+sign(coalesce(q3,0))+sign(coalesce(q4,0)))as mean
from quarterly_sales;

 

• sign 함수를 사용해서, 해당 년도에 매출이 존재하지 않는 분기는 계산에서 제외
• coalesce(val, 0) : val이 null이면 0으로 변환
  
  

---

5-1. 정수 자료형 분할

 

-- 정수형 return
select dt, ad_id, 
	cast(clicks / impressions as double precision) as ratio,
	100.0 * clicks / impressions as per
from advertising_stats
where impressions != 0;

-- 실수형 return
select dt, ad_id, 
	cast(clicks as double precision) / impressions as ratio,
	round(100.0 * clicks / impressions,2) as per
from advertising_stats
where impressions != 0;

-- 0으로 나누는 것 피하기
select dt, ad_id, case
	when impressions > 0 then round(100.0 * clicks / impressions,2)
	end as per,
	round(100.0*clicks / nullif(impressions, 0),2) as per_null
from advertising_stats;

 

• SQL은 기본적으로 정수 나누기 정수는 정수를 반환한다. 즉, 이미 정수로 반환된 값을 cast로 변환하니 정수가 출력되는 것
• 실수를 상수로 앞에 두면 암묵적 자료형 변환이 발생
• double precision : double 자료형
• where절로 0을 제거하면 해당하는 행 전체가 사라진다.
• case when 혹은 nullif(val,0) 함수를 사용하면, 행을 유지한 상태로 출력할 수 있다.
  
  

---

6. 두 값의 거리 계산하기

 

-- 1차원 데이터 차이 절댓값과 RMSE 계산
select abs(x1-x2) as abs, sqrt(power(x1-x2,2)) as rmse from location_1d;

-- 2차원 데이터 유클리드 거리 계산
select sqrt(power(x2-x1,2)+power(y2-y1,2)) as dist from location_2d;
select point(x1,y1) <-> point(x2,y2) as dist from location_2d;

 

• abs : 절대값 함수
• sqrt : 제곱근 함수
• power(val, x) : x차 제곱 함수
• 유클리드 거리 계산을 위해 sqrt, power 함수 활용, postgresql은 point 함수와 <-> 연산자 사용 가능

 

출처

한빛미디어 - 데이터 분석을 위한 SQL 레시피