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_pvfrom (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 31when 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_pvfrom (select xx from xxx   ) tempwhere f_date >= %begin_date% and f_date <= %end_date% group by  --substr(to_char(f_date,'99999999')  , 1 , 7) as f_monthcase 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 31when 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

selectf_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_pvfrom (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_pvfrom (  ) tempwhere f_date >= %begin_date% and f_date <= %end_date% group by substr(to_char(f_date,'99999999')  , 1 , 7)  ) t