jMLVmau0

1-- DROP TABLE IF EXISTS dim_date_base;
2CREATE TABLE IF NOT EXISTS dim_date_base (
3	`dt` BIGINT COMMENT '日期dt，示例：20160821',
4	`date_id` BIGINT COMMENT '日期id，示例：20160821',
5	`date` STRING COMMENT '日期，示例：2016-08-21',
6
7	`day_num` BIGINT COMMENT '日计数，起始日期开始',
8	`day_of_week` BIGINT COMMENT '所在周的第几天，从每周一开始',
9	`day_of_month` BIGINT COMMENT '所在月的第几天，从每月1号开始',
10	`day_of_year` BIGINT COMMENT '所在年的第几天，从1月1号开始',
11
12	`week_num` BIGINT COMMENT '自然周计数，从起始日期开始(周一至周日)',
13	`week_of_month` BIGINT COMMENT '一年中的第几周',
14	`week_of_year` BIGINT COMMENT '当月中的第几周',
15	`week_begin_date` STRING COMMENT '自然周起始日期',
16	`week_begin_dt` BIGINT COMMENT '自然周起始日期datekey',
17	`week_end_date` STRING COMMENT '自然周结束日期',
18	`week_end_dt` BIGINT COMMENT '自然周结束日期datekey',
19	`year_week_num` STRING COMMENT '年内周计数，如2016wk32',
20
21	`month_num` BIGINT COMMENT '月计数，从起始日期开始',
22	`mo` BIGINT COMMENT '同month_num，计算方式(year-1970)*12+month-1',
23	`month` BIGINT COMMENT '月，如8',
24	`year_mo` BIGINT COMMENT '年月，如201608',
25	`month_begin_date` STRING COMMENT '本月起始日期',
26	`month_begin_dt` BIGINT COMMENT '本月起始日期datekey',
27	`month_end_date` STRING COMMENT '本月结束日期',
28	`month_end_dt` BIGINT COMMENT '本月结束日期datekey',
29
30	`quarter_num` BIGINT COMMENT '季度计数，从起始日期开始',
31	`quarter` BIGINT COMMENT '季度',
32	`year_quarter` BIGINT COMMENT '年与季度的组合，如20163',
33	`year_quarter_name` STRING COMMENT '年与季度的组合，如2016q3',
34
35	`year` BIGINT COMMENT '年',
36    
37	`is_weekend` BIGINT COMMENT '是否周末',
38	`is_workday` BIGINT COMMENT '是否工作日'
39	-- `is_holiday` BIGINT COMMENT '是否节假日',
40	-- `festival_name` STRING COMMENT '节假日名称，如中秋节',
41	-- `day_of_week_cn` STRING COMMENT '星期'
42)
43COMMENT '日期维度表-基础表 时间跨度为2017-2029';
44
45with
46base_id as ( 
47    -- 要实现时间跨度为20170101至20291231, 需构造一张行数刚好等于天数的母表, 即 4748=365*13+3 (3个闰日)
48    -- 选择一张至少有 4748 行记录的表, 这里取名为 x
49    select
50        (count(1) over (partition by 1 order by 1)) - 1 id --从0开始自增
51    from x
52    limit 4748
53),
54base_timeline as ( -- 日期自增
55    select
56        dateadd('2017-01-01 00:00:00', id, 'dd') time
57        -- dateadd('1970-01-01 00:00:00', id, 'dd') time
58    from base_id
59), 
60base_dt as ( -- 日期组件
61    select
62        time
63        , to_char(time, 'yyyymmdd') dt
64        , to_char(time, 'yyyy-mm-dd') date
65        , to_char(time, 'yyyy') year
66        , to_char(time, 'mm') month
67        , to_char(time, 'dd') day
68        , floor(int(to_char(time, 'mm'))/4)+1 quarter
69
70        , to_char(time, 'yyyy-01-01 00:00:00') year_begin_time
71        , to_char(time, 'yyyy0101') year_begin_dt
72        , to_char(time, 'yyyy-mm-01 00:00:00') month_begin_time
73        , to_char(time, 'yyyymm01') month_begin_dt
74
75        , datediff(time, '1970-01-01 00:00:00', 'dd')+1 day_num
76        , datediff(time, '1970-01-01 00:00:00', 'mm')+1 month_num
77        , floor(datediff(time, '1969-12-29 00:00:00', 'dd')/7)+1 week_num  --19700101是周四, 这周的周一是19691229
78        , floor(datediff(time, '1970-01-01 00:00:00', 'mm'))+1 quarter_num
79    from base_timeline
80),
81base_dtx as ( 
82    -- 冗余 year/month_begin_week_num字段用于算 week_of_year/month
83    select
84        t.*
85        , ybd.week_num year_begin_week_num
86        , mbd.week_num month_begin_week_num
87    from base_dt t -- today
88    join base_dt ybd on t.year_begin_dt=ybd.dt
89    join base_dt mbd on t.month_begin_dt=mbd.dt
90),
91dim_date as (
92    select
93        dt dt
94        , dt date_id
95        , date date
96
97        -- day
98        , day_num day_num
99        , weekday(time)+1 day_of_week
100        , int(day) day_of_month
101        , datediff(time, year_begin_time, 'dd')+1 day_of_year
102        
103        -- week
104        , week_num week_num
105        , week_num - month_begin_week_num + 1 week_of_month
106        , week_num - year_begin_week_num + 1 week_of_year
107        -- , dateadd(time, -weekday(time), 'dd') week_begin_datetime
108        , to_char(dateadd(time, -weekday(time), 'dd'), 'yyyy-mm-dd') week_begin_date
109        , to_char(dateadd(time, -weekday(time), 'dd'), 'yyyymmdd') week_begin_dt
110        -- , dateadd(time, 6-weekday(time), 'dd') week_end_datetime
111        , to_char(dateadd(time, 6-weekday(time), 'dd'), 'yyyy-mm-dd') week_end_date
112        , to_char(dateadd(time, 6-weekday(time), 'dd'), 'yyyymmdd') week_end_dt
113        , concat(year, 'wk', week_num - year_begin_week_num + 1) year_week -- year_week_num
114
115        -- month
116        , month_num month_num
117        , month_num mo
118        , month
119        , concat(year, month) year_mo
120        , to_char(month_begin_time, 'yyyy-mm-dd') month_begin_date
121        , month_begin_dt
122        , to_char(dateadd(dateadd(month_begin_time, 1, 'mm'), -1, 'dd'), 'yyyy-mm-dd') month_end_date
123        , to_char(dateadd(dateadd(month_begin_time, 1, 'mm'), -1, 'dd'), 'yyyymmdd') month_end_dt
124
125        -- quarter
126        , quarter_num
127        , quarter
128        , concat(year, quarter) year_quarter
129        , concat(year, 'q', quarter) year_quarter_name
130
131        -- year
132        , year
133
134        -- 假期相关 这里只有周的工作日和非工作日, 假期逻辑需单独实现
135        , if(weekday(time)>=5, 1, 0) is_weekend
136        , if(weekday(time)>=5, 1, 0) is_workday
137
138        -- , is_holiday
139        -- , festival_name
140        -- , day_of_week_cn
141
142    from base_dtx t
143)
144insert overwrite table dim_date_base
145select * from dim_date;
146-- select * from base_dtx;