挑战100天不停更之hive sql16天-时间序列，构造时间（几乎包含所有的需要的时间）

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

NUM: 第16天 -构造时间

马上12点了，抓住今天的小尾巴，把今天的sql更新了，今天突然通知居家办公，早上就去公司搬电脑，耽误了不少时间，但是该做的事情还是要做，把握好每一天，今天的sql主要是以时间为主，下面将每条sql进行拆解，生僻的时间函数都添加了注释和详解，希望能帮助看博客的人更好的理解~~

🧨不废话，刷题~~🧨

🎈先给出要求得的表结构

-- 创建日期表
create table if not exists dim_date
(
  `date`    string comment '日期',
  d_week    string comment '年内第几周',
  weeks     string comment '周几',
  w_start   string comment '周开始日',
  w_end     string comment '周结束日',
  d_month   string comment '第几月',
  m_start   string comment '月开始日',
  m_end     string comment '月结束日',
  d_quarter int comment '第几季',
  q_start   string comment '季开始日',
  q_end     string comment '季结束日',
  d_year    int comment '年份',
  y_start   string comment '年开始日',
  y_end     string comment '年结束日'
);

👓使用sql创建一张日期维度表

**本条sql非常全面的考察对hive日期函数的理解，日期计算有点复杂，需要花点时间哦~~**

请往下看：

select `date`
     , d_week
--年内第几周
     , case weekid
           when 0 then '周日'
           when 1 then '周一'
           when 2 then '周二'
           when 3 then '周三'
           when 4 then '周四'
           when 5 then '周五'
           when 6
               then '周六' end                                                                  as weeks
     -- 周
     , date_add(next_day(`date`, 'MO'), -7)                                                   as w_start -- 周一
     , date_add(next_day(`date`, 'MO'), -1)                                                   as w_end   -- 周日_end
     -- 月份日期
     , concat('第', monthid, '月')                                                              as d_month
     , m_start
     , m_end                                                                                             -- 季节
     , quarterid                                                                              as d_quart
     , concat(d_year, '-', substr(concat('0', (quarterid - 1) * 3 + 1), -2), '-01')           as q_start --季开始日
     , date_sub(concat(d_year, '-', substr(concat('0', (quarterid) * 3 + 1), -2), ' -01'), 1) as q_end   --季结束日
     , d_year                                                                                            -- 年
     , y_start
     , y_end
from (select `date`
           , pmod(datediff(`date`, '2012-01-01'), 7)                     as weekid    --获取周几
           , cast(substr(`date`, 6, 2) as int)                           as monthid   --获取月份
           , case
                 when cast(substr(`date`, 6, 2) as int) <= 3 then 1
                 when cast(substr(`date`, 6, 2) as int) <= 6 then 2
                 when cast(substr(`date`, 6, 2) as int) <= 9 then 3
                 when cast(substr(`date`, 6, 2) as int) <= 12 then 4 end as quarterid --获取季节 可以直接使用quarter(`date`)
           , substr(`date`, 1, 4)                                        as d_year    -- 获取年份
           , trunc(`date`, 'YYYY')                                       as y_start   --年开始日
           , date_sub(trunc(add_months(`date`, 12), 'YYYY'), 1)          as y_end     --年 结束日
           , date_sub(`date`, dayofmonth(`date`) - 1)                    as m_start   --当月第一天
           , last_day(date_sub(`date`, dayofmonth(`date`) - 1))             m_end     --当月最后一天
           , weekofyear(`date`)                                          as d_week    --年内第几周
      from ( -- '2021-04-01'是开始日期, '2022-03-31'是截止日期
               select date_add('2021-04-01', t0.pos) as `date`
               from (select posexplode(split(repeat('o',
                                                    datediff(from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'),
                                                                           'yyyy-mm-dd '), '2021-04-01')), 'o'))) t0
           ) t1) t2;

🎨分析

由于时间函数比较多，我把注释和详解都写在了代码里，主要总结了一些生僻的，还有执行流程

-- t0 通过炸裂行数取出日期间隔所需要的行数
-- 1.1根据日期格式返回时间戳
select unix_timestamp('2022-03-31', 'yyyy-mm-dd');
desc function unix_timestamp;
-- 1.2将时间戳转换为指定格式
select from_unixtime(1643558580, 'yyyy-mm-dd');
-- 1.3 结果 -366  小的日期放在前面结果为负数,放在后面为正 所以,要想使用炸裂函数必要要把大的日期写在前面
-- 1.3.1 select repeat('o', -366)结果为空
select datediff('2022-03-31', '2021-04-01');
-- 364
-- 1.4 repeat替换，第二个参数为int值
select repeat('o', datediff(from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'), 'yyyy-mm-dd '), '2021-04-01'));
-- 1.5 根据’o‘来切割成数组
select split(repeat('o',
                    datediff(from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'), 'yyyy-mm-dd '), '2021-04-01')),
             'o');
-- 1.6 通过炸裂函数转换多行,这里的posexplode()为单独使用，这里需要注意的是posexplode()没有结合 laterval view 使用,所以查询结果默认有两个字段  pos，val,下一层的查询可以直接引用
select posexplode(split(
        repeat('o', datediff(from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'), 'yyyy-mm-dd '), '2021-04-01')),
        'o'));

--t1 给一个起始的函数,通过炸裂出的函数累加计算出天数
-- '2021-04-01'是开始日期, '2022-03-31'是截止日期 date_add(当前时间,n) 结果为当前时间+n天
select date_add('2021-04-01', t0.pos) as `date`
from (select posexplode(split(
        repeat('o', datediff(from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'), 'yyyy-mm-dd '), '2021-04-01')),
        'o'))) t0;
--t2 t1的时间取出来后就可以根据日期函数不断的喜欢要找的结果可
-- 根据日期函数进行详解 抽取部分复杂的进行讲解
-- 2.1 pmod(int a, int b) 返回 a 除 b的余数的绝对值
select pmod(5, 2);
-- 2.2 trunc() 获取当年的第一天，测试 dd mm 等获取其他日期参数都无返回结果，这个是应该是hive的语法不一样，mysql就可以
select trunc('2022-04-14', 'YYYY');
-- 2.3 date_sub() 返回日期的前一天
select date_sub('2022-04-14', 1);
-- 2.4 next_day 语法 next_day( date, weekday )返回当下周的周一时间 结合date_add可以取当前周一和周日的时间
select next_day('2022-04-14', 'MO')