Hive SQL进阶案例（一）：使用LAG函数判断日期连续性_hive sql lag_德瑞-Derry的博客

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

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

-- 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', '$10,000.90', 1, 2001, 9001)
,(7002, 'Astro',     '2021-01-01', '$20,020.87', 2, 2001, 9001)
,(7003, 'Atlantis',  '2019-12-10', '$30,000.65', 2, 2002, 9001)
,(7004, 'Barcelona', '2018-08-17', '$10,000.12', 2, 2003, 9002)
,(7005, 'Bender',    '2021-04-14', '$9,986.14', 2, 2003, 9002)
,(7006, 'Blue Moon', '2020-08-16', NULL, 3, 2004, 9003)
,(7007, 'Bordeaux',  '2013-09-16', '$105,000.67', 3, 2005, 9003)
,(7008, 'Camelot',   '2016-02-04', '$10,00.23', 3, 2006, 9004)
,(7009, 'Cheerio',   '2008-11-02', '$38,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）来判断，将会有以下几种情况：

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

一、累计计算窗口函数 sum(field) over(partition by field order by field asc/desc rows between … and …) avg(field) over(partition by field order by field asc/desc rows between … and …) max(field) over(partition by field order by field asc/desc rows between … and …)

create table t (qdate datetime,vcode varchar(50)); insert into t values('2013-06-01','A001'); insert into t values('2013-06-02','A001'); insert into t values('2013-06-02','B001'); insert into t values...

注意：聚合函数常与 SELECT 语句的 GROUP BY 子句一块儿使用。换句话说使用聚合函数时，一个列字段要不在group by里，要没必要须在聚合函数里面，不能单独出现，不然报错。不支持等值连接，一般使用left join、right join 或者inner join替代。 2、分号字符分号是sql语句的结束符号，在hive中也是，但是hive对分号的识别没有那么智能，有时需要进行转义 “；” --> “\073”sql中null代表空值，但是在Hive中，String类型的字段若是空（empt

OVER(PARTITION BY order by)函数的其它用法，可查看博文https://blog.csdn.net/qq_41712271/article/details/109224538创建表和数据 cookie1,2015-04-10 10:00:02,url2 cookie1,2015-04-10 10:00:00,url1 cookie1,2015-04-10 10:03:04,1url3 cookie1,2015-04-10 10:50:05,url6 cookie1,2015-0.

Spark SQL和Hive中的字符串函数包括很多常用的函数，如字符串拼接、子串截取、字符串替换、大小写转换等。这些函数可以帮助我们更方便地处理字符串类型的数据。例如，我们可以使用concat函数将多个字符串拼接在一起： SELECT concat('hello', ' ', 'world') as result; 输出结果为： +-------------+ | result | +-------------+ | hello world | +-------------+ 另外，我们还可以使用substring函数截取字符串的子串： SELECT substring('hello world', 1, 5) as result; 输出结果为： +-------+ | result| +-------+ | hello | +-------+ 除此之外，还有很多其他的字符串函数，可以根据具体的需求进行选择和使用。