数据库生成维度表

当“数据仓库”、“OLAP”这样的概念出现时,势必会有时间维度表的登场,以下是创建的语句示例……

 

#=======================时间维度数据START============================
DROP TABLE IF EXISTS time_dim_basic_num;
DROP PROCEDURE IF EXISTS dim_number_insert;
DROP TABLE IF EXISTS report_time_dimension;
#时间维度表及数据填充
#1-创建基准表
CREATE TABLE `time_dim_basic_num`
(
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    type varchar(8) NOT NULL DEFAULT '' COMMENT '类型',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='时间维度数据基准表';

#2-创建存储过程
CREATE PROCEDURE dim_number_insert ()
BEGIN
   DECLARE n int DEFAULT 1;
        loopname:LOOP
            INSERT INTO time_dim_basic_num(type)VALUES('dim');
            SET n=n+1;
        IF n=20000 THEN
            LEAVE loopname;
        END IF;
        END LOOP loopname;
END;

#3-执行存储过程
CALL dim_number_insert();

#4-设置时间维度跨度
SET @d0 = "2009-01-01";
SET @d1 = "2059-12-31";
SET @date = date_sub(@d0, interval 1 day);

#5-创建时间维度表
CREATE TABLE `report_time_dimension` (
  `rtd_date` date DEFAULT NULL COMMENT '日期',
  `rtd_id` int(11) NOT NULL COMMENT '时间维度ID',
  `rtd_year` smallint(6) DEFAULT NULL COMMENT '年',
  `rtd_month` smallint(6) DEFAULT NULL COMMENT '月',
  `rtd_day` smallint(6) DEFAULT NULL COMMENT '日',
  `rtd_year_week` smallint(6) DEFAULT NULL COMMENT '年周',
  `rtd_week` smallint(6) DEFAULT NULL COMMENT '周',
  `rtd_quarter` smallint(6) DEFAULT NULL COMMENT '季',
  `rtd_weekday` smallint(6) DEFAULT NULL COMMENT '星期几',
  `rtd_month_name` char(10) DEFAULT NULL COMMENT '月份名称',
  `rtd_weekday_name` char(10) DEFAULT NULL COMMENT '星期几名称',
  PRIMARY KEY (`rtd_id`),
  KEY `rtd_year` (`rtd_year`),
  KEY `rtd_month` (`rtd_month`),
  KEY `rtd_week` (`rtd_week`),
  KEY `rtd_quarter` (`rtd_quarter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='时间维度表';

#6-填充时间维度数据
INSERT INTO report_time_dimension
SELECT
  @date := DATE_ADD(@date, INTERVAL 1 DAY) AS DATE,
  DATE_FORMAT(@date, "%Y%m%d") AS rtd_id,
  YEAR(@date) AS rtd_year,
  MONTH(@date) AS rtd_month,
  DAY(@date) AS rtd_day,
  DATE_FORMAT(@date, "%x") AS rtd_year_week,
  WEEK(@date, 3) AS rtd_week,
  QUARTER(@date) AS rtd_quarter,
  WEEKDAY(@date) + 1 AS rtd_weekday,
  MONTHNAME(@date) AS rtd_month_name,
  DAYNAME(@date) AS rtd_weekday_name
FROM
  time_dim_basic_num
WHERE DATE_ADD(@date, INTERVAL 1 DAY) <= @d1
ORDER BY DATE;
#=================================时间维度数据END============================================