java和sql生成时间维度数据

JAVA:
POM依赖：
 

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4.2</version>
        </dependency>

代码：

package com.kone.kcdp.common;

import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import org.apache.logging.log4j.core.lookup.JmxRuntimeInputArgumentsLookup;

import java.util.List;

public class DateGenerator {

    private static final String DATE_STD_FORMAT = "yyyy-MM-dd";
    private static final String DATE_TRIM_FORMAT = "yyyyMMdd";
    private static final String DATE_CN_FORMAT = "yyyy年M月d日";
    private static final String MONTH_STD_FORMAT = "yyyy-MM";
    private static final String MONTH_TRIM_FORMAT = "yyyyMM";
    private static final String MONTH_CN_FORMAT = "yyyy年M月";
    private static final String YEAR_STD_FORMAT = "yyyy";
    private static final String YEAR_CN_FORMAT = "yyyy年";

    public static void dateTemplate() {

        List<DateTime> dateTimes = DateUtil.rangeToList(DateUtil.date(963843260000l), DateUtil.date(), DateField.DAY_OF_MONTH);
        dateTimes.forEach(dateTime -> {
            //日
            String dateStd = DateUtil.format(dateTime, DATE_STD_FORMAT);
            String dateTrim = DateUtil.format(dateTime, DATE_TRIM_FORMAT);
            String dateCn = DateUtil.format(dateTime, DATE_CN_FORMAT);
            String beginOfDay = DateUtil.beginOfDay(dateTime).toString();
            String endOfDay = DateUtil.endOfDay(dateTime).toString();
            int age = DateUtil.ageOfNow(dateTime);

            //周
            int weekOfMonth = DateUtil.weekOfMonth(dateTime);
            int weekOfYear = DateUtil.weekOfYear(dateTime);
            int dayOfWeek = DateUtil.dayOfWeek(dateTime);
            String week = DateUtil.dayOfWeekEnum(dateTime).toString();
            String beginDateOfWeek = DateUtil.format(DateUtil.beginOfWeek(dateTime), DATE_STD_FORMAT);
            String endDateOfWeek = DateUtil.format(DateUtil.endOfWeek(dateTime), DATE_STD_FORMAT);


            //月
            String monthStd = DateUtil.format(dateTime, MONTH_STD_FORMAT);
            String monthTrim = DateUtil.format(dateTime, MONTH_TRIM_FORMAT);
            String monthCn = DateUtil.format(dateTime, MONTH_CN_FORMAT);
            String monthEn = DateUtil.monthEnum(dateTime).toString();
            String beginDateOfMonth = DateUtil.format(DateUtil.beginOfMonth(dateTime), DATE_STD_FORMAT);
            String endDateOfMonth = DateUtil.format(DateUtil.endOfMonth(dateTime), DATE_STD_FORMAT);


            //季度
            String quarterId = DateUtil.yearAndQuarter(dateTime);
            int quarterNum = DateUtil.quarter(dateTime);
            String quarterEn = DateUtil.quarterEnum(dateTime).toString();
            String quarterCn;
            String quarterYearCn = quarterId.substring(0, 4) + "年第" + quarterId.substring(4, 5) + "季度";
            String quarterYearEn = quarterId.substring(0, 4) + " " + quarterEn;
            switch (quarterEn) {
                case "Q1":
                    quarterCn = "第一季度";
                    break;
                case "Q2":
                    quarterCn = "第二季度";
                    break;
                case "Q3":
                    quarterCn = "第三季度";
                    break;
                default:
                    quarterCn = "第四季度";
                    break;
            }
            String beginDateOfQuarter = DateUtil.format(DateUtil.beginOfQuarter(dateTime), DATE_STD_FORMAT);
            String endDateOfQuarter = DateUtil.format(DateUtil.endOfQuarter(dateTime), DATE_STD_FORMAT);

            //年
            String yearStd = DateUtil.format(dateTime, YEAR_STD_FORMAT);
            String yearCn = DateUtil.format(dateTime, YEAR_CN_FORMAT);
            String chineseZodiac = DateUtil.getChineseZodiac(Integer.parseInt(yearStd));
            String beginDateOfYear = DateUtil.format(DateUtil.beginOfYear(dateTime), DATE_STD_FORMAT);
            String endDateOfYear = DateUtil.format(DateUtil.endOfYear(dateTime), DATE_STD_FORMAT);


        });


    }
}

SQL：

-- CREATE EXTERNAL TABLE `dim.dim_date_base`(
--   `nature_date` string COMMENT '自然日期', 
--   `dim_date` bigint COMMENT '维度日期', 
--   `year` int COMMENT '年', 
--   `month` int COMMENT '月', 
--   `week` int COMMENT '周', 
--   `week_start` string COMMENT '周开始', 
--   `week_end` string COMMENT '周结束', 
--   `week_of_year` int COMMENT '周所属年', 
--   `month_start` string COMMENT '月开始', 
--   `month_end` string COMMENT '月结束', 
--   `is_last_day_of_month` int COMMENT '是否是最后天所属月', 
--   `month_date_cnt` int COMMENT '月日期总数', 
--   `day_of_month` int COMMENT '天所属月', 
--   `day_of_year` int COMMENT '天所属年', 
--   `is_week_end` int COMMENT '是否是周结束', 
--   `quarter` int COMMENT '所属季度')
-- COMMENT '日期域'




WITH
  DateArray AS (
    SELECT
      sequence(
       to_date('2000-01-01', 'yyyy-MM-dd'),
       to_date('2100-12-31', 'yyyy-MM-dd'),
        interval 1 day
      ) AS dateArray
  )
  
  insert into table dim.dim_date_base
  
SELECT
  dateLog `date` , 
  CAST(regexp_replace(dateLog, '-', '') AS INT) dt,
 YEAR(dateLog) AS `year`,
 MONTH(dateLog) AS `month`,
    DAYOFWEEK(date_add(dateLog,-1)) AS `week`,
    date_add(dateLog,(DAYOFWEEK(dateLog-1)-1)*-1) week_begin_date ,
    date_add(dateLog, 7-DAYOFWEEK(dateLog-1) ) week_end_date ,
    WEEKOFYEAR(dateLog)  week_of_year,
    concat(substr(dateLog,0,8),'01')  month_begin_date,
     last_day(dateLog)  month_end_date,
     if(dateLog=last_day(dateLog),1,0) is_last_day_of_month ,
     day(last_day(dateLog)) month_date_cnt ,
      day(dateLog) day_of_month ,
      dayofyear(dateLog) day_of_year,
      if( DAYOFWEEK(dateLog) in(1,7),1,0) is_week_end,
      quarter(dateLog) `quarter`
FROM
  (
    SELECT
      explode(dateArray) AS dateLog
    FROM
      DateArray
  ) t