01-01到当前日期的日期

5rgfhyps  于 2021-06-27  发布在  Hive
关注(0)|答案(2)|浏览(781)

如何生成包含“dayofweek”、“weekofyear”等字段的日期表;行等于从2010-01-01到当前日期的日期,如下所示:

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=一周的天数,sun=1,sat=7 dayofmonth=一个月的天数

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

所以我需要做的第一步可能是创建从2010-01-01到当前日期的记录,我想知道hive和mssql中的循环会做什么?然后我准备好了专栏,最后把它们结合起来。
我试过了

"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
k5ifujac

k5ifujac1#

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
;

增加更多假期。

oxalkeyp

oxalkeyp2#

您可以使用以下解决方案获得日历表:

SELECT calendar.*, 
    DATEPART(dw, calendar.date_value) AS DayOfWeek, 
    DATEPART(dd, calendar.date_value) AS DayOfMonth, 
    DATEPART(dy, calendar.date_value) AS DayOfYear,
    DATEPART(wk, calendar.date_value) AS WeekOfYear, 
    CASE WHEN holidays.date_value IS NULL THEN 'N' ELSE 'J' END AS Holiday 
FROM (
  SELECT DATEADD(DAY, t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0, '1970-01-01') AS date_value FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) calendar INNER JOIN (
  SELECT '2010-01-01' AS date_value UNION SELECT '2010-01-06'
) holidays ON calendar.date_value = holidays.date_value
WHERE calendar.date_value BETWEEN CAST('2010-01-01' AS DATE) AND GETDATE()
ORDER BY calendar.date_value

基本日历表是基于stackoverflow的解决方案。与 INNER JOIN 你可以增加一些假期。
DATEPART 你可以得到一些关于日期的信息。

相关问题