挑战100天不停更之第15天 - 时间序列,进度及剩余

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

NUM: 第15天 - 时间序列,进度及剩余

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

🧨不废话，刷题~~🧨

🎈表结构

🎉建表

create table t15
(
    date_id string,
    is_work string
);

insert into t15 (date_id, is_work)
values ('2017-07-30', '0'),
       ('2017-07-31', '1'),
       ('2017-08-01', '1'),
       ('2017-08-02', '1'),
       ('2017-08-03', '1'),
       ('2017-08-04', '1'),
       ('2017-08-05', '0'),
       ('2017-08-06', '0'),
       ('2017-08-07', '1');

👓求每天的累计周工作日，剩余周工作日

👙输出结果

🎨思考

1. 这里的计算应该是不加上周末加班,只是统计周一到周五的时间,但是又给了is_work标记,可以不考虑,那么下面的第一种解法就够了（下面提供两种方式解答）
2. 累计周工作日: 对已工作的天数进行求和
3. 剩余周工作日：去除已工作的时间, 可以用 总的 - 已工作的

🧨SQL

解法一：

利用case when的方式,这种方式比较简单,只需要date_format(date_id, 'u')取出对应日期的周几即可**,结尾部分补充下****date_format()**的常用用法

-- 解法一 
select date_id,
       case date_format(date_id, 'u')
           when 1 then 1
           when 2 then 2
           when 3 then 3
           when 4 then 4
           when 5 then 5
           when 6 then 5
           when 7 then 5
           end as week_to_work,
       case date_format(date_id, 'u')
           when 1 then 4
           when 2 then 3
           when 3 then 2
           when 4 then 1
           when 5 then 0
           when 6 then 0
           when 7 then 0
           end as week_left_work
from t15;

解法二：

此种方法可以更灵活的解决实际问题

1. 根据date_id计算出 当前年 和 当前第几周
2. 利用sum() 开窗函数去统计is_work的天数（具体内容看代码注释）
3. 总工作天数 - 已工作天数

select date_id,
week_to_work,
-- 总工作天数 - 已工作天数
week_sum_work - week_to_work as week_left_work
from (
  --2,利用sum() 开窗函数去统计is_work的天数,
  select date_id,
  --2.1,通过date_id进行排序,累计统计每一周的上班天数
  sum(cast(is_work as int)) over (partition by year,week order by date_id ) as week_to_work,
  --2.2,去除order by排序统计总的上班天数,这里求的是所有is_work的和
  sum(cast(is_work as int)) over (partition by year,week)                  as week_sum_work
  from (
    --1,根据date_id计算出 当前年 和 当前第几周
    select date_id, is_work, year(date_id) as year, weekofyear(date_id) as week from t15
  ) ta
) tb
order by date_id;

date_format()的常用用法

desc function date_format;
官方：date_format(date/timestamp/string, fmt) - converts a date/timestamp/string to a value of string in the format specified by the date format fmt.
注意：这里的日期格式必须用‘-’连接

– 获取年
select date_format(‘2022-04-13’, ‘y’);
2022
select date_format(‘2022-04-13’, ‘yyyy’);
2022
– 获取年 月
select date_format(‘2022-04-13’, ‘yyyy-MM’);
2022-04
select date_format(‘2022-04-13 10:10:01’, ‘yyyy-MM’);
2022-04
– 获取天
select date_format(‘2022-04-13’, ‘d’);
– 获取当前周几
select date_format(‘2022-04-13’, ‘u’);