我如何生成带有等于“ Dayofweek”，“ weekofyear”字段的日期表？等等;并且行等于从2010-01-01到current_date的日期 [英] How can i generate a Date table with fields equals to "Dayofweek", "weekofyear" etc ;and rows equals to date from 2010-01-01 till current_date

问题描述

我如何生成包含 Dayofweek， weekofyear等字段的Date表，并且行等于从2010-01-01到current_date
的日期，如下所示：

How can i generate a Date table with fields like "Dayofweek", "weekofyear" etc ;and rows equals to date from 2010-01-01 till current_date like below:

             Dayofweek   Dayofmonth  Dayofyear Weekofmonth Weekofyear Holiday
2010-01-01       6           1            1          1          1        Y
2010-01-02       7           2            2          1          1        N
2010-01-03       1           3            3          1          1        N
.....
2019-03-31       1           31           90         6         14        N

PS DayofWeek =星期几，Sun = 1，星期六= 7
Dayofmonth =月的星期几

P.S. DayofWeek = day num of the week, Sun = 1, Sat = 7 Dayofmonth = day num of the month

 Holiday is a flag to distinguish whether the records is a public holiday

所以我需要做的第一步可能是创建从2010-01-01到current_date的记录，我想知道蜂巢和mssql中的while循环会做什么吗？
然后我准备好列
最后将它们组合起来。

So first step i need to do may be create records from 2010-01-01 till current_date, i wonder while loop in hive and mssql will do? Then I have the column ready Finally combine them.

我已经尝试过

"Declare @startdate date
Declare @enddate date

set @startdate = '2010-01-01'
set @end_date = current_date

while @ start_date <=end_date
BEGIN
    DATEADD(DAY,1,@startdate)
END

"Declare @startdate date
Declare @enddate date

set @startdate = '2010-01-01'
set @end_date = current_date

while @ start_date <=end_date
BEGIN
    DATEADD(DAY,1,@startdate)
END

             Dayofweek   Dayofmonth  Dayofyear Weekofmonth Weekofyear Holiday
2010-01-01       6           1            1          1          1        Y
2010-01-02       7           2            2          1          1        N
2010-01-03       1           3            3          1          1        N
.....
2019-03-31       1           31           90         6         14        N

推荐答案

Hive解决方案：

set hivevar:start_date=2010-01-01; --replace with your start_date
set hivevar:end_date=current_date; --replace with your end_date

with date_range as 
(--this query generates date range
select date_add ('${hivevar:start_date}',s.i) as dt 
  from ( select posexplode(split(space(datediff(${hivevar:end_date},'${hivevar:start_date}')),' ')) as (i,x) ) s
),

holiday as (
select stack(5, --add more
             '01-01', 'New Year',
             '01-21', 'Martin Luther King Day',
             '02-18', 'Presidents Day',
             '05-27', 'Memorial Day',
             '07-04', 'Independence Day'
        ) as ( mtdt,holiday_name)
) 

select d.dt                                 as date,
       date_format(current_date,'u')        as dayofweek,
       day(dt)                              as dayofmonth,
       date_format(current_date,'D')        as dayofyear,
       date_format(current_date,'W')        as weekofmonth,
       weekofyear(dt)                       as weekofyear, 
       case when h.mtdt is not null then 'Y' else 'N' end as Holiday,
       h.holiday_name
  from date_range d 
       left join holiday h on substr(d.dt,6)= h.mtdt
;

添加更多假期。

这篇关于我如何生成带有等于“ Dayofweek”，“ weekofyear”字段的日期表？等等;并且行等于从2010-01-01到current_date的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！