Hive 求7天、4周、6个月的数据（1.日期）

1.前言

对于7天、4周、6个月的数据要明确取值范围，例如7天：包含今天、一个是不包含今天，4周：不包含本周、包含本周等等取值规范。

2.常用到的函数

2.1 nvl函数

1）nvl函数基本语法

NVL（表达式1，表达式2）

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。

该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

2）案例实操

hive (lzy)> select nvl(1,0);

1

hive (lzy)> select nvl(null,"hello");

hello

 2.2 日期处理函数

1）date_format函数（根据格式整理日期）

hive (lzy)> select date_format('2020-06-14','yyyy-MM');

2020-06

2）date_add函数（加减日期）

hive (lzy)> select date_add('2020-06-14',-1);

2020-06-13

hive (lzy)> select date_add('2020-06-14',1);

2020-06-15

3）next_day函数

（1）取当前天的下一个周一

hive (lzy)> select next_day('2020-06-14','MO');

2020-06-15

说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

（2）取当前周的周一

hive (lzy)> select date_add(next_day('2020-06-14','MO'),-7);

2020-06-8

4）last_day函数（求当月最后一天日期）

hive (lzy)> select last_day('2020-06-14');

2020-06-30
5)months_between(string date1, string date2)函数 --（返回date1与date2之间的月份差）

hive (lzy)> select months_between('2022-06-14','2023-06-14');

12

6）获取当天0点时间
select date_format(current_date(),'yyyy-MM-dd HH:mm:ss');
2022-04-19 00:00:00

7）weekofyear(string date)：返回指定日期是一年中的第几周，范围为0到53，返回值类型是int。

3. 7天、4周、6个月实现

3.1 求7天数据（包含今天和不包括今天的）

-- 包含今天

  where DATEDIFF("2021-12-12",s.qzrq) BETWEEN 0 AND 6   -- 前-后范围在0-6直接，生产中一般是当天（当前时间）-业务时间 差值在0-6

--不包括今天

  where DATEDIFF("2021-12-12",s.qzrq) BETWEEN 1 AND 7

包不包括当天 很多时候都要自己去验证  ，已求7天为例：

between 0 -6    还是  0-7

3.2 求4周数据的数据（不包含本周）

说一下我的思路：因为不包含本周，所以先求出上周日是哪一天（日期），where条件取要求所有周的数据，在根据case then 筛选出第一周、第二周、第三周、第四周（大佬们欢迎留言更好的思路）

核心代码： WHERE  DATEDIFF(date_add(next_day(current_date(),'MO'),-8),a.jzsj) BETWEEN 0 AND 27 --求上周日的日期+取4周时间范围

SELECT g.zd1,g.zd2,g.week
   (
                 SELECT '字段1' AS zd1,'字段2' AS zd2,
                 (
                      CASE  WHEN DATEDIFF(current_date(),a.jzsj) BETWEEN 0 AND 6  
                            THEN  concat(YEAR(a.jzsj),'-',WEEKOFYEAR(a.jzsj),'周')  --第四周
                          WHEN DATEDIFF(current_date(),a.jzsj) BETWEEN 7 AND 13 
                            THEN  concat(YEAR(a.jzsj),'-',WEEKOFYEAR(a.jzsj),'周') --第三周
                          WHEN DATEDIFF(current_date(),a.jzsj) BETWEEN 14 AND 20 
                            THEN concat(YEAR(a.jzsj),'-',WEEKOFYEAR(a.jzsj),'周') ----第二周
                          WHEN DATEDIFF(current_date(),a.jzsj) BETWEEN 21 AND 27 
                            THEN  concat(YEAR(a.jzsj),'-',WEEKOFYEAR(a.jzsj),'周') --第一周
                      END
                  ) as week                      

                 FROM 测试表 AS a

               WHERE  DATEDIFF(date_add(next_day(current_date(),'MO'),-8),a.jzsj) BETWEEN 0 AND 27 --求上周日的日期+取4周时间范围
)AS g

GROUP BY g.zd1,g.zd2,g.week

3.3 求6个月的（不包含本月和不包括本月）

where floor(months_between(current_date(), s.time)) between 1 and 6 
 --函数具体介绍看第2章常用函数（这个方法好像是跨年有缺陷，自测自测！！！）
--用下边这个，生产环境验证没有问题
--包括本月
WHERE DATE_FORMAT(s.time,'yyyy-MM')>=DATE_FORMAT(add_months((CURRENT_DATE),-5),'yyyy-MM')

--不包括本月
WHERE DATE_FORMAT(s.time,'yyyy-MM')>DATE_FORMAT(add_months((CURRENT_DATE),-6),'yyyy-MM')

4.重点，干货！！！！！！！！！！！

hive 、spark函数大全（hivesql和spark sql基本一致）

日期函数_数据湖探索 DLI_Spark SQL语法参考_内置函数_华为云