快速生成日期维度数据

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

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

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