精华内容
下载资源
问答
  • 日期维度表 格式
    2021-03-23 16:16:34

    MYSQL 生成日期维度表

    在报表统计中经常要用到日期维度进行汇总,所以需要提前生成通用的日期维度表。

    1、创建建维度表

    DROP TABLE IF EXISTS tb_rpt_dim_day;
    CREATE TABLE tb_rpt_dim_day (
    day_id bigint(0) NOT NULL COMMENT ‘主健’,
    day_short date NOT NULL COMMENT ‘日期’,
    year int(0) NOT NULL COMMENT ‘所属年份’,
    week int(0) NOT NULL COMMENT ‘星期几’,
    week_cn varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘星期几(中文)’,
    week_en varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘星期几(英文)’,
    month int(0) NOT NULL COMMENT ‘所属月份’,
    month_cn varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属月份(中文)’,
    month_en varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属月份(英文)’,
    quarter int(0) NOT NULL COMMENT ‘所属季度’,
    quarter_cn varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属季度(中文)’,
    week_of_year int(0) NOT NULL COMMENT ‘本年第几周’,
    day_of_month int(0) NOT NULL COMMENT ‘本月第几天’,
    day_of_year int(0) NOT NULL COMMENT ‘本年第几天’,
    PRIMARY KEY (day_id) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

    SET FOREIGN_KEY_CHECKS = 1;

    2、生成维度数据函数

    CREATE DEFINER=root@% PROCEDURE f_dim_day( IN start_date VARCHAR ( 20 ), IN date_count INT )
    BEGIN
    DECLARE
    i INT;

    SET i = 0;
    DELETE 
    FROM
    	tb_rpt_dim_day;
    WHILE
    		i < date_count DO
    		INSERT INTO tb_rpt_dim_day SELECT REPLACE
    		( start_date, '-', '' ) AS day_id,-- 日期主健
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%Y-%m-%d' ) AS day_short,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%Y' ) AS `year`,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%w' ) AS `week`,
    	CASE
    			DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%w' ) 
    			WHEN 1 THEN
    			'星期一' 
    			WHEN 2 THEN
    			'星期二' 
    			WHEN 3 THEN
    			'星期三' 
    			WHEN 4 THEN
    			'星期四' 
    			WHEN 5 THEN
    			'星期五' 
    			WHEN 6 THEN
    			'星期六' 
    			WHEN 0 THEN
    			'星期天' 
    		END AS `week_cn`,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%W' ) AS `week_en`,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%c' ) AS `month`,
    	CASE
    			DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%c' ) 
    			WHEN 1 THEN
    			'一月' 
    			WHEN 2 THEN
    			'二月' 
    			WHEN 3 THEN
    			'三月' 
    			WHEN 4 THEN
    			'四月' 
    			WHEN 5 THEN
    			'五月' 
    			WHEN 6 THEN
    			'六月' 
    			WHEN 7 THEN
    			'七月' 
    			WHEN 8 THEN
    			'八月' 
    			WHEN 9 THEN
    			'九月' 
    			WHEN 10 THEN
    			'十月' 
    			WHEN 11 THEN
    			'十一月' 
    			WHEN 12 THEN
    			'十二月' 
    		END AS `month_cn`,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%b' ) AS `month_en`,
    		QUARTER ( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) AS `quarter`,
    	CASE
    			QUARTER ( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) 
    			WHEN 1 THEN
    			'一季度' 
    			WHEN 2 THEN
    			'二季度' 
    			WHEN 3 THEN
    			'三季度' 
    			WHEN 4 THEN
    			'四季度' 
    		END AS quarter_cn,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%u' ) AS `week_of_year`,
    		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%e' ) AS `day_of_month`,
    		DAYOFYEAR( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) AS `day_of_year` 
    	FROM
    		DUAL;
    	
    	SET i = i + 1;
    	
    	SET start_date = DATE_FORMAT( date_add( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), INTERVAL 1 DAY ), '%Y-%m-%d' );
    	
    END WHILE;
    

    END

    调用函数如下:call f_dim_day(‘2021-01-01’,365)

    更多相关内容
  • 行政区域维度、时间维度日期维度表结构、数据、建表语句、生成数据的python脚本。
  • 维护了2022年全年的日期,特殊的法定节假日,调休,周末,工作日都有标识
  • 该代码是关于创建数据仓库日期维度表的存储过程
  • 数据仓库之日期维度表构建

    千次阅读 2022-08-07 18:10:35
    我们上一篇文章 数据仓库之维度 介绍了数据仓库中维度,本篇文章在此基础上介绍和构建维度中的日期维度。Kimball 维度建模中的设计原则提到确保每一个事实都具有关联的日期维度。数据仓库项目中,日期维度是...

    导读

    大家好,今日立秋,立秋是阳气渐收、阴气渐长,由阳盛逐渐转变为阴盛的转折。我们上一篇文章 数据仓库之维度表 介绍了数据仓库中维度表,本篇文章在此基础上介绍和构建维度表中的日期维度。Kimball 维度建模中的设计原则提到确保每一个事实表都具有关联的日期维度。数据仓库项目中,日期维度是最常见的,不可缺少的维度表之一。

    数据仓库之维度表
    数据仓库之维度表

    本文中 ETL 转换使用 Kettle 9.3 版本实现,关注公众号回复关键字 dim_date 获取。

    为什么需要日期维度表

    Kimball 认为数据仓库中最重要的三个主题是向下钻取,横向钻取和时间处理。时间处理正式其中之一,而日期维度为数据仓库提供了时间处理的可能性。这里我们复习一下数据仓库的概念,Inmon 对数据仓库的描述是:“面向主题的,整合的,随时间变化的,包含汇总和明细的,稳定的历史数据集合”。 数据仓库中存储的是企业中某个时间段的数据,数据仓库中的数据像快照一样,每一个张快照都反映了某个时点的数据状态。这也意味着,无论用户什么时间到数据仓库查询,都会得到某个时间段的相同结果。

    alt

    四维时空理论中,零维是点,一维是线,二维是面,三维是提,四维是由无穷多个三维加一个时间维度,也称为 3+1维时空。四维时空中的时间维是一直向前的,科学家都认为时间为是一种伪维度。

    日期维度的特点

    虽然人类的世界是三维的,但是在数据仓库中,时间维度确保数据仓库具有一下几个主要特征:

    1,时间有效性 数据仓库的日期维度可以确保数据仓库的数据在某个时间段的明确有效性。通过日期维度可以明确了解这部分数据的有效时间区间。这里主要是对事实表中的度量的时间有效性的描述。

    2,时间变化性

    除了事实表可以通过日期维度的关联确保数据在某个时点的数据准确性外,维度表也可以通过 SCD ( Slowly Changing Dimension ) 代表某个业务数据在起始时间这段时间的快照属性信息。SCD 维度表就是我们常称为拉链表的维度表。时间变化性确保了事实表中的某个度量关联了正确的实体,确保了针对企业不同数据实体随着时间变化不同时期版本的正确性。这里主要是对维度表中相同实体随着时间变化而属性不同的需求

    3,时间变化带来的数据查询方式变化

    前文中有见到事实表其实分为事务粒度,定期快照粒度和累计快照粒度。这三种事实表自然粒度代表的就是不同时间窗口变化。企业中日常交易数据是按照企业业务流程中粒度最细的明细数据,也就是事务数据。我们会在后文中用一篇文章讲解事实表。 1),简单理解,事务粒度可以理解为空间和时间中的一个点。事务粒度代表的就是这点的相关业务过程事实表中的该业务流程发生了什么?往往事实粒度取决于源系统,数据也来源于源系统,源系统一般为企业中的 OLTP 系统。
    2),定期快照粒度也代表某个时间点,但是与事务粒度不同的是,定期快照表示的是一个总的时间点,比如常见于财务报告中的会计期间,这里的期间代表可能是某个季度,或者某个年度,但必须是重复的测量。
    3),累计快照粒度代表的具有有限起止节点的过程的当前状态。累计快照与前两种事实表不同的是累计快照具有一组多个(4~12)描述业务流程中典型场景的日期。比如一张卡从申请到注销,往往具备申请日期,开卡日期,首笔交易日期,最近一笔交易日期,注销时间等等。这里需要企业根据实际业务过程对不同快照时间点的定义。在累计快照粒度的事实表中需要记录或者更新业务实体数据在全生命周期中的某个业务场景的日期值。

    日期维度属性

    alt

    日期维度中一般具有一组含有多个日期格式的属性,国家法律法规相关要求的日历特征以及业务需求相关的日期特征。我们需要考虑以下集中于日期维度相关的主要需求点。

    1),日期具有不同的表示格式,不同国家或者不同部门对日期的格式有不同的定义,比如 yyyy-mm-dd,yyyymmdd,mmddyyyy等等,这里不一一列举,日期维度表中需要具备与企业中周边系统对接,以及不同部门对BI报表需求所具备的日期格式能力。

    2),国家法律法规相关的日历属性,除了国际通用性假期,如五一之外,不同地区具有不同的假期安排,需要根据实际也需要扩充日期维度的属性字段。再比如国内具有除了阳历之外,还有公历日期,同样在日期维度表中可以根据实际需要增加公历日期的字段。

    3),业务需求相关的日期特征,比如 ERP 系统中财务模块需要的对账日期,或者不同企业财务部分制定的不同财年等等,都与日期维度相关。

    代理键和自然键

    是的,数据仓库中永远存在两种声音,是否使用代理键,这种讨论在大数据技术的出现后愈加激烈。不可忽视的是代理键为数据仓库各层之间的解耦和快速数据定位带来了更大的便利性,本文中不做代理键和自然键的优劣分析。日期维度上既可以使用代理键或者直接使用自然键作为主键都可以。当然在构建数据仓库过程中如果使用 "unknow"/"n/a" 作为默认值时,是不存在自然键的,此时使用代理键貌似更合理一些。

    如何构建日期维度表

    我们对日期维度表已经了解了许多了,需要在数据仓库中实现日期维度表的实现,采用何种技术实现日期维度表与使用的平台有很大的关系。如果是在传统数仓的关系型数据库中,存储过程会是一种很快的选择,存储过程的开发是数据开发者的必修课,存储过程的开发也很简单,需要符合关系型数据存储过程开发的规则,变量的定义规则,符合逻辑的程序结构定义以及异常的捕捉就可以了。我们这里采用的是开源 ETL 工具 Kettl 实现日期维度表的开发。 Kettle 是一款使用 Java 开发的开源 ETL 框架,基于使用简单,组件丰富的特点。,Kettle 的使用用户也非常庞大,尤其在互联网企业中应用最为广泛。Kettle 的入门文章可以参考本公众号的 Kettle 系列文章。

    1),日期维度表建表语句

    CREATE TABLE dbo.DimDate(
     DateKey int NOT NULL,
     FullDateAlternateKey date NOT NULL,
     DayNumberOfWeek tinyint NOT NULL,
     EnglishDayNameOfWeek nvarchar(10NOT NULL,
     SpanishDayNameOfWeek nvarchar(10NOT NULL,
     FrenchDayNameOfWeek nvarchar(10NOT NULL,
     DayNumberOfMonth tinyint NOT NULL,
     DayNumberOfYear smallint NOT NULL,
     WeekNumberOfYear tinyint NOT NULL,
     EnglishMonthName nvarchar(10NOT NULL,
     SpanishMonthName nvarchar(10NOT NULL,
     FrenchMonthName nvarchar(10NOT NULL,
     MonthNumberOfYear tinyint NOT NULL,
     CalendarQuarter tinyint NOT NULL,
     CalendarYear smallint NOT NULL,
     CalendarSemester tinyint NOT NULL,
     FiscalQuarter tinyint NOT NULL,
     FiscalYear smallint NOT NULL,
     FiscalSemester tinyint NOT NULL,
     CONSTRAINT PK_DimDate_DateKey PRIMARY KEY CLUSTERED 
    (
     DateKey ASC
    )
    );

    2),创建维度表转换

    ETL 开发之前,需要了解数据流与控制流的定义,数据流,可以理解为 Kettle 中的 Transformation,其定义了数据从数据源,经过满足业务需求的数据转换后流向目标数据源的过程。控制流,就可以认为是 Kettle 中的 Job,可以包含一个或者多个数据流,以及数据流之间的先后与依赖关系。转换开发完成后,可以手动执行测试转换的正确性,我们这里在 Kettle 工作面板上执行转换,执行后可以看到如下截图,可以看到每个组件的右上角为绿色的,代表执行成功,如果执行失败,组件上会出现红色的符号,非常容易理解。

    Kettle 维度表转换
    Kettle 维度表转换

    本次的转换主要是生成日期维度表所需要的数据,如果执行成功后,可以到目标表中检查日期维度表数据是否创建成功,此时也可以通过转换步骤中设置的创建天数以及开始日期到日期维度表中检查数据创建成功以及每个属性的正确性(包括日期格式,假期名称以及其中年月日等描述字段)。

    日期维度表查询结果
    日期维度表查询结果

    3,创建作业并部署计划任务

    转换测试成功后,根据需要创建日期维度表的作业任务并定期执行,由于此次是一次性创建了10年的日期数据,该任务可以设置为请求任务,无需设置为每日/每年的定时任务。当然还需要根据实际项目需要,本次联系任务就不再添加计划任务了。

    Kettle 作业执行日志
    Kettle 作业执行日志

    至此我们已经完成了维度表中日期维度表的创建,除了日期维度表,和时间相关的就是小时维度表了,为了更精确的统计业务指标,数据仓库中加入小时维度表成为了必须。根据日期维度表的构建,相信小时维度表的构建也更简单与容易。

    往期文章

    数据仓库系列

    数据仓库之维度表

    Hive 系列

    Hive 必知必会(一)介绍

    Hive 必知必会(二)基本操作

    Hive 必知必会(三)基本操作(续)

    SQL Server 优化

    一文读懂 SQL Server 执行计划

    本文由 mdnice 多平台发布

    展开全文
  • 数仓日期维度表生成

    2022-05-10 15:09:46
    日期维度表 在mysql中建并更新节假日信息,然后再导到hive上 建表 create table dim_date1( datekey varchar(10), dateymd varchar(10), datech varchar(20), dimyear varchar(10), quarterid int COMMENT '季度...

    日期维度表

    在mysql中建表并更新节假日信息,然后再导到hive上

    建表

    create table dim_date1(
    datekey varchar(10),
    dateymd varchar(10),
    datech varchar(20),
    dimyear varchar(10),
    quarterid int COMMENT '季度数字',
    quartercH varchar(10),
    yearmonth varchar(10),
    dimmonth varchar(10),
    dimday varchar(10)  COMMENT '一年中的第几天',
    dimmonthday varchar(10) comment '一个月中的第几天',
    dimweek varchar(10)  COMMENT '一年中的第几周 星期一是星期的第一天',
    yearweek  varchar(10)  COMMENT '年周',
    dimweek1 varchar(10)  COMMENT '一年中的第几周 星期天是星期的第一天',
    dimyear1  varchar(10)  COMMENT '对应dimweek1的年份',
    dimweekday varchar(10) comment '一周的中的第几天',
    weekdaych varchar(10) COMMENT '星期几',
    holidayname varchar(10) COMMENT '节假日名称',
    isdayoff int COMMENT '是否放假 1表示放假,0表示不放假 2表示调班'
    #mysql5版本的数据库默认charset是latin1 不改的话存储过程插入中文字段会报错
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    

    存储过程初始化日期维度

    drop procedure if exists f_dim_day;  
    create procedure f_dim_day(in start_date VARCHAR(20),in end_date VARCHAR(20))
    begin  
    declare i int;  
    set i=0;  
    DELETE from dim_date1;  
    while DATE_FORMAT(start_date,'%Y-%m-%d %H:%i:%s') < DATE_FORMAT(end_date,'%Y-%m-%d %H:%i:%s') do  
    INSERT into dim_date1 
    (datekey,dateymd ,datech,dimyear,quarterid,quarterch ,yearmonth,dimmonth,dimday,dimmonthday,dimweek,yearweek,dimyear1,dimweek1,dimweekday,weekdaych,holidayname,isdayoff)  
    SELECT  
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m%d')  datekey,  
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') dateymd,  
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') datech, 
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') dimyear, 
    quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s')) quarterid, 
    CONCAT(quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') quarterch,  
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') yearmonth, 
    CONVERT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%m'),
    SIGNED) dimmonth, 
    DAYOFYEAR(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) dimday,
    CONVERT( DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%d'
    ),SIGNED) dimmonthday,
    CONVERT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%u'),
    SIGNED) dimweek,  
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%u') yearweek,
    
    case CONVERT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%U'),SIGNED)+1
    when 53 then
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y')+1
    else
    DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y')
    end 
    dimyear1,
    
    case CONVERT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%U'),SIGNED)+1
    when 53 then
    1
    else
    CONVERT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%U'),SIGNED)+1
    end 
    dimweek1,
    
    DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))  dimweekday,
    case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))  when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end weekdaych,
    '' holidayname,
    0 isdayoff
    from dual;  
    set i=i+1;  
    set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');  
    end while;  
    end;   
    
    #调用存储过程
    call f_dim_day('2018-01-01 00:00:00','2024-01-02 00:00:00'); 
    
    
    

    节假日放假信息更新

    研究百度日历的请求,得到日历url返回的json,(以前没有暴露这个url还得通过selenium去模拟请求, 网上有的日历接口也不那么友好),对我们有用的是以下字段
    在这里插入图片描述

    python爬取

    # -*- coding: utf-8 -*-
    # @Time     : 2022/5/6 19:50
    # @Author   : bailin
    # @Description: 获取百度上的日历信息更新日期维度表
    import json
    import requests
    import pymysql
    
    # 插入数据库
    CONN = pymysql.connect("localhost", "root", "gogobi", "bidm", charset='utf8')
    def insertDB(sql):
        cursor = CONN.cursor()
        try:
            cursor.execute(sql)
            CONN.commit()
            print(sql)
        except Exception as e:
            print(sql)
            print(e)
    
    
    #获取日历上每天的节假日信息
    def getMonthDayInfo(url):
        html = requests.get(url).text
        #print(html)
    
        # 转化成字典
        str1 = html.replace('/**/op_aladdin_callback1651906546123(', '').replace(');', '')
        #字符串转字典
        dict_test = json.loads(str1)
    
        for dayinfs in dict_test['data']:
            for dayinf in dayinfs["almanac"]:
                #拼接update的sql
                update_sql ="update dim_date1 set holidayname ='"+try_except(dayinf,"term")+"', isdayoff='"+ try_except(dayinf, "status") \
                            +"' where dimyear="+try_except(dayinf,"year")+" and dimmonth="+try_except(dayinf,"month")+" and dimmonthday="+try_except(dayinf,"day");
                insertDB(update_sql)
    
    #处理字典中没有索引的值 防止程序报错
    def try_except(dict,index):
        try:
            return str(dict[index])
        except Exception as e:
            if index=="status":
                return  '0'
            else:
                return ''
    
    
    if __name__ == '__main__':
        #月份 年份在url里手动改
        counter = 1
        while counter <= 12:
            url = "https://sp1.baidu.com/8aQDcjqpAAV3otqbppnN2DJv/api.php?tn=wisetpl&format=json&resource_id=39043&query=2022年"+str(counter)+"月&t=1651906546123&cb=op_aladdin_callback1651906546123"
            getMonthDayInfo(url)
            counter += 1
    
    #将不调班的周末置位放假
    update dim_date1 set isdayoff = 1 where weekdaych in ('星期六','星期天' ) and isdayoff <>2;
    

    验证

    在这里插入图片描述

    转存到数仓

    create external table  if not exists  dim_date(
    datekey string,
    dateymd string,
    datech string,
    dimyear string,
    quarterid int COMMENT '季度数字',
    quartercH string,
    yearmonth string,
    dimmonth string,
    dimday string  COMMENT '一年中的第几天',
    dimmonthday string comment '一个月中的第几天',
    dimweek string  COMMENT '一年中的第几周 星期一是星期的第一天',
    yearweek  string  COMMENT '年周',
    dimweek1 string  COMMENT '一年中的第几周 星期天是星期的第一天',
    dimyear1  string  COMMENT '对应dimweek1的年份',
    dimweekday string comment '一周的中的第几天',
    weekdaych string COMMENT '星期几',
    holidayname string COMMENT '节假日名称',
    isdayoff int COMMENT '是否放假 1表示放假,0表示不放假 2表示调班'
    )row format delimited fields terminated by '\t'
    stored as textfile;
    

    sqoop导入

    sqoop import \
    --connect jdbc:mysql://hadoop102:3306/bidm \
    --username root \
    --password gogobi \
    --table dim_date1 \
    --num-mappers 1 \
    --hive-import \
    --fields-terminated-by "\t" \
    --hive-overwrite \
    --hive-table myhive.dim_date
    
    展开全文
  • 日期维度生成语句

    2017-11-17 11:21:57
    日期维度生成语句 日期维度 批量生产数据 生成日期维度表
  • PL_SQL生成日期维度表

    2017-04-24 11:34:25
    PL_SQL生成日期维度表
  • 2022年日期维度(包含国家法定节假日和其他节假日)
  • hive生成日期维度表Hql

    千次阅读 2022-01-08 10:49:23
    hive生成日期维度表 利用现有的函数,生成维度 结构 日期key 具体日期 第几月份 第几季度 年份 周几 当前周的第几天 当前月的第几天 当前年的第几天 当前年的第几周 当前周的第一天 当前周的最后一天 分析 ...

    hive生成日期维度表

    利用现有的函数,生成维度表

    表结构

    日期key
    具体日期
    第几月份
    第几季度
    年份
    周几
    当前周的第几天
    当前月的第几天
    当前年的第几天
    当前年的第几周
    当前周的第一天
    当前周的最后一天
    

    分析

    上述的表字段都是基于某个具体日期上计算的,所有首先要生成一个日期表,然后再计算每一天的具体属性

    范围根据自己的需求,比如我这里定义的日期范围是2022-01-01~2022-12-31

    # 1.使用date_add()以及posexplode()生成目标日期范围数据
    select date_add("2022-01-01", a.pos) as range_date
    from (
       select posexplode(split(repeat("@", datediff("2022-12-31", "2022-01-01")), "@"))
         ) a
    
    #2.计算每一天的属性
    with date_range as (
        select date_add("2022-01-01", a.pos) as d
    from (
       select posexplode(split(repeat("@", datediff("2022-12-31", "2022-01-01")), "@"))
         ) a
    )
    select
        date_format(d, 'yyyyMMdd') as date_key
         ,d as `date`
         ,date_format(d,'yyyMM') as month
         ,month(d) as month_short
         ,quarter(d) as quarter
         ,year(d) as year
         ,date_format(d,'EEEE') as zhouji
         ,date_format(d, 'u') as daynumber_of_week
         ,day(d) as daynumer_of_month
         ,date_format(d, 'D') as daynumber_of_year
         ,weekofyear(d) as year_weeks
         ,date_add(d,1 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end) as week_first_day
         ,date_add(d,7 - case when dayofweek(d) = 1 then 7 else dayofweek(d) - 1 end) as week_last_day
    from date_range;
    

    补充几个一个关于date_format的用法

    -- 当前日期是周几
    select date_format(`current_date`(),'EEE'); 
    
    -- 当前日期是本周第几天
    select date_format(`current_date`(),'u');
    
    -- 当前日期是当前年的第几天
    select date_format(`current_date`(),'D');
    -- 当前日期是当前月份的第几天
    select day(`current_date`())
    
    
    展开全文
  • Hive生成超级日期维度表

    千次阅读 2021-12-28 22:34:43
    创建日期 drop table dim.dim_datetime; --CREATE EXTERNAL TABLE IF NOT EXISTS dim.dim_datetime( CREATE TABLE IF NOT EXISTS dim.dim_datetime( `datetimestr` STRING COMMENT '当前时间(yyyy-MM-dd HH:mm...
  • 创建转换,添加“生成记录”控件,“增加序列”控件,“JavaScript代码”控件,输出 配置生成记录控件 配置增加序列控件 JavaScript代码 配置输出 执行结果:
  • 使用Sql Server的存储过程生成日期维度表 你好! 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章,了解一下Markdown的基本语法知识。 新的改变 我们...
  • Mysql 快速生成日期时间维度表

    千次阅读 2021-12-07 17:01:55
    Mysql 快速生成日期时间维度表 创建结构 CREATE TABLE `dim_date` ( `id` int(11) NOT NULL COMMENT '日期键 20000101', `date` date DEFAULT NULL COMMENT '日期 2000-01-01', `year` smallint(6) DEFAULT ...
  • 日期维度是我们最常用的维度,平台初始,最先初始化的可能就是日期维度,这里我们就简单介绍下日期维度。 什么是日期维度 我们日常生活,数据的产生都和日期有关,每一分、每一秒都会产生数据,数据分析也离不开...
  • 一个sql生成hive日期维度表

    千次阅读 2020-12-28 14:27:35
    日期 yyyMMdd格式 星期,数字型 0-6 星期中文名 星期一…… 一年中的第几周 1 2 3…… 本周周一日期 本周周日日期 年月,yyyyMM格式 月份简写,MM格式1~12 月份中文名 一月…… 季度,yyyyQ1\2\3\4 季度 数字型 1-4
  • kettle创建日期维度表

    千次阅读 2020-02-16 20:11:18
    2.增加日期序列 3.生成日期的属性(JavaScript代码) //locale var locale = new java.util.Locale(language.getString(),country.getString()); //calendar var calendar = new java.util.GregorianCalendar(...
  • 一个完整的数据仓库,日期维度表是必不可少的,对于数据的多维度处理是一个很好的东西。现在的工作环境是阿里云,在这个环境下用纯sql构建了一个日期维度表,主要字段包含以下结果,后续农历日期将通过udf实现,再...
  • 在Hive中创建日期维度表

    千次阅读 2020-05-11 21:27:34
    输出: 2020-01-01 2020-01-02 2020-01-03 2020-01-04 2020-01-05 2020-01-06 2020-01-07 2020-01-08 2020-01-09 2020-01-10 日期维度 with dates as ( select date_add("2020-01-01", a.pos) as d from ( select ...
  • power bi日期表相对 In this article, I am going to describe how to use a date dimension table in Power BI. Using a date dimension table becomes extremely important while visualizing facts and figures...
  • 教你用SQL生成一张日期维度表

    千次阅读 2021-02-09 10:00:00
    点击关注“SQL数据库开发”,设为“置顶或星标”,第一时间送达干货问题描述输入想要生成日历的年份,通过调用存储过程,即可生成该年的全部日历。创建结构我们根据常见的日历来创建一个含有年,...
  • 事实维度表

    千次阅读 2021-08-26 15:49:19
    1.5、事实的优点二、维度表2.1、维度表类型2.1.1、SCD(缓慢变化维)2.1.2、一致维度2.1.3、垃圾维度2.1.4、退化维度2.1.5、角色扮演维度2.2、它在数据仓库中是如何工作的?2.3、维度表的好处三、维度表 vs 事实...
  • 快速生成日期维度数据

    千次阅读 2019-04-06 08:38:32
    日期维度在数据仓库中是一个特殊角色。日期维度包含时间概念,而时间是最重要的,因为数据仓库的主要功能之一就是存储和追溯历史数据,所以每个数据仓库里的数据都有一个时间... 假设建立有如下日期维度表: cre...
  • Kettle生成日期维度数据(数仓项目)

    千次阅读 2021-02-16 21:47:04
    目录生成日期维度数据Hive创建日期维度表使用Kettle构建以下组件结构图 生成日期维度数据 经过需求分析我们发现指标统计需要的日期类型比较多如果在事实的时间字段上做文章,比较复杂,甚至有些根本无法获取到比如...
  • 数据仓库维度建模-维度表设计

    千次阅读 2020-12-29 08:02:41
    1 维度表的定义在维度建模中,通常将指标的度量称之为“事实”,将产生度量的环境称之为“维度”。将描述同一个业务实体的的多个维度列组合在一起,就是常说的“维度表”。维度表是用户分析数据的窗口,其提供了事件...
  • 创建日期维度表 CREATE TABLE `date_dimension` ( `date_id` int(8) NOT NULL COMMENT '日期ID', `date_format` varchar(20) DEFAULT NULL COMMENT '日期格式化', `curr_day` int(8) DEFAULT NULL COMMENT '...
  • 维度建模-维度表

    2022-02-25 22:23:34
    但是日期维度不需要遵守这个规则,因为日期维度是高度可预测的且稳定的维度。 自然键、持久键和超自然键 自然键:受业务规则影响 持久键:也叫超自然键,不受业务规则影响 下钻 从粗粒度到细粒度的过程,会...
  • 创建日期时间的维度表,以年为维度,以季度为维度,以月为维度,以天为维度,各自创建时间维度hive, --创建年维度表 create table dimension_common.t_dim_year( year_id int comment '年份ID:yyyy', year_name...
  • 创建日期维度表

    千次阅读 2018-10-29 23:20:58
    --日期维度表 CREATE TABLE #DimDate (  [DateID] [int] NOT NULL primary key,  [DateName] [datetime] NULL,  [DateNameCN] [nvarchar...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 32,334
精华内容 12,933
热门标签
关键字:

日期维度表 格式