如何生成字段等于“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"等字段的日期表；并且行等于从 2010-01-01 到 current_date 的日期如下:
Dayofweek Dayofmonth Dayofyear Weekofmonth Weekofyear 假期2010-01-01 6 1 1 1 1 是2010-01-02 7 2 2 1 1 否2010-01-03 1 3 3 1 1 否......2019-03-31 1 31 90 6 14 否<块引用>
附:DayofWeek = 一周中的第几天，Sun = 1，Sat = 7Dayofmonth = 月份的第几天
假期是一个标志，用来区分记录是否是公共假期所以我需要做的第一步可能是创建从 2010-01-01 到 current_date 的记录，我想知道 hive 和 mssql 中的 while 循环会做什么?然后我准备好专栏最后将它们组合起来.
我试过了
"声明@startdate 日期声明@enddate 日期设置@startdate = '2010-01-01'设置@end_date = current_date而@start_date <=end_date开始DATEADD(DAY,1,@startdate)结尾"声明@startdate 日期声明@enddate 日期设置@startdate = '2010-01-01'设置@end_date = current_date而@start_date <=end_date开始DATEADD(DAY,1,@startdate)结尾Dayofweek Dayofmonth Dayofyear Weekofmonth Weekofyear 假期2010-01-01 6 1 1 1 1 是2010-01-02 7 2 2 1 1 否2010-01-03 1 3 3 1 1 否......2019-03-31 1 31 90 6 14 否Hive 解决方案:
设置 hivevar:start_date=2010-01-01;--替换为您的 start_date设置 hivevar:end_date=current_date;--替换为您的 end_date日期范围为(--此查询生成日期范围选择 date_add ('${hivevar:start_date}',s.i) 作为 dtfrom (selectposexplode(split(space(datediff(${hivevar:end_date},'${hivevar:start_date}')),'')) as (i,x)) s),假期为 (选择堆栈(5，--添加更多'01-01', '新年','01-21'，'马丁路德金纪念日'，'02-18', '总统日','05-27', '阵亡将士纪念日','07-04', '独立日') 作为 ( mtdt,holiday_name))选择 d.dt 作为日期，date_format(current_date,'u') 作为星期几，day(dt) 作为 dayofmonth，date_format(current_date,'D') as dayofyear,date_format(current_date,'W') as weekofmonth,weekofyear(dt) 作为 weekofyear，如果 h.mtdt 不为空，则 'Y' 否则 'N' 以假日结束，h.holiday_name从日期范围 d在 substr(d.dt,6)= h.mtdt 上左加入假日 h;添加更多假期.
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
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
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.
I have tried
"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
Solution for 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
;
Add more holidays.
这篇关于如何生成字段等于“Dayofweek"、“weekofyear"的日期表等；并且行等于从 2010-01-01 到 current_date 的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
