快速生成日期维度数据

        日期维度在数据仓库中是一个特殊角色。日期维度包含时间概念,而时间是最重要的,因为数据仓库的主要功能之一就是存储和追溯历史数据,所以每个数据仓库里的数据都有一个时间特征。装载日期数据有三个常用方法:预装载、每日装载一天、从源数据装载日期。在三种方法中,预装载最为常见也最容易实现。在数据仓库生命周期中,只需要预装载日期维度一次。

        假设建立有如下日期维度表:

create table date_dim (    
    date_sk int,            -- 代理键  
    date date,              -- 日期
    month smallint,         -- 月份
    month_name varchar(9),  -- 月份名称
    quarter smallint,       -- 季度
    year smallint           -- 年份
);

        采用预装载方法一次性生成21年的日期维度数据,从2000年1月1日到2020年12月31日。在数据库中生成日期维度数据很简单,因为数据库一般都提供了丰富的日期时间函数,而且可以在存储过程中循环插入数据。下面对比HAWQ中两个生成日期数据函数的性能。

方法一:平凡低效

create or replace function fn_populate_date (start_dt date, end_dt date)    
returns void as $$    
declare    
    v_date date:= start_dt;   
    v_datediff int:= end_dt - start_dt;  
begin    
    for i in 0 .. v_datediff loop      
        insert into date_dim    
        values(i, 
               v_date, 
               extract(month from v_date), 
               to_char(v_date,'mon'), 
               extract(quarter from v_date), 
               extract(year from v_date));   
        v_date := v_date + 1;  
    end loop;  
    analyze date_dim;  
end; $$    
language plpgsql;

        关于这个函数没什么好说的,就是一个大循环,每次插入一条数据。以起始日期和终止日期参数的相差天数作为循环次数。在我的环境中执行这个函数需要将近9分钟,原因主要在于insert语句被执行了7671次。

postgres=# select fn_populate_date(date '2000-01-01', date '2020-12-31'); 
 fn_populate_date 
------------------
 
(1 row)

Time: 533999.903 ms

方法二:高效迭代

create or replace function fn_populate_date (start_dt date, end_dt date)  
returns void as  
$$  
declare  
    i int:=1;
    v_date date:= start_dt;
    v_datediff int:= end_dt - start_dt;
begin  
    truncate table date_dim;
    insert into date_dim(date_sk, date, month, month_name, quarter, year)
    values(i, 
           v_date, 
           extract(month from v_date), 
           to_char(v_date,'mon'), 
           extract(quarter from v_date), 
           extract(year from v_date));

    while i <= v_datediff
    loop    
        insert into date_dim(date_sk, date, month, month_name, quarter, year)  
        select date_sk + i, date + i, 
               extract(month from date+i),
               to_char(date+i,'mon'),
               extract(quarter from date+i),
               extract(year from date+i)
          from date_dim where date +i <= end_dt;

        i := i*2;
    end loop;
    analyze date_dim;
end;  
$$  
language plpgsql;

        这次执行只用了不到5秒钟。

postgres=# select fn_populate_date(date '2000-01-01', date '2020-12-31');
 fn_populate_date 
------------------
 
(1 row)

Time: 4987.249 ms

        在这个函数中,变量 i 保存插入date_dim表的行数。循环开始前先插入 1 条数据,然后当 date +i <= end_dt 成立时执行循环。在每次迭代中,该函数把日期维度表当前所有行的值加上 i 后再插入日期维度表中。这样每次循环插入的行数以2的幂次方递增,insert语句只被执行了14次,其中还包括作为种子数据的第一次插入。因此这个函数的执行速度很快。

        这种思想具有一定的通用性,例如在MySQL中生成数字辅助表数据时,就可以用下面的过程快速生成。

delimiter //
create procedure pfastcreatenums(cnt int)
begin
    declare s int default 1;
    truncate table nums;
    insert into nums select s;
    while s<=cnt do
        insert into nums select id+s from nums where id+s <=cnt;
        set s=s*2;
    end while;
    commit;
end;
//

方法三:一次生成

insert into date_dim
select date_sk, 
       date,
       extract(month from date),
       to_char(date,'mon'),
       extract(quarter from date),
       extract(year from date)
  from (select rn date_sk,date('2000-01-01') + rn - 1 date
          from (select generate_series(1,7671) rn) t) t;

          这种方法利用 generate_series 函数生成的序列一次性生成所有日期,只需要1秒多。

postgres=# insert into date_dim
postgres-# select date_sk, 
postgres-#        date,
postgres-#        extract(month from date),
postgres-#        to_char(date,'mon'),
postgres-#        extract(quarter from date),
postgres-#        extract(year from date)
postgres-#   from (select rn date_sk,date('2000-01-01') + rn - 1 date
postgres(#           from (select generate_series(1,7671) rn) t) t;
INSERT 0 7671
Time: 1225.582 ms

 

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值