postgresql日期相关的截取和计算

select  
	half_month
	,case when half_month like '%15' then all_pv/15
		  when half_month like '%28' then all_pv/13
		  when half_month like '%30' then all_pv/15
		  when half_month like '%31' then all_pv/16
          when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2)>='16'  
			then all_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 16 + 1)
          when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2) <'15'  
			then all_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 1 + 1)
	end as all_pv
	,case when half_month like '%15' then webservice_pv/15
		  when half_month like '%28' then webservice_pv/13
		  when half_month like '%30' then webservice_pv/15
		  when half_month like '%31' then webservice_pv/16
		  when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2)>='16'  
			then webservice_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 16 + 1)
          when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2) <'15'  
			then webservice_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 1 + 1)
	end as webservice_pv
	,case when half_month like '%15' then part_all_pv/15
		  when half_month like '%28' then part_all_pv/13
		  when half_month like '%30' then part_all_pv/15
		  when half_month like '%31' then part_all_pv/16
		  when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2)>='16'  
			then part_all_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 16 + 1)
          when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2) <'15'  
			then part_all_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 1 + 1)
	end as part_all_pv
	,case when location_pv = 0 then 0.0 else (
		case when half_month like '%15' then location_pv/15
		     when half_month like '%28' then location_pv/13
		     when half_month like '%30' then location_pv/15
		     when half_month like '%31' then location_pv/16
     		 when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2)>='16'  
				then location_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 16 + 1)
          	 when half_month like '%' || substr(to_char(%end_date%,'99999999')  , 8 , 2) and  substr(to_char(%end_date%,'99999999')  , 8 , 2) <'15'  
				then location_pv/(cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int) - 1 + 1)
		end ) end as location_pv
from (
select 
	 case when substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '01'  --在01-15之间，f_date 月份<当前所选的月份 显示01-15
		  and substr(to_char(f_date,'99999999')  , 8 , 2)  <= '15'  
		  and substr(to_char(f_date,'99999999')  , 1 , 7) < substr(to_char(%end_date%,'99999999')  , 1 , 7)  				
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'01~',substr(to_char(f_date,'99999999')  , 1 , 7),'15')        
	when substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '01'  ----在01-15之间，f_date 月份=当前所选的月份，且%end_date% >=15, 显示01-15
		 and substr(to_char(f_date,'99999999')  , 8 , 2)  <= '15'   
		 and substr(to_char(f_date,'99999999')  , 1 , 7) = substr(to_char(%end_date%,'99999999')  , 1 , 7) 
		 and substr(to_char(%end_date%,'99999999')  , 8 , 2) >= '15' 
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'01~',substr(to_char(f_date,'99999999')  , 1 , 7),'15')	
	when substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '01'    ----在01-15之间，f_date 月份=当前所选的月份，且%end_date% <15, 显示01-end_date
		 and substr(to_char(f_date,'99999999')  , 8 , 2)  <= '15'   
		 and substr(to_char(f_date,'99999999')  , 1 , 7) = substr(to_char(%end_date%,'99999999')  , 1 , 7) 
		 and substr(to_char(%end_date%,'99999999')  , 8 , 2) < '15' 
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'01~','%end_date%')

	when  substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '16'   --在16-月末之间，f_date 月份<当前所选的月份 显示16-月末；每个月天数不同，根据具体天数显示
		  and substr(to_char(f_date,'99999999')  , 1 , 7) < substr(to_char(%end_date%,'99999999')  , 1 , 7)  						
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'16~',substr(to_char(f_date,'99999999')  , 1 , 7),
                     case   when  substr(to_char(f_date,'99999999')  , 6, 2) in ('01','03','05','07','08','10','12') then 31
							when substr(to_char(f_date,'99999999')  , 6, 2) in ('02') then 28
					 else 30 
				end ) 
	when  substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '16'   --在16-月末之间，f_date 月份=当前所选的月份 且%end_date%>15,显示16-end_date；
		  and substr(to_char(f_date,'99999999')  , 1 , 7) = substr(to_char(%end_date%,'99999999')  , 1 , 7)  
		  and substr(to_char(%end_date%,'99999999')  , 8 , 2) > '15' 
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'16~','%end_date%')                 
	end as half_month
	,sum(pv)  as all_pv
	,sum(case when type in ('webservice') then pv else 0 end) as webservice_pv
   	,sum(case when type in ('jsAPI','H5','map_sdk','static_map') then pv else 0 end) as part_all_pv
    ,sum(case when type ='location_sdk'  then pv else 0 end) as location_pv
from (
select xx from xxx 	
  ) temp
where f_date >= %begin_date% and f_date <= %end_date%		 
group by  --substr(to_char(f_date,'99999999')  , 1 , 7) as f_month
case when substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '01'  --在01-15之间，f_date 月份<当前所选的月份 显示01-15
		  and substr(to_char(f_date,'99999999')  , 8 , 2)  <= '15'  
		  and substr(to_char(f_date,'99999999')  , 1 , 7) < substr(to_char(%end_date%,'99999999')  , 1 , 7)  				
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'01~',substr(to_char(f_date,'99999999')  , 1 , 7),'15')        
	when substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '01'  ----在01-15之间，f_date 月份=当前所选的月份，且%end_date% >=15, 显示01-15
		 and substr(to_char(f_date,'99999999')  , 8 , 2)  <= '15'   
		 and substr(to_char(f_date,'99999999')  , 1 , 7) = substr(to_char(%end_date%,'99999999')  , 1 , 7) 
		 and substr(to_char(%end_date%,'99999999')  , 8 , 2) >= '15' 
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'01~',substr(to_char(f_date,'99999999')  , 1 , 7),'15')	
	when substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '01'    ----在01-15之间，f_date 月份=当前所选的月份，且%end_date% <15, 显示01-end_date
		 and substr(to_char(f_date,'99999999')  , 8 , 2)  <= '15'   
		 and substr(to_char(f_date,'99999999')  , 1 , 7) = substr(to_char(%end_date%,'99999999')  , 1 , 7) 
		 and substr(to_char(%end_date%,'99999999')  , 8 , 2) < '15' 
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'01~','%end_date%')

	when  substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '16'   --在16-月末之间，f_date 月份<当前所选的月份 显示16-月末；每个月天数不同，根据具体天数显示
		  and substr(to_char(f_date,'99999999')  , 1 , 7) < substr(to_char(%end_date%,'99999999')  , 1 , 7)  						
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'16~',substr(to_char(f_date,'99999999')  , 1 , 7),
                     case   when  substr(to_char(f_date,'99999999')  , 6, 2) in ('01','03','05','07','08','10','12') then 31
							when substr(to_char(f_date,'99999999')  , 6, 2) in ('02') then 28
					 else 30 
				end ) 
	when  substr(to_char(f_date,'99999999')  , 8 , 2)  >=  '16'   --在16-月末之间，f_date 月份=当前所选的月份 且%end_date%>15,显示16-end_date；
		  and substr(to_char(f_date,'99999999')  , 1 , 7) = substr(to_char(%end_date%,'99999999')  , 1 , 7)  
		  and substr(to_char(%end_date%,'99999999')  , 8 , 2) > '15' 
	        then concat(substr(to_char(f_date,'99999999')  , 1 , 7),'16~','%end_date%')                 
	end
) t

select
	f_month
	,all_pv / 
	    (case when substr(f_month  , 6, 2) in ('01','03','05','07','08','10','12') and f_month  < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 31
      when substr(f_month  , 6, 2) in ('02') and  f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 28
      when substr(f_month  , 6, 2) in ('04','06','09','11') and f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 30 
      when f_month = substr(to_char(%end_date%,'99999999')  , 1 , 7) then  cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int)     
 end ) as all_pv
	,webservice_pv /
		(case when substr(f_month  , 6, 2) in ('01','03','05','07','08','10','12') and f_month  < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 31
      when substr(f_month  , 6, 2) in ('02') and  f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 28
      when substr(f_month  , 6, 2) in ('04','06','09','11') and f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 30 
      when f_month = substr(to_char(%end_date%,'99999999')  , 1 , 7) then  cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int)     
 end ) as webservice_pv
	,part_all_pv /
		(case when substr(f_month  , 6, 2) in ('01','03','05','07','08','10','12') and f_month  < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 31
      when substr(f_month  , 6, 2) in ('02') and  f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 28
      when substr(f_month  , 6, 2) in ('04','06','09','11') and f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 30 
      when f_month = substr(to_char(%end_date%,'99999999')  , 1 , 7) then  cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int)     
 end ) as part_all_pv
	,case when location_pv = 0 then 0  else location_pv / 
		(case when substr(f_month  , 6, 2) in ('01','03','05','07','08','10','12') and f_month  < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 31
      when substr(f_month  , 6, 2) in ('02') and  f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 28
      when substr(f_month  , 6, 2) in ('04','06','09','11') and f_month < substr(to_char(%end_date%,'99999999')  , 1 , 7) then 30 
      when f_month = substr(to_char(%end_date%,'99999999')  , 1 , 7) then  cast(substr(to_char(%end_date%,'99999999')  , 8 , 2) as int)     
 end ) end as location_pv
from (
select 
	substr(to_char(f_date,'99999999')  , 1 , 7) as f_month
	,sum(pv) 
          as all_pv
	,sum(case when type in ('webservice') then pv else 0 end) as webservice_pv
   	,sum(case when type in ('jsAPI','H5','map_sdk','static_map') then pv else 0 end) as part_all_pv
    ,sum(case when type ='location_sdk'  then pv else 0 end) as location_pv
from (
	
  ) temp
where f_date >= %begin_date% and f_date <= %end_date%		 

group by substr(to_char(f_date,'99999999')  , 1 , 7)
  ) t