常用日期字典生成代码

日常在数据处理过程中，经常会用到日期字典，下述代码将常用日期字典字段信息生成，纯sql版，方便

select 
    rn as id                -- 唯一主键                
   ,iso_date as iso_date          -- 日期 yyyy-MM-dd格式     
   ,dayofweek as dayofweek         -- 当天在本周的序号 1-7        
   ,dayofmonth as dayofmonth        -- 当天在本月的序号 1-31       
   ,dayofyear as dayofyear         -- 当天在本年的序号 1-366      
   ,weekofmonth as weekofmonth       -- 本周在本月序号 1-4         
   ,weekofyear as weekofyear        -- 本周在本年序号 1-52        
   ,iso_week_start as iso_week_start    -- 本周周一 yyyy-MM-dd格式   
   ,iso_week_end as iso_week_end      -- 本周周末 yyyy-MM-dd格式   
   ,monthofyear as monthofyear       -- 本月在本年的序号 1-12       
   ,iso_month_start as iso_month_start   -- 本月第一天 yyyy-MM-dd格式  
   ,iso_month_end as iso_month_end     -- 本月最后一在 yyyy-MM-dd格式 
   ,quarterofyear as quarterofyear     -- 季度在本年序号 1-4         
   ,concat(year,'-01-01') as iso_year_start    -- 本年第一天 yyyy-MM-dd格式  
   ,concat(year,'-12-31') as iso_year_end      -- 本年最后一天 yyyy-MM-dd格式 
   ,year as year              -- 年                   
   ,case when substr(iso_week_start,1,4)!=substr(iso_week_end,1,4) and weekofyear=1 then concat(substr(iso_week_end,1,4),'-',lpad(weekofyear,2,'0')) else concat(substr(iso_week_start,1,4),'-',lpad(weekofyear,2,'0')) end as iso_week          -- 周 yyyy-NN           
   ,concat(year,'-',lpad(monthofyear,2,'0')) as iso_month         -- 月份 yyyy-MM格式        
   ,concat(year,'-0',quarterofyear) as iso_quarter       -- 季度 yyyy-0N格式        
   ,concat(year,'-Q',quarterofyear) as iso_q_quarter     -- 季度 yyyy-QN格式        
   ,min(iso_date) over(partition by year,quarterofyear) as iso_quarter_start -- 本季度第一天 yyyy-MM-dd格式 
   ,max(iso_date) over(partition by year,quarterofyear) as iso_quarter_end   -- 本季度最后一在 yyyy-MM-dd格式
   ,rank() over(order by rn) as date_id           -- 日期主键                
   ,rank() over(order by case when substr(iso_week_start,1,4)!=substr(iso_week_end,1,4) and weekofyear=1 then concat(substr(iso_week_end,1,4),'-',lpad(weekofyear,2,'0')) else concat(substr(iso_week_start,1,4),'-',lpad(weekofyear,2,'0')) end ) as week_id           -- 周主键                 
   ,rank() over(order by concat(year,'-',lpad(monthofyear,2,'0'))) as month_id          -- 月主键                 
   ,rank() over(order by concat(year,'-0',quarterofyear)) as quarter_id        -- 季度主键                
   ,rank() over(order by year ) as year_id           -- 年主键                 
   ,regexp_replace(iso_date ,'-','') as st_date           -- 日期短格式yyyyMMdd       
   ,regexp_replace(concat(year,'-',lpad(monthofyear,2,'0')) ,'-','') as st_month          -- 月份短格式yyyyMM         
   ,regexp_replace(iso_week_start ,'-','') as st_week_start     -- 本周周一 yyyyMMdd格式     
   ,regexp_replace(iso_week_end ,'-','') as st_week_end       -- 本周周末 yyyyMMdd格式     
   ,regexp_replace(iso_month_start ,'-','') as st_month_start    -- 本月第一天 yyyyMMdd格式    
   ,regexp_replace(iso_month_end ,'-','') as st_month_end      -- 本月最后一在 yyyyMMdd格式   
   ,regexp_replace(concat(year,'-01-01') ,'-','') as st_year_start     -- 本年第一天 yyyyMMdd格式    
   ,regexp_replace(concat(year,'-12-31') ,'-','') as st_year_end       -- 本年最后一天 yyyyMMdd格式   
   ,regexp_replace(min(iso_date) over(partition by year,quarterofyear) ,'-','') as st_quarter_start  -- 本季度第一天 yyyyMMdd格式   
   ,regexp_replace(max(iso_date) over(partition by year,quarterofyear) ,'-','') as st_quarter_end    -- 本季度最后一在 yyyyMMdd格式  
from 
(
    select 
        rn 
       ,iso_date 
       ,date_format(iso_date,'u') dayofweek
       ,date_format(iso_date,'d') dayofmonth
       ,date_format(iso_date,'D') dayofyear
       ,date_format(iso_date,'W') weekofmonth
       ,date_format(iso_date,'w') weekofyear
       ,date_sub(iso_date,cast(date_format(iso_date,'u') as smallint)-1) iso_week_start
       ,date_add(iso_date,7-cast(date_format(iso_date,'u') as smallint)) iso_week_end
       ,date_format(iso_date,'M') monthofyear 
       ,date_format(iso_date,'yyyy-MM-01') iso_month_start
       ,last_day(iso_date) iso_month_end
       ,case when date_format(iso_date,'M') in ('1','2','3') then '1'
             when date_format(iso_date,'M') in ('4','5','6') then '2'
             when date_format(iso_date,'M') in ('7','8','9') then '3'
             when date_format(iso_date,'M') in ('10','11','11') then '4'
        end quarterofyear
       ,date_format(iso_date,'yyyy') year
    from 
    (
        select 
            rn,date_add('1970-01-01',rn) iso_date 
        from 
        (
            select
                row_number() over(order by user_id) rn 
            from dwa.dwa_pty_user_all_info_da 
            where dt='20180906'
        ) tt 
    ) tt 
) tt ;