postgresql generate_series 간단 설명 및 날짜 생성 쿼리

postgresql 에는 generate_series 라는 함수가 있다.

 

대략

 

SELECT generate_series(시작, 종료, 간격)

(간격은 생략가능)

 

의 문법으로 쓰인다.

 

예를들어

 

1

select generate_series(1, 100)

cs

이렇게 쓰면

1부터 100까지 1씩 증가하는 데이터가 만들어진다.

 

만약

 

1

select generate_series(1, 100, 5)

cs

이런식으로 간격을 설정하게 되면

 

이런 데이터가 나오게 된다.

 

이 generate_series 를 이용하여 날짜 데이터를 생성할 수 있는데

사용법은 다음과 같다.

 

우선 시작날짜와 종료 날짜 사이의 데이터를 뽑고 싶다면

 

1

select generate_series('2020-01-01'::date , '2020-12-31'::date, '1 day'::interval)::date

cs

이렇게 하면 2020년 1월 1일 부터

2020년 12월 31일까지

하루 간격으로 데이터가 출력되고

 

 

1

select generate_series('2020-01-01'::date , '2020-12-31'::date, '1 month'::interval)::date

cs

이렇게 하면 한달 단위

 

그리고

1

select generate_series('2020-01-01'::date , '2020-12-31'::date, '7 day'::interval)::date

cs

이렇게 하면 일주일 단위의 날짜 데이터가 생성이 된다

 

그리고 만약

시작 종료 일자가 아닌

시작일로부터 몇일뒤의 날짜가 알고 싶다면

 

1 2 3 4 5 6 7 8

SELECT (ROW_NUMBER() OVER()) AS ROWNUM,         a.date + b.idx as D  FROM (          SELECT '2020-01-01'::date as date  ) a  CROSS JOIN (      SELECT generate_series(0, 100) as idx  ) b Colored by Color Scripter

cs

이런식으로 날짜 데이터 생성이 가능하다

이걸 응용하면

시작일로부터 n일이 지난 날짜의 월 데이터를 추출할 수도 있다.

 

1 2 3 4 5 6 7 8 9 10 11 12

SELECT DISTINCT to_char(D, 'YYYY-MM') AS date FROM (     SELECT (ROW_NUMBER() OVER()) AS ROWNUM,             a.date + b.idx as D      FROM (              SELECT '2020-01-01'::date as date      ) a      CROSS JOIN (          SELECT generate_series(0, 9999) as idx      ) b) T WHERE D::date <= to_date('2020-12','YYYY-MM')::date ORDER BY date Colored by Color Scripter

cs