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

7. 미래 또는 과거의 날짜/시간 계산

 

select user_id, register_stamp, 
	cast(register_stamp as timestamp) + interval '1 hour' as after_1_hour,
	cast(register_stamp as timestamp) - interval '30 minutes' as before_30_minutes,
	cast(register_stamp as date),
	cast(cast(register_stamp as date) + interval '1 day' as date) as after_1_day,
	cast(cast(register_stamp as date) - interval '1 month' as date) as before_1_month
from mst_users_with_dates;

 

• postgresql : interval 키워드로 시간 간격 표시
• redshift : dateadd 함수 사용 / ex) dateadd(hour, 1, register_stamp::timestamp), dateadd(month, -1, register_stamp::date)
• bigquery : timestamp_add/sub, date_add/sub 함수 사용 / ex) date_add(date(timestamp(register_day)), interval 1 day)
• hive, spark : 날짜/시각 계산 함수가 없음. from_unixtime으로 초 단위 계산을 적용한 뒤 다시 timestamp로 변환해야함.
  
  

---

8. 두 날짜 데이터의 차이 계산

 

select user_id, current_date as today, 
	register_stamp::date, current_date-cast(register_stamp as date) as diff_days
from mst_users_with_dates;

 

• cast 함수를 사용하거나, :: 뒤에 형식을 지정해서 날짜 형식 사용 가능
• bigquery : date_diff 함수 사용
• hive, spark : datediff 함수 사용

 

---

 

9. age 함수를 사용해 나이 계산

 

select user_id, current_date as today, register_stamp::date as register_date, birth_date::date, 
	extract(year from age(birth_date::date)), 
    extract(year from age(register_stamp::date, birth_date::date)) as register_age
from mst_users_with_dates;

 

• post에서는 age 함수를 사용해 나이를 계산할 수 있다. 단, 인자의 개수에 따라 계산 방식이 달라진다.
• 인자가 1개인 경우 오늘 날짜를 기준으로 첫 번째 인자를 뺀 나이(연, 월, 일)을 계산하고, 인자가 2개면 첫 번째 인자에서 두 번째 인자를 뺀 나이(연,월,일)을 반환한다. 최대 2개의 인자까지만 허용된다.
  
  

---

10. 날짜를 정수로 표현해서 나이 계산

 

select floor((20231120-20020319) / 10000) as age;

 

• floor 함수 : 주어진 숫자보다 작거나 같은 가장 큰 정수를 반환 (나이를 연도로 계산하려면 내림해야 함)
  
  

---

11. 등록 시점과 현재 시점의 나이를 문자열로 계산

 

select 
	user_id, register_stamp, birth_date,
	floor((cast(replace(substring(register_stamp,1,10),'-','') as integer) - cast(replace(birth_date,'-','') as integer))/10000) ,
	floor((cast(replace(cast(current_date as text),'-','') as integer) - cast(replace(birth_date,'-','') as integer))/10000) as age
from mst_users_with_dates;

 

• floor, cast, replace, substring 함수를 활용, 날짜 형식에 비해 비효율적임
  
  

---

12. inet 자료형을 사용한 IP 주소 비교

 

select 
	cast('127.0.0.1' as inet) < cast('127.0.0.2' as inet) as lt,
	cast('127.0.0.1' as inet) > cast('192.168.0.1' as inet) as gt;
    
-- inet 자료형에서 IP 범위 주소를 확인(포함 여부)
select cast('127.0.0.1' as inet) << cast('127.0.0.0/8' as inet) as contain;

 

• inet 자료형 : postgresql에서 ip 주소를 다루기 위한 자료형
• True or False로 return

 

---

13. IP 주소에서 4개의 10진수 부분을 추출

 

select ip,
	cast(split_part(ip,'.',1) as integer) as ip_part_1,
	cast(split_part(ip,'.',2) as integer) as ip_part_2,
	cast(split_part(ip,'.',3) as integer) as ip_part_3,
	cast(split_part(ip,'.',4) as integer) as ip_part_4
from (select '192.168.0.1' as ip) as t;

-- 정수 자료형 표기로 변환, 크기 비교

select ip,
	 cast(split_part(ip,'.',1) as integer) * 2^24
	+cast(split_part(ip,'.',2) as integer) * 2^16
	+cast(split_part(ip,'.',3) as integer) * 2^8
	+cast(split_part(ip,'.',4) as integer) * 2^0
	as ip_integer
from (select '192.168.0.1' as ip) as t;

 

• split_part : 문자열 분할 함수로, 특정 구분자로 분할 후 지정된 위치의 값을 반환
• bigquery, hive, spark : split 함수로 배열 분해, n번째 요소 추출
• ipv4 주소는 4개의 8비트(1byte) 주소로 구성되며, 각 세그먼트는 0부터 255까지의 정수를 가진다. 이를 2진수로 변환하려면 각 세그먼트를 해당 위치의 2의 제곱으로 곱해줘야 한다.

 

---

14. IP 주소를 0으로 채운 문자열로 반환

 

select ip,
	lpad(split_part(ip,'.',1),3,'0')||
	lpad(split_part(ip,'.',2),3,'0')||
	lpad(split_part(ip,'.',3),3,'0')||
	lpad(split_part(ip,'.',4),3,'0')
from (select '192.168.0.1' as ip) as t;

 

• lpad : 문자열의 왼쪽에 특성 문자를 추가하여 주어진 길이로 만드는 역할을 수행, 만약 원래 문자열의 길이가 이미 주어진 길이보다 길다면, 문자열은 잘린다.
•  || 연산자로 문자열을 연결해서 결과 출력