데이터 분석을 위한 SQL 레시피 3장 공부

데이터 가공을 위한 SQL

현재 날짜와 타임스탬프 추출

select current_date as dt, current_timestamp as stamp;

 

지정한 값의 날짜/시각 데이터 추출

• CAST( 칼럼명 AS 변환하고자 하는 타입)
  • 데이터 타입을 다른 형식으로 변환시켜 보여주도록 도와주는 타입 변환 함수

select cast('2024-06-11' as date) as dt, cast('2024-06-11 00:09:48' as timestamp) as stamp;

 

날짜/시각에서 특정 필드 추출

• extract('날짜요소' from 칼럼명)
  • 날짜 유형의 데이터로부터 날짜 정보를 분리하여 새로운 컬럼의 형태로 추출해 주는 함수

select current_timestamp as stamp
	, extract(year from current_timestamp) as year
	, extract(month from current_timestamp) as month
	, extract(day from current_timestamp) as day
	, extract(hour from current_timestamp) as hour;

• SUBSTR(문자열, 시작 위치, 길이)
  • 문자열을 시작 위치부터 길이만큼 자르기

select stamp
	-- substring
	, substring(stamp, 1, 4) as year
	, substring(stamp, 6, 2) as month
	, substring(stamp, 9, 2) as day
	, substring(stamp, 12, 2) as hour
	, substring(stamp, 1, 7) as year_month
	-- substr
	, substr(stamp, 1, 4) as year
	, substr(stamp, 6, 2) as month
	, substr(stamp, 9, 2) as day
	, substr(stamp, 12, 2) as hour
	, substr(stamp, 1, 7) as year_month
	
from (
	select cast('2024-06-11 00:17:49' as text) as stamp
) as t;

 

결손 값을 디폴트 값으로 대치

• COALESCE(value1, value2,...)
  • 주어진 컬럼들 중에서 NULL이 아닌 첫 번째 값을 반환하는 함수

 

여러 개의 값 비교하기

• SIGN(number)
  • number가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴하는 함수

select year
	, q1
	, q2
	-- q1과 q2 매출 변화 평가
	-- 1. case
	, case
		when q1 < q2 then '+'
		when q1 > q2 then '-'
		else ' '
	  end as judge_q1_q2
	-- 2. 차이
	, q2 - q1 as diff_q2_q1
	-- 3. sign
	, sign(q2-q1) as sign_q2_q1
from quarterly_sales
order by year;

좌 -> 우

 

연간 최대/최소 분기 매출 찾기

• GREATEST(value1, valu2,...)
  • 주어진 컬럼들 중에서 가장 큰 값을 반환하는 함수
• LEAST(value1, value2,...)
  • 주어진 컬럼들 중에서 가장 작은 값을 반환하는 함수

select year 
	-- 최대 매출
	, greatest(q1, q2, q3, q4) as greatest_sales
	-- 최소 매출
	, least(q1, q2, q3, q4) as least_sales
from quarterly_sales
order by year;

 

전/후 행 값 추출

select product_id
	-- 현재 행보다 앞에 있는 행의 값 추출하기
	, lag(product_id) over(order by score desc) as lag1
	, lag(product_id, 2) over(order by score desc) as lag2
	-- 현재 행보다 뒤에 있는 행의 값 추출하기
	, lead(product_id) over(order by score desc) as lead1
	, lead(product_id, 2) over(order by score desc) as lead2
from popular_products;

 

윈도 프레임 지정

• ROWS BETWEEN start AND end
  • CURRENT ROW (현재의 행)
  • n PRECEDING (n행 앞)
  • n FOLLOWING (n행 뒤)
  • UNBOUNDED PRECEDING (이전 행 전부)
  • UNBOUNDED FOLLOWING (이후 행 전부)
• 윈도 함수에 프레임 지정을 하지 않으면 ORDER BY 구문이 없는 경우 모든 행, ORDER BY 구문이 있는 경우 첫 행에서 현재 행까지가 디폴트 프레임으로 지정

-- 윈도 프레임 사용 예시
SELECT 
    -- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산
    AVG(score) 
        OVER(ORDER BY score DESC 
            ROWS BETWWEN 1 PRECEDING AND 1 FOLLOWING)
    AS local_avg
FROM popular_products;

 

행을 열로 변환하기

select dt
	, MAX(case when indicator = 'impressions' then val END) as impressions
	, MAX(case when indicator = 'sessions' then val END) as sessions
	, MAX(case when indicator = 'users' then val END) as users
from daily_kpi
group by dt
order by dt;

좌 -> 우

 

행을 쉼표로 구분한 문자열로 집약

• STRING_AGG(컬럼명, '구분 기호')
  • 데이터를 구분 기호로 구분한 문자열로 변환하는 함수

 

열로 표현된 값을 행으로 변환하기

4분기 매출을 하나의 레코드로 가지는 테이블 -> '연도', '4분기 레이블', '매출' 이라는 3개의 컬럼을 가진 테이블로 변환

select q.year
	-- q1에서 q4까지의 레이블 이름 출력
	, case
		when p.idx = 1 then 'q1'
		when p.idx = 2 then 'q2'
		when p.idx = 3 then 'q3'
		when p.idx = 4 then 'q4'
	  end as quarter
	  
	-- q1에서 q4까지의 매출 출력
	, case
		when p.idx = 1 then q.q1
		when p.idx = 2 then q.q2
		when p.idx = 3 then q.q3
		when p.idx = 4 then q.q4
	  end as sales
	
from quarterly_sales as q
	cross join
		-- 행으로 전개하고 싶은 열의 수만큼 순번 테이블 만들기
		(         select 1 as idx
		union all select 2 as idx
		union all select 3 as idx
		union all select 4 as idx
		) as p;

전

후

 

임의의 길이를 가진 배열을 행으로 전개하기

• UNNEST(배열)
  • 배열을 행으로 전개

select unnest(array['A','B','C']) as alpha;

 

임의의 레코드를 가진 유사 테이블 만들기

• SELECT 구문으로 유사 테이블 만들기

with mst_devices as (
              select 1 as deviced_id, 'PC' as device_name
    union all select 2 as deviced_id, 'SP' as device_name
    union all select 3 as deviced_id, '애플리케이션' as device_name
)

select * from mst_devices;

• VALUES 구문을 사용한 유사 테이블 만들기

with mst_devices(deviced_id, device_name) as (
	values
		(1, 'PC')
		, (2, 'SP')
		, (3, '애플리케이션')
)

select * from mst_devices;

• 순번을 가진 유사 테이블 만들기
  • GENERATE_SERIES(start, end)
    • start에서 end까지 숫자를 행으로 반환하는 함수
  • PostgreSQL에서만 지원하는 함수

with series as (
	select generate_series(1,5) as idx
)

select * from series;