C# 生成日期维度值

1. 时间维度表结构

/*==============================================================*/
/* Table: dim_date_day  日期维度_天   */
/*==============================================================*/
DROP TABLE IF EXISTS dw_realestate_sales.dim_date_day;
CREATE TABLE dw_realestate_sales.dim_date_day
( 
date_key                INT            NOT NULL  DEFAULT   '1'                  COMMENT '维度主键',
alternate_key           DATE           NOT NULL  DEFAULT   '1900-01-01'         COMMENT '自然日日期值',
day_number_of_week      TINYINT                  DEFAULT   '1'                  COMMENT '星期中的第几天',
day_name_of_week        VARCHAR(20)              DEFAULT   ''                   COMMENT '星期中名称',
day_number_of_month     TINYINT                  DEFAULT   '1'                  COMMENT '月份中的天序号，从1开始',
day_number_of_year      INT                      DEFAULT   '1'                  COMMENT '一年中的天数序号，从1开始',
week_number_of_year     TINYINT                  DEFAULT   '1'                  COMMENT '年中的星期序号，从1开始',
month_name              VARCHAR(20)              DEFAULT   ''                   COMMENT '月份名称',
month_number_of_year    TINYINT                  DEFAULT   '1'                  COMMENT '月份序号，从1开始',
quarter_of_year         TINYINT                  DEFAULT   '1'                  COMMENT '季度序号',
year_number             INT                      DEFAULT   '1'                  COMMENT '公历年数字'
) 
UNIQUE KEY(date_key)
DISTRIBUTED BY HASH(date_key) BUCKETS 2
properties
(
"replication_num"="3"
);

 

2. C#生成预置数据代码

  private static void generate_dim_date_data()
        {
            DateTime startDate = new DateTime(2000, 1, 1);
            DateTime endDate = new DateTime(2100, 1, 1);

            StringBuilder sb = new StringBuilder();
            GregorianCalendar gc = new GregorianCalendar();
            //int weekOfYear = gc.GetWeekOfYear(dt, CalendarWeekRule.FirstDay, DayOfWeek.Monday);
            //return weekOfYear;

            int linecount = 0;
            while (startDate < endDate)
            {
                // like 20050701,2005-07-01,6,Friday,1,182,27,July,7,3,2005
                sb.Append(string.Format("{0}{1:D2}{2:D2}", startDate.Year, startDate.Month, startDate.Day)).Append(',').
                   Append(startDate.ToString("yyyy-MM-dd")).Append(',').
                   Append((int)startDate.DayOfWeek+1).Append(',').
                   Append(GetWeekDayName(startDate.DayOfWeek)).Append(',').
                   Append(startDate.Day).Append(',').
                   Append(startDate.DayOfYear).Append(',').
                   Append(gc.GetWeekOfYear(startDate, CalendarWeekRule.FirstDay, DayOfWeek.Monday)).Append(',').
                   Append(GetMonthName(startDate.Month)).Append(',').
                   Append(startDate.Month).Append(',').
                   Append((startDate.Month - 1) / 3 + 1).Append(',').
                   Append(startDate.Year).AppendLine();
                linecount+=1;
                startDate = startDate.AddDays(1);
                //if (linecount == 100)
                //{
                //    Write(sb);
                //}
            }

            Write(sb);
        }

        public static void Write(StringBuilder sb)
        {
            FileStream fs = new FileStream("c:\\tmp\\dim_date.txt", FileMode.OpenOrCreate);
            //获得字节数组
            byte[] data = System.Text.Encoding.UTF8.GetBytes(sb.ToString());
            //开始写入
            fs.Write(data, 0, data.Length);
            //清空缓冲区、关闭流
            fs.Flush();
            fs.Close();
            sb.Clear();
        }

        private static string GetWeekDayName(DayOfWeek dayofWeek)
        {
            string[] Day = new string[] {  "星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期日" };
            string week = Day[Convert.ToInt32(dayofWeek.ToString("d"))].ToString();
            return week;
        }

        private static string GetMonthName(int monthNumber)
        {
            string[] monthNames = new string[] { "一月份", "二月份", "三月份", "四月份", "五月份", "六月份", "七月份", "八月份", "九月份", "十月份", "十一月份", "十二月份" };
            return monthNames[monthNumber - 1];
        }

 

3. 结果预览