Hive SQL进阶案例（一）：使用LAG函数判断日期连续性

Hive SQL进阶案例（一）：使用LAG函数判断日期连续性 一、LAG函数介绍

LAG函数是一个常用的窗口函数，作用是取当前行之后的数据，即把该列数据向上错位。使用方法如下：

LAG(col ,n ,Default)

• col是字段名称，指明要 *** 作的列，必须指定该参数；

• n表示取当前行的后n行的col列数据，可以不指定默认为1；

• Default 表示前n行没有数据时的默认值，可以不指定默认为NULL.

---

二、案例表及测试数据准备

首先创建该案例的表结构、插入测试数据。

-- project表为项目表
CREATE TABLE project(
    proj_number varchar(20)  -- 项目编号
    ,name varchar(20)        -- 项目名称
    ,start_date date         -- 项目启动日期
    ,total_cost varchar(12)  -- 项目总成本：项目未完成则置NULL
    ,dept_code varchar(20)   -- 项目负责部门编号
    ,client_id varchar(20)   -- 客户编号
    ,code varchar(20)        -- 项目编码
 );
-- assignment表存储员工在项目的工作情况
CREATE TABLE assignment(
    assign_num varchar(20)   -- 作业编号
    ,proj_number varchar(20) -- 项目编号
    ,emp_num varchar(20)     -- 员工编号
    ,date_assigned date      -- 本月开始作业日期
    ,date_ended date         -- 本月结束作业日期
    ,hours_used varchar(10)  -- 当月工时【当月作业没有结束则存NULL】
  );
 -- 插入测试数据
INSERT INTO project 
VALUES (7001, 'Apollo',    '2020-12-17', ',000.90', 1, 2001, 9001)
,(7002, 'Astro',     '2021-01-01', ',020.87', 2, 2001, 9001)
,(7003, 'Atlantis',  '2019-12-10', ',000.65', 2, 2002, 9001)
,(7004, 'Barcelona', '2018-08-17', ',000.12', 2, 2003, 9002)
,(7005, 'Bender',    '2021-04-14', ',986.14', 2, 2003, 9002)
,(7006, 'Blue Moon', '2020-08-16', NULL, 3, 2004, 9003)
,(7007, 'Bordeaux',  '2013-09-16', '5,000.67', 3, 2005, 9003)
,(7008, 'Camelot',   '2016-02-04', ',00.23', 3, 2006, 9004)
,(7009, 'Cheerio',   '2008-11-02', ',020.77', 3, 2008, 9005)
,(7010, 'Deepmind',  '2016-08-01', NULL, 3, 2010, 9005); 

INSERT INTO assignment 
VALUES (6001, 7001, 1001, '2020-12-20', '2020-12-30',  180)
,(6002, 7002, 1002, '2021-01-10', '2021-01-28',  150)
,(6003, 7003, 1001, '2019-10-12', '2019-10-20',  80)
,(6004, 7003, 1004, '2019-12-11', '2019-12-20',  90)
,(6005, 7003, 1005, '2019-12-10', '2019-12-21',  90) 
,(6006, 7003, 1006, '2019-10-11', '2019-10-24',  90)
,(6007, 7003, 1008, '2019-12-12', '2019-12-25',  100)
,(6008, 7003, 1008, '2020-01-01', '2020-01-30',  300)
,(6009, 7003, 1008, '2020-02-01', '2020-02-28',  320)
,(6010, 7004, 1010, '2018-08-17', '2018-08-29',  120)
,(6011, 7005, 1005, '2021-04-18', '2021-04-25',  36)
,(6012, 7005, 1001, '2021-04-14', '2021-04-29',  120)
,(6013, 7006, 1007, '2020-08-16', '2020-08-29',  88)
,(6014, 7006, 1008, '2020-08-11', '2020-08-27',  100)
,(6015, 7007, 1009, '2013-09-16', '2013-09-26',  120)
,(6016, 7007, 1010, '2015-01-13', '2015-01-23',  160)
,(6017, 7008, 1009, '2016-02-04', '2016-02-28',  230)
,(6018, 7009, 1007, '2010-11-03', '2010-11-29',  108)
,(6019, 7009, 1007, '2010-12-03', '2010-12-29',  108)
,(6020, 7009, 1010, '2008-11-03', '2008-11-30',  390)
,(6021, 7009, 1008, '2017-11-03', '2017-11-30',  290)
,(6022, 7009, 1002, '2009-01-01', '2009-01-30',  390)
,(6023, 7010, 1005, '2016-01-08', '2016-01-30',  398) 
,(6024, 7010, 1005, '2021-11-08', NULL, NULL);

---

三、LAG函数使用示例

-- 按项目编号排序后，同时查询项目编号和下一个项目编号
select 
  proj_number -- 项目编号
  ,lag(proj_number,1) over () as nextrow_proj_number  --按项目编号排序后的下一个项目编号
from project  
order by proj_number asc;

查询结果：

proj_number  nextrow_proj_number
7001         7002
7002         7003
7003         7004
7004         7005
7005         7006
7006         7007
7007         7008
7008         7009
7009         7010
7010         NULL

---

四、日期连续性判断问题解决

由以上结果可以直观理解LAG函数的用户，接下来提出问题：

根据员工在项目的工作时间找出非连续进行的项目名称、项目启动时间、项目当前状态。

分析：该问题的项目名称、项目启动时间、项目状态很容易取到，关键在于识别出有中断过的项目，由此会用到assignment表的员工在项目当月的开始日期（date_assigned）和结束日期（date_ended）来判断，将会有以下几种情况：

1. 该项目在assignment表仅有一条记录，则项目未中断进行；

2. 该项目在assignment表有多条记录，按照该项目分配员工的开始日期（date_assigned）升序排列，每个员工当月的开始日期（date_assigned）早于或等于上个员工的结束日期（date_ended）+1天，则项目未中断进行；

3. 该项目在assignment表有多条记录，按照该项目全部员工工作记录的开始日期（date_assigned）升序排列，每个员工当月的开始日期（date_assigned）晚于上个员工的结束日期（date_ended）+1天，则项目中断过。

根据以上分析进行编写SQL：

select
   temp_1.name
   ,min(temp_2.date_assigned) as proj_start_date
   ,min(case when temp_1.total_cost is not null then 'completed'
   else 'on-going' end) as proj_status
from (
  select
    t1.proj_number
    ,t1.name
    ,t2.date_assigned
    ,t2.date_ended
    ,t1.total_cost
    ,lag(t2.date_assigned ) over (partition by t1.proj_number order by t2.date_assigned asc) as date_assigned_1
    ,lag(t2.date_ended ) over (partition by t1.proj_number order by t2.date_assigned asc) as date_ended_1
    ,case when lag(t2.date_assigned ) over (partition by t1.proj_number order by t2.date_assigned asc) is null then 'continued'
      when lag(t2.date_ended ) over (partition by t1.proj_number order by t2.date_assigned asc) >= t2.date_assigned then 'continued'
      when lag(t2.date_ended ) over (partition by t1.proj_number order by t2.date_assigned asc) < date_add(t2.date_assigned,1) then 'discontinued' end as is_continue
  from project t1
  left join assignment t2 on t1.proj_number = t2.proj_number
) temp_1
left join assignment temp_2 on temp_2.proj_number = temp_1.proj_number
where temp_1.is_continue = 'discontinued'
group by temp_1.name;

执行以上结果，找出中断过的四个项目，结果如下：

name      proj_start_date proj_status
Atlantis  2019-10-11      completed
Bordeaux  2013-09-16      completed
Cheerio   2008-11-03      completed
Deepmind  2016-01-08      on-goin