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

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

select
	f_month,all_pv / 
	    (case when substr(f_month,'12') and f_month  < substr(to_char(%end_date%,7) then 31
      when substr(f_month,2) in ('02') and  f_month < substr(to_char(%end_date%,7) then 28
      when substr(f_month,2) in ('04','06','09','11') and f_month < substr(to_char(%end_date%,7) then 30 
      when f_month = substr(to_char(%end_date%,7) then  cast(substr(to_char(%end_date%,2) as int)     
 end ) as all_pv,webservice_pv /
		(case when substr(f_month,2) as int)     
 end ) as webservice_pv,part_all_pv /
		(case when substr(f_month,2) as int)     
 end ) as part_all_pv,case when location_pv = 0 then 0  else location_pv / 
		(case when substr(f_month,2) as int)     
 end ) end as location_pv
from (
select 
	substr(to_char(f_date,7) as f_month,sum(pv) 
          as 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,7)
  ) t


   

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!