比较全的时间日期维表sql

最新推荐文章于 2025-05-06 15:25:46 发布
最新推荐文章于 2025-05-06 15:25:46 发布
阅读量1.1k 收藏 6
点赞数
CC 4.0 BY-SA版权
分类专栏： hive数据仓库 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/LookDreamGo/article/details/122089585
本文介绍了一个日期维度表的构建过程，包括从日、周到年的各个层级的数据转换和计算。通过对日期进行细致划分，便于后续的数据分析和报表制作。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

--日期维表基本信息
       with  tmp_es_date_00 as 
       (
        select  
        regexp_replace(predict_date_day,'-','')                                                           as date_id             -- 日(yyyymmdd)
       ,predict_date_day                                                                                  as date_code           -- 日(yyyy-mm-dd)      
	   ,concat(year(predict_date_day),'年',month(predict_date_day),'月',day(predict_date_day),'日')                                   as date_name           -- 日(yyyy年mm月dd日)
	   ,dayofweek(predict_date_day)                                                                       as week_day_id         -- 周ID（周几）
	   ,case dayofweek(predict_date_day)  when 1 then '星期日' when 2 then '星期一' 
                            	   when 3 then '星期二' when 4 then '星期三' 
                                   when 5 then '星期四' when 6 then '星期五' 
                                   when 7 then '星期六' end                                 as week_day_name       -- 星期(星期几)
	   ,concat(year(predict_date_day),case when weekofyear(predict_date_day) < 10 then concat('0',(weekofyear(predict_date_day))) else weekofyear(predict_date_day) end)	as week_ofyearid -- 年周的id
	   ,weekofyear(predict_date_day)                                                                      as week_ofyear         -- 年周
	   ,concat(year(predict_date_day),'年第',weekofyear(predict_date_day),'周')                                         as week_ofyear_name    -- 年周名称
	   ,concat(date_add(predict_date_day,1 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end),
	   '~',date_add(predict_date_day,7 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end))  as week_ofyear_period  -- 年周段
	   ,concat(
	    regexp_replace(substr(split(concat(date_add(predict_date_day,1 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end),
	   '~',date_add(predict_date_day,7 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end)), '~') [0], 6, 5),'-','.'),
         '-',
        regexp_replace(substr(split(concat(date_add(predict_date_day,1 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end),
	   '~',date_add(predict_date_day,7 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end)), '~') [1], 6, 5),'-','.')
        )                                                                                   as week_ofyear_period_short     --年周段
	 --,dense_rank()over(partition by year(predict_date_day),month(predict_date_day) order by weekofyear(predict_date_day) asc)       as week_ofmonth        -- 月周
	 --,concat(month(predict_date_day),'月第',dense_rank()over(partition by year(predict_date_day),month(predict_date_day) order by weekofyear(predict_date_day) asc),'周') as week_ofmonth_name   -- 月周名称  
	   ,month(predict_date_day)                                                                           as month_code          -- 月(mm)
	   ,substr(regexp_replace(predict_date_day,'-','') ,1,6)                                              as yearmonth_id        -- 年月(yyyymm)
	   ,quarter(predict_date_day)                                                                         as quarter_id          -- 季度
       ,concat(year(predict_date_day),'Q',quarter(predict_date_day) )                                                   as quarter_code        -- 年季度
       ,year(predict_date_day)                                                                            as year_id             -- 年
       ,case when MONTH(predict_date_day) > 6 then 'f_half' else 'l_half' end                             as half_code           -- 半年
	   ,regexp_replace(date_add(predict_date_day,-1),'-','')                                             as last_date_id        -- 昨天
	   ,regexp_replace(date_add(predict_date_day,0 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end),'-','')      as last_sunday          -- 上个周日 1
	   ,regexp_replace(date_add(predict_date_day,0 - case when dayofweek(predict_date_day) = 1 then 14 else dayofweek(predict_date_day) + 6 end),'-','')     as L_last_sunday        -- 上上个周日 2 
       ,regexp_replace(date_add(predict_date_day,0 - case when dayofweek(predict_date_day) = 1 then 21 else dayofweek(predict_date_day) + 13 end),'-','')    as LL_last_sunday       -- 上上上个周日 3 
       ,regexp_replace(date_add(predict_date_day,0 - case when dayofweek(predict_date_day) = 1 then 28 else dayofweek(predict_date_day) + 20 end),'-','')    as LLL_last_sunday      -- 上上上上个周日 4
	   ,regexp_replace(date_add(trunc(predict_date_day,'MM'),-1),'-','')                                                         as lastday_of_lastmonth -- 上个月末
	   ,regexp_replace(date_add(trunc(predict_date_day,'YY'),-1),'-','')                                                         as lastday_oflastyear   -- 上个年末  
	   ,regexp_replace(date_add(predict_date_day,1 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end),'-','')      as firstday_ofweek      -- 当周第一天
	   ,regexp_replace(date_add(predict_date_day,7 - case when dayofweek(predict_date_day) = 1 then 7 else dayofweek(predict_date_day) - 1 end),'-','')      as lastday_ofweek       -- 当周最后一天  
	   ,trunc(predict_date_day,'MM')															            as firstday_ofmonth    -- 当月第一天
	   ,last_day(predict_date_day)                                                                        as lastday_ofmonth     -- 当月最后一天
	   ,to_date(concat(year(predict_date_day),'-',lpad(ceil(month(predict_date_day)/3) * 3-2,2,0),'-01'))               as firstday_ofseason   -- 当季第一天
	   ,last_day(to_date(concat(year(predict_date_day),'-',lpad(ceil(month(predict_date_day)/3) * 3,2,0),'-01')))       as lastday_ofseason    -- 当季最后一天
	   ,trunc(predict_date_day,'YY')                                                                      as firstday_ofyear     -- 当年第一天
	   ,last_day(add_months(trunc(predict_date_day,'YY'),11))                                             as lastday_ofyear      -- 当年最后一天
   from(
         select
           date_add("2020-01-14", t.pos) as predict_date_day
         from(
             select
               posexplode(
                 split(
                   repeat(
                     "m",
                     --datediff(
                     --  date_sub(from_unixtime(unix_timestamp('21001231', 'yyyyMMdd')),-1),
                    --            from_unixtime(unix_timestamp('19000101', 'yyyyMMdd'),'yyyy-MM-dd')
                    -- )
					datediff("2030-01-13", "2020-01-14")
                   ),"m"
                 )
               )
           ) t
      ) T 
      )
	  
	  
	   --更新月周
      --[dim][房产业务][时间维表]dim_es_date_c_d 	   
	  -- insert overwrite  table  dim_es_date_c_d
       select 	   
	          M.date_id                                          --日(yyyymmdd)           
	         ,M.date_code                                        --日(yyyy-mm-dd)
	         ,M.date_name                                        --日(yyyy年mm月dd日)
             ,M.week_day_id                                      --周ID（周几）
	         ,M.week_day_name                                    --星期(星期几)
             ,N.week_ofyearid                                    --年周的id			 
	         ,M.week_ofyear                                      --年周               
	         ,N.week_ofyear_name                                 --年周名称 
	         ,M.week_ofyear_period                               --年周段     
	         ,M.week_ofyear_period_short                         --年周段简称 
			 ,N.month_code as  month_week_code                   --周所在月 
        --     ,dense_rank()over(partition by M.year_id,N.month_code order by M.week_ofyear asc)  as week_ofmonth                                       --月周
        	 ,dense_rank()over(partition by N.year_id,N.month_code order by N.week_ofyear asc)  as week_ofmonth                                       --月周
			 ,concat(N.month_code,'月第',dense_rank()over(partition by N.year_id,N.month_code order by N.week_ofyear asc),'周') as week_ofmonth_name  --月周名称
	         ,M.month_code                                       --月(mm)         
	         ,M.yearmonth_id                                     --年月(yyyymm)            
	         ,M.quarter_id                                       --季度              
	         ,M.quarter_code                                     --年季度            
	         ,M.year_id                                          --年                  
	         ,M.half_code                                        --半年
             ,M.last_date_id                                     --昨天
			 ,M.last_sunday                                      --上个周日
			 ,M.L_last_sunday                                    --上上个周日
             ,M.LL_last_sunday                                   --上上个周日
             ,M.LLL_last_sunday                                  --上上上个周日
			 ,M.lastday_of_lastmonth                             --上个月末
			 ,M.lastday_oflastyear                               --上个年末 	 
	         ,M.firstday_ofweek                                  --当周第一天          
	         ,M.lastday_ofweek                                   --当周最后一天           
	         ,M.firstday_ofmonth                                 --当月第一天          
	         ,M.lastday_ofmonth                                  --当月最后一天       
	         ,M.firstday_ofseason                                --当季第一天        
	         ,M.lastday_ofseason                                 --当季最后一天         
	         ,M.firstday_ofyear                                  --当年第一天          
	         ,M.lastday_ofyear                                   --当年最后一天 
             ,from_utc_timestamp(current_timestamp(),"GMT+8")  as last_update_time				 
        from  tmp_es_date_00 M
        left join 
		(
	    select  year_id
	           ,month_code        
	           ,week_ofyear
          	   ,week_ofyear_period
               ,week_ofyearid
               ,week_ofyear_name
	           ,count(date_id) day_cnt            
	    from  tmp_es_date_00
	    group by year_id
	            ,month_code        
	            ,week_ofyear
          		,week_ofyear_period
                ,week_ofyearid
                ,week_ofyear_name
	    having 	day_cnt >= 4
		) N                           -- 统计周所在月
	--	on M.year_id = N.year_id and M.week_ofyear = N.week_ofyear 
    	on M.week_ofyear_period = N.week_ofyear_period
    --    where M.date_id <= 	'${yyyyMMdd}'
		where M.date_code <=	date_add(from_unixtime(unix_timestamp('${yyyyMMdd}', 'yyyyMMdd'),'yyyy-MM-dd'),1)
	  ;
	  
	  
	  
	  --
	  CREATE TABLE `dim_es_date_c_d`(
`day_code` string COMMENT '',
`day_long_desc` string COMMENT '',
`day_medium_desc` string COMMENT '',
`day_short_desc` string COMMENT '',
`week_code` string COMMENT '',
`week_long_desc` string COMMENT '',
`week_medium_desc` string COMMENT '',
`week_short_desc` string COMMENT '',
`week_day` string COMMENT '',
`month_code` string COMMENT '',
`month_long_desc` string COMMENT '',
`month_medium_desc` string COMMENT '',
`month_short_desc` string COMMENT '',
`quarter_code` string COMMENT '',
`quarter_long_desc` string COMMENT '',
`quarter_medium_desc` string COMMENT '',
`quarter_short_desc` string COMMENT '',
`half_year_code` string COMMENT '',
`half_long_desc` string COMMENT '',
`half_medium_desc` string COMMENT '',
`half_short_desc` string COMMENT '',
`year_code` string COMMENT '',
`year_long_desc` string COMMENT '',
`year_medium_desc` string COMMENT '',
`year_short_desc` string COMMENT '',
`last_update_time` string COMMENT '')
COMMENT '时间维表'
日期公共维表设计及数据填充SQL
xiexiangyu109的博客
01-06 639
日期公共维表
使用Sql Server的存储过程生成日期维度表
haiMary的博客
07-22 1395
使用Sql Server的存储过程生成日期维度表 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。 新的改变 我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客： 全新的界面设计 ，将会带来全新的写作体验； 在创作中心设置你喜爱的代码高亮样式，Markdown 将代码片显示选择的高亮样式 进行
oracle 日期维表 原始版本 带注解
10-19 1210
create table d_time_date as SELECT to_number(TO_CHAR(TRUNC(sd + rn), 'YYYYMMDD')) day_id, TRUNC(sd + rn) day_date, to_char(sd + rn, 'YYYY-MM-DD') DAY_CN_DESC, to_char(sd + rn, 'YY
SQL 与 Python：日期维度表创建的不同选择
最新发布
dudly的博客
05-06 2067
在数据仓库和数据分析领域，日期维度表如同精准的时间标尺，为数据赋予时间维度的深度解析能力。无论是分析销售数据的周期性波动，还是研究用户行为的时间规律，日期维度表都扮演着至关重要的角色。本文将深入探讨如何使用 SQL 和 Python 两种工具创建日期维度表，分析它们各自的优势与适用场景，助力数据从业者根据实际需求灵活选择合适的方法。
Sqlserver存储过程生成日期维度
weixin_34100227的博客
12-18 507
话不多说，之前已经有一篇日志是利用oracle的存储过程生成日期维度表，接下来我们就用sqlserver来实现这个操作，如下面的步骤所示 1:创建日期维度表（Dim_time）   USE [DW]GO /****** Object: Table [dbo].[Dim_time] Script Date: 12/19/2015 15:29:26 ******/SET ANSI_...
教你用SQL生成一张日期维度表
SQL数据库开发
02-09 4228
点击关注“SQL数据库开发”，设为“置顶或星标”，第一时间送达干货问题描述输入想要生成日历的年份，通过调用存储过程，即可生成该年的全部日历。创建表结构我们根据常见的日历表来创建一个含有年，...
SQL建立日期维度表
weixin_50646402的博客
08-05 605
工作中一些项目，特别是系统或看板开发过程中，因为要保证日期在特定区间可能需要建立一张日期维度表，每天取过去某个日期到当天的日期。
在SQL中创建时间维度表
LYY1045691954的博客
12-11 2440
如标题，用sql语句生成一张从2020年1月1日至今日的日期表，这是考察在SQL中创建时间维度表。 这里采用循环的方式： 先创建一个表格，只有日期字段，字段类型为date create table ab_date(date date); DELIMITER $$ CREATE PROCEDURE create_date () BEGIN declare indexdate Date; set indexdate=date(“2020-01-01”); while indexdate <= curdat
PL_SQL生成日期维度表
04-24
### PL/SQL 生成日期维度表 #### 一、创建日期维度表 在数据库系统中，日期维度表是数据仓库中的一个关键组成部分，用于存储与日期相关的各种属性，以支持复杂的查询和分析需求。以下是对如何使用 PL/SQL 创建一个...
ORACLE的SQL练习---6. 利用oracle日期函数制作日期维度表
yuyan64的博客
11-26 664
oracle常用的日期函数： sysdate 【功能】：返回当前日期。 【参数】：没有参数，没有括号 【返回】：日期 【示例】select sysdate from dual; 返回：2020/11/26 10:04:37 add_months(d1,n1) 【功能】：返回在日期d1基础上再加n1个月后新的日期。 【参数】：d1，日期型，n1数字型 【返回】：日期 【示例】select sysdate,add_months(sysdate,3) hz from dual; 返回： 2020/11/2
SQL server生成日期维度表（chatGPT）
buzai5的博客
09-20 534
替换为你的日期维度表的实际表名。然后，执行此存储过程来生成2024年、2025年和2026年的日期数据。
生成日期维表数据库代码
11-26
Oracle、sqlserver、db2数据库自动建日期维度表代码，一般用于BI期间模型
数据分析中的时间维度表生成
10-09
Oracle数据库中的时间维度生成 通过执行包中的procedure和function，然后根据设定的参数，自动生成从给定参数时间到当前时间的时间维度数据 ROW_WID DAY_DT WEEK MONTH QUARTER HALF_YEAR YEAR WEEK_DAY MONTH_DAY QUARTER_DAY HALF_YEAR_DAY YEAR_DAY MONTH_WEEK QUARTER_WEEK HALF_YEAR_WEEK YEAR_WEEK QUARTER_MONTH HALF_YEAR_MONTH YEAR_MONTH HALF_YEAR_QUARTER YEAR_QUARTER YEAR_HALF_YEAR DAY_AGO_WID CREATED_BY_WID CHANGED_BY_WID CREATED_ON_DT CHANGED_ON_DT DELETE_FLG W_INSERT_DT W_UPDATE_DT DATASOURCE_NUM_ID ETL_PROC_WID INTEGRATION_ID 20000101 1/1/2000 2000/01 2000/01 2000/Q1 2000/Half1 2000 7 1 1 1 1 1 1 1 1 1 1 1 1 1 1 19991231 0 0 8/31/2012 15:08 8/31/2012 15:08 N 8/31/2012 15:08 8/31/2012 15:08 1000 0 20000101
mysql 时间维度表_Mysql时间维度表
weixin_36131257的博客
01-19 311
1、建一张表test_date用来日期的自增长，里面只放一列 ID，写一个存储，插入顺序数字BEGIN#Routinebodygoeshere...declareidint;setid=1;whileid<=6000doinsertintotest_datevalues(id);setid=id+1;endwhile;END2. 建立时间维度表，插入记录SET@d...
数据仓库 - hive - 日期维表，日，周，月份，年份，节假日，工作日
DolphinF的博客
07-05 1263
数仓中常用的日期维表，每年生成一次，供大家参考。
时间维度表的生成
JAVA_LuZiMaKei的博客
05-18 899
前言： 今天和小伙伴们分享下时间维度表的应用，先说个简单的业务场景，有一张记录的用户注册信息，然后想在后台管理系统中开发一个可以看到每天注册用户数量的图表统计功能，那么你会怎么处理呢？ 正文： 一、模拟场景 1.我们先看下用户表的数据 2.然后根据这张表开始查询每天的用户注册数据量 select count(1) as registerNum, gmt_date from user GROUP BY DATE_FORMAT(gmt_date,’%Y-%m-%d’) 查询结果： 我们会发现一个问题 ，虽然
mysql日期维表sql文件_《MySQL必知必会》笔记（SQL练习+建表语句）
weixin_30068505的博客
01-28 1408
站在巨人的肩上Standing On Shoulders Of Giants部分转自：https://www.jianshu.com/p/294502893128https://blog.youkuaiyun.com/qq_33704186/article/details/81909168大概目录：1.SQL语句学习2.本书练习SQL案例3.类似Oracle中的经典SQL练习案例《MySQL必知必会》学习笔记...
sql构造时间列表
weixin_37530941的博客
05-06 1128
1、获取某时间开始24小时每小时列表数据 SELECT TO_DATE('2021-05-06','YYYY-MM-DD HH24:MI:SS')-(LEVEL-1.0)/24 AS DAT FROM DUAL CONNECT BY LEVEL <> 24 2、获取某时间开始，往后推若干天时间列表 SELECT level,TO_DATE('2021-05-06','YYYY-MM-DD')- LEVEL + 1 AS DAT FROM DUAL CON...
数据仓库之日期维度表构建
weixin_56046673的博客
08-07 4846
导读 大家好，今日立秋，立秋是阳气渐收、阴气渐长，由阳盛逐渐转变为阴盛的转折。我们上一篇文章 数据仓库之维度表 介绍了数据仓库中维度表，本篇文章在此基础上介绍和构建维度表中的日期维度。Kimball 维度建模中的设计原则提到确保每一个事实表都具有关联的日期维度。数据仓库项目中，日期维度是最常见的，不可缺少的维度表之一。 数据仓库之维度表 本文中 ETL 转换使用 Kettle 9.3 版本实现，关注公众号回复关键字 dim_date 获取。 为什么需要日期维度表 Kimball 认为数据仓库中最重要的三
Blink SQL维表join
05-05
Blink SQL 支持在维表（dimension table）和事实表（fact table）之间进行 join 操作，以便在查询中使用维表的属性。维表通常包含静态数据，例如产品信息、客户信息等，而事实表则包含动态数据，例如销售记录、订单记录等。 维表 join 的语法如下： ```sql SELECT fact_table.*, dim_table.* FROM fact_table JOIN dim_table ON fact_table.dimension_column = dim_table.dimension_column ``` 其中，`fact_table` 是事实表，`dim_table` 是维表，`dimension_column` 是两个表中共同的维度列。在 join 操作中，通过将维度列作为 join 条件，将事实表中的每一行与维表中匹配的行进行组合。这样，查询结果将包含事实表和维表的所有列。 举个例子，假设有一个事实表 `sales_fact` 包含销售记录，和一个维表 `product_dim` 包含产品信息。如果我们想要查询某个时间范围内的销售记录，并且需要显示每个产品的名称和价格等信息，可以使用如下的 SQL 语句： ```sql SELECT sales_fact.*, product_dim.product_name, product_dim.price FROM sales_fact JOIN product_dim ON sales_fact.product_id = product_dim.product_id WHERE sales_fact.order_date BETWEEN '2022-01-01' AND '2022-01-31' ``` 在这个查询中，我们通过 `JOIN` 操作将事实表 `sales_fact` 和维表 `product_dim` 进行了组合，并通过 `ON` 子句指定了维度列 `product_id` 作为 join 条件。最终的查询结果将包含销售记录和产品信息的所有列，并限制了销售日期在 2022 年 1 月份的记录。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值