[PostgreSQL] 월별,시간별 데이터 집계

-- 월별 데이터 집계

select to_char(reg_dt, 'YYYY-MM') as ym , count(*) from table1

where reg_dt between '2021-01-01' and '2021-06-30'

group by ym

order by ym;

 

result

ym	count
2021-01	253
2021-02	5325
2021-03	532
2021-04	315
2021-05	325
2021-06	442

 

-- 일별 데이터 집계

select to_char(reg_dt, 'YYYYMMDD') as ymd , count(*) from table1

where reg_dt between '2021-01-01' and '2021-01-30'

group by ymd

order by ymd;

 

ymd	count
20210101	32
20210105	23
20210107	111
20210108	4

 

-- 메일 발송현황  집계 (발송일기준 / 건수 / 발송시작시간 / 발송종료시간)

select substring(send_date,1,10) as ymd , count(*),  min(send_date) as min ,max(send_date) as max

  from table1

where send_date between '2021-06-01 00:00:00' and '2021-07-22 23:59:00'

and col1='MAIL'

group by ymd 

order by ymd;

 

result

ymd	count	min	max
2021-01-01	5544	2021-01-01 03:43:14	2021-01-01 15:50:14
2021-01-30	325	2021-01-30 12:10:10	2021-01-30 17:10:10
2021-10-11	33125	2021-10-11 01:10:10	2021-10-11 23:10:10

 

 

-- 시간대별 건수

select to_char(reg_Date, 'MM/DD HH24') as ymdh, count(1)

from table1

where reg_date between '2021-01-01 00:00:00' and '2021-04-06 23:59:59'

and col1='CODE1'

group by ydmh

order by ymdh asc;

 

 

result

ymdh	count
01/01 00	2
01/01 01	4
01/01 02	5
...	...
01/01 24	3

 

 

                                

to_date(substring(end_date,1,8),'YYYYMMDD')- to_date(substring(start_date,1,8),'YYYYMMDD')