2023年日历维度数据

建表语句,如下:

    DROP TABLE IF EXISTS dim_date_info;
    CREATE EXTERNAL TABLE dim_date_info(
        `date_id` STRING COMMENT '日',
        `week_id` STRING COMMENT '周ID',
        `week_day` STRING COMMENT '周几',
        `day` STRING COMMENT '每月的第几天',
        `month` STRING COMMENT '第几月',
        `quarter` STRING COMMENT '第几季度',
        `year` STRING COMMENT '年',
        `is_workday` STRING COMMENT '是否是工作日',
        `holiday_id` STRING COMMENT '节假日'
    ) COMMENT '时间维度表'
    STORED AS ORC
    LOCATION '/warehouse/xxx/dim/dim_date_info/'
    TBLPROPERTIES ("orc.compress"="snappy");

日期内容,如下:
   
2023/1/1    53    7    1    1    1    2023    0    元旦
    2023/1/2    1    1    2    1    1    2023    0    元旦
    2023/1/3    1    2    3    1    1    2023    1    \N
    2023/1/4    1    3    4    1    1    2023    1    \N
    2023/1/5    1    4    5    1    1    2023    1    \N
    2023/1/6    1    5    6    1    1    2023    1    \N
    2023/1/7    1    6    7    1    1    2023    0    \N
    2023/1/8    1    7    8    1    1    2023    0    \N
    2023/1/9    2    1    9    1    1    2023    1    \N
    2023/1/10    2    2    10    1    1    2023    1    \N
    2023/1/11    2    3    11    1    1    2023    1    \N
    2023/1/12    2    4    12    1    1    2023    1    \N
    2023/1/13    2    5    13    1    1    2023    1    \N
    2023/1/14    2    6    14    1    1    2023    0    \N
    2023/1/15    2    7    15    1    1    2023    0    \N
    2023/1/16    3    1    16    1    1    2023    1    \N
    2023/1/17    3    2    17    1    1    2023    1    \N
    2023/1/18    3    3    18    1    1    2023    1    \N
    2023/1/19    3    4    19    1    1    2023    1    \N
    2023/1/20    3    5    20    1    1    2023    1    \N
    2023/1/21    3    6    21    1    1    2023    0    春节
    2023/1/22    3    7    22    1    1    2023    0    春节
    2023/1/23    4    1    23    1    1    2023    0    春节
    2023/1/24    4    2    24    1    1    2023    0    春节
    2023/1/25    4    3    25    1    1    2023    0    春节
    2023/1/26    4    4    26    1    1    2023    0    春节
    2023/1/27    4    5    27    1    1    2023    0    春节
    2023/1/28    4    6    28    1    1    2023    1    \N
    2023/1/29    4    7    29    1    1    2023    1    \N
    2023/1/30    5    1    30    1    1    2023    1    \N
    2023/1/31    5    2    31    1    1    2023    1    \N
    2023/2/1    5    3    1    2    1    2023    1    \N
    2023/2/2    5    4    2    2    1    2023    1    \N
    2023/2/3    5    5    3    2    1    2023    1    \N
    2023/2/4    5    6    4    2    1    2023    0    \N
    2023/2/5    5    7    5    2    1    2023    0    \N
    2023/2/6    6    1    6    2    1    2023    1    \N
    2023/2/7    6    2    7    2    1    2023    1    \N
    2023/2/8    6    3    8    2    1    2023    1    \N
    2023/2/9    6    4    9    2    1    2023    1    \N
    2023/2/10    6    5    10    2    1    2023    1    \N
    2023/2/11    6    6    11    2    1    2023    0    \N
    2023/2/12    6    7    12    2    1    2023    0    \N
    2023/2/13    7    1    13    2    1    2023    1    \N
    2023/2/14    7    2    14    2    1    2023    1    \N
    2023/2/15    7    3    15    2    1    2023    1    \N
    2023/2/16    7    4    16    2    1    2023    1    \N
    2023/2/17    7    5    17    2    1    2023    1    \N
    2023/2/18    7    6    18    2    1    2023    0    \N
    2023/2/19    7    7    19    2    1    2023    0    \N
    2023/2/20    8    1    20    2    1    2023    1    \N
    2023/2/21    8    2    21    2    1    2023    1    \N
    2023/2/22    8    3    22    2    1    2023    1    \N
    2023/2/23    8    4    23    2    1    2023    1    \N
    2023/2/24    8    5    24    2    1    2023    1    \N
    2023/2/25    8    6    25    2    1    2023    0    \N
    2023/2/26    8    7    26    2    1    2023    0    \N
    2023/2/27    9    1    27    2    1    2023    1    \N
    2023/2/28    9    2    28    2    1    2023    1    \N
    2023/3/1    9    3    1    3    1    2023    1    \N
    2023/3/2    9    4    2    3    1    2023    1    \N
    2023/3/3    9    5    3    3    1    2023    1    \N
    2023/3/4    9    6    4    3    1    2023    0    \N
    2023/3/5    9    7    5    3    1    2023    0    \N
    2023/3/6    10    1    6    3    1    2023    1    \N
    2023/3/7    10    2    7    3    1    2023    1    \N
    2023/3/8    10    3    8    3    1    2023    1    \N
    2023/3/9    10    4    9    3    1    2023    1    \N
    2023/3/10    10    5    10    3    1    2023    1    \N
    2023/3/11    10    6    11    3    1    2023    0    \N
    2023/3/12    10    7    12    3    1    2023    0    \N
    2023/3/13    11    1    13    3    1    2023    1    \N
    2023/3/14    11    2    14    3    1    2023    1    \N
    2023/3/15    11    3    15    3    1    2023    1    \N
    2023/3/16    11    4    16    3    1    2023    1    \N
    2023/3/17    11    5    17    3    1    2023    1    \N
    2023/3/18    11    6    18    3    1    2023    0    \N
    2023/3/19    11    7    19    3    1    2023    0    \N
    2023/3/20    12    1    20    3    1    2023    1    \N
    2023/3/21    12    2    21    3    1    2023    1    \N
    2023/3/22    12    3    22    3    1    2023    1    \N
    2023/3/23    12    4    23    3    1    2023    1    \N
    2023/3/24    12    5    24    3    1    2023    1    \N
    2023/3/25    12    6    25    3    1    2023    0    \N
    2023/3/26    12    7    26    3    1    2023    0    \N
    2023/3/27    13    1    27    3    1    2023    1    \N
    2023/3/28    13    2    28    3    1    2023    1    \N
    2023/3/29    13    3    29    3    1    2023    1    \N
    2023/3/30    13    4    30    3    1    2023    1    \N
    2023/3/31    13    5    31    3    1    2023    1    \N
    2023/4/1    13    6    1    4    2    2023    0    \N
    2023/4/2    13    7    2    4    2    2023    0    \N
    2023/4/3    14    1    3    4    2    2023    1    \N
    2023/4/4    14    2    4    4    2    2023    1    \N
    2023/4/5    14    3    5    4    2    2023    0    清明节
    2023/4/6    14    4    6    4    2    2023    1    \N
    2023/4/7    14    5    7    4    2    2023    1    \N
    2023/4/8    14    6    8    4    2    2023    0    \N
    2023/4/9    14    7    9    4    2    2023    0    \N
    2023/4/10    15    1    10    4    2    2023    1    \N
    2023/4/11    15    2    11    4    2    2023    1    \N
    2023/4/12    15    3    12    4    2    2023    1    \N
    2023/4/13    15    4    13    4    2    2023    1    \N
    2023/4/14    15    5    14    4    2    2023    1    \N
    2023/4/15    15    6    15    4    2    2023    0    \N
    2023/4/16    15    7    16    4    2    2023    0    \N
    2023/4/17    16    1    17    4    2    2023    1    \N
    2023/4/18    16    2    18    4    2    2023    1    \N
    2023/4/19    16    3    19    4    2    2023    1    \N
    2023/4/20    16    4    20    4    2    2023    1    \N
    2023/4/21    16    5    21    4    2    2023    1    \N
    2023/4/22    16    6    22    4    2    2023    0    \N
    2023/4/23    16    7    23    4    2    2023    1    \N
    2023/4/24    17    1    24    4    2    2023    1    \N
    2023/4/25    17    2    25    4    2    2023    1    \N
    2023/4/26    17    3    26    4    2    2023    1    \N
    2023/4/27    17    4    27    4    2    2023    1    \N
    2023/4/28    17    5    28    4    2    2023    1    \N
    2023/4/29    17    6    29    4    2    2023    0    劳动节
    2023/4/30    17    7    30    4    2    2023    0    劳动节
    2023/5/1    18    1    1    5    2    2023    0    劳动节
    2023/5/2    18    2    2    5    2    2023    0    劳动节
    2023/5/3    18    3    3    5    2    2023    0    劳动节
    2023/5/4    18    4    4    5    2    2023    1    \N
    2023/5/5    18    5    5    5    2    2023    1    \N
    2023/5/6    18    6    6    5    2    2023    1    \N
    2023/5/7    18    7    7    5    2    2023    0    \N
    2023/5/8    19    1    8    5    2    2023    1    \N
    2023/5/9    19    2    9    5    2    2023    1    \N
    2023/5/10    19    3    10    5    2    2023    1    \N
    2023/5/11    19    4    11    5    2    2023    1    \N
    2023/5/12    19    5    12    5    2    2023    1    \N
    2023/5/13    19    6    13    5    2    2023    0    \N
    2023/5/14    19    7    14    5    2    2023    0    \N
    2023/5/15    20    1    15    5    2    2023    1    \N
    2023/5/16    20    2    16    5    2    2023    1    \N
    2023/5/17    20    3    17    5    2    2023    1    \N
    2023/5/18    20    4    18    5    2    2023    1    \N
    2023/5/19    20    5    19    5    2    2023    1    \N
    2023/5/20    20    6    20    5    2    2023    0    \N
    2023/5/21    20    7    21    5    2    2023    0    \N
    2023/5/22    21    1    22    5    2    2023    1    \N
    2023/5/23    21    2    23    5    2    2023    1    \N
    2023/5/24    21    3    24    5    2    2023    1    \N
    2023/5/25    21    4    25    5    2    2023    1    \N
    2023/5/26    21    5    26    5    2    2023    1    \N
    2023/5/27    21    6    27    5    2    2023    0    \N
    2023/5/28    21    7    28    5    2    2023    0    \N
    2023/5/29    22    1    29    5    2    2023    1    \N
    2023/5/30    22    2    30    5    2    2023    1    \N
    2023/5/31    22    3    31    5    2    2023    1    \N
    2023/6/1    22    4    1    6    2    2023    1    \N
    2023/6/2    22    5    2    6    2    2023    1    \N
    2023/6/3    22    6    3    6    2    2023    0    \N
    2023/6/4    22    7    4    6    2    2023    0    \N
    2023/6/5    23    1    5    6    2    2023    1    \N
    2023/6/6    23    2    6    6    2    2023    1    \N
    2023/6/7    23    3    7    6    2    2023    1    \N
    2023/6/8    23    4    8    6    2    2023    1    \N
    2023/6/9    23    5    9    6    2    2023    1    \N
    2023/6/10    23    6    10    6    2    2023    0    \N
    2023/6/11    23    7    11    6    2    2023    0    \N
    2023/6/12    24    1    12    6    2    2023    1    \N
    2023/6/13    24    2    13    6    2    2023    1    \N
    2023/6/14    24    3    14    6    2    2023    1    \N
    2023/6/15    24    4    15    6    2    2023    1    \N
    2023/6/16    24    5    16    6    2    2023    1    \N
    2023/6/17    24    6    17    6    2    2023    0    \N
    2023/6/18    24    7    18    6    2    2023    0    \N
    2023/6/19    25    1    19    6    2    2023    1    \N
    2023/6/20    25    2    20    6    2    2023    1    \N
    2023/6/21    25    3    21    6    2    2023    1    \N
    2023/6/22    25    4    22    6    2    2023    0    端午节
    2023/6/23    25    5    23    6    2    2023    0    端午节
    2023/6/24    25    6    24    6    2    2023    0    端午节
    2023/6/25    25    7    25    6    2    2023    1    \N
    2023/6/26    26    1    26    6    2    2023    1    \N
    2023/6/27    26    2    27    6    2    2023    1    \N
    2023/6/28    26    3    28    6    2    2023    1    \N
    2023/6/29    26    4    29    6    2    2023    1    \N
    2023/6/30    26    5    30    6    2    2023    1    \N
    2023/7/1    26    6    1    7    3    2023    0    \N
    2023/7/2    26    7    2    7    3    2023    0    \N
    2023/7/3    27    1    3    7    3    2023    1    \N
    2023/7/4    27    2    4    7    3    2023    1    \N
    2023/7/5    27    3    5    7    3    2023    1    \N
    2023/7/6    27    4    6    7    3    2023    1    \N
    2023/7/7    27    5    7    7    3    2023    1    \N
    2023/7/8    27    6    8    7    3    2023    0    \N
    2023/7/9    27    7    9    7    3    2023    0    \N
    2023/7/10    28    1    10    7    3    2023    1    \N
    2023/7/11    28    2    11    7    3    2023    1    \N
    2023/7/12    28    3    12    7    3    2023    1    \N
    2023/7/13    28    4    13    7    3    2023    1    \N
    2023/7/14    28    5    14    7    3    2023    1    \N
    2023/7/15    28    6    15    7    3    2023    0    \N
    2023/7/16    28    7    16    7    3    2023    0    \N
    2023/7/17    29    1    17    7    3    2023    1    \N
    2023/7/18    29    2    18    7    3    2023    1    \N
    2023/7/19    29    3    19    7    3    2023    1    \N
    2023/7/20    29    4    20    7    3    2023    1    \N
    2023/7/21    29    5    21    7    3    2023    1    \N
    2023/7/22    29    6    22    7    3    2023    0    \N
    2023/7/23    29    7    23    7    3    2023    0    \N
    2023/7/24    30    1    24    7    3    2023    1    \N
    2023/7/25    30    2    25    7    3    2023    1    \N
    2023/7/26    30    3    26    7    3    2023    1    \N
    2023/7/27    30    4    27    7    3    2023    1    \N
    2023/7/28    30    5    28    7    3    2023    1    \N
    2023/7/29    30    6    29    7    3    2023    0    \N
    2023/7/30    30    7    30    7    3    2023    0    \N
    2023/7/31    31    1    31    7    3    2023    1    \N
    2023/8/1    31    2    1    8    3    2023    1    \N
    2023/8/2    31    3    2    8    3    2023    1    \N
    2023/8/3    31    4    3    8    3    2023    1    \N
    2023/8/4    31    5    4    8    3    2023    1    \N
    2023/8/5    31    6    5    8    3    2023    0    \N
    2023/8/6    31    7    6    8    3    2023    0    \N
    2023/8/7    32    1    7    8    3    2023    1    \N
    2023/8/8    32    2    8    8    3    2023    1    \N
    2023/8/9    32    3    9    8    3    2023    1    \N
    2023/8/10    32    4    10    8    3    2023    1    \N
    2023/8/11    32    5    11    8    3    2023    1    \N
    2023/8/12    32    6    12    8    3    2023    0    \N
    2023/8/13    32    7    13    8    3    2023    0    \N
    2023/8/14    33    1    14    8    3    2023    1    \N
    2023/8/15    33    2    15    8    3    2023    1    \N
    2023/8/16    33    3    16    8    3    2023    1    \N
    2023/8/17    33    4    17    8    3    2023    1    \N
    2023/8/18    33    5    18    8    3    2023    1    \N
    2023/8/19    33    6    19    8    3    2023    0    \N
    2023/8/20    33    7    20    8    3    2023    0    \N
    2023/8/21    34    1    21    8    3    2023    1    \N
    2023/8/22    34    2    22    8    3    2023    1    \N
    2023/8/23    34    3    23    8    3    2023    1    \N
    2023/8/24    34    4    24    8    3    2023    1    \N
    2023/8/25    34    5    25    8    3    2023    1    \N
    2023/8/26    34    6    26    8    3    2023    0    \N
    2023/8/27    34    7    27    8    3    2023    0    \N
    2023/8/28    35    1    28    8    3    2023    1    \N
    2023/8/29    35    2    29    8    3    2023    1    \N
    2023/8/30    35    3    30    8    3    2023    1    \N
    2023/8/31    35    4    31    8    3    2023    1    \N
    2023/9/1    35    5    1    9    3    2023    1    \N
    2023/9/2    35    6    2    9    3    2023    0    \N
    2023/9/3    35    7    3    9    3    2023    0    \N
    2023/9/4    36    1    4    9    3    2023    1    \N
    2023/9/5    36    2    5    9    3    2023    1    \N
    2023/9/6    36    3    6    9    3    2023    1    \N
    2023/9/7    36    4    7    9    3    2023    1    \N
    2023/9/8    36    5    8    9    3    2023    1    \N
    2023/9/9    36    6    9    9    3    2023    0    \N
    2023/9/10    36    7    10    9    3    2023    0    \N
    2023/9/11    37    1    11    9    3    2023    1    \N
    2023/9/12    37    2    12    9    3    2023    1    \N
    2023/9/13    37    3    13    9    3    2023    1    \N
    2023/9/14    37    4    14    9    3    2023    1    \N
    2023/9/15    37    5    15    9    3    2023    1    \N
    2023/9/16    37    6    16    9    3    2023    0    \N
    2023/9/17    37    7    17    9    3    2023    0    \N
    2023/9/18    38    1    18    9    3    2023    1    \N
    2023/9/19    38    2    19    9    3    2023    1    \N
    2023/9/20    38    3    20    9    3    2023    1    \N
    2023/9/21    38    4    21    9    3    2023    1    \N
    2023/9/22    38    5    22    9    3    2023    1    \N
    2023/9/23    38    6    23    9    3    2023    0    \N
    2023/9/24    38    7    24    9    3    2023    0    \N
    2023/9/25    39    1    25    9    3    2023    1    \N
    2023/9/26    39    2    26    9    3    2023    1    \N
    2023/9/27    39    3    27    9    3    2023    1    \N
    2023/9/28    39    4    28    9    3    2023    1    \N
    2023/9/29    39    5    29    9    3    2023    0    中秋节
    2023/9/30    39    6    30    9    3    2023    0    中秋节
    2023/10/1    39    7    1    10    4    2023    0    国庆节
    2023/10/2    40    1    2    10    4    2023    0    国庆节
    2023/10/3    40    2    3    10    4    2023    0    国庆节
    2023/10/4    40    3    4    10    4    2023    0    国庆节
    2023/10/5    40    4    5    10    4    2023    0    国庆节
    2023/10/6    40    5    6    10    4    2023    0    国庆节
    2023/10/7    40    6    7    10    4    2023    1    \N
    2023/10/8    40    7    8    10    4    2023    1    \N
    2023/10/9    41    1    9    10    4    2023    1    \N
    2023/10/10    41    2    10    10    4    2023    1    \N
    2023/10/11    41    3    11    10    4    2023    1    \N
    2023/10/12    41    4    12    10    4    2023    1    \N
    2023/10/13    41    5    13    10    4    2023    1    \N
    2023/10/14    41    6    14    10    4    2023    0    \N
    2023/10/15    41    7    15    10    4    2023    0    \N
    2023/10/16    42    1    16    10    4    2023    1    \N
    2023/10/17    42    2    17    10    4    2023    1    \N
    2023/10/18    42    3    18    10    4    2023    1    \N
    2023/10/19    42    4    19    10    4    2023    1    \N
    2023/10/20    42    5    20    10    4    2023    1    \N
    2023/10/21    42    6    21    10    4    2023    0    \N
    2023/10/22    42    7    22    10    4    2023    0    \N
    2023/10/23    43    1    23    10    4    2023    1    \N
    2023/10/24    43    2    24    10    4    2023    1    \N
    2023/10/25    43    3    25    10    4    2023    1    \N
    2023/10/26    43    4    26    10    4    2023    1    \N
    2023/10/27    43    5    27    10    4    2023    1    \N
    2023/10/28    43    6    28    10    4    2023    0    \N
    2023/10/29    43    7    29    10    4    2023    0    \N
    2023/10/30    44    1    30    10    4    2023    1    \N
    2023/10/31    44    2    31    10    4    2023    1    \N
    2023/11/1    44    3    1    11    4    2023    1    \N
    2023/11/2    44    4    2    11    4    2023    1    \N
    2023/11/3    44    5    3    11    4    2023    1    \N
    2023/11/4    44    6    4    11    4    2023    0    \N
    2023/11/5    44    7    5    11    4    2023    0    \N
    2023/11/6    45    1    6    11    4    2023    1    \N
    2023/11/7    45    2    7    11    4    2023    1    \N
    2023/11/8    45    3    8    11    4    2023    1    \N
    2023/11/9    45    4    9    11    4    2023    1    \N
    2023/11/10    45    5    10    11    4    2023    1    \N
    2023/11/11    45    6    11    11    4    2023    0    \N
    2023/11/12    45    7    12    11    4    2023    0    \N
    2023/11/13    46    1    13    11    4    2023    1    \N
    2023/11/14    46    2    14    11    4    2023    1    \N
    2023/11/15    46    3    15    11    4    2023    1    \N
    2023/11/16    46    4    16    11    4    2023    1    \N
    2023/11/17    46    5    17    11    4    2023    1    \N
    2023/11/18    46    6    18    11    4    2023    0    \N
    2023/11/19    46    7    19    11    4    2023    0    \N
    2023/11/20    47    1    20    11    4    2023    1    \N
    2023/11/21    47    2    21    11    4    2023    1    \N
    2023/11/22    47    3    22    11    4    2023    1    \N
    2023/11/23    47    4    23    11    4    2023    1    \N
    2023/11/24    47    5    24    11    4    2023    1    \N
    2023/11/25    47    6    25    11    4    2023    0    \N
    2023/11/26    47    7    26    11    4    2023    0    \N
    2023/11/27    48    1    27    11    4    2023    1    \N
    2023/11/28    48    2    28    11    4    2023    1    \N
    2023/11/29    48    3    29    11    4    2023    1    \N
    2023/11/30    48    4    30    11    4    2023    1    \N
    2023/12/1    48    5    1    12    4    2023    1    \N
    2023/12/2    48    6    2    12    4    2023    0    \N
    2023/12/3    48    7    3    12    4    2023    0    \N
    2023/12/4    49    1    4    12    4    2023    1    \N
    2023/12/5    49    2    5    12    4    2023    1    \N
    2023/12/6    49    3    6    12    4    2023    1    \N
    2023/12/7    49    4    7    12    4    2023    1    \N
    2023/12/8    49    5    8    12    4    2023    1    \N
    2023/12/9    49    6    9    12    4    2023    0    \N
    2023/12/10    49    7    10    12    4    2023    0    \N
    2023/12/11    50    1    11    12    4    2023    1    \N
    2023/12/12    50    2    12    12    4    2023    1    \N
    2023/12/13    50    3    13    12    4    2023    1    \N
    2023/12/14    50    4    14    12    4    2023    1    \N
    2023/12/15    50    5    15    12    4    2023    1    \N
    2023/12/16    50    6    16    12    4    2023    0    \N
    2023/12/17    50    7    17    12    4    2023    0    \N
    2023/12/18    51    1    18    12    4    2023    1    \N
    2023/12/19    51    2    19    12    4    2023    1    \N
    2023/12/20    51    3    20    12    4    2023    1    \N
    2023/12/21    51    4    21    12    4    2023    1    \N
    2023/12/22    51    5    22    12    4    2023    1    \N
    2023/12/23    51    6    23    12    4    2023    0    \N
    2023/12/24    51    7    24    12    4    2023    0    \N
    2023/12/25    52    1    25    12    4    2023    1    \N
    2023/12/26    52    2    26    12    4    2023    1    \N
    2023/12/27    52    3    27    12    4    2023    1    \N
    2023/12/28    52    4    28    12    4    2023    1    \N
    2023/12/29    52    5    29    12    4    2023    1    \N
    2023/12/30    52    6    30    12    4    2023    0    \N
    2023/12/31    52    7    31    12    4    2023    0    \N

 

posted @ 2023-04-10 13:27  DB乐之者  阅读(80)  评论(0编辑  收藏  举报