日期维度表

1 python date+中国节假日

1.1 date

# 字段	
# date	日期
# month	月份
# year	年
# year_month	年月
# quarter	季度
# week	一年第几周
# weekday	一周第几天
# dt_of_month	一个月第几天
# dt_of_year	一年第几天
# is_last_day_of_month	是否一个月最后一天
# is_workday	是否工作日
# update_date	数据更新日期


import datetime

start_date = datetime.datetime(2018,1,1)
end_date = datetime.datetime(2031,1,1)
# start_date = datetime.datetime(2019,1,1)
# end_date = datetime.datetime(2019,2,1)
day = start_date

while(day < end_date):
    dt = day.strftime("%Y-%m-%d")
    month=str(day.month)
    year=str(day.year)
    year_month=day.strftime("%Y-%m")
    quarter=''
    if month in('1','2','3'):
        quarter='1'
    elif month in ('4','5','6'):
        quarter='2'
    elif month in ('7','8','9'):
        quarter='3'
    elif month in ('10','11','12'):
        quarter='4'
    week=str(day.isocalendar()[1])
    weekday=str(day.isocalendar()[2])
    dt_of_month=str(day.day)
    dt_of_year=str(day.timetuple().tm_yday)
    is_workday=str(day.isoweekday())


    # print(quarter)

    print('('+str(dt)+','+month+','+year+','+year_month+','+quarter+','+week+','
    +weekday+','+dt_of_month+','+dt_of_year+','+is_workday+'),')

    # lunar_dt = get_lunar_dt(dt)
    # quarter_of_year = quarter(day.month)
    # week_of_year = get_week_of_year(day)
    # dt_of_week = get_day_of_week(day)
    # dt_of_month = day.day
    # dt_of_year = get_day_of_year(day)
    # is_last_day_of_month = last_day_of_month(calendar_year,month_of_year,dt_of_month)
    # is_weekend = get_is_weekend(day)
    # is_holiday = get_is_holiday(day)
    # holiday_type = get_holiday_type(day,is_weekend)
    # holiday_cnt = get_holiday_cnt(day)
    # data_raw = [dt,lunar_dt,calendar_year,quarter_of_year,month_of_year,week_of_year,dt_of_week,dt_of_month,dt_of_year,is_last_day_of_month,is_weekend,is_holiday,holiday_type,holiday_cnt]
    # print data_raw
    # write_csv(data_raw)
    # # print '日期：'+dt+ '年份'+str(calendar_year)+'Q'+str(quarter_of_year)+'月份'+str(month_of_year)+'周'+str(week_of_year)+'星期'+str(dt_of_week)+' 月的第'+str(dt_of_month)+' 年的第'+str(dt_of_year)+str(is_weekend)

    day = day + datetime.timedelta(days=1)

1.2 holiday

import datetime
import chinese_calendar


start_time=datetime.date(2019,1,1)
end_time=datetime.date(2024,12,31)

holiday=chinese_calendar.get_holidays(start_time,end_time)

for i in holiday:
    on_holiday, holiday_name=chinese_calendar.get_holiday_detail(i)
    w=i.isoweekday()
    if holiday_name==None:
        if w == 6:
            holiday_name='周六'
        elif w==7:
            holiday_name='周天'
    elif holiday_name=="New Year's Day":
        holiday_name='元旦'
    elif holiday_name=="Spring Festival":
        holiday_name='春节'
    elif holiday_name=="Tomb-sweeping Day":
        holiday_name='清明节'
    elif holiday_name=="Labour Day":
        holiday_name='劳动节'
    elif holiday_name=="Dragon Boat Festival":
        holiday_name='端午节'
    elif holiday_name=="National Day":
        holiday_name='国庆节'
    elif holiday_name=="Mid-autumn Festival":
        holiday_name='中秋节'  
    print("('"+str(i.year)+'-'+str(i.month)+'-'+str(i.day)+"','"+str(holiday_name)+"',"+str(w)+'),')

('2019-1-1','元旦',2),

1.3 workday

import datetime
import chinese_calendar


start_time=datetime.date(2019,1,1)
end_time=datetime.date(2024,12,31)

workday=chinese_calendar.get_workdays(start_time,end_time)

for i in workday:
    w=i.isoweekday()
    print('('+str(i.year)+'-'+str(i.month)+'-'+str(i.day)+",工作日,"+str(w)+','+
          str(i.year)+','+str(i.month)+','+str(i.day)+','++','++','++'),')

(2019-1-2,工作日,3),

2 处理

-- 节假日表
CREATE TABLE atlasdb.dim_holidays (
	holiday varchar(10) NULL,
	"name" varchar(50) NULL,
	wage int4 NULL,
	"date" varchar(50) NULL,
	"after" varchar(10) NULL,
	"target" varchar(50) NULL
);

-- 日期维度表
CREATE TABLE fddw.dim_date (
	"date" oradate NULL,
	"year" float8 NULL,
	"month" float8 NULL,
	month_name text NULL,
	"day" float8 NULL,
	day_of_year float8 NULL,
	weekday_name text NULL,
	calendar_week float8 NULL,
	char_date text NULL,
	quarter text NULL,
	year_quarter text NULL,
	year_month text NULL,
	year_calendar_week text NULL,
	weekend text NULL,
	american_holiday text NULL,
	austrian_holiday text NULL,
	canadian_holiday text NULL,
	chinese_holiday text NULL,
	remark varchar(50) NULL,
	wage int4 NULL,
	is_workday text NULL,
	cw_start oradate NULL,
	cw_end oradate NULL,
	month_start oradate NULL,
	month_end oradate NULL

)
WITH (
	orientation=row,
	compression=no,
	fillfactor=80
);

COMMENT ON COLUMN dim_date.date is '日期';
COMMENT ON COLUMN dim_date.year is '月';
COMMENT ON COLUMN dim_date.month is '年';
COMMENT ON COLUMN dim_date.month_name is '月';
COMMENT ON COLUMN dim_date.day is '日';
COMMENT ON COLUMN dim_date.day_of_year is '年日';
COMMENT ON COLUMN dim_date.weekday_name is '星期一~星期日';
COMMENT ON COLUMN dim_date.calendar_week is '日期周几';
COMMENT ON COLUMN dim_date.char_date is '文本型日期';
COMMENT ON COLUMN dim_date.quarter is '季度';
COMMENT ON COLUMN dim_date.year_quarter is '年季度';
COMMENT ON COLUMN dim_date.year_month is '年月';
COMMENT ON COLUMN dim_date.year_calendar_week is '年周数';
COMMENT ON COLUMN dim_date.weekend is '工作日还是周末';
COMMENT ON COLUMN dim_date.american_holiday is '美国节日';
COMMENT ON COLUMN dim_date.austrian_holiday is '澳大利亚节日';
COMMENT ON COLUMN dim_date.canadian_holiday is '加拿大节日';
COMMENT ON COLUMN dim_date.chinese_holiday is '中国节日';
COMMENT ON COLUMN dim_date.remark is '中国节日备注';
COMMENT ON COLUMN dim_date.wage is '薪资倍数';
COMMENT ON COLUMN dim_date.is_workday is '是否工作日';
COMMENT ON COLUMN dim_date.cw_start is '当周开始日期';
COMMENT ON COLUMN dim_date.cw_end is '当周结束日期';
COMMENT ON COLUMN dim_date.month_start is '月开始日期';
COMMENT ON COLUMN dim_date.month_end is '月结束日期';



INSERT INTO FDDW.dim_date (
	SELECT
		datum as date,
		extract(year from datum) AS year,
		extract(month from datum) AS month,
		-- Localized month name
		-- to_char(datum, 'TMMonth') AS month_name,
		to_char(datum, 'mm"月"') AS month_name,
		extract(day from datum) AS day,
		extract(doy from datum) AS day_of_year,
		-- Localized weekday
		to_char(datum, 'TMDay') AS weekday_name,
		-- ISO calendar week
		extract(week from datum) AS calendar_week,
		to_char(datum, 'yyyy-mm-dd') AS char_date,
		'Q' || to_char(datum, 'Q') AS quarter,
		to_char(datum, 'yyyy"年Q"Q') AS year_quarter,
		to_char(datum, 'yyyy"年"mm"月"') AS year_month,
		-- to_char(datum, 'yyyy/"Q"Q') AS year_quarter,
		-- to_char(datum, 'yyyy/mm') AS year_month,
		-- ISO calendar year and week
		to_char(datum, 'iyyy/IW') AS year_calendar_week,
		-- Weekend
		CASE WHEN extract(isodow from datum) in (6, 7) THEN '周末' ELSE '工作日' END AS weekend,
		-- Fixed holidays 
			-- for America
			CASE WHEN to_char(datum, 'MMDD') IN ('0101', '0704', '1225', '1226')
			THEN '是' ELSE '否' END
			AS american_holiday,
			-- for Austria
		CASE WHEN to_char(datum, 'MMDD') IN 
			('0101', '0106', '0501', '0815', '1101', '1208', '1225', '1226') 
			THEN '是' ELSE '否' END 
			AS austrian_holiday,
			-- for Canada
			CASE WHEN to_char(datum, 'MMDD') IN ('0101', '0701', '1225', '1226')
			THEN '是' ELSE '否' END 
			AS canadian_holiday,
			
		CASE WHEN d.holiday = 1 THEN '是' ELSE '否' END  AS chinese_holiday,
		d.name AS remark,
		
		d.wage,
		CASE 
			WHEN d.holiday = 1 THEN '否' 
			WHEN d.holiday = 0 THEN '是'
			WHEN d.holiday IS NULL AND extract(isodow from datum) in (6, 7) THEN '否'
			ELSE '是' END AS is_workday,
		
		-- Some periods of the year, adjust for your organisation and country
	--	CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831' THEN 'Summer break'
	--	     WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225' THEN 'Christmas season'
	--	     WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106' THEN 'Winter break'
	--		ELSE 'Normal' END
	--		AS period,
		-- ISO start and end of the week of this date
		datum + (1 - extract(isodow from datum))::integer AS cw_start,
		datum + (7 - extract(isodow from datum))::integer AS cw_end,
		-- Start and end of the month of this date
		datum + (1 - extract(day from datum))::integer AS month_start,
		(datum + (1 - extract(day from datum))::integer + '1 month'::interval)::date - '1 day'::interval AS month_end
	FROM (
		-- There are 3 leap years in this range, so calculate 365 * 10 + 3 records
		SELECT '2000-01-01'::DATE + sequence.day AS datum
		FROM generate_series(0,36520) AS sequence(day)
		GROUP BY sequence.day
		
	) DQ
	LEFT JOIN fddw.dim_holidays d ON to_char(DQ.datum, 'yyyy-mm-dd') = d.date
	ORDER BY 1 ASC  
)