Greenplum自定义函数集锦

区间炸裂函数

  该自定义函数功能类似Hive中explode函数，将给定的区间按照月份炸裂,返回区间所在的月初和月末。

--传入参数
start_date：'2023-07-17'
 end_date：'2023-09-03'

--返回值：
| begin_of_month | end_of_month |
|----------------|--------------|
|   2023-07-01   |  2023-07-31  |
|   2023-08-01   |  2023-08-31  |
|   2023-09-01   |  2023-09-30  |

CREATE OR REPLACE FUNCTION prd_dws.period_split_into_months(start_date date, end_date date) RETURNS SETOF text AS
$BODY$ 	

--功能描述: 传入时间区间的开始日期和结束日期,返回区间包含的全部月初与月末（区间炸裂函数）
--参数说明：
--      start_date date：区间开始日期
--      end_date date:区间结束日期
--返回值：
--      begin_of_month date: 月初；end_of_month date:月末

declare
    i_date          date;      --循环条件
    begin_of_month  date;      --月初
    end_of_month    date;      --月末
    r               text;      --结果集
begin
    i_date  := start_date;
    if(i_date <= end_date) then      
        while date_trunc('month', i_date) <= date_trunc('month', end_date) loop
            --月初
            begin_of_month := date_trunc('month', i_date);
            --月末
            end_of_month := date_trunc('month', i_date + interval '1 month') - interval '1 day';
            --结果集
            r := begin_of_month||','||end_of_month;
            --出口
            i_date := date_trunc('month', i_date) + interval '1 month';
            return next r;
        end loop;
    elsif(i_date > end_date) then
        --直接返回start_date所在月份
        --月初
        begin_of_month := date_trunc('month', i_date);
        --月末
        end_of_month := date_trunc('month', i_date + interval '1 month') - interval '1 day';
        --结果集
        r := begin_of_month||','||end_of_month;
        return next r;
    end if;
end
 $BODY$ LANGUAGE plpgsql VOLATILE EXECUTE ON ANY COST 100.0 ROWS 1000.0;

日期计算函数（加减月份）

  对日期进行加减月份计算时，GP原生的日期计算方式（‘2023-04-30’::date - interval ‘1 month’）结果是’2023-03-30’，并非预期中的’2023-03-31’。需要自定义函数解决该问题。

--传入参数
in_timestamp：'2023-04-30 12:00:00'
cnt：-1

--返回值：
res：'2023-03-3112:00:00 '

CREATE OR REPLACE FUNCTION public.add_months(in_timestamp timestamp without time zone, cnt integer) RETURNS timestamp without time zone AS
$BODY$
--功能说明：月份计算函数，解决in_timestamp为月末最后一天月份加减不准确的问题
--参数说明：
--      in_timestamp timestamp without time zone：传入日期
--      cnt int:需要加减的月份
--返回值：
--      res timestamp without time zone：计算后的日期

declare
  months interval := (cnt || 'month');
  d1 date := date(date_trunc('month',in_timestamp) + interval '1 month' - interval '1 day');
  d2 date := date(in_timestamp);
  res timestamp;
begin
  select
        case
            when d1=d2
                then date_trunc('month',in_timestamp + months + interval '1 month')::date - interval '1 day' + in_timestamp::time
            else in_timestamp + months
            end into res;
  return res::timestamp without time zone;
end;
$BODY$ LANGUAGE plpgsql VOLATILE EXECUTE ON ANY COST 100.0;